Is a database-wide REINDEX safe in a migration?
Database-wide REINDEX in a migration
Critical: this fails outright or takes production down.
What happens
REINDEX SYSTEM and REINDEX DATABASE rebuild every index in the database, system catalogs included, and catalog indexes can never be rebuilt concurrently, so their rebuild takes locks that stall catalog lookups for every session. Runtime scales with total index volume across the database. Whatever a migration needs, it is never this; rebuild the specific index or table instead.
Why it is dangerous on a populated table
On an empty table this finishes before anyone notices. On a populated one the lock is held for the whole operation, every query on the table queues behind it, and the connection pool fills from the back.
Measured
Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.
| Statement | Time | Worst write wait | Worst read wait | Lock | |
|---|---|---|---|---|---|
| Fires | REINDEX TABLE (every index rebuilt under lock) | 10.3 s | 10.2 s | 10.2 s | ShareLock |
| Safe | REINDEX TABLE CONCURRENTLY | 12.5 s | 688 ms | 12 ms | ShareUpdateExclusiveLock |
Fires on
REINDEX DATABASE app;The safe pattern
Rebuild only what is actually bloated or corrupt, online: REINDEX INDEX CONCURRENTLY or REINDEX TABLE CONCURRENTLY (Postgres 12+), each alone in its own migration outside a transaction. Catalog/system reindexing is a superuser maintenance action for a window, never schema history.
-- Own migration, outside a transaction (PG12+):
REINDEX INDEX CONCURRENTLY idx_orders_region;
-- or per table: REINDEX TABLE CONCURRENTLY orders;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (2)
REINDEX DATABASE app;REINDEX SYSTEM;Stays silent (1)
REINDEX INDEX CONCURRENTLY idx_orders_user;How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote