Building Indexes Online With CREATE INDEX CONCURRENTLY

A query started timing out under load, the fix is obvious — add a composite index on orders (customer_id, created_at) — and you write the one-line migration you have written a hundred times: CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);. On staging it returns in a blink. In production it triggers an incident: the statement takes a SHARE lock that blocks every INSERT, UPDATE, and DELETE on orders for the entire build, and on a hundred-million-row table that build runs for several minutes. During those minutes the connection pool fills with writers queued behind a lock they will never get in time, timeouts cascade into checkout and fulfillment, and one index change becomes a site-wide outage. The whole point of CREATE INDEX CONCURRENTLY (CIC) is to build that same index while writers keep flowing — but it comes with a rule that trips up every migration tool the first time: it cannot run inside a transaction, and if it fails it does not roll back cleanly. This page is the runnable procedure for building an index online on PostgreSQL, with the MySQL ALGORITHM=INPLACE, LOCK=NONE equivalent alongside.

Symptom / Error Signatures

You are on this page because of one of these signals — either an outage from a blocking build, or an error the moment you reached for the concurrent one.

Writers pile up during a plain CREATE INDEX: pg_stat_activity shows dozens of sessions in wait_event_type = Lock, wait_event = relation, all blocked on the table you are indexing.
The tell-tale migration-tool failure: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
A concurrent build that “finished” but left damage behind: the index exists yet \d orders prints it as INVALID, and EXPLAIN still shows a Seq Scan on the query you meant to speed up.
The build never returns and pg_stat_progress_create_index sits in phase waiting for old snapshots to be released — the signature of a long transaction blocking the reconciliation pass, not a slow scan.
MySQL rejecting the online path: ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: ... Try ALGORITHM=COPY.
MySQL failing under write pressure: Creating index 'idx_orders_customer_created' required more than 'innodb_online_alter_log_max_size' bytes of modification log.

If you see the transaction-block error, jump to Immediate Mitigation. If you see INVALID, the build already failed and the repair is a separate procedure covered under cleaning up invalid indexes after a failed build.

Root Cause Analysis

A plain CREATE INDEX does one table scan under a heavy lock: fast, but it freezes writers for the whole build. CREATE INDEX CONCURRENTLY trades that single locked scan for two lighter scans plus a wait, holding only a SHARE UPDATE EXCLUSIVE lock that permits concurrent writes throughout. The first pass builds the index from a point-in-time snapshot. Then the build blocks until every transaction that started before it has committed or aborted, so it can be sure it will see any rows those transactions touched. The second pass reconciles everything written during the first. This two-pass, commit-in-the-middle design is exactly why CIC cannot run inside a transaction block: it must commit intermediate state and observe the concurrent writes it exists to capture, and a wrapping transaction would forbid both. The same design explains why a failure does not roll back cleanly — a killed session or a second-pass error leaves an INVALID index that still taxes every write while the planner refuses to read it.

MySQL 8.0 with InnoDB frames the same goal as online DDL controlled by two clauses on ALTER TABLE: ALGORITHM and LOCK. For a secondary index, ALGORITHM=INPLACE, LOCK=NONE builds the index while permitting concurrent reads and writes, buffering the changes made during the build in an online log applied at the end. Where the engines diverge matters when you write the migration:

Aspect	PostgreSQL (`CREATE INDEX CONCURRENTLY`)	MySQL 8.0 InnoDB (`ALGORITHM=INPLACE, LOCK=NONE`)
Lock held during build	`SHARE UPDATE EXCLUSIVE` for the whole run, never blocks writes	Brief exclusive metadata lock at start and end only
Transaction boundary	MUST run outside any transaction block	DDL auto-commits; runs in the implicit-commit model
Number of table scans	Two scans plus a wait-for-old-transactions barrier	One build plus an online-log apply
On failure	Leaves an `INVALID` index that must be dropped explicitly	Rolls back cleanly, no invalid state to clean up
Blocked by long transactions	Yes — waits on every transaction older than itself	Yes — brief metadata locks queue behind long queries
Overflow failure mode	`maintenance_work_mem` affects speed, not correctness	Fails if writes exceed `innodb_online_alter_log_max_size`

The trap on MySQL is the Try ALGORITHM=COPY suggestion: COPY rebuilds the entire table under a blocking lock, which is precisely the outage you came here to avoid — never accept that fallback on a live table. For very large MySQL tables an external tool that copies in throttled chunks, compared in gh-ost vs pt-online-schema-change, is often safer than a native INPLACE build under heavy write.

Both builds occupy the same wall-clock span, but the plain build holds a write-blocking lock the whole time while CONCURRENTLY holds only SHARE UPDATE EXCLUSIVE across two scans and a wait barrier.

Immediate Mitigation

If a plain build is blocking writes right now, or your migration tool just threw the transaction-block error, here is right-now relief.

Cancel a blocking build to release writers immediately. A plain CREATE INDEX can be cancelled with no lasting damage — nothing is left half-built to clean up.

-- PostgreSQL · run as a superuser or the table owner in a second session · read pg_stat_activity first
-- Context: cancels the blocking build so queued writers proceed; safe — a non-concurrent build leaves nothing behind.
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query ILIKE 'CREATE INDEX%' AND query NOT ILIKE '%CONCURRENTLY%';

Tell your migration tool to run the statement outside a transaction. The cannot run inside a transaction block error means the tool wrapped your DDL in BEGIN/COMMIT. Disable that per migration: Flyway executeInTransaction=false, Rails disable_ddl_transaction!, and in raw psql simply do not type BEGIN.
Set a lock budget before you start, so the brief metadata lock fails fast instead of queueing the pool.

-- PostgreSQL · run in the same session as the build · sets the ceiling for the brief metadata lock
-- Context: MUST run OUTSIDE a transaction; a 2s cap means a contended lock errors instead of blocking writers.
SET lock_timeout = '2s';

Rebuild the index online in its own connection. With autocommit on and no surrounding transaction, issue the concurrent build; IF NOT EXISTS makes the step re-runnable after a partial failure.

-- PostgreSQL · run as the table owner or a role with CREATE on the table · autocommit ON
-- Context: MUST run OUTSIDE a transaction block — no BEGIN/COMMIT around this statement.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
  ON orders (customer_id, created_at);

On MySQL, express the same intent with explicit clauses. The ALTER auto-commits, so there is no transaction to disable — but bound the metadata lock and assert LOCK=NONE so the engine errors rather than silently downgrading to a blocking build.

-- MySQL 8.0 (InnoDB) · run as a user with ALTER on the table · DDL auto-commits
-- Context: LOCK=NONE keeps reads AND writes flowing; if the engine cannot honor it, it errors instead of blocking.
SET SESSION lock_wait_timeout = 2;
ALTER TABLE orders
  ADD INDEX idx_orders_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

The right-now path: cancel a plain blocking build, take the DDL out of a transaction, cap the lock wait, run the engine-appropriate online build, and confirm the index came out valid.

Permanent Fix / Long-Term Pattern

An index build is an additive, forward-compatible change, so the durable pattern is to ship it as its own migration step ahead of the code that depends on it — the same expand-first discipline the Online Index Management topic applies to every index operation. Three habits keep concurrent builds boring.

Clear the path before you build. A concurrent build waits for every transaction older than itself, so a single forgotten idle in transaction session or a long analytics query can stall it for hours. Check first, and set idle_in_transaction_session_timeout globally so such sessions cannot accumulate.

-- PostgreSQL · run against the PRIMARY as a monitoring role · read-only, safe anytime
-- Context: any transaction older than a few minutes will delay the build's wait phase — resolve it before starting.
SELECT pid, state, now() - xact_start AS txn_age, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY txn_age DESC
LIMIT 10;

Watch the build instead of waiting blind. PostgreSQL 12+ exposes live progress through pg_stat_progress_create_index, which reports the current phase and how many blocks it has scanned — so you can distinguish a slow-but-healthy scan from a build wedged behind an old transaction.

-- PostgreSQL 12+ · run against the PRIMARY in a second session · read-only
-- Context: phase "building index: scanning table" is healthy; "waiting for old snapshots to be released" means a long txn is blocking it.
SELECT p.phase, p.blocks_done, p.blocks_total,
       round(100.0 * p.blocks_done / NULLIF(p.blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON a.pid = p.pid;

Never trust an index you did not verify. Because a concurrent build can return control while having left an INVALID index behind, absence of an error is not proof of success. Confirm indisvalid before the migration reports done.

-- PostgreSQL · run against the PRIMARY · read-only, safe anytime
-- Context: indisvalid = false means the second pass failed — the index costs writes but serves no reads.
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_orders_customer_created';

If indisvalid comes back false, do not retry blindly — the leftover index must be dropped concurrently first, following cleaning up invalid indexes after a failed build. And when a build competes with a heavy backfill on the same table, coordinate the two so neither starves the other — the batch-size trade-offs in tuning backfill batch size against replication lag apply directly, since both the build’s second scan and a backfill compete for the same write bandwidth and replication budget. The complete lifecycle — build, verify, rebuild, drop — lives in the parent Online Index Management topic.

A healthy concurrent build moves scan → barrier → scan to a valid index; a failed second pass drops out to an INVALID index that must be dropped concurrently before the build can be retried.

Verification Checklist

The build ran outside any transaction block (PostgreSQL) or auto-committed as its own ALTER (MySQL), with no cannot run inside a transaction block error.
pg_stat_activity showed no transaction older than the build stalling its wait phase before you started.
lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL) was set small so the brief metadata lock failed fast instead of queueing writers.
The finished index reports indisvalid = true (PostgreSQL) or appears in SHOW INDEX FROM orders with expected cardinality (MySQL) — no INVALID index remains.
EXPLAIN on the target query now shows an Index Scan / Index Only Scan (PostgreSQL) or ref/range on the new key (MySQL), not a Seq Scan / full table scan.
Write latency on the table did not regress after the build — index maintenance cost is within budget.

Up to the parent topic: Online Index Management.

Frequently Asked Questions

Why can’t CREATE INDEX CONCURRENTLY run inside a transaction? Because it is not a single atomic operation. It performs two table scans separated by a wait for pre-existing transactions to finish, and between those phases it must commit intermediate state and observe rows written by other sessions. A surrounding transaction would prevent both the intermediate commit and the visibility of concurrent writes the build exists to capture. That same non-atomic design is why a failed concurrent build leaves an INVALID index instead of rolling back the way ordinary DDL does — so your migration tool must run the statement in its own connection with autocommit on.

How do I make Flyway or Rails run it outside a transaction? Most migration frameworks wrap each migration in a transaction by default, which produces the cannot run inside a transaction block error. Opt out per migration: in Flyway set executeInTransaction=false on the SQL migration, in Rails call disable_ddl_transaction! in the migration class, and in raw psql simply avoid typing BEGIN. The statement then runs on its own connection where it can commit the intermediate state a concurrent build requires.

How do I watch the progress of a running build? On PostgreSQL 12+, query pg_stat_progress_create_index from a second session to see the current phase and the blocks_done / blocks_total ratio. A phase of building index: scanning table with a climbing block count is healthy; a build parked in waiting for old snapshots to be released is blocked by a transaction older than itself, not scanning slowly — find and resolve that transaction in pg_stat_activity. On MySQL, inspect information_schema.innodb_trx and the performance-schema stage events to see whether the online build is progressing or waiting on a metadata lock.

Related Articles