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

On Tue, Feb 1, 2011 at 8:13 PM, Peter Geoghegan
<peter(dot)geoghegan86(at)gmail(dot)com> wrote:
> 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 know these things. I'm pretty sure it's even in the docs by now.

> I'm not sure why you'd advocate CLUSTER as a way to reclaim disk space.

Because it can reclaim disk space?

http://www.postgresql.org/docs/8.4/static/sql-cluster.html

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

That makes no sense whatsoever. You decrease fill factor (not
increase btw) so there will be some space for future updates. If he's
getting bloat it may well help quite a bit to have a lower than 100%
fill factor.