"Jim C. Nasby" <jim@xxxxxxxxx> 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
