PostgreSQL 10 Administration Cookbook - Fourth Edition

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 your database for possibly hours, and I advise that you think about not doing that.

Try these steps instead:

1. First, let's create a test table with two indexes—a primary key and ...