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 E4FC99FA60C for ; Sun, 23 Jul 2006 12:53:47 -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 21951-07 for ; Sun, 23 Jul 2006 15:53:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 6EEEB9FA5DD for ; Sun, 23 Jul 2006 12:53:40 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k6NFrdRX043329 for ; Sun, 23 Jul 2006 12:53:39 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k6NFf8EY042267 for pgsql-admin@postgresql.org; Sun, 23 Jul 2006 12:41:08 -0300 (ADT) (envelope-from news) From: Christopher Browne X-Newsgroups: pgsql.admin Subject: Re: Unexplained growth of tables Date: Sun, 23 Jul 2006 11:09:28 -0400 Organization: cbbrowne Computing Inc Lines: 27 Message-ID: <873bcstl1z.fsf@wolfe.cbbrowne.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux) Cancel-Lock: sha1:ZwEMro6ulQ73JAZY2c2LcUfN+4M= To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.273 tagged_above=0 required=5 tests=INFO_TLD X-Spam-Level: * X-Archive-Number: 200607/180 X-Sequence-Number: 22426 After a long battle with technology, kraj@illumen.com ("Benjamin Krajmalnik"), an earthling, wrote: > Any assistance or guidance will be deeply appreciated. I am pulling > hairs on this one. It needs to be vacuumed a lot more aggressively. If you're using pg_autovacuum, the defaults seem to be not quite aggressive enough. Mind you, now that the table has grown to huge size, it won't be cut down to size by a mere VACUUM ANALYZE. To draw it back down to size, you need to run VACUUM FULL, which will cut the number of tuples down to size, and then reindex the table, which will "do a number" on the indices. Alternatively, "CLUSTER [some index] on [table];" will reorganize the whole thing at once, reshaping the table to conform with the specified index, and regenerating all indices, at the cost that it (at present, up to 8.1; I'm not sure about 8.2) doesn't follow MVCC and keep old, but still-visible obsolete copies of tuples. CLUSTER is the fast answer, assuming you haven't any old transactions you need to keep in flight... -- (format nil "~S@~S" "cbbrowne" "gmail.com") http://linuxfinances.info/info/slony.html The difference between a child and a hacker is the amount he flames about his toys. -- Ed Schwalenberg