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

"Vivek Khera" <vivek(at)khera(dot)org> writes:

> On Sep 24, 2007, at 12:00 PM, Phoenix Kiula wrote:
>
>> I feel your pain. But I seem to have (mostly) solved my problem in three
>> ways:
>
> My particular usage pattern (add data continuously, purge *some* of the data
> once per week or every other week. The purge is what seems to kill it. Last
> time I reindexed, I reclaimed over 20Gb of disk space. That was after 6
> months from the prior reindex.

Do you have a pattern of loading a ton of data covering a range of indexed key
values and then deleting all but a few values spread evenly throughout that
range? And then never inserting new key values in those ranges again? For
example loading records indexed by timestamp and then deleting all but the
first record of the day.

That kind of pattern does need a regular reindex because the index pages will
have those few values left on them preventing them from being reused.

If that's not your usage pattern then perhaps you should describe your usage
pattern in more detail. But I suspect you would be best served by simply
vacuuming much more often.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com