X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 33CA656828 for ; Thu, 17 Mar 2005 23:15:46 +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 15136-05 for ; Thu, 17 Mar 2005 23:15:37 +0000 (GMT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id 87E4856624 for ; Thu, 17 Mar 2005 23:15:36 +0000 (GMT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 7F4391038E for ; Thu, 17 Mar 2005 15:15:38 -0800 (PST) From: Steve Crawford Organization: Pinpoint Research To: Subject: Excessive growth of pg_attribute and other system tables Date: Thu, 17 Mar 2005 15:15:38 -0800 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503171515.38214.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.104 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/277 X-Sequence-Number: 16775 I'm having trouble with physical growth of postgresql system tables. Server is 7.4.6 and there are several databases in the cluster. The autovacuum daemon has been running since the data was restored after an upgrade a few months ago. Unfortunately my system tables are taking an unreasonable amount of space. For example, on one of the databases pg_attribute holds fewer than 10,000 records but is using more than 600 megabytes and the associated indexes are huge, too. Reindexing dropped the total usage for that database from 3.2G to 2.5G and a vacuum full (when I can do it off hours) will probably drop it to around 1.9G. In other words, one system table alone was accounting for around 40% of the storage used by that database. Now that 1.9G still includes other oversized files like pg_index for which the table alone dropped from 48M to 78K with vacuum full. Vacuum full + index on a selection of other tables yielded savings of: pg_depend: 200M pg_type: 120M pg_class: 50M My autovacuum config is running and I do see regular periodic vacuums of these pg_ tables but still they grow. Any ideas on why, in spite of autovacuum, these files are becoming so huge and, more importantly, the best way to keep them under control. Cheers, Steve