> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])