Sluggish PostgreSQL Databases and Reindexing Indexes

The natural process of erosion affects not only the organic elements of the Earth, but also the hardware we use, the code we write, and the databases we keep. When it comes to maintaining databases, there are several things that can be done to fight the degradation in query run time, and the increase in disk usage. There is one such action that is especially useful in PostgreSQL DBs and this is the command REINDEX INDEX. This command works on a single index and rebuilds it from scratch. Along the way it frees up any unnecessary disk space used by the index, and improves the response time of all queries using the index.

How much can this help? The answer is: a lot. In one example I witnessed reindexing an index improved the run time of a query using that index from several minutes to a few seconds. In another example, reindexing all of the indexes in a DB reduced the disk space used by that DB by over 40%.

So how can it be done? Simple:
REINDEX INDEX <index_name>;

Now assume that you want to reindex all of the indexes related to a list of tables in your database, all you need to do is create a script that extracts the names of the indexes in your DB that are related to these tables and then run the REINDEX command on each of them. Here is an example bash script that does just that:

#!/bin/bash

database=mydb
indexes=$(psql -q -t -A -P pager=off -c ”
select
c.relname
from
pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid        = c.relnamespace
join pg_catalog.pg_index i     on i.indexrelid = c.oid
join pg_catalog.pg_class t     on i.indrelid   = t.oid
where
c.relkind = ‘i’
and n.nspname not in (‘pg_catalog’, ‘pg_toast’)
and pg_catalog.pg_table_is_visible(c.oid)
and t.relname in (‘table1’, ‘table2’, ‘table3’)” $database)

for index in $indexes
do
echo `date “+%y-%m-%d %H:%M:%S”`” – Reindexing index $index”
psql -c “REINDEX INDEX $index” $database
done

echo `date “+%y-%m-%d %H:%M:%S”`” – Done”