Re: index growth problem

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
>> When I run the same command to find the size after the VACUUM, it hasn't
>> changed.

> That's not really a useful test to see if VACUUM is working. VACUUM can
> only trim space off the end of a relation (index or table), where by
> 'end' I mean the end of the last file for that relation on the
> filesystem. This means it's pretty rare for VACUUM to actually shrink
> files on-disk for tables. This can be even more difficult for indexes (I
> think it's virtually impossible to shrink a B-tree index file).

Right; IIRC, a plain VACUUM doesn't even try to shorten the physical
index file, because of locking considerations. The important question
is whether space gets recycled properly for re-use within the index.
If the index continues to grow over time, then you might have a problem
with insufficient FSM space (or not vacuuming often enough).

It might be worth pointing out that VACUUM isn't intended to try to
reduce the disk file to the shortest possible length --- the assumption
is that you are doing vacuuming on a regular basis and so the file
length should converge to a "steady state", wherein the internal free
space runs out about the time you do another VACUUM and reclaim some
more space for re-use. There's not really any point in being more
aggressive than that; we'd just create additional disk I/O when the
filesystem releases and later reassigns space to the file.

Of course, this argument fails in the scenario where you make a large
and permanent reduction in the amount of data in a table. There are
various hacks you can use to clean up in that case --- use TRUNCATE not
DELETE if you can, or consider using CLUSTER (not VACUUM FULL). Some
variants of ALTER TABLE will get rid of internal free space, too.

regards, tom lane