Database Reference
In-Depth Information
Note also that the only way to know for certain the exact bloat of a table or index is to scan the
whole relation. Anything else is just an estimate, and might lead to you running maintenance
either too early or too late.
Maintaining indexes
Indexes can become a problem in many database applications that involve a high proportion
of inserts/deletes. Just as tables can become bloated, so do indexes.
In the last recipe we saw that non-HOT updates can cause bloated indexes. Non-primary
key indexes are also prone to some bloat from normal inserts, as is common in most
relational databases.
Autovacuum does not detect bloated indexes, nor does it do anything to rebuild indexes.
So we need to look at ways to maintain indexes.
Getting ready
PostgreSQL supports commands that will rebuild indexes for you. The client utility reindexdb
allows you to execute the
REINDEX
command in a convenient way from the operating system:
$ reindexdb
This executes the SQL
REINDEX
command on every table in the default database. If you want
to reindex all databases, then use the following:
$ reindexdb -a
That's what the manual says anyway. My experience is that most indexes don't need
rebuilding, and even if they do, REINDEX puts a full table lock (AccessExclusiveLock) on the
table while it runs. That locks up your database for possibly hours, and I advise that you think
about
not
doing that.
Try this recipe
instead
.
First, let's create a test table with two indexes: a primary key and an additional index as follows:
F
DROP TABLE IF EXISTS test;
F
CREATE TABLE test
F
(id INTEGER PRIMARY KEY
F
,category TEXT
F
, value TEXT);
F
CREATE INDEX ON test (category);
