Re: Postgresql backend to perform vacuum automatically

Tom Lane wrote:
>
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Nicolas Bazin wrote:
> > From what I read in the recent exchanges in the PostgreSQL vs ORACLE thread it would seem a good idea for the backend to keep track of the number of update performed on a database and after a certain threshold start a vacuum in a separate process by itself.
> >> Any comments?
>
> > Yes, makes sense to me, especially now that we have a nolocking vacuum.
> > Tom, do you have any ideas on this?
>
> There's a TODO item about this already.
>
> * Provide automatic scheduling of background vacuum (Tom)

I have been thinking about this. I like the idea, but it may be problematic.

I suggested running a vacuum process on a constant low priority in the
background, and it was pointed out that this may cause some deadlock issues.

For vacuum to run in the background, it needs to be more regulated or targeted.

It needs to be able to know which tables need it. Many tables are static and
never get updated, vacuuming them would be pointless. It needs to be sensitive
to database load, and be tunable to vacuum only when safe or necessary to
reduce load.

Are there tables that track information that would be useful for guiding
vacuum? Could I write a program which queries some statistical tables and and
knows which tables need to be vacuumed?

If the info is around, I could whip up something pretty easily, I think.