Does REINDEX lock the table in PostgreSQL?

Short answer

REINDEX INDEX orders_status_idx rebuilds the index from scratch. It takes a SHARE lock on the table, which would only block writes, but also an ACCESS EXCLUSIVE lock on the index. PostgreSQL’s planner opens every index of a table when it plans a query, whether it uses them or not, so in practice reads wait too. In our tests, even SELECT count(*) waited while a different, unrelated index was being rebuilt.

REINDEX INDEX CONCURRENTLY orders_status_idx (PostgreSQL 12 and later) builds a replacement next to the old index and swaps them, holding only SHARE UPDATE EXCLUSIVE locks, so reads and writes carry on. Use it on anything in production.

What it locks

The PostgreSQL documentation says some prepared statements whose cached plan doesn’t use the index can get through; ordinary queries don’t.

REINDEX INDEX and REINDEX TABLE can run inside a transaction, which keeps the locks until COMMIT. REINDEX SCHEMA, REINDEX DATABASE and anything CONCURRENTLY can’t:

ERROR:  REINDEX CONCURRENTLY cannot run inside a transaction block

Does it rewrite the table?

No. The table’s data file stays the same; each index gets a new file. The table is read once per index being rebuilt: REINDEX TABLE on a table with two indexes scanned it twice. On our 3-million-row table, rebuilding one index took 0.6 to 0.8 s, all of it with reads and writes blocked.

Run it safely

Use CONCURRENTLY, outside a transaction:

REINDEX INDEX CONCURRENTLY orders_status_idx;
-- or every index of a table:
REINDEX TABLE CONCURRENTLY orders;

Like CREATE INDEX CONCURRENTLY, it waits for transactions that are writing to the table, and for older snapshots, before it can finish. It holds up other DDL and vacuum on the table meanwhile, not queries.

Don’t give it a short lock_timeout or statement_timeout. If it’s cancelled part-way, the original index carries on working but the half-built copy stays behind, invalid, with _ccnew on the end of its name:

ERROR:  canceling statement due to statement timeout

SELECT indexrelid::regclass AS index, indisvalid
FROM pg_index WHERE indrelid = 'seo_mig.big'::regclass;

          index          | indisvalid
-------------------------+------------
 seo_mig.big_n_idx       | t
 seo_mig.big_n_idx_ccnew | f
(2 rows)

Drop the leftover, then run the reindex again:

DROP INDEX CONCURRENTLY orders_status_idx_ccnew;

According to the documentation, an index ending in _ccold is the old copy left after a failure late in the swap; drop that one too. See statement timeout.

Fixing an invalid index from a failed CREATE INDEX CONCURRENTLY. REINDEX INDEX CONCURRENTLY works on those too: in our test it turned an invalid 20 MB index valid on every version.

If you must use plain REINDEX (on a small table, or a quiet system), set lock_timeout so it doesn’t wait in front of everyone else, and expect queries to wait for the whole rebuild:

SET lock_timeout = '2s';
REINDEX INDEX orders_status_idx;

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac):

CREATE TABLE seo_mig.t (id int PRIMARY KEY, a int, s varchar(50), ts timestamp, body text);
INSERT INTO seo_mig.t SELECT g, g, 'x' || g, now(), 'b' FROM generate_series(1, 1000) g;
CREATE INDEX t_a_idx ON seo_mig.t (a);

BEGIN;
REINDEX INDEX seo_mig.t_a_idx;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
ROLLBACK;

A second session held each REINDEX open while a third ran statements with lock_timeout = '1s'. Results were identical on all five versions:

On the 3-million-row seo_mig.big, with an index on n (median of three, rolled back):

For CONCURRENTLY, one session kept an INSERT open for 3 s; REINDEX INDEX CONCURRENTLY seo_mig.big_n_idx started half a second later. On every version, pg_stat_progress_create_index showed REINDEX CONCURRENTLY / waiting for writers before build, it held ShareUpdateExclusiveLock on big, big_n_idx and big_n_idx_ccnew, a SELECT using the index and an INSERT both ran meanwhile, and it finished in 3.6 to 4.1 s. With statement_timeout = '300ms' it was cancelled and left big_n_idx_ccnew invalid on all five.

In Inlet

While a reindex runs, the Activity monitor shows which sessions are waiting on it, and lets you cancel it if queries start piling up.