X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 80A7B53488 for ; Fri, 25 Mar 2005 23:06:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96172-03 for ; Fri, 25 Mar 2005 23:06:02 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id AB36953486 for ; Fri, 25 Mar 2005 23:06:00 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2PN63gv016055; Fri, 25 Mar 2005 18:06:03 -0500 (EST) To: "Matthew T. O'Connor" Cc: Otto Blomqvist , "pgsql-performance@postgresql.org" Subject: Re: pg_autovacuum not having enough suction ? In-reply-to: <42435408.30303@zeut.net> References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> Comments: In-reply-to "Matthew T. O'Connor" message dated "Thu, 24 Mar 2005 18:58:00 -0500" Date: Fri, 25 Mar 2005 18:06:02 -0500 Message-ID: <16054.1111791962@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/569 X-Sequence-Number: 11274 > 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