Re: index bloat on partial index 8.4

On Tue, Oct 4, 2011 at 12:28 PM, Kevin Ricords <kevin(at)silverback(dot)com> wrote:
> Thank you for your response.  I believe I misunderstood "reclaimed for
> re-use".  Using contrib/pg_freespace, I see empty index pages can be reused
> elsewhere in the same index, but are not deallocated.  To keep my index at
> the size I expected, I will have to vacuum more frequently or
> reindex/rebuild regularly.

You might need to adjust the various vacuum_* settings to be able to
vacuum aggressively enough to keep with your high update rate. Lower
vacuum_cost_delay and / or autovacuum_vacuum_cost_delay to 1 to 5 ms,
or 0 if you've got an impressive enough IO subsystem. Increate
vacuum_cost_limit to 2 to 10 times what the default is as well. then
keep a close eye on how busy your IO subsystem gets during peak load.
If it goes up too high (stays at 100% for extended periods). then turn
delay back up / cost back down.

As Greg Smith often points out, the solution to autovacuum problems is
usually to make autovacuum more aggressive not less so.