X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 8E5339FA64B for ; Sun, 23 Jul 2006 14:48:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 74105-02 for ; Sun, 23 Jul 2006 17:48:27 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id BC5A49FA47F for ; Sun, 23 Jul 2006 14:48:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k6NHmLMa003581; Sun, 23 Jul 2006 13:48:21 -0400 (EDT) To: Peter Eisentraut cc: pgsql-admin@postgresql.org, "Benjamin Krajmalnik" Subject: Re: Unexplained growth of tables In-reply-to: <200607231319.29697.peter_e@gmx.net> References: <200607231319.29697.peter_e@gmx.net> Comments: In-reply-to Peter Eisentraut message dated "Sun, 23 Jul 2006 13:19:28 +0200" Date: Sun, 23 Jul 2006 13:48:21 -0400 Message-ID: <3580.1153676901@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/181 X-Sequence-Number: 22427 Peter Eisentraut writes: > Benjamin Krajmalnik wrote: >> What can be causing this growth? Not vacuuming often enough? > The is exactly the reason. >> I hav >> pg_autovacuum running every 60 seconds. These tables have 10-15 >> insert/update statements per second. > You should probably run VACUUM FULL to get the table back to a normal > size and then closely monitor what pg_autovacuum actually does. CLUSTER might be a better answer, since VACUUM FULL won't do anything to help shrink the indexes. As for the pg_autovacuum frequency, realize that pg_autovacuum processes one database per firing. So if you have N databases, any one database is going to be looked at every N*60 seconds, not every 60 seconds. You might need a shorter autovacuum cycle. Lastly, what PG version is this? We had some bugs that kept autovacuum from being fully informed in some cases, but AFAIK they're all fixed in the latest minor releases. regards, tom lane