Reindexing PostgreSQL Databases with reindexdb utility

As a developer using a PostgreSQL RDS database instance on AWS, I needed to reindex my database on RDS to improve query performance. I logged into one of my EC2 instances and used the reindexdb utility to rebuild the indexes on my RDS database. In this post, I’ll explain how to use the key options and capabilities of reindexdb.

Overview

The reindexdb utility rebuilds indexes for a PostgreSQL database. It is located in the bin directory within the database installation. Reindexing is useful when database indexes have become bloated over time, degrading query performance. Reindexdb reconstructs the indexes to reclaim space and speed up queries.

Key Options

Here are some key options for reindexdb:

• -d – The name of the database to reindex
• -a – Reindex all databases
• -s – Reindex a specific schema
• -t – Reindex a specific table
• -i – Reindex a specific index
• -v – Verbose output shows reindex progress
• -w – Use .pgpass file for password authentication
• -j – Number of concurrent jobs for faster reindex
• -h – Database server host address
• -p – Server port number

Usage

Reindex the whole database

reindexdb -h db.example.com -p 5432 -U db_user -d mydb

Reindex a specific table

reindexdb -h db.example.com -p 5432 -U db_user -d mydb -t mytable

Reindex a schema

reindexdb -h db.example.com -p 5432 -U db_user -d mydb -s myschema

Reindex a specific index

reindexdb -h db.example.com -p 5432 -U db_user -d mydb -i myindex

Authentication

Reindexdb needs access credentials for the target database. I create a .pgpass file containing:

hostname:port:database:username:password

And place it in the home directory of the user running reindexdb, with 0600 permissions. The -w option tells reindexdb to use this file.

reindexdb -h db.example.com -p 5432 -U db_user -w -d mydb

Parallel Reindexing

By default reindexdb uses only one worker job. For faster reindexing on servers with multiple cores, you can use the -j option to specify more parallel jobs.

reindexdb -h db.example.com -p 5432 -U db_user -d mydb -j 4

Monitoring Progress

Reindexing large databases can take hours. I monitor progress by enabling verbose mode -v and redirecting output to a log file:

reindexdb -h db.example.com -p 5432 -U db_user -d mydb -v 2> reindex.log

The log shows the timeline for each index being rebuilt.

Reindexing in Background

To start a reindex in the background, I append & like:

reindexdb -h db.example.com -p 5432 -U db_user -d mydb -j 4 &> reindex.log &

This allows me to continue other work while reindex runs.

Wrapping Up

The reindexdb utility is indispensable for maintaining PostgreSQL database performance. Its options for targeting specific objects, monitoring progress, parallel reindexing, and running in background allow me to effectively reindex both small and huge databases.