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.
