Re: Autovacuum in the backend

Alvaro Herrera <alvherre(at)surnet(dot)cl> writes:
> Now, I'm hearing people don't like using libpq.

Yeah --- a libpq-based solution is not what I think of as integrated at
all, because it cannot do anything that couldn't be done by the existing
external autovacuum process. About all you can buy there is having the
postmaster spawn the autovacuum process, which is slightly more
convenient to use but doesn't buy any real new functionality.

> Some people say "keep it simple and have one process per cluster." I
> think they don't realize it's actually more complex, not the other way
> around.

Agreed. If you aren't connected to a specific database, then you cannot
use any of the normal backend infrastructure for catalog access, which
is pretty much a killer limitation.

A simple approach would be a persistent autovac background process for
each database, but I don't think that's likely to be acceptable because
of the amount of resources tied up (PGPROC slots, open files, etc).

One thing that might work is to have the postmaster spawn an autovac
process every so often. The first thing the autovac child does is pick
up the current statistics dump file (which it can find without being
connected to any particular database). It looks through that to
determine which database is most in need of work, then connects to that
database and does some "reasonable" amount of work there, and finally
quits. Awhile later the postmaster spawns another autovac process that
can connect to a different database and do work there.

This design would mean that the autovac process could not have any
long-term state of its own: any long-term state would have to be in
either system catalogs or the statistics. But I don't see that as
a bad thing really --- exposing the state will be helpful from a
debugging and administrative standpoint.

regards, tom lane