Re: Why does my DB size differ between Production and DR? (Postgres 8.4)

peter(dot)geoghegan86(at)gmail(dot)com (Peter Geoghegan) writes:
> On 1 February 2011 03:52, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> You can reclaim that space by doing a cluster or vacuum full on the
>> subject table.
>
> Yes, but this is a fairly bad idea, particularly prior to PG 9.0 . 9.0
> has a new vacuum full implementation that makes it not so bad - it
> just rewrites the entire table.
>
> VACUUM FULL will take exclusive locks on tables being vacuumed. It
> also causes index bloat. You should be very careful about using it on
> a production system.
>
> I'm not sure why you'd advocate CLUSTER as a way to reclaim disk space.

Because it works pretty well; it reorganizes the table on the basis of
the order indicated by one index, and simultaneously:
a) Shortens the table, removing all dead space;
b) Regenerates all indices, so they too have no dead space.

Traditional VACUUM FULL tends to worsen the dead space problem on
indices, so adds the "insult to injury" problem that after running
VACUUM FULL, you might need to reindex, and that aftermath is nearly as
expensive as CLUSTER.

CLUSTER is likely to be quicker than VACUUM FULL, and it gives nice,
squeaky-tight indexes.

The new form of VACUUM FULL in 9.0 changes things, but it wasn't obvious
that the original poster was on 9.0.

> I wouldn't increase index fill factor as an optimisation, unless you
> had the unusual situation of having very static data in the table.

--
output = reverse("gro.mca" "@" "enworbbc")
http://linuxfinances.info/info/wp.html
"The world needs more people like us and fewer like them." -- Unknown