Online Index Management

An index is the one piece of schema you touch most often and least carefully. New query patterns demand new indexes; abandoned features leave dead ones consuming write throughput; bloated ones need rebuilding. The naive statement for each — CREATE INDEX, DROP INDEX, REINDEX — takes a lock strong enough to freeze every writer on the table for as long as the operation runs, which on a hundred-million-row table is minutes, not milliseconds. During that window the connection pool fills with queries waiting on a lock they will never get in time, and the outage propagates outward from one table to the whole application. This part of the Zero-Downtime Schema Evolution Patterns runbook covers how to add, rebuild, and remove indexes on a live table without ever blocking a write — and it serves the engineer shipping a query-performance fix under load and the DBA who has to sign off that the primary survives it.

Index changes are the expand-and-contract lifecycle in miniature. Building an index is an additive, forward-compatible change that should ship ahead of the code that relies on it, exactly as the Expand and Contract Methodology prescribes; dropping one is a contract step that is only safe once you have proven no query plan still depends on it. The difference from a column change is the lock model: an online index build does most of its work while holding only a light lock, but it does so by scanning the whole table at least twice, which interacts with long transactions, replication, and the lock_timeout budget in ways that ordinary DDL does not.

Concept & Mechanism

A standard CREATE INDEX in PostgreSQL takes an ACCESS EXCLUSIVE-adjacent SHARE lock that blocks all writes (and blocks reads only briefly) for the entire build. CREATE INDEX CONCURRENTLY (often abbreviated CIC) trades speed for availability: instead of one table scan under a heavy lock, it performs two scans plus a wait, holding only a SHARE UPDATE EXCLUSIVE lock that permits concurrent INSERT, UPDATE, and DELETE throughout. The first pass builds the index from a snapshot; then the build waits for every transaction that started before it to finish, so it can see the rows they may have changed; the second pass reconciles everything written during the first. Because it must wait on pre-existing transactions, a single long-running transaction elsewhere on the database can stall a concurrent build indefinitely. The full procedure and its pitfalls are the subject of building indexes with CREATE INDEX CONCURRENTLY.

The critical consequence of the two-pass design is that CIC is not atomic. It cannot run inside a transaction block, and if the second pass fails — a deadlock, a lock_timeout, a unique-constraint violation discovered during reconciliation, or the session simply being killed — PostgreSQL leaves behind an INVALID index. That index still costs write overhead (every insert maintains it) but the planner refuses to use it for reads, so you get the worst of both worlds: the write tax with none of the read benefit. Recognising and repairing that state is common enough to warrant its own procedure, cleaning up invalid indexes after a failed build.

MySQL 8.0 with InnoDB frames the same problem as online DDL governed by two clauses: ALGORITHM and LOCK. For secondary indexes, ALGORITHM=INPLACE, LOCK=NONE builds the index while permitting concurrent reads and writes, buffering changes made during the build in an online log that is applied at the end. Unlike PostgreSQL, InnoDB does not leave an invalid index on failure — a failed online build rolls back cleanly — but it takes brief exclusive metadata locks at the start and end of the operation, and those short locks can still queue behind (or ahead of) long-running queries. The engine also refuses LOCK=NONE for some operations (notably adding a FULLTEXT or SPATIAL index), silently needing LOCK=SHARED unless you assert LOCK=NONE explicitly and let it error if the guarantee cannot be met.

Dropping an index is deceptively cheap on both engines — it is a metadata operation, not a data rewrite — but it still needs a lock on the table’s definition, and that lock queues behind in-flight queries. On PostgreSQL, DROP INDEX CONCURRENTLY exists precisely so the drop does not block; on MySQL, DROP INDEX is instant metadata work but still contends for the metadata lock. The subtler risk is not the lock at all: it is dropping an index some query plan silently relies on, turning a fast index scan into a sequential scan the moment the index disappears. That correctness-versus-availability tension is covered in dropping indexes online without blocking queries.

Prerequisites & Decision Criteria

Not every index change needs the online path. On a small or low-traffic table a plain CREATE INDEX finishes in under your lock budget and is simpler and faster. The online machinery earns its cost only when the table is large enough or hot enough that a blocking lock would breach the budget. Use the table below to pick the algorithm before you write the statement.

Before starting any online build, confirm the operational preconditions. A concurrent build that collides with a long transaction or an unbounded lock wait is worse than a blocking one because it fails slowly.

• The table’s write traffic and the change are large enough to justify the online path — otherwise a plain blocking build under a tight lock_timeout is simpler
• No long-running transaction is open (pg_stat_activity on PostgreSQL, information_schema.innodb_trx on MySQL) — an online build waits on every transaction older than itself
• lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL) is set to a small value so a build that would block fails fast instead of queueing the pool
• There is enough disk headroom for a second copy of the index during the build, plus temporary sort space (maintenance_work_mem / innodb_online_alter_log_max_size)
• The build runs as a discrete, forward-only migration step before the code that depends on the index, and outside any surrounding transaction on PostgreSQL

The innodb_online_alter_log_max_size setting deserves attention on a write-heavy MySQL table: the online log buffers every row change made during the build, and if it overflows the build fails with Creating index 'x' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Raise it before a build on a table under heavy concurrent write.

Step-by-Step Procedure

The following walks a PostgreSQL concurrent build end to end; the MySQL equivalent is shown at the step where it diverges. The sequence is: check for blockers, set the lock budget, build outside a transaction, then verify validity before trusting the index.

1. Confirm no long transaction will stall the build. A concurrent build waits for every transaction older than itself, so a forgotten open transaction can hang it for hours.

-- PostgreSQL · run against the PRIMARY as a monitoring role · read-only, safe anytime.
-- Context: any row older than a few minutes will delay CREATE INDEX CONCURRENTLY — resolve it first.
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;

Verify before proceeding: the oldest transaction age is small (seconds, not minutes). If a long transaction is holding things open, wait for it or coordinate its termination before you start.

2. Set a lock budget, then build the index outside any transaction. CREATE INDEX CONCURRENTLY must not be wrapped in BEGIN/COMMIT; most migration tools need an explicit flag to run it in its own connection.

-- PostgreSQL · run as the table owner or a role with CREATE on the table.
-- Context: MUST run OUTSIDE a transaction block; no BEGIN/COMMIT around this statement.
SET lock_timeout = '2s';                 -- the brief metadata lock fails fast if it cannot be taken
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
  ON orders (customer_id, created_at);

On MySQL the same intent is expressed with explicit algorithm and lock clauses, which run inside the implicit-commit DDL model rather than outside a transaction.

-- MySQL 8.0 (InnoDB) · run as a user with ALTER on the table.
-- Context: DDL auto-commits; LOCK=NONE keeps reads AND writes flowing during the build.
SET SESSION lock_wait_timeout = 2;       -- bound the brief metadata lock at start/end
ALTER TABLE orders
  ADD INDEX idx_orders_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Verify before proceeding: the statement returns without error. On MySQL, if it raises ALGORITHM=INPLACE is not supported. Reason: ... Try ALGORITHM=COPY, do not fall back to COPY on a live table — that rewrites the whole table under a blocking lock. Reassess the operation instead.

3. Confirm the index is valid and ready. A PostgreSQL concurrent build can return control while having left an INVALID index behind if the second pass failed; never assume success from the absence of an error alone.

-- PostgreSQL · run against the PRIMARY · read-only, safe anytime.
-- Context: indisvalid = false means the build did not complete — 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';

Verify before proceeding: indisvalid and indisready are both true. If indisvalid is false, the build failed silently — follow the recovery path in cleaning up invalid indexes after a failed build before doing anything else.

4. Rebuild rather than recreate when an index is merely bloated. If the index exists and is valid but has bloated, rebuild it in place concurrently instead of dropping and recreating, which keeps its name and dependencies intact.

-- PostgreSQL 12+ · run as the index owner · MUST run OUTSIDE a transaction.
-- Context: rebuilds without blocking writes; on failure it leaves a transient invalid index (see recovery).
REINDEX INDEX CONCURRENTLY idx_orders_customer_created;

Verification & Observability

An online build is a long operation, and you want to watch it rather than wait blind. On PostgreSQL, track the build’s phase and progress through pg_stat_progress_create_index, which reports which of the scan/wait phases the build is in and how many blocks it has processed.

-- PostgreSQL 12+ · run against the PRIMARY in a second session · read-only.
-- Context: shows live build phase — "building index: scanning table", "waiting for old snapshots", etc.
SELECT p.phase, p.blocks_done, p.blocks_total,
       round(100.0 * p.blocks_done / NULLIF(p.blocks_total, 0), 1) AS pct,
       a.query
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON a.pid = p.pid;

Watch for a build stuck in the waiting for old snapshots to be released phase — that is the signature of a long transaction blocking the reconciliation pass, not a slow scan. On MySQL, inspect the same class of contention through the metadata-lock and transaction views.

-- MySQL 8.0 · run as a user with PROCESS privilege · read-only.
-- Context: a long-running row under trx_started can block the brief exclusive locks the online build needs.
SELECT trx_id, trx_state, trx_started, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

After the build, confirm the planner actually uses the new index rather than assuming it will. An index that exists but is never chosen is pure write overhead — the exact thing you were trying to avoid elsewhere.

• The index reports indisvalid = true (PostgreSQL) or appears in SHOW INDEX FROM orders with the expected cardinality (MySQL)
• EXPLAIN on the target query 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 — the index maintenance cost is within budget
• No INVALID index remains behind from an earlier failed attempt

Rollback Path

Because building an index is additive and forward-compatible, the rollback for a successful build is simply to drop it — the pre-index query plans still work, so removing the index returns the table to its prior state with no data loss. Do the drop concurrently so the rollback itself does not block.

-- PostgreSQL · run as the index owner · MUST run OUTSIDE a transaction.
-- Context: non-blocking drop; safe because removing an index only reverts query plans, never data.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

The more common rollback is for a failed concurrent build, which is not a no-op: it leaves an INVALID index that must be dropped explicitly, or it will silently tax every write while serving no reads. Drop it concurrently and, only once the table is clean, retry the build.

-- PostgreSQL · run as the index owner · MUST run OUTSIDE a transaction.
-- Context: safe anytime — an INVALID index is never used for reads, so dropping it changes no query result.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

On MySQL the rollback story is simpler: a failed online build rolls back atomically, leaving no partial index, so there is nothing to clean up — you retry the ALTER after fixing the cause (usually a too-small innodb_online_alter_log_max_size or a blocking transaction). Rollback is only unsafe once code depends on the index, at which point removing it is no longer a schema reversal but a performance regression — treat that as a contract step and prove no plan needs the index first.

Common Errors & Fixes

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block (PostgreSQL). Root cause: the statement is wrapped in an explicit or migration-tool-implicit transaction. Fix: run it in its own connection with autocommit on; in Flyway mark the migration executeInTransaction=false, in Rails use disable_ddl_transaction!, in raw psql do not wrap it in BEGIN.

INVALID index left behind, writes slow but the index is never used (PostgreSQL). Root cause: the second reconciliation pass of a concurrent build failed (deadlock, lock_timeout, killed session), leaving indisvalid = false. Fix: DROP INDEX CONCURRENTLY the invalid index and retry; the full diagnosis is in cleaning up invalid indexes after a failed build.

Build hangs forever in waiting for old snapshots to be released (PostgreSQL). Root cause: a transaction older than the build is still open — often an idle-in-transaction session or a long analytics query — and the build cannot finish its wait phase until it closes. Fix: find it in pg_stat_activity and resolve it; set idle_in_transaction_session_timeout to prevent recurrence.

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: ... Try ALGORITHM=COPY (MySQL). Root cause: the requested change cannot be done in place for this column type or index kind. Fix: do not accept the COPY fallback on a live table — it rewrites the whole table under a blocking lock. Reassess whether the change can be restructured, or schedule it in a maintenance window.

Creating index 'x' required more than 'innodb_online_alter_log_max_size' bytes of modification log (MySQL). Root cause: the table took more concurrent writes during the build than the online log could buffer. Fix: raise innodb_online_alter_log_max_size and retry, ideally during a lower-write window so the log accumulates more slowly.

What This Section Covers

The work splits into three procedures, one for each thing you do to an index on a live table. Building indexes with CREATE INDEX CONCURRENTLY covers the online build itself — running it outside a transaction, tuning the lock budget, watching the two-pass build through pg_stat_progress_create_index, and the MySQL ALGORITHM=INPLACE, LOCK=NONE equivalent. Cleaning up invalid indexes after a failed build covers the aftermath when that build fails: how to detect the indisvalid = false state, why the index still taxes writes, and the concurrent drop-and-retry that clears it. Dropping indexes online without blocking queries covers safe removal — proving an index is genuinely unused before you drop it, using DROP INDEX CONCURRENTLY, and avoiding the sequential-scan regression that follows dropping an index a plan silently depended on.

Each of these is a specific instance of the same discipline that governs the rest of the Zero-Downtime Schema Evolution Patterns section: change structure additively, verify before you trust it, and remove nothing until you have proven it is safe to remove.

Frequently Asked Questions

Why can’t CREATE INDEX CONCURRENTLY run inside a transaction? Because it is not a single atomic operation — it performs two separate table scans with a wait in between, and between those phases it must commit intermediate state and see rows written by other transactions. A surrounding transaction would prevent it from committing that intermediate state and from observing the concurrent writes it exists to capture. That is also why a failed concurrent build cannot cleanly roll back the way ordinary DDL does, and instead leaves an INVALID index behind.

What is an invalid index and why does it hurt performance? An invalid index is one whose concurrent build did not complete, so PostgreSQL marks indisvalid = false. The planner will not use it for reads because it may be incomplete, but the storage engine still maintains it on every INSERT, UPDATE, and DELETE — so you pay the full write cost of an index while getting none of its read benefit. It must be dropped explicitly with DROP INDEX CONCURRENTLY and the build retried; it never repairs itself.

How does MySQL online DDL differ from PostgreSQL’s concurrent index build? MySQL frames the same problem through ALGORITHM and LOCK clauses on ALTER TABLE: ALGORITHM=INPLACE, LOCK=NONE builds a secondary index while allowing concurrent reads and writes, buffering changes in an online log applied at the end. The key differences are that MySQL takes brief exclusive metadata locks at the start and end (PostgreSQL’s SHARE UPDATE EXCLUSIVE is held throughout but never blocks writes), that a failed MySQL build rolls back cleanly with no invalid-index state to clean up, and that MySQL DDL auto-commits rather than needing to run outside a transaction.

Is dropping an index a safe, non-blocking operation? The drop itself is cheap — it is metadata work, not a data rewrite — but a plain DROP INDEX still needs a lock on the table definition that queues behind in-flight queries, so use DROP INDEX CONCURRENTLY on PostgreSQL to avoid blocking. The real risk is not the lock but the plan: dropping an index some query silently relies on turns a fast index scan into a full sequential scan the instant it disappears. Always prove the index is genuinely unused before removing it.

Related

• Building Indexes with CREATE INDEX CONCURRENTLY
• Cleaning Up Invalid Indexes After a Failed Build
• Dropping Indexes Online Without Blocking Queries
• Expand and Contract Methodology
• Up to parent: Zero-Downtime Schema Evolution Patterns