From: Alvaro Herrera <alvherre@commandprompt.com>
To: Peter Koczan <pjkoczan@gmail.com>
Cc: Gabriele Bartolini <g.bartolini@comune.prato.it>
Cc: pgsql-admin@postgresql.org
Subject: Re: VACUUM FULL ANALYSE hanging
Date: Wed, 2 May 2007 11:50:20 -0400
Message-ID: <20070502155020.GQ4585@alvh.no-ip.org> (raw)
In-Reply-To: <4638AA6E.2090506@gmail.com>
References: <910CF843580B3C40A25CD0D04B3908E213050E@exchange4.comune.prato.local>
<4638AA6E.2090506@gmail.com>
Peter Koczan escribió:
> I've noticed in my own experiments and experiences with VACUUM FULL that
> it tries to reindex all the indexes to compress them. While a good idea,
> this unfortunately takes a *long* time.
Huh, this is not an accurate description of what happens. VACUUM FULL
tries to keep indexes up to date, but it doesn't *reindex* them (like a
REINDEX command would do).
There was another thread about VACUUM FULL in which Tom Lane described
exactly what happened to indexes.
> In my experiences, doing a dump/restore was far faster, but this method
> creates downtime. (e.g. a 10 GB database took 2 hours to restore, while
> reindexing/vacuuming full was still on the same table after 4 hours).
It has been reported that CLUSTER is also faster than VACUUM FULL, and
it leaves the indexes more compact than VACUUM FULL to boot. So it may
be the best option. It is also easier to do than dump/restore.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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-admin@postgresql.org
Cc: alvherre@commandprompt.com, pjkoczan@gmail.com, g.bartolini@comune.prato.it
Subject: Re: VACUUM FULL ANALYSE hanging
In-Reply-To: <20070502155020.GQ4585@alvh.no-ip.org>
* 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