Re: VACUUM/VACUUM FULL/REINDEX

Sam Barnett-Cormack <s(dot)barnett-cormack(at)lancaster(dot)ac(dot)uk> writes:
> I'm a little confused about comments I've seen on this list - a VACUUM
> is not enough, clearly, to maintain a trim, efficient DB. People have
> mentioned that REINDEX is also needed periodically.

If you have an index whose range of values continually shifts (for
example, a timestamp or serial-number column) then you will probably
need to REINDEX it every so often to reclaim empty space in the index.
This is at least partially fixed for 7.4, but it's necessary in all
prior versions. The problem is that VACUUM had no mechanism to reclaim
btree pages that become totally empty because there's no longer any data
in the range of key values they represent.

When the indexed column's statistics aren't changing much over time,
I wouldn't think you'd need to do routine reindexing --- just keeping
after the table with VACUUM ought to be enough.

regards, tom lane