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(at)acm(dot)org> wrote:
> peter(dot)geoghegan86(at)gmail(dot)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.