From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Index bloat of 4x
Date: Wed, 17 Jan 2007 21:12:43 +0100
Message-ID: <45AE833B.60407@kaltenbrunner.cc> (raw)
In-Reply-To: <24871.1169052974@sss.pgh.pa.us>
References: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
<20070117154251.GG26080@alvh.no-ip.org>
<20070117113216.a6d72185.wmoran@collaborativefusion.com>
<24871.1169052974@sss.pgh.pa.us>
Tom Lane wrote:
> Bill Moran <wmoran@collaborativefusion.com> writes:
>> The entire database was around 28M prior to the upgrades, etc. Immediately
>> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
>> 165M. Following a database-wide reindex, it dropped to 30M.
>
> As Alvaro said, vacuum full doesn't shrink indexes but in fact bloats them.
> (Worst case, they could double in size, if the vacuum moves every row;
> there's an intermediate state where there have to be index entries for
> both old and new copies of each moved row, to ensure things are
> consistent if the vacuum crashes right there.)
>
> So the above doesn't sound too unlikely. Perhaps we should recommend
> vac full + reindex as standard cleanup procedure. Longer term, maybe
> teach vac full to do an automatic reindex if it's moved more than X% of
> the rows. Or forget the current vac full implementation entirely, and
> go over to something acting more like CLUSTER ...
we have to recommend the CLUSTER "way" to fix overly bloated databases
quite often to people on IRC because vacuum full is unreasonably slow on
highly fragmented databases.
Doing something like that internally for vacuum full sounds like a
reasonable idea except for the additional disk usage during the process
which might cause issues for people ...
Stefan
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 using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: stefan@kaltenbrunner.cc, tgl@sss.pgh.pa.us, wmoran@collaborativefusion.com, alvherre@commandprompt.com
Subject: Re: Index bloat of 4x
In-Reply-To: <45AE833B.60407@kaltenbrunner.cc>
* 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