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 F01CC52A5D for ; Fri, 25 Mar 2005 23:31:37 +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 00928-07 for ; Fri, 25 Mar 2005 23:31:35 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2D14653476 for ; Fri, 25 Mar 2005 23:31:33 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id C304830959; Sat, 26 Mar 2005 00:31:36 +0100 (MET) From: "Otto Blomqvist" X-Newsgroups: pgsql.performance Subject: Re: pg_autovacuum not having enough suction ? Date: Fri, 25 Mar 2005 15:34:00 -0800 Organization: Hub.Org Networking Services Lines: 49 Message-ID: References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> <16054.1111791962@sss.pgh.pa.us> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.972 tagged_above=0 required=5 tests=AWL, PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200503/574 X-Sequence-Number: 11279 ok, Thanks a lot for your time guys ! I guess my table is pretty unusual and thats why this problem has not surfaced until now. Better late then never ;) I'll cron a "manual" vacuum full on the table. "Tom Lane" wrote in message news:16054.1111791962@sss.pgh.pa.us... > > 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 majordomo@postgresql.org) >