How PostgreSQL Avoids Blocking Writes During Index Creation

When a busy PostgreSQL table needs an index, a structure that helps PostgreSQL find rows, use CREATE INDEX CONCURRENTLY to let inserts, updates, and deletes continue while PostgreSQL builds it. A regular CREATE INDEX blocks writes until the index is ready. Concurrent creation avoids that lock, but it uses more scans, waits, and resources. PostgreSQL’s CREATE INDEX documentation describes both behaviors.

A table lock controls which operations can access a table at the same time. A regular index build takes a SHARE lock, which allows reads and blocks writes. A concurrent build takes a SHARE UPDATE EXCLUSIVE lock, which allows ordinary writes while PostgreSQL creates the index. That lock still conflicts with some other operations, so concurrent creation reduces write blocking but does not eliminate all lock conflicts.

How concurrent creation lets writes continue

PostgreSQL builds a concurrent index in stages. It first records the new index as invalid in the system catalogs, PostgreSQL's database metadata, so queries cannot use it yet. PostgreSQL then scans the table and adds entries to the index. After waiting for transactions that could affect the result, it scans the table again to check that the index includes the needed rows. It then waits for older transactions that could still rely on an earlier view of the table before marking the index valid.

The scans and waits let PostgreSQL account for writes made during creation. They also make a concurrent build take longer than a regular build, which scans the table once. Long-running transactions extend the waits, and the extra scans use more I/O and CPU. For a large or busy table, check available capacity and schedule the build when you can manage the extra load.

Check the build’s progress

PostgreSQL reports index-build activity in pg_stat_progress_create_index. The phase column shows what PostgreSQL is doing. Block counters show progress through table storage, and tuple counters show progress through rows, for the current phase. A simple query is:

SELECT
  pid,
  relid::regclass AS table_name,
  index_relid::regclass AS index_name,
  phase,
  blocks_done,
  blocks_total,
  tuples_done,
  tuples_total
FROM pg_stat_progress_create_index;

The progress reporting documentation describes the view’s fields and phases. A wait phase does not mean the build has stalled. PostgreSQL waits for certain transactions to finish so the index stays consistent.

Handle failures and limits

A failed concurrent build can leave an invalid index behind. Check the indisvalid field in PostgreSQL's index catalog to find indexes it has not marked usable:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

An invalid index remains unavailable to ordinary queries, occupies space, and adds overhead to writes. Confirm that PostgreSQL has stopped building the index, then remove it before retrying. For an index that can be dropped independently, DROP INDEX CONCURRENTLY index_name avoids blocking ordinary writes during removal. PostgreSQL does not allow that command, like CREATE INDEX CONCURRENTLY, inside a transaction block.

PostgreSQL allows only one concurrent index build on a given table at a time, so any additional concurrent build on that table must wait. A concurrent build of a unique index also checks for duplicate values. Writes that add duplicates can make the build fail. Review the index’s status and the error before deciding whether to remove it or retry.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com