skip to content

In PostgreSQL, why use CREATE INDEX CONCURRENTLY on a busy 400 GB orders table, and what does it cost versus a plain CREATE INDEX?

level: middleimportance: must knowfreq 72%

answer

  1. writers versus readers during a build
  2. two table scans, several transactions
  3. waits out existing transactions
  4. no BEGIN ... COMMIT around it
  5. failure leaves something behind

basics

~20 s

Plain CREATE INDEX blocks every INSERT, UPDATE and DELETE on the table until it finishes; CONCURRENTLY keeps writes flowing but pays with two table scans, waits on older transactions, a longer build and an INVALID leftover if it fails.

solid answer

~40 s

A plain `CREATE INDEX` takes a `SHARE` lock on the table: reads continue, but every `INSERT`, `UPDATE` and `DELETE` on `orders` queues behind it for the whole multi-hour build. `CREATE INDEX CONCURRENTLY` takes `SHARE UPDATE EXCLUSIVE`, which writers do not conflict with, so traffic keeps going. The price: it registers the index as invalid, waits for transactions already writing the table, scans the table once to build, waits again, scans a second time to add rows that changed meanwhile, then waits for every older snapshot in the database before marking the index valid. It does more total work and takes noticeably longer, cannot run inside a transaction block, allows one concurrent build per table at a time, and if it fails it leaves an `INVALID` index that you must drop or rebuild.

code

sql · 8 lines
sql
-- Sent as its own statement: never inside BEGIN ... COMMIT
CREATE INDEX CONCURRENTLY orders_seller_created_idx
    ON marketplace.orders (seller_id, created_at);

-- Confirm the build finished and the index is usable
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'marketplace.orders'::regclass;

go deeper

for a junior

Remember that a normal CREATE INDEX blocks writes but not reads, and that adding CONCURRENTLY is how you index a live table without stopping INSERTs and UPDATEs.

for a middle

Walk through the phases: invalid catalog entry, wait for writers, first scan, ready for inserts, second validation pass, wait for older snapshots, valid. Say why each wait exists and why it cannot run in a transaction block.

for a senior

Show the production habits: run it outside the migration's transaction, one build per table, check indisvalid afterwards, look for long transactions that would stall it, and budget the extra I/O of two passes over 400 GB.

for a principal

Discuss when a short write outage with a plain build beats hours of concurrent build load, such as small tables or real maintenance windows, and how index rollouts fit the release process.

## Why a plain build hurts a live table A **plain `CREATE INDEX`** takes a `SHARE` lock on the table. That lock lets other sessions keep reading, but it conflicts with the `ROW EXCLUSIVE` lock that every `INSERT`, `UPDATE` and `DELETE` needs. On a 400 GB `marketplace.orders` table the build can run for hours, and for all of that time checkout, payment callbacks and status updates queue behind it. The build itself is efficient: **one table scan**, one sort, one write of the index. `CREATE INDEX CONCURRENTLY` (often shortened to CIC) exists to remove that write outage. It takes a `SHARE UPDATE EXCLUSIVE` lock instead, which does not conflict with writers. It still conflicts with schema changes, `VACUUM`, `ANALYZE` and a second concurrent build on the same table; the full lock-conflict table and `lock_timeout` belong to the schema-changes topic. ## What a concurrent build does, step by step Because writers keep changing the table while it works, PostgreSQL splits the build into several transactions and waits at the seams: 1. **Register the index** in the catalog `pg_index` with `indisvalid = false` and `indisready = false`, and commit. From now on every new transaction knows the index exists. 2. **Wait for writers**: every transaction that already had the table open for writing must finish, so nobody is still writing without knowing about the index. 3. **First scan**: take a snapshot, scan the whole table and build the index from the rows that snapshot sees. Then set `indisready = true`, so later `INSERT`s and `UPDATE`s start adding entries to it. 4. **Wait for writers again**, then take a new reference snapshot. 5. **Second (validation) pass**: scan the index, sort its entries, then scan the table and insert entries for rows that arrived or changed during the first scan. 6. **Wait for older snapshots**: every transaction in the same database whose snapshot predates the validation pass must finish. 7. **Mark the index valid** (`indisvalid = true`); the planner can now use it. ## What it costs | | Plain `CREATE INDEX` | `CREATE INDEX CONCURRENTLY` | |---|---|---| | Table lock | `SHARE` (blocks writes) | `SHARE UPDATE EXCLUSIVE` (writes continue) | | Table scans | one | two, plus an index scan and a sort | | Waits on other transactions | only to acquire its lock | three separate waits | | Inside `BEGIN ... COMMIT` | allowed | refused (SQLSTATE 25001) | | Several builds on one table at once | allowed | one concurrent build at a time | | On failure | rolled back, nothing left | an `INVALID` index remains | The extra passes mean **more total I/O and CPU** and a **noticeably longer** run, and that load lands on a production server while it serves traffic. The waits mean one forgotten long-running transaction can hold the build at a phase for as long as it lives. Because the command commits internal transactions of its own, it **cannot run inside a transaction block**: PostgreSQL answers `ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block`. ## Practical rules for the orders table - **Send it as its own statement.** Many migration frameworks wrap each migration in a transaction; switch that off for the migration holding the build, and keep nothing else in it. - **Build one index at a time per table.** A second concurrent build on `orders` waits for the first. - **Check validity afterwards.** Query `pg_index.indisvalid` or look for `INVALID` in psql's `\d marketplace.orders`; a failed build leaves its index behind and it must be dropped or rebuilt. - **Look for long transactions before starting**: an idle-in-transaction session in the same database can stall the final wait. - **Expect a short delay before use.** Even after the command returns, transactions that predate the build may not be able to use the new index. - **Partitioned tables** need a per-partition approach, because a concurrent build on the partitioned parent is not supported. - **Temporary tables** ignore `CONCURRENTLY`: no other session can see them, so the cheaper plain build is used. ## When a plain build is still the right call A small table that indexes in seconds, a table nobody writes to, or a maintenance window with traffic stopped are all cases where the plain build's single scan wins. On a busy 400 GB table during business hours the concurrent build is the standard tool, and the costs above are what you accept for keeping writes alive.

  • What happens if a migration tool wraps CREATE INDEX CONCURRENTLY in BEGIN ... COMMIT?
    PostgreSQL rejects it at once with `CREATE INDEX CONCURRENTLY cannot run inside a transaction block` (SQLSTATE 25001, `active_sql_transaction`). The command commits several internal transactions of its own, so it must arrive as a top-level statement. Most migration frameworks have a per-migration switch to drop their wrapping transaction; that migration should contain only the concurrent build.
  • Once CREATE INDEX CONCURRENTLY returns, can every query use the new index immediately?
    Not necessarily. The manual warns that in the worst case the index cannot be used by transactions that predate the start of the build, because the table may hold row versions those older snapshots can see that the index does not describe. New transactions can use it; long-running ones that began before the build will plan without it until they end.
  • How do you add an index concurrently to a partitioned orders table?
    Concurrent builds on a partitioned parent are not supported in PostgreSQL 18. Build the index concurrently on each partition, then run a plain `CREATE INDEX` on the parent: it finds the matching partition indexes and attaches them, so the parent step is metadata-only and writes are locked out only briefly. The partitioning details live with the partitioning topic.

  • CREATE INDEX CONCURRENTLY takes no lock on the table at all.
  • A plain CREATE INDEX blocks SELECT queries on the table as well as writes.
  • CONCURRENTLY is as fast as a plain build, so there is no reason not to always use it.
  • Wrap it in a transaction so a failure rolls back cleanly.
  • If CREATE INDEX CONCURRENTLY fails, nothing is left behind.