Skip to main content

How to add a PostgreSQL index without downtime

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: it can't run inside a transaction block, it scans the table twice so it takes longer, and if it fails it leaves an INVALID index you must drop and rebuild.

Why a plain CREATE INDEX is downtime​

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 duration of the build. On a small table that's a blink. On a 100-million-row table it's a multi-minute write outage, which for most applications is an incident.

The fix: CREATE INDEX CONCURRENTLY​

CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON orders (customer_id);

CONCURRENTLY builds the index while reads and writes continue. It does that by being more careful — and that carefulness is the source of every gotcha below.

It's not impact-free — it still holds a lock​

CONCURRENTLY isn't 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 self-conflicts with VACUUM, ANALYZE, autovacuum, and any other CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY on the same table — those serialize behind it. Only one concurrent build can run on a given table at a time, and a long-running build can starve autovacuum, letting dead tuples and bloat pile up while it runs.

It can't run in a transaction block​

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. You have to disable the wrapping transaction for that migration (Rails: disable_ddl_transaction!; Django: atomic = False; or run the statement outside your tool's transaction).

It's slower — two scans and a wait​

Building concurrently does more total work: it scans the table twice (build, then a second pass to catch rows changed during the build) and waits for transactions that started before it to finish. Expect it to take noticeably longer than a blocking build — you're trading wall-clock time for availability.

It doesn't work on a partitioned parent table​

Concurrent builds on partitioned tables aren't supported. Do it in two steps: 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.

On failure it leaves an INVALID index​

If the build fails — a unique violation, a deadlock, a 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 won't use it. Find them and clean up:

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 a plain DROP INDEX would take. One exception: it can't drop an index backing a primary key, unique, or exclusion constraint — that needs CASCADE, which the concurrent form doesn't support. Drop or alter the constraint instead.

The unique-index trap​

A unique index has an extra wrinkle. CONCURRENTLY scans the table twice, and by the time the second scan starts the uniqueness constraint is already being enforced against other transactions. So duplicate inserts can start failing before the index is usable — and per the PostgreSQL docs, "if a failure does occur in the second scan, the 'invalid' index continues to enforce its uniqueness constraint afterwards."

That's the worst of both worlds: write overhead and rejected duplicates, with no query benefit (the planner still ignores it). A build that fails in the first scan leaves the ordinary invalid index — ignored for reads, update overhead only. Either way the fix is the same: drop it and retry. But if inserts start failing on a uniqueness violation you can't explain, look for a leftover invalid unique index before anything else.

Adding a UNIQUE constraint without downtime​

Adding a unique constraint directly builds its index under an ACCESS EXCLUSIVE lock. Do it in 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 just attaches the already-built index, so the strong lock is held only briefly.

Why does AI-generated migration code miss this?​

Your AI coding agent (Claude Code, Cursor) writes CREATE INDEX because that's the textbook statement, or it writes CREATE INDEX CONCURRENTLY inside the transaction its migration template already opened — and the statement fails at run time. It has no view of the table's size (is this a blink or a ten-minute outage?) or of the fact that a previous concurrent build left an invalid index sitting there. Those are runtime and operational facts, not properties of the schema in the source.

How do I make this routine?​

  • 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.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the operational context — the table's size, existing indexes, and any invalid index left from a failed build — so it can recommend CONCURRENTLY, keep the statement out of a transaction, and catch a leftover invalid index. It surfaces and explains; it does not run your migrations. Get started free →

FAQ​

Does CREATE INDEX lock the table? A plain CREATE INDEX takes a SHARE lock that blocks writes (allowing reads) for the whole build — a write outage on a big table. CREATE INDEX CONCURRENTLY avoids blocking writes.

Why can't CREATE INDEX CONCURRENTLY run in a transaction? It commits between internal phases, so it can't run inside an explicit transaction block. Disable your migration tool's wrapping transaction for that statement.

What is an INVALID index? A failed or interrupted concurrent build can leave an index with indisvalid = false: it takes space and is maintained on writes, but the planner won't use it. For a unique index that failed during the second table scan, it also keeps enforcing uniqueness, so duplicate inserts keep failing. Drop it with DROP INDEX CONCURRENTLY and retry.

Can I use CONCURRENTLY on a partitioned table? Not on the partitioned parent — concurrent builds there aren't 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.

How do I add a unique constraint without downtime? CREATE UNIQUE INDEX CONCURRENTLY, then ALTER TABLE ... ADD CONSTRAINT ... USING INDEX to attach it — a fast catalog step.