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 A275F9DC890 for ; Fri, 24 Mar 2006 11:02:01 -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 33973-06 for ; Fri, 24 Mar 2006 11:02:05 -0400 (AST) 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 1F5CE9DC813 for ; Fri, 24 Mar 2006 11:01:58 -0400 (AST) 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 k2OF21wk004554; Fri, 24 Mar 2006 10:02:01 -0500 (EST) To: Peter Eisentraut cc: pgsql-admin@postgresql.org, "Gregory Maxwell" Subject: Re: Bloated pg_shdepend_depender_index In-reply-to: <200603241150.20021.peter_e@gmx.net> References: <27588.1143175698@sss.pgh.pa.us> <200603241150.20021.peter_e@gmx.net> Comments: In-reply-to Peter Eisentraut message dated "Fri, 24 Mar 2006 11:50:19 +0100" Date: Fri, 24 Mar 2006 10:02:01 -0500 Message-ID: <4553.1143212521@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/235 X-Sequence-Number: 20992 Peter Eisentraut writes: > Am Freitag, 24. März 2006 05:48 schrieb Tom Lane: >> Well, the VACUUM FULL algorithm is incapable of shrinking indexes --- >> the only way is REINDEX, or something else that reconstructs indexes >> from scratch, such as CLUSTER. One of the things we need to look into >> is putting more smarts into VACUUM so that it automatically does >> something reasonable when faced with extreme cases like these. > If the user is running VACUUM FULL, he has presumably determined that the > table is too bloated to be recovered in a graceful way, and quite likely the > indexes are going to be bloated similarly. So seemingly one might as well > launch a reindexing on the table after VACUUM FULL has done its thing. > Whether that should be automatic is another question but perhaps the advice > should be documented somewhere? Actually, I wonder whether VACUUM FULL shouldn't be thrown away and replaced by something else entirely. That algorithm only really works nicely when just a small percentage of the rows need to be moved to re-compact the table --- if you're moving lots of rows, it makes the index bloat situation *worse* not better because of the transient need for index entries pointing to both copies of moved rows. Lazy VACUUM has become the de-facto standard for situations where there's not a huge amount of empty space, and so it's not clear where the sweet spot is for VACUUM FULL anymore. If you've got enough disk space, a rewrite (like CLUSTER or ALTER TABLE) is going to blow the doors off VACUUM FULL, let alone VACUUM FULL plus REINDEX. Not to mention that for sufficiently huge tables, VACUUM FULL fails outright because it runs out of RAM. We need to fix CLUSTER to make it MVCC-safe (ie, not discard recently-dead rows), and it'd be nice to have something like it that didn't worry about ordering but just did a seqscan of the source table. Then I'd be inclined to recommend that instead of VACUUM FULL for most cases of severe bloat. Unfortunately this all breaks down for shared system catalogs and the core (nailed-in) catalogs, because we can't change their relfilenodes and so the crash-safe CLUSTER/REINDEX approach doesn't work. We still need a new idea or two there. regards, tom lane