X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8AF279DC8DA for ; Thu, 23 Mar 2006 22:31:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84778-01 for ; Thu, 23 Mar 2006 22:31:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx1.surnet.cl (mx1.surnet.cl [216.155.73.180]) by postgresql.org (Postfix) with ESMTP id BAECF9DC803 for ; Thu, 23 Mar 2006 22:31:01 -0400 (AST) Received: from unknown (HELO cluster.surnet.cl) ([216.155.73.165]) by mx1.surnet.cl with ESMTP; 23 Mar 2006 22:31:05 -0400 X-IronPort-AV: i="4.03,124,1141614000"; d="scan'208"; a="48515612:sNHT18523648" Received: from alvh.no-ip.org (201.221.200.224) by cluster.surnet.cl (7.0.043) (authenticated as alvherre@surnet.cl) id 43A978FD00BC3A32; Thu, 23 Mar 2006 22:31:05 -0400 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 2B54EC3A2D0; Thu, 23 Mar 2006 22:31:05 -0400 (CLT) Date: Thu, 23 Mar 2006 22:31:05 -0400 From: Alvaro Herrera To: Gregory Maxwell Cc: pgsql-admin@postgresql.org Subject: Re: Bloated pg_shdepend_depender_index Message-ID: <20060324023105.GB11242@surnet.cl> Mail-Followup-To: Gregory Maxwell , pgsql-admin@postgresql.org References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.11+cvs20060126 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.579 required=5 tests=[AWL=-0.340, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.579 X-Spam-Level: * X-Archive-Number: 200603/225 X-Sequence-Number: 20982 Gregory Maxwell wrote: > I recently noticed that this database has grown to a huge size. ... > Which I found to be somewhat odd because none of the tables have more > than around 1000 rows. I hadn't been vacuuming because I didn't > think that anything would ever be deleted.... so I performed a vacuum > full... but no luck, it was still about 6.4GB. > > With some help of the folks on IRC I discovered... > postgres=# select relname, pg_relation_size(oid) FROM pg_class ORDER > BY 2 DESC LIMIT 2; > relname | pg_relation_size > -----------------------------+------------------ > pg_shdepend_depender_index | 159465472 > pg_shdepend_reference_index | 97271808 > (2 rows) Huh, that's very strange. The pg_shdepend table is a shared catalog, unlike most other catalogs. Still I don't see why would the indexes not shrink. Do pg_authid, pg_database, or pg_tablespace show a similar behavior? Is amarok creating a lot of temp tables or something? When you did that vacuum full, was there a process connected to the same or another database that could be holding onto the pg_shdepend table? I'll have a look at the code, but I thought I might throw that out, just in case it rings a bell. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support