May 2018
Intermediate to advanced
576 pages
30h 25m
English
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: