Database Reference
In-Depth Information
Yes, that's right. We just updated the core internal catalog tables of PostgreSQL. So make a
mistake here, and you're in a big world of hurt. Make sure your backups are nicely polished
before doing this.
How it works...
CREATE
INDEX
CONCURRENTLY
allows inserts, updates, and deletes while the index is being
created. It cannot be executed inside another transaction, and only one per table can be
created at any time.
Swapping the indexes is easy and doesn't use any trickery.
Swapping the primary keys used some internals knowledge. The indexes themselves don't know
which numbers they are, so you can swap them over without problems—as long as you swap
the correct two indexes, and they really do have identical definitions. Be especially careful about
creating the indexes in the same tablespace, as the above will fail if they're different.
There's more...
If you are fairly new to database systems, you might think rebuilding indexes for performance
is something that only PostgreSQL needs to do. Other DBMS require this also, just maybe
don't say so.
Indexes are designed for performance, and in all databases, deleting index entries causes
contention and loss of performance. PostgreSQL does not remove index entries for a row
when that row is deleted, so an index can fill with dead entries. PostgreSQL does attempt to
remove dead entries when a block becomes full, though that doesn't stop small numbers of
dead entries accumulating in many data blocks.
See also
I'm writing this just as PostgreSQL 9.0 is coming out. Its likely that in later versions, we will get
a simple
REINDEX
CONCURRENTLY
command that can be used more easily.
Finding the unused indexes
Selecting the correct set of indexes for a workload is known to be a hard problem. It usually
involves trial and error by developers and DBAs to get a good mix of indexes.
Tools exist to identify slow queries and many
SELECT
statements can be improved by the
addition of an index.
What many people forget is to check whether the mix of indexes remains valuable over time,
which is something for the DBA to investigate and optimize.
