Subject: Section "Routine Reindexing"

From: Peter Eisentraut <[email-masked]>
Date: Mon, 3 Nov 2003 20:49:02 +0100 (CET)

The section "Routine Reindexing" (21.2) has been reduced to:

"""
In some situations it is worthwhile to rebuild indexes periodically with
the REINDEX command. (There is also contrib/reindexdb which can reindex an
entire database.) However, PostgreSQL 7.4 has substantially reduced the
need for this activity compared to earlier releases.
"""

Can someone add something that is of substantial value and less hand-wavy?

-- 
Peter Eisentraut   [email-masked]

---

From: Josh Berkus <[email-masked]>
Date: Mon, 3 Nov 2003 12:13:15 -0800

Peter,

> need for this activity compared to earlier releases.

Is this for the main docs, or for the Annotated release notes?

If for the main docs:

<para>
PostgreSQL 7.4 manages deleted nodes from indexes on tables with many deletes 
and/or updates using the Free Space Map (FSM) as it does for the tables 
themselves (this is a new feature).  If your max_fsm_pages setting in 
postgresql.conf is correctly set and you vacuum regularly, there should be no 
need for reindexing for most databases most of the time.
</para>
<para>
If your settings are not correct or if your database usage pattern is one that 
FSM and vaccum cannot cope with, such as huge batch updates, then you will 
need to reindex some or all of your indexes on the updated fields 
periodically.  You can determine this by watching for sluggish index response 
times and index growth out of proportion to the growth of the underlying 
table.  In order to reindex an entire database, see the contrib/reindexdb 
module in the PostgreSQL source code.  Please note that reindex exclusively 
locks each table while processing.
</para>

-- 
Josh Berkus
Aglio Database Solutions
San Francisco