Rebuilding indexes concurrently

Once in a while, it can be necessary (in rare circumstances) to recreate an index. For quite some time, PostgreSQL has provided CREATE INDEX CONCURRENTLY, which allows end users to create an index while a table is under a heavy write load. A normal CREATE INDEX statement blocks the table while the index is created and, therefore, it is hardly possible to create large indexes in a 24 x 7 OLTP database. 

However, in some cases, it might be necessary to recreate all indexes in a database or a specific schema. PostgreSQL 12 allows you to run REINDEX CONCURRENTLY on an entire database, schema, or table:

test=# \h REINDEX
Command:  REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name

URL: https://www.postgresql.org/docs/12/sql-reindex.html

REINDEX CONCURRENTLY will dramatically reduce the pain caused by REINDEX and achieve results with minimal locking.