Skip to content

How to add a PostgreSQL index without downtime

Last updated

View as Markdown

Quick answer: Use CREATE INDEX CONCURRENTLY. It builds the index without blocking writes, unlike a plain CREATE INDEX, which holds a write-blocking lock for the whole build. The trade-offs are that it cannot run inside a transaction block, scans the table twice, takes longer, and leaves an INVALID index if it fails. You must drop and rebuild that index.

A normal CREATE INDEX takes a SHARE lock on the table. That allows reads but blocks all writes: every INSERT, UPDATE, and DELETE waits for the entire build. On a small table, that takes a moment. On a 100-million-row table, it can cause a multi-minute write outage.

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);

CONCURRENTLY builds the index while reads and writes continue. The additional work required for this causes the limitations below.

It is not impact-free, it still holds a lock

Section titled “It is not impact-free, it still holds a lock”

CONCURRENTLY is not lock-free; it holds a SHARE UPDATE EXCLUSIVE lock on the table for the entire build. That lock allows concurrent INSERT, UPDATE, and DELETE, but it conflicts with VACUUM, ANALYZE, autovacuum, and any other CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY on the same table. Those operations wait behind it. Only one concurrent build can run on a given table at a time. A long-running build can also delay autovacuum, allowing dead tuples and bloat to accumulate.

CREATE INDEX CONCURRENTLY commits between its internal phases, so it cannot run inside an explicit BEGIN … COMMIT. Most migration frameworks wrap each migration in a transaction by default, which makes the statement fail. Disable the wrapping transaction for that migration. For example, in Rails use disable_ddl_transaction!, in Django use atomic = False, or run the statement outside your tool’s transaction.

Building concurrently does more total work: it scans the table twice, first to build the index and then to catch rows changed during the build. It also waits for transactions that started before it to finish. The build takes longer than a blocking build, but the table remains writable.

It does not work on a partitioned parent table

Section titled “It does not work on a partitioned parent table”

Concurrent builds on partitioned tables are not supported. Build the index on each partition with CREATE INDEX CONCURRENTLY, then create the matching index on the parent without CONCURRENTLY. Postgres attaches the partition indexes you already built, so that step is metadata-only.

If the build fails because of a unique violation, deadlock, or cancelled statement, it can leave an index marked invalid. That index takes up space and is kept up to date on writes, but the planner will not use it. Find the invalid indexes and remove them:

SELECT c.relname AS index_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indisvalid = false;
DROP INDEX CONCURRENTLY idx_orders_customer_id; -- then re-create

DROP INDEX CONCURRENTLY likewise avoids the ACCESS EXCLUSIVE lock that a plain DROP INDEX would take. One exception is an index backing a primary key, unique, or exclusion constraint. You cannot drop that index with the concurrent form because it does not support CASCADE. Drop or alter the constraint instead.

A unique index has an extra complication. CONCURRENTLY scans the table twice, and by the time the second scan starts, the uniqueness constraint is already being enforced against other transactions. Duplicate inserts can therefore start failing before the index is usable. Per the PostgreSQL docs, “if a failure does occur in the second scan, the ‘invalid’ index continues to enforce its uniqueness constraint afterwards.”

The result is write overhead and rejected duplicates, with no query benefit because the planner still ignores the index. A build that fails in the first scan leaves the ordinary invalid index, ignored for reads, update overhead only.

The fix is the same in either case: drop the index and retry. If inserts start failing with a uniqueness violation that you cannot explain, check for a leftover invalid unique index first.

Adding a UNIQUE constraint without downtime

Section titled “Adding a UNIQUE constraint without downtime”

Adding a unique constraint directly builds its index under an ACCESS EXCLUSIVE lock. Use two steps instead:

CREATE UNIQUE INDEX CONCURRENTLY orders_email_uidx ON orders (email);
ALTER TABLE orders ADD CONSTRAINT orders_email_unique
UNIQUE USING INDEX orders_email_uidx;

The ADD CONSTRAINT ... USING INDEX step attaches the existing index, so the strong lock lasts only briefly.

Why does AI-generated migration code miss this?

Section titled “Why does AI-generated migration code miss this?”

Your AI coding agent writes CREATE INDEX because it is the textbook statement, or it writes CREATE INDEX CONCURRENTLY inside the transaction that its migration template already opened. The statement then fails at run time. The agent cannot see the table’s size, whether the build will take a short time or cause a ten-minute outage, or whether a previous concurrent build left an invalid index behind. Those runtime and operational facts are not present in the schema source.

  • Default to CONCURRENTLY for any index on a table that takes production writes.
  • Configure your migration tool to run index creation outside a transaction.
  • After a failed concurrent build, check pg_index.indisvalid and drop the invalid index before retrying.
  • Does CREATE INDEX lock the table in PostgreSQL?

    A plain CREATE INDEX takes a SHARE lock that blocks writes (INSERT, UPDATE, DELETE) to the table for the entire build, while still allowing reads. On a large table that is effectively a write outage. CREATE INDEX CONCURRENTLY avoids blocking writes.

  • Why can't CREATE INDEX CONCURRENTLY run in a transaction?

    CREATE INDEX CONCURRENTLY manages its own multi-step process and must commit between phases, so it cannot run inside an explicit transaction block. Migration tools that wrap each migration in a transaction need that transaction disabled for this statement.

  • What is an INVALID index in PostgreSQL?

    If CREATE INDEX CONCURRENTLY fails or is interrupted, it can leave an index marked invalid (indisvalid = false). The index still consumes space and is maintained on writes, but the planner will not use it. One special case: for a unique index, if the failure happened during the second table scan, the invalid index continues to enforce its uniqueness constraint, so duplicate inserts keep failing. Drop it with DROP INDEX CONCURRENTLY and retry.

  • How do I add a unique constraint without downtime?

    Build a unique index with CREATE UNIQUE INDEX CONCURRENTLY, then attach it with ALTER TABLE ... ADD CONSTRAINT ... USING INDEX, which is a fast catalog operation. Adding the constraint directly would build the index under an ACCESS EXCLUSIVE lock.

  • Can I use CREATE INDEX CONCURRENTLY on a partitioned table?

    Not on the partitioned parent: concurrent builds there are not supported. Build each partition's index with CREATE INDEX CONCURRENTLY, then create the index on the parent non-concurrently. Attaching the existing partition indexes is a metadata-only operation.