Ordering: create an index online on a large table Intermediate

CREATE INDEX CONCURRENTLY does not block writes, but it is not safe on its own: inside a transaction it errors, a failure midway leaves an INVALID index behind, and rerunning it with IF NOT EXISTS silently skips it.

Order the 7 steps below. Press Check to see which steps are out of place and what breaks when they are. The behaviors in the explanations were tested on PostgreSQL 17.

Ordering

Adding an index to an 80-million-row orders table under live writes

The "customer's recent orders" API runs WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20, p99 900 ms. The orders table has 80 million rows, 3,000 writes/second at peak, and already has the index orders_customer_id_idx (customer_id). Migrations run with Knex, and the app role has statement_timeout = 30s.

Order the steps to get the new index without blocking writes for a single second and without leaving garbage behind.

Use ▲▼ to move each step, or tap a step’s ⠿ and then tap its new position. On a computer you can drag and drop.

1. 1⠿Check pg_index.indisvalid; if false, DROP INDEX CONCURRENTLY and create it againSELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'orders'::regclass▲▼
2. 2⠿Migration session: SET statement_timeout = 0, lock_timeout longer than the longest normal transaction; check there are no long-open transactionsSELECT pid, application_name, now() - xact_start FROM pg_stat_activity WHERE xact_start < now() - interval '5 min'▲▼
3. 3⠿ANALYZE if it is an expression index, then EXPLAIN to confirm the plan uses the new index and p99 dropsIndex Scan Backward using orders_customer_created_idx, no more Sort▲▼
4. 4⠿After a few days with idx_scan of orders_customer_id_idx not increasing (on the primary and replicas): DROP INDEX CONCURRENTLY▲▼
5. 5⠿EXPLAIN (ANALYZE, BUFFERS) the query with real parameters, settle on (customer_id, created_at) as the index neededCurrent plan: Bitmap Index Scan on orders_customer_id_idx → Sort → Limit▲▼
6. 6⠿Watch pg_stat_progress_create_index: phase, blocks_done / blocks_total, current_locker_pid▲▼
7. 7⠿CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at) in a migration with config.transaction = false▲▼

Check order↺ Shuffle again

For the lock each kind of DDL and index takes, see Table-level locks.