Excerpts from Greg Smith's message of lun jun 07 12:23:44 -0400 2010:
> It is a periodic preventative maintenance operation you can expect to
> need occasionally, but certainly not often. Indexes maintain themselves
> just fine most of the time. They can get out of whack if you delete a
> lot of data out of them and there are some use patterns that tend to a
> aggravate the problems here (like tables where you're always inserting
> new data and deleting old), but it's certainly not something you run all
> the time.
Indexes on which you always insert new data and delete old can keep
themselves in good shape too. The really problematic cases are those in
which you delete new data and delete most, but not all, old data. Those
cases would result in almost empty pages that can never be recycled (we
do not have btree page merging).
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support