Thread: Section "Routine Reindexing"

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   peter_e@gmx.net

Peter,

> """
> 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.
> """

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