Re: pg_autovacuum not having enough suction ?

Tom Lane wrote:
> > Otto Blomqvist wrote:
> >> This table is basically a queue full of records waiting to get transfered
> >> over from our 68030 system to the PG database. The records are then moved
> >> into folders (using a trigger) like file_92_myy depending on what month the
> >> record was created on the 68030. During normal operations there should not
> >> be more than 10 records at a time in the table, although during the course
> >> of a day a normal system will get about 50k records. I create 50000 records
> >> to simulate incoming traffic, since we don't have much traffic in the test
> >> lab.
>
> Really the right way to do housekeeping for a table like that is to
> VACUUM FULL (or better yet, TRUNCATE, if possible) immediately after
> discarding a batch of records. The VACUUM FULL will take very little
> time if it only has to repack <10 records. Plain VACUUM is likely to
> leave the table nearly empty but physically sizable, which is bad news
> from a statistical point of view: as the table fills up again, it won't
> get physically larger, thereby giving the planner no clue that it
> doesn't still have <10 records. This means the queries that process
> the 50K-record patch are going to get horrible plans :-(
>
> I'm not sure if autovacuum could be taught to do that --- it could
> perhaps launch a vacuum as soon as it notices a large fraction of the
> table got deleted, but do we really want to authorize it to launch
> VACUUM FULL? It'd be better to issue the vacuum synchronously
> as part of the batch updating script, I feel.

I added this to the TODO section for autovacuum:

o Do VACUUM FULL if table is nearly empty?

I don't think autovacuum is every going to be smart enough to recycle
during the delete, especially since the rows can't be reused until the
transaction completes.

One problem with VACUUM FULL would be autovacuum waiting for an
exclusive lock on the table. Anyway, it is documented now as a possible
issue.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073