On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
> In addition to making sure databases are vacuumed regularly, it is worth
> running REINDEX on tables that see a lot of updates (or insert/deletes).
>
> Running REINDEX on a regular basis will keep the indexes compacted and
> can noticeably improve the database performance.
>
> The other benefit is that the disk space taken by your database can be
> significantly reduced.
>
> This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.
>
> FWIW, in my experience it is DEFINITELY worth reindexing regularly.
But note that reindex is one of those "invasive" commands that may cause
problems for certain types of 24/7 operations, while vacuum is meant to
run concurrently almost any time of day. Reindex was originally
designed to fix broken indexes, and, at least in earlier encarnations,
should something stop it in the middle of reindexing I believe it is
possible to be left with no index.
It's got its uses, but it's got its issues as well.