Mastering PostgreSQL 12 - Third Edition

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 REINDEXCommand:     REINDEXDescription: rebuild indexesSyntax:REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | ...