From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-admin@postgresql.org, Gregory Maxwell <gmaxwell@gmail.com>
Subject: Re: Bloated pg_shdepend_depender_index
Date: Mon, 10 Apr 2006 14:35:01 -0400 (EDT)
Message-ID: <200604101835.k3AIZ1s07060@candle.pha.pa.us> (raw)
In-Reply-To: <4553.1143212521@sss.pgh.pa.us>
TODO already has:
* Improve speed with indexes
For large table adjustments during VACUUM FULL, it is faster to
reindex rather than update the index.
---------------------------------------------------------------------------
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: pgman@candle.pha.pa.us, tgl@sss.pgh.pa.us, peter_e@gmx.net, gmaxwell@gmail.com
Subject: Re: Bloated pg_shdepend_depender_index
In-Reply-To: <200604101835.k3AIZ1s07060@candle.pha.pa.us>
References: <4553.1143212521@sss.pgh.pa.us> <200604101835.k3AIZ1s07060@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox