Re: Vacuum Daemon

Matthew T. O'Connor wrote:
> On Saturday 29 June 2002 08:14 pm, Tom Lane wrote:
> > Launching VACUUMs on some automatic schedule, preferably using feedback
> > about where space needs to be reclaimed, seems like a pretty
> > straightforward small-matter-of-programming. The thing that would
> > really be needed to make it unobtrusive is to find a way to run the
> > vacuum processing at low priority, or at least when the system is not
> > heavily loaded. I don't know a good way to do that. Nice'ing the
> > vacuum process won't work because of priority-inversion problems.
> > Making it suspend itself when load gets high might do; but how to
> > detect that in a reasonably portable fashion?
>
> Are we sure we want it to be unobtrusive? If vacuum is performed only where
> and when it's needed, it might be better for overall throughput to have it
> run even when the system is loaded. Such as a constantly updated table.
>
> As for a portable way to identify system load (if this is what we want) I was
> thinking of looking at the load average (such as the one reported by the top
> command) but I don't know much about portability issues.
>
> Since there appears to be sufficient interest in some solution, I'll start
> working on it. I would like to hear a quick description of what
> small-matter-of-programming means. Do you have specific ideas about what how
> best to get that feedback?

Another idea is that the statistics tables keep information on table
activity, so that could be used to determine what needs vacuuming.

As far as collecting info on which rows are expired, I think a table
scan is pretty quick and the cleanest solution to finding them. Trying
to track the exact tuples and when they aren't visible to anyone is just
a major pain, while with a table scan it is very easy.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026