On Monday, December 24, 2012, Richard Neill wrote:
Dear All,
I think periodic reindex may be the solution. Even after reducing the autovacuum fraction to 0.05, the index still seems to bloat.
Since your index is so tiny compared to the table, I'd lower it even more. 0.0001, maybe.
However, vacuums can't overlap a table, so you can't get the table to be vacuumed more often than the time it takes to run the vacuum (which took around 4 minutes at default vacuum cost settings in a toy system) and so you may need to lower the autovacuum_vacuum_cost_delay to 0 for this table. (I suspect it is all in cache anyway, so the default settings are too pessimistic.) Finally, you might need to lower autovacuum_naptime, because the usually table also won't be vacuumed any more often than that.
1. The documentation still suggests that reindex should not be needed in "normal" operation... is this true? Or are the docs wrong? Or have I got such an edge case?
Your case seems pretty far out there to me.
Cheers,
Jeff