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