Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Concurrent index builds: verify validity after the command exits

Last updated: 5 Oct 20266 min read
tutorial
AdvancedBy AITrove Editorial

PostgreSQL can build an index concurrently so ordinary table writes continue during most of the operation. It performs more work than a regular index build, waits for relevant transactions, and cannot run inside a transaction block. A failed concurrent build can leave an invalid index object, and a unique concurrent build may start enforcing uniqueness before it becomes valid for queries.

Operational decision

A receipt table needs an index for customer history queries. Rehearse the build against a production-like copy with measured row count, write rate, free disk, replication lag, and representative long transactions. Run the statement as its own migration step, outside the framework's automatic transaction wrapper. The SQL includes a read-only catalog check for the index's validity. After completion, inspect pg_index.indisvalid, query plan, write latency, disk use, and replica catch-up before declaring success. If the build fails, identify whether the remaining index is invalid and follow a reviewed drop-and-retry procedure; do not blindly rerun a command under the same name. For a unique index, inspect duplicate data and user-visible uniqueness errors as part of the rollout. Pause another concurrent build on the same table until this one finishes. A low-traffic window reduces contention but does not make the operation free of I/O cost.

sql
CREATE INDEX CONCURRENTLY receipt_customer_created_idx
ON receipts (customer_id, created_at);
SELECT c.relname, i.indisvalid, i.indisready
FROM pg_class AS c
JOIN pg_index AS i ON i.indexrelid = c.oid
WHERE c.relname = 'receipt_customer_created_idx';

Cost and verification

Extra table scans and index writes consume CPU, I/O, and storage; replicas must apply the resulting changes. A concurrent build lowers write blocking but can run longer than a regular build. An invalid index can still consume space and update work, so it needs a recorded cleanup decision. Keep build duration, lock waits, replica lag, query plan, and transaction error rate in the acceptance record.

Common Mistakes

  • Do not wrap CREATE INDEX CONCURRENTLY in a migration transaction.
  • Do not equate command completion with a valid usable index without checking catalog state.
  • Do not ignore a failed unique build that may still affect writes.

Connected lessons

Practice and check

devops
operations
Storage details