From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: When will my database crash?
Date: Wed, 08 Sep 2004 00:41:56 -0400
Message-ID: <15658.1094618516@sss.pgh.pa.us> (raw)
In-Reply-To: <m3d60xxx54.fsf@wolfe.cbbrowne.com>
References: <20040908020214.M78932@eg-1.com>
<m3d60xxx54.fsf@wolfe.cbbrowne.com>
Christopher Browne <cbbrowne@acm.org> writes:
> Not vacuuming for four months was a severe error, which means that you
> now need to run a really full maintenance cycle. At the next possible
> opportunity, you really need to do a VACUUM FULL on the whole
> database.
Dump, initdb, reload would very likely be less painful ...
Something that hasn't ever been very clearly documented is that the
VACUUM FULL mechanism is designed for compacting out relatively small
amounts of dead space, like maybe 10-20%. When you've got a table
that's got so much dead space that VACUUM FULL is going to move nearly
all the live rows, the proposition is a loser. CLUSTER or dump/reload
will be better, because those approaches don't bother to try to maintain
the existing indexes incrementally: they throw away the indexes and
start from scratch.
regards, tom lane
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: tgl@sss.pgh.pa.us, cbbrowne@acm.org
Subject: Re: When will my database crash?
In-Reply-To: <15658.1094618516@sss.pgh.pa.us>
References: <20040908020214.M78932@eg-1.com> <m3d60xxx54.fsf@wolfe.cbbrowne.com> <15658.1094618516@sss.pgh.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