Re: pg_autovacuum not having enough suction ?

> 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.

regards, tom lane