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

On Wed, Feb 2, 2011 at 10:45 AM, Chris Browne <cbbrowne@acm.org> wrote:
> peter.geoghegan86@gmail.com (Peter Geoghegan) writes:
>> 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.

It's important at this point to set fill factor before the cluster if
something besides the default 100% makes sense.  any randomly updated
table full of small records will usually benefit from a fill fact even
as high as 95% which is very little "wasted" space for a gain in HOT
updates starting in 8.3.  HOT saved our bacon at work.  They really
lowered the requirements for disk access / index update a LOT.  I wish
I'd have saved the pg_stat_index from 8.1 versus 8.3.   And IO
numbers.  Our load dropped by a power of ten more or less.