Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

On 24/09/2007, Vivek Khera <vivek(at)khera(dot)org> wrote:
>
> my FSM is way bigger than I ever use (vacuum never reports shortage)
> and I still get bloat that needs to be purged out with a reindex on
> occasion.

Vivek,

I feel your pain. But I seem to have (mostly) solved my problem in three ways:

1. Increase the shared_buffer and effective_cache_size settings in
postgresql.conf. There are some websites that suggest that increasing
shared_buffer beyond 40,000 may in fact have counter-intuitive
results, but not in my case. I'm at 60,000 and it seems to work well.
Effective_cache_size is 512000.

2. Reduce the fill factor on your table. This is the single most
performance boost. On a table that is frequently updated on a TEXT
column, I reduced it to 60 and have never looked back. For others, I'm
experimenting with 80 and it seems to be working well.

3. Make your autovacuum settings as aggressive as can be. Basically I
found that doing a cronjob of vacuuming every five hours worked really
well, which suggested that autovacuum was not really kicking in as
often it was needed. So I reduced the threshold (100 for vacuum, 80
for analyze...i.e., the number of tuples that get updated before
either process kicks in) and reduced quite aggressively the scores.
Here are my settings:

autovacuum = on
autovacuum_vacuum_cost_delay = 10
vacuum_cost_delay = 10
autovacuum_naptime = 10
autovacuum_vacuum_threshold = 75
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_scale_factor = 0.01

Hope this helps some.

PK.