This resume is also available in English.

6. Deploying the system4 / 5 · All columns

Columns › 6. Deploying the system › Part 24

Adding Indexes Safely: Indexing Large Tables Without an Outage

正確加索引:大型資料表上線不出事

Adding an index to a large table that is taking orders can block writes and take the whole service down if one step is wrong. This article starts from how indexes work and the evidence in EXPLAIN, designs the right index, and ships it safely with CONCURRENTLY, lock_timeout and a retrying Go migration runner.

  • 資料庫
  • PostgreSQL
  • 索引
  • Go
In this article
  1. What this article solves
  2. Step 1: How indexes work, just enough to decide
  3. Step 2: Finding the right index from evidence
  4. Step 3: Designing the index correctly
  5. Step 4: Adding an index to a large live table without an incident
  6. Step 5: A safe migration runner in Go
  7. Step 6: How MySQL (InnoDB) differs
  8. Step 7: Pre-flight checklist, rollback plan and an incident timeline
  9. Common pitfalls
  10. Summary
  11. Next

The previous article, “From Monolith to Microservices: Evolving the Architecture at 100K, 1M and 10M Scale”, noted at the 100K stage that a slow database query is usually a missing index, and that before adding a cache or more machines you should get the indexes and queries right: it is the cheapest step and often the most effective one. This article does that job properly: when to add an index, what kind of index to add, and the part most easily overlooked — how to add an index to a table with tens of millions of rows that is taking orders right now, without taking the service down.

What this article solves

“If a query is slow, add an index” is only half right. What commonly happens is this:

  • An index is added on a hunch and the query is just as slow, because that query cannot use it; the table, meanwhile, has gained a structure that every write must maintain.
  • The columns of a composite index are in the wrong order, so only some of the queries can use it.
  • Someone runs a plain CREATE INDEX on a large table at peak time, writes to the whole table are blocked for minutes, and every order times out.
  • They switch to CREATE INDEX CONCURRENTLY, but the migration tool wraps it in a transaction and it fails outright; or the build fails halfway and leaves an INVALID index nobody notices, which every write then maintains for nothing.
  • A DDL statement that should take a few milliseconds gets stuck waiting for a lock, every query behind it forms a long queue, and the connection pool runs dry within seconds.

This article uses the online shop that runs through the series (example.com/shop, with the services api-gateway, order, payment and inventory). The database is PostgreSQL, and the star is the orders table built in “database/sql, Transactions and Connection Pools in Go”. The order is:

  1. How indexes work, just enough to make decisions.
  2. Finding the index you really need from evidence (pg_stat_statements, the slow query log, EXPLAIN (ANALYZE, BUFFERS)).
  3. Designing the index correctly: column order in composite indexes, covering indexes, partial indexes, expression indexes, unique indexes, and removing indexes nobody uses.
  4. The core: adding an index to a large live table without an incident.
  5. A safe migration runner in Go that handles running outside a transaction, timeouts, retries and failed leftovers.
  6. How MySQL (InnoDB) differs.
  7. A pre-flight checklist, a rollback plan, and an incident timeline of what happens without lock_timeout.

Tip: the SQL in this article targets PostgreSQL. The EXPLAIN output was produced on a development machine with five million rows of test data; the numbers are only for comparing before and after, and say nothing about the performance of any production system.

Step 1: How indexes work, just enough to decide

B-tree: a sorted directory you can search quickly

PostgreSQL's default index is the B-tree. Think of it as the index at the back of a book: entries sorted by a “key”, each pointing at where the data actually lives in the table (PostgreSQL calls this a TID: which data page, and which item on that page). The upper levels of the tree only navigate; the leaf level at the bottom is chained together in order. So a B-tree is good at three things:

  • Equality lookups: WHERE user_id = 42 is found a few levels down from the root.
  • Range lookups: WHERE created_at >= ... AND created_at < ... finds the starting point and then reads along the leaves.
  • Sorting: the index is already sorted, so ORDER BY created_at DESC LIMIT 20 can read twenty entries from one end and stop, without fetching and sorting everything.

The table itself (the heap) has no order. Without a usable index, the database can only do a sequential scan (Seq Scan): read every page of the table and check the condition row by row.

The key of a composite index is made of several columns, sorted like a phone book: by the first column, then by the second column where the first is equal, and so on. This decides which queries it can help, which Step 3 returns to.

Composite index (user_id, status, created_at DESC) Root Internal node: navigation Internal node 41 · paid 11-02 42 · cancelled 10-30 42 · paid 11-14 42 · paid 11-09 42 · paid 10-21 42 · pending 11-15 user_id = 42 AND status = 'paid': one contiguous run already newest first by created_at; LIMIT stops when it has enough TID The table (heap) Rows have no order; without a usable index, every page must be read (Seq Scan)

Figure: the leaves of the composite index (user_id, status, created_at DESC) are in order; user_id = 42 AND status = 'paid' falls in one contiguous run, already sorted newest first by created_at, so LIMIT 20 reads the first twenty entries and stops.

Indexes are not free

Every index is an extra data structure that the database must keep consistent with the table:

  • Write amplification. Every INSERT adds an entry to every index; an UPDATE that changes an indexed column adds new entries to the index as well. With eight indexes on a table, one write touches nine places, and writes the matching WAL (write-ahead log), which every read replica must replay too.
  • Storage and cache. Indexes take disk space and compete for shared memory (shared buffers) and the operating system's page cache. In the test data above, the orders table itself is about 330 MB, and one (user_id, id DESC) index is about 190 MB. The more indexes, the harder it is to keep the data that is really used in memory.
  • VACUUM and HOT updates. A PostgreSQL UPDATE writes a new version of the row. If the update changes no indexed column and the same data page has room, it can be a HOT (heap-only tuple) update: the old and new versions are linked inside the data page and no index has to change. Conversely, indexing a frequently updated column (such as status or updated_at) takes HOT away from those updates: every one of them writes to the index, and VACUUM has more to clean up. Columns used in the WHERE condition of a partial index count as indexed columns too.
  • The planner's choices. More indexes give the query planner more paths to consider; a more common problem is the planner choosing the wrong index based on its statistics. When the statistics are stale (after heavy writes, before ANALYZE), the estimated row counts are far from reality and the chosen plan can be poor.

pg_stat_user_tables shows how many of a table's updates are HOT:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';

Before indexing a frequently updated table, note this ratio, and look at it again afterwards.

When an index helps and when it does not

Selectivity. The more rows a condition filters out, the more useful an index. Ninety-five percent of the orders rows are paid, so even with an index the planner chooses a sequential scan for WHERE status = 'paid': when most rows come back, jumping to the data pages one entry at a time through the index is slower than reading the whole table in order. Conversely, status = 'pending' is a small minority, and an index is very useful — Step 3's partial index is designed for exactly this case.

Small tables. A table of a few dozen pages is read in one sequential pass; an index only adds a lookup. A Seq Scan on a small table is nothing to worry about.

Functions or casts on the column. The index stores the column's raw values. When the condition is created_at::date = '2026-11-01', the database must compute created_at::date for every row before comparing, so an index on created_at cannot be used:

-- cannot use the created_at index: every row has to be cast first
EXPLAIN SELECT id FROM orders WHERE created_at::date = '2026-11-01';
--   Parallel Seq Scan on orders
--     Filter: ((created_at)::date = '2026-11-01'::date)

-- rewritten as a range, the index can be used
EXPLAIN SELECT id FROM orders
WHERE created_at >= '2026-11-01' AND created_at < '2026-11-02';
--   Index Scan using orders_created_at_idx on orders

Implicit casts. When types do not match, the side that gets cast may be the column. user_id is a BIGINT, but if the condition compares it with a NUMERIC, PostgreSQL casts user_id to NUMERIC on every row before comparing, and again the index cannot be used:

EXPLAIN SELECT id FROM orders WHERE user_id = 42::numeric;
--   Parallel Seq Scan on orders
--     Filter: ((user_id)::numeric = '42'::numeric)

EXPLAIN SELECT id FROM orders WHERE user_id = 42;
--   Index Only Scan using orders_user_id_idx on orders
--     Index Cond: (user_id = 42)

With Go's database/sql and placeholders, the server usually infers the parameter type from the column, so this trap is less common; the usual sources are casts written by hand in SQL, or an ORM or query builder giving a parameter a different type.

LIKE with a leading wildcard. LIKE 'abc%' has a fixed prefix and can, in principle, be found as a B-tree range; LIKE '%abc' or '%abc%' has no prefix to locate, and a B-tree cannot help. For “contains this text” searches, use the pg_trgm extension with a GIN index, or hand the job to a dedicated search engine. Note also that for text columns with a non-C collation, even LIKE 'abc%' needs an index built with the text_pattern_ops operator class.

OR. A condition like WHERE user_id = 42 OR total = 1234 usually cannot be served by one composite index. The planner can combine two separate indexes with a BitmapOr, but only if both sides have a usable index; if either side does not, the whole table is scanned. Sometimes rewriting it as a UNION of two queries is clearer.

Step 2: Finding the right index from evidence

Do not guess. Which index to add should be decided by three kinds of evidence: which queries cost the most time, what their execution plans look like now, and what the plans become once the index is added.

Finding the most expensive queries: pg_stat_statements

The pg_stat_statements extension accumulates, per “normalised query text” (parameters replaced with $1, $2), the number of calls, total time, mean time and data pages read for each kind of query. It must be loaded through shared_preload_libraries (which needs a database restart), and then the extension is created in the database; managed PostgreSQL services usually have it enabled already or let you turn it on in their settings.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid,
       calls,
       round(total_exec_time)                AS total_ms,
       round(mean_exec_time::numeric, 2)     AS mean_ms,
       rows,
       shared_blks_hit,
       shared_blks_read,
       left(query, 80)                       AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total_exec_time first, not mean_exec_time: a query averaging 2 milliseconds that runs three thousand times a second weighs far more on the database than a report averaging 3 seconds that runs once a day. Queries with a high mean and few calls deserve a look too, but the ones with the most total time come first. A high shared_blks_read means the query often reads data from disk (or the operating system's cache), which usually means it reads many pages it does not need.

The slow query log and auto_explain

pg_stat_statements tells you which kind of query it is, but not which parameters were used on the occasions it was especially slow. The slow query log fills that gap:

-- write statements slower than 500 milliseconds to the database log (new connections only)
ALTER DATABASE shop SET log_min_duration_statement = '500ms';

The auto_explain extension goes further and writes the execution plans of slow queries to the log too, which suits queries that are “slow only for some parameters”. Turning on auto_explain.log_analyze adds some measurement overhead to every query, so evaluate it first in production, or enable it only for a period.

Reading EXPLAIN (ANALYZE, BUFFERS)

Take one of the shop's background jobs as an example: every minute it finds orders created more than thirty minutes ago that are still unpaid, cancels them and releases the stock. The query is:

SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '30 minutes'
ORDER BY created_at
LIMIT 100;

EXPLAIN shows only the plan the planner estimates; EXPLAIN (ANALYZE, BUFFERS) actually runs the query and adds the real time, row counts and data pages read. Because it really runs, wrap UPDATE and DELETE in a transaction and roll it back: BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;.

Before the index, the plan on the five-million-row orders table:

Limit  (cost=84784.96..84796.63 rows=100 width=32) (actual time=86.613..88.846 rows=100 loops=1)
  Buffers: shared hit=997 read=41113
  ->  Gather Merge  (cost=84784.96..85287.13 rows=4304 width=32) (actual time=86.611..88.839 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=83784.94..83790.32 rows=2152 width=32) (actual time=84.628..84.630 rows=67 loops=3)
              Sort Key: created_at
              Sort Method: top-N heapsort  Memory: 32kB
              ->  Parallel Seq Scan on orders  (cost=0.00..83702.69 rows=2152 width=32) (actual time=84.267..84.491 rows=1568 loops=3)
                    Filter: ((status = 'pending'::text) AND (created_at < (now() - '00:30:00'::interval)))
                    Rows Removed by Filter: 1665098
                    Buffers: shared hit=923 read=41113
Planning Time: 0.341 ms
Execution Time: 88.870 ms

Read it step by step:

  1. Read inside out, bottom up. The most deeply indented node runs first and passes its result up a level. Here the bottom is a Parallel Seq Scan: three processes (the leader plus two workers) scan the whole table in parallel.
  2. cost is an estimate, actual is real. cost=startup..total is an abstract unit the planner uses to compare plans, not milliseconds; actual time=first row..last row is in milliseconds.
  3. loops. The actual time and rows are averages per loop; multiply by loops for the total. rows=1568 loops=3 means the three processes found about 4,700 matching rows in all.
  4. Compare estimated and actual rows. The estimate is rows=2152 (per process) and the actual is 1568, close enough to show the statistics are accurate. If the estimate were 10 rows and the actual a hundred thousand, the problem would be the statistics, not the index — run ANALYZE first.
  5. Rows Removed by Filter. Each process threw away about 1.66 million rows; the three together are the whole table. Reading five million rows for 100 results is the clearest signal: an index that locates the rows directly is missing.
  6. Buffers. shared hit counts data pages found in shared memory, and read counts pages that had to be read in (possibly from the operating system cache, possibly really from disk). Just over forty-one thousand pages, about 330 MB, is the whole table.
  7. The Sort and Limit at the top. Because the data has no order, each process must sort its matching rows (top-N heapsort) before the results are merged and the first 100 taken.

This query only needs the small slice of data that is “pending, ordered by creation time”. After adding a partial index (explained in Step 3):

CREATE INDEX CONCURRENTLY orders_pending_created_idx
    ON orders (created_at)
    WHERE status = 'pending';
Limit  (cost=0.29..4.16 rows=100 width=32) (actual time=0.008..0.020 rows=100 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using orders_pending_created_idx on orders  (cost=0.29..200.45 rows=5166 width=32) (actual time=0.008..0.016 rows=100 loops=1)
        Index Cond: (created_at < (now() - '00:30:00'::interval))
        Buffers: shared hit=3
Planning Time: 0.636 ms
Execution Time: 0.030 ms

Three data pages and 0.03 milliseconds, with no sort node (the index is already ordered by created_at), and the condition status = 'pending' has disappeared — this index contains only pending orders, and the planner knows it need not check again. The index is only 128 kB, while the (user_id, id DESC) index covering every order is 190 MB.

Before the index With the partial index Limit (100 rows) Gather Merge ← Sort each process sorts, then merge Parallel Seq Scan on orders three processes read the whole table Rows Removed by Filter ≈ 5,000,000 Buffers ≈ 42,110 pages (≈ 330 MB) Execution Time ≈ 89 ms the whole table read for 100 rows Limit (100 rows) Index Scan using orders_pending_created_idx holds only status = 'pending' already sorted by created_at, no sort stops at the 100th row Buffers = 3 pages Execution Time ≈ 0.03 ms the index itself is only 128 kB

Figure: the same query; before the index it is a parallel sequential scan of the whole table that filters out about five million rows and then sorts; with the partial index it reads 100 rows in order, and the pages read drop from about forty-one thousand to 3.

Tip: always run EXPLAIN (ANALYZE, BUFFERS) again after adding the index to confirm the plan really uses it. Ideally use parameters that have actually occurred in production, or test on a copy close to production size: the same query on a development database with a thousand rows may get a completely different plan.

Step 3: Designing the index correctly

Column order in a composite index: equality first, then range and sort

The “My orders” page lists one user's orders in one status, newest first, 20 per page:

SELECT id, status, total, created_at
FROM orders
WHERE user_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 20;

The rules for column order:

  1. Columns with equality conditions go first (user_id, status). Equality conditions narrow the range to one contiguous run of the index.
  2. Then the column used for a range or for sorting (created_at DESC). Within the run where the earlier columns are equal, the data is already sorted by this column, so the sort and LIMIT can use it directly.
  3. Columns after a range condition help little. Once a column is used with a range condition, the columns after it are no longer contiguous in the index and can only be filtered row by row.

So the index is (user_id, status, created_at DESC, id DESC). With created_at before status, status = $2 could only be filtered row by row over the entries scanned; with status first, its selectivity is too low, and it helps less for a single user's query.

Composite indexes also follow the leftmost prefix rule: (user_id, status, created_at) can serve WHERE user_id = $1 and WHERE user_id = $1 AND status = $2, but usually not a query with only WHERE status = $2 or only ORDER BY created_at. From PostgreSQL 18, a B-tree can in some cases “skip” leading columns (skip scan), but that only pays off when the leading column has few distinct values; do not base a design on it.

Paginate with a keyset, and make the sort match the index. OFFSET 10000 makes the database read ten thousand rows and throw them away, getting slower the further you go. Keyset pagination remembers the (created_at, id) of the last row on the previous page and starts the next page after it:

SELECT id, status, total, created_at
FROM orders
WHERE user_id = $1 AND status = $2
  AND (created_at, id) < ($3, $4)        -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

id is there to make the order unique (several orders can share the same second), so it belongs in the index too, in the same direction as the ORDER BY. If the index were only (user_id, status, created_at DESC), the database could still use it but would need an extra “sort by id” step (the plan shows an Incremental Sort); when the index matches the ORDER BY exactly, (created_at, id) < ($3, $4) becomes part of the index condition and the database reads twenty entries straight down from that point.

Covering indexes: INCLUDE

If every column a query needs is in the index, PostgreSQL can do an Index Only Scan without going back to the table for the rows. The query above also needs total, which is neither a filter nor a sort key, so there is no point in making it part of the key; INCLUDE attaches it to the index's leaf entries:

CREATE INDEX CONCURRENTLY orders_user_status_created_idx
    ON orders (user_id, status, created_at DESC, id DESC)
    INCLUDE (total);
Limit  (actual time=0.004..0.006 rows=16 loops=1)
  Buffers: shared hit=6
  ->  Index Only Scan using orders_user_status_created_idx on orders  (actual time=0.004..0.005 rows=16 loops=1)
        Index Cond: ((user_id = 4242) AND (status = 'paid'::text) AND (ROW(created_at, id) < ROW('2026-10-01 00:00:00+08'::timestamp with time zone, 4000000)))
        Heap Fetches: 0

Heap Fetches: 0 means the table was not touched at all. Index Only Scans depend on the visibility map: data pages that were just updated heavily and not yet processed by VACUUM still need a trip to the table to check visibility, and Heap Fetches rises. Use INCLUDE sparingly too: included columns make the index bigger, and wide columns (long text, JSON) are poor candidates.

Partial indexes: index only the small part you need

Step 2's orders_pending_created_idx is a partial index: WHERE status = 'pending' makes the index contain only pending orders. Most orders are paid or cancelled quickly, so the index only ever holds a small share of the data, which keeps it small and fast; paid orders never appear in this index and need no maintenance in it.

For the planner to use a partial index, the query's condition must imply the index's condition. WHERE status = 'pending' does; WHERE status = $1 does not reveal the parameter's value at planning time, so it may not be usable (especially with the generic plan of a prepared statement). Partial indexes therefore suit queries whose condition is a fixed constant, like this background job.

The cost mentioned earlier applies here as well: status appears in the partial index's condition, so the update that moves an order from pending to paid is not a HOT update. For this table it is a trade worth making, but you should know it happens.

Expression indexes: index a computed value

If the business really needs to query orders “by date in Taipei time”, the expression itself can be indexed, and queries must use exactly the same expression:

CREATE INDEX CONCURRENTLY orders_created_tw_date_idx
    ON orders (((created_at AT TIME ZONE 'Asia/Taipei')::date));

SELECT id FROM orders
WHERE (created_at AT TIME ZONE 'Asia/Taipei')::date = '2026-11-01';

An index expression must be IMMUTABLE (the same input always gives the same result). For a TIMESTAMPTZ, created_at::date depends on the connection's TimeZone setting, so indexing it directly fails with functions in index expression must be marked IMMUTABLE; naming the time zone explicitly removes the problem. Still, if the condition can be rewritten as a range, prefer the rewrite and skip the extra index.

Unique indexes: let the database guard idempotency keys

“Idempotency and Distributed Locks” explained that retried requests are recognised by an idempotency key, and that the last line of defence is a unique constraint in the database. Suppose orders gains a client-generated idempotency_key column, and one user's key may create only one order:

CREATE UNIQUE INDEX CONCURRENTLY orders_user_idem_uidx
    ON orders (user_id, idempotency_key);

Old orders whose idempotency_key is NULL are unaffected: by default PostgreSQL treats multiple NULLs as distinct from each other. Adding a unique index to a table that already has data also means dealing with existing duplicates first, which Step 4 covers.

Avoid duplicate and redundant indexes

  • Exact duplicates: the same columns in the same order created twice, often by different people in different migrations.
  • Redundant prefixes: (user_id, id DESC) already exists and someone adds (user_id). The former already serves queries on user_id alone; the latter only adds write cost. The exceptions are unique indexes and primary keys, which carry a constraint and cannot be judged by their columns alone.
  • Never used: added for a feature that has since been retired, while the index lives on.

Queries to find candidates:

-- indexes never scanned since statistics began (excluding unique indexes and primary keys)
SELECT s.indexrelid::regclass                    AS index_name,
       s.relid::regclass                         AS table_name,
       s.idx_scan,
       s.last_idx_scan,                          -- PostgreSQL 16 and later
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- indexes whose key is a prefix of (or equal to) another index's: candidates only; check sort direction and operator class by hand
SELECT a.indrelid::regclass   AS table_name,
       a.indexrelid::regclass AS candidate,
       b.indexrelid::regclass AS covered_by,
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS size
FROM pg_index a
JOIN pg_index b ON b.indrelid = a.indrelid AND b.indexrelid <> a.indexrelid
WHERE (b.indkey::text = a.indkey::text OR b.indkey::text LIKE a.indkey::text || ' %')
  AND a.indpred IS NULL AND b.indpred IS NULL
  AND a.indexprs IS NULL AND b.indexprs IS NULL
  AND NOT a.indisunique
  AND NOT a.indisprimary;

When looking at idx_scan = 0, check three things: when the statistics started accumulating (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();; a restart or a manual reset can zero the numbers); whether the observation period covered infrequent but important jobs such as month-end or annual reports; and the statistics on every read replica — queries on a replica count only in that replica's own pg_stat_user_indexes, so a 0 on the primary does not mean nobody uses the index on a replica.

Step 4: Adding an index to a large live table without an incident

This is the core of the article. The indexes designed above now have to go onto an orders table with tens of millions of rows that is taking orders.

Why a plain CREATE INDEX blocks writes

A plain CREATE INDEX takes a SHARE lock on the table. That does not conflict with the ACCESS SHARE taken by SELECT, so reads carry on; but it conflicts with the ROW EXCLUSIVE taken by INSERT, UPDATE and DELETE, so every write waits for the whole build. On a small table the build takes seconds and nobody notices; on a table with tens of millions of rows it can take minutes to tens of minutes, and orders, payment callbacks and stock deductions all stall, requests time out and the connection pool runs dry.

Worse: a DDL waiting for a lock makes everyone behind it wait

PostgreSQL queues lock requests. While a DDL statement waits for a lock, later lock requests that conflict with it generally queue behind it, even if they do not conflict with whoever currently holds the lock.

Picture this: a report query has been reading orders for twenty minutes (holding ACCESS SHARE). Someone then runs an operation that needs an ACCESS EXCLUSIVE lock — say a DROP INDEX without CONCURRENTLY, or an ALTER TABLE ... ADD CONSTRAINT — even though it only takes a few milliseconds itself. It conflicts with the report, so it waits; then every new SELECT ... FROM orders needs ACCESS SHARE, which conflicts with the queued ACCESS EXCLUSIVE, so it queues behind it. The result: a DDL statement that should take milliseconds stops all reads and writes on the table until the report finishes.

The lock queue on the orders table Holds the lock a 15-minute report ACCESS SHARE DDL waiting e.g. a plain DROP INDEX needs ACCESS EXCLUSIVE Every later request new reads and writes conflicts with the DDL waits for it queues behind Pool exhausted timeouts, retries double No lock_timeout: the DDL waits for the report a millisecond operation stops the table for minutes SET lock_timeout = '3s' After 3 seconds without the lock, the DDL fails (55P03) and the queue moves on The runner backs off and retries; prefer CONCURRENTLY, which blocks no reads or writes

Figure: a long transaction holds ACCESS SHARE; the DDL waiting for ACCESS EXCLUSIVE queues behind it; every later read and write that conflicts with the DDL queues behind the DDL, and the connection pool fills with waiting requests. With lock_timeout set, the DDL gives up when it cannot get the lock and the queue drains at once.

A plain CREATE INDEX has the same problem: the SHARE lock it wants conflicts with writes, so if a transaction that wrote to orders has not committed (a program that forgot to commit, or a stuck batch job), CREATE INDEX waits for it, and every write after that queues behind CREATE INDEX.

The fix is lock_timeout: it caps how long each lock acquisition may wait; if the lock is not granted in time, the statement gives up with an error (SQLSTATE 55P03), the queue drains, and you try again later. It counts only time spent waiting for locks, which is different from statement_timeout, which caps the whole statement. Both can be set for the current connection only:

SET lock_timeout = '3s';         -- wait at most 3 seconds for a lock
SET statement_timeout = '60s';   -- the whole statement at most 60 seconds

The official documentation also advises against setting either in postgresql.conf, because it would affect every session. Set them on the migration's connection.

CREATE INDEX CONCURRENTLY: building without blocking writes

With CONCURRENTLY, PostgreSQL takes a SHARE UPDATE EXCLUSIVE lock instead, which does not conflict with reads or writes, so INSERT, UPDATE and DELETE carry on during the build:

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_user_status_created_idx
    ON orders (user_id, status, created_at DESC, id DESC)
    INCLUDE (total);

Learn its costs and limits all at once:

  • It cannot run inside a transaction block. BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT; fails straight away with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Many migration tools wrap every migration file in a transaction by default, so check that the tool has a “do not wrap this migration in a transaction” setting. When several statements are sent at once, PostgreSQL also runs them in one implicit transaction, with the same error — put only one CONCURRENTLY statement in a migration.
  • It is slower and heavier. It scans the table twice and waits for old transactions in between, so the total work exceeds a plain build and it takes noticeably longer; the extra CPU and I/O can also slow other queries.
  • It waits for every old transaction. Before each scan it waits for every transaction that has modified the table to finish; after the second scan it also waits for every transaction whose snapshot predates the second scan. A transaction left open and uncommitted for two hours makes CONCURRENTLY wait there for two hours. The wait does not block normal reads and writes, but the migration is stuck.
  • Only one concurrent index build per table at a time. Its SHARE UPDATE EXCLUSIVE lock also conflicts with VACUUM, ANALYZE and other DDL, so autovacuum on the table is postponed during the build.
  • A failure leaves an INVALID index. The next section covers this.
  • Not supported on the parent of a partitioned table. Build the index CONCURRENTLY on each partition, then create it on the parent last (at that point it is a metadata-only operation).
The phases of CREATE INDEX CONCURRENTLY 1. Register INVALID in the catalog 2. Wait for writers transactions that modified it 3. First scan building index 4. Wait for writers before validation 5. Second scan adds changes made meanwhile 6. Old snapshots waiting for old snapshots 7. Marked VALID Any step fails: deadlock, duplicate, cancel, lock_timeout the command errors, but the half-built index stays INVALID index: unused by queries, still maintained and IF NOT EXISTS skips the rebuild because of it Clean up and retry check pg_index.indisvalid DROP INDEX CONCURRENTLY and build it again Note: a build in progress is INVALID too; check none is running

Figure: an INVALID index is first registered in the system catalog, then the build waits for writers, builds in a first scan, waits for writers again, validates in a second scan and waits for old snapshots, and only then marks the index VALID; a failure at any step leaves an INVALID index, which must be removed with DROP INDEX CONCURRENTLY before retrying.

After a failure: detecting and cleaning up INVALID indexes

When CREATE INDEX CONCURRENTLY fails halfway — a deadlock, a uniqueness violation, a cancellation, lock_timeout firing — the half-built index is not removed automatically; it is left behind as an INVALID index. Queries ignore it (it may be incomplete), but every write still maintains it; if it is a unique index, it may even keep enforcing uniqueness. In psql, \d orders marks it INVALID; with SQL:

SELECT c.relname              AS index_name,
       i.indrelid::regclass   AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

Be careful: an index that is still being built with CONCURRENTLY also has indisvalid = false until it finishes. When you see an INVALID index, first make sure no build is in progress (pg_stat_progress_create_index is empty) before concluding it is a failed leftover.

The officially recommended fix is to drop it and build again:

DROP INDEX CONCURRENTLY IF EXISTS orders_user_status_created_idx;
CREATE INDEX CONCURRENTLY orders_user_status_created_idx
    ON orders (user_id, status, created_at DESC, id DESC)
    INCLUDE (total);

(REINDEX INDEX CONCURRENTLY can also rebuild the INVALID index directly.)

There is a very easy trap here: when CREATE INDEX CONCURRENTLY IF NOT EXISTS sees an INVALID index with the same name, it assumes the index exists, skips the build and reports success. The migration tool then records the version as done, while the table has only a useless index that still has to be maintained. So before retrying, always check for and drop an INVALID index of the same name — the Go program in Step 5 does exactly that.

Also, lock_timeout does not fire only when the lock is first requested; it can also fire while CONCURRENTLY is waiting for old transactions, again leaving an INVALID index (the integration test in Step 5 reproduces this on PostgreSQL 17). So when you set lock_timeout for CONCURRENTLY, cleaning up after a failure is essential, and the value should be much longer than for ordinary DDL.

Unique indexes: find and fix duplicates first

CREATE UNIQUE INDEX CONCURRENTLY fails on existing duplicates with could not create unique index, leaving an INVALID index behind. Worse, uniqueness is already enforced against other transactions once the second scan begins, so during the build (and even when the build eventually fails), the application's writes may already receive uniqueness violations; and if the failure happens in the second scan, the INVALID leftover keeps enforcing uniqueness.

So find the duplicates first, decide how to handle them (merge, keep the newest, or set the old rows' keys to NULL), and only then build:

SELECT user_id, idempotency_key, count(*)
FROM orders
WHERE idempotency_key IS NOT NULL
GROUP BY user_id, idempotency_key
HAVING count(*) > 1;

The application can still write new duplicates during the build, so first ship a version that checks the idempotency key before writing (or that handles it with ON CONFLICT), then build the index.

Adding a constraint using an existing unique index

In PostgreSQL a unique index and a unique constraint (UNIQUE constraint) behave almost the same, but the constraint appears in the table definition, and ORMs and ON CONFLICT ON CONSTRAINT rely on it. A direct ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...) builds the whole index while holding a strong lock; the better way is to build the unique index with CONCURRENTLY first, then “promote” it to a constraint:

SET lock_timeout = '3s';
ALTER TABLE orders
    ADD CONSTRAINT orders_user_idem_key UNIQUE USING INDEX orders_user_idem_uidx;

This step does not rescan the data and completes quickly, but ALTER TABLE still needs an ACCESS EXCLUSIVE lock, so set lock_timeout here too. The index is renamed to the constraint's name. The restrictions: the index must be a B-tree with default sort order, with no expressions, and not partial.

Before running it: timing, space, memory and replicas

Pick an off-peak window. CONCURRENTLY does not block writes, but two full-table scans and the sort consume CPU and I/O, and the WAL volume surges.

Check disk space. You need room for the new index itself, for temporary sort files when the sort exceeds memory, and for the WAL generated during the build. Estimate the new index's size from a similar existing index (in the five-million-row test data above, an index on two BIGINT columns is about 190 MB); a more reliable way is to build it once on a copy close to production size.

SELECT pg_size_pretty(pg_table_size('orders'))   AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

maintenance_work_mem. Index build speed depends heavily on it: the larger it is, the more of the sort can happen in memory. You can raise it on the migration's connection only (for example SET maintenance_work_mem = '1GB';), but not beyond the memory the machine really has, or it starts swapping. For index types that support parallel builds (B-tree among them), the value is the limit for the whole build, shared by all workers.

Replicas and replication lag. With physical (streaming) replication, the WAL produced by building the index on the primary is replayed on every read replica, which writes an index of the same size, so replication lag may rise during the build; features that read from replicas must tolerate that lag (the previous article covered handling replication lag). Watch it on the primary:

SELECT application_name, client_addr, state, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_behind
FROM pg_stat_replication;

Logical replication does not replicate DDL; if a subscriber needs the same index, create it there separately.

Long transactions. Before starting, look for long-running transactions, which would keep CONCURRENTLY waiting:

SELECT pid, now() - xact_start AS xact_age, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;

Watching progress: pg_stat_progress_create_index

During the build, query from another connection:

SELECT p.pid,
       p.phase,
       round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS blocks_pct,
       p.lockers_done, p.lockers_total,
       p.current_locker_pid,
       a.query AS waiting_on
FROM pg_stat_progress_create_index p
LEFT JOIN pg_stat_activity a ON a.pid = p.current_locker_pid;

phase moves through initializing, waiting for writers before build, building index, waiting for writers before validation, index validation: scanning index, index validation: sorting tuples, index validation: scanning table and waiting for old snapshots. If it sits in one of the waiting for ... phases for a long time, current_locker_pid is the process it is waiting for, and waiting_on shows what that process is doing — usually that transaction someone forgot to commit.

Index bloat: REINDEX CONCURRENTLY

After many updates and deletes, a B-tree accumulates half-empty pages and grows larger than it needs to be; this is bloat. The pgstattuple extension's pgstatindex('index name') shows the average leaf density. To rebuild:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

It also cannot run in a transaction and is also slower. On failure it leaves an INVALID index whose name ends in _ccnew or _ccold: _ccnew is the half-built new index, which you drop before retrying; _ccold means the new index was built and only the old one could not be dropped, so just drop it.

Dropping indexes safely

A plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, which is exactly the kind of operation that makes everyone queue, as described above. To drop, use:

SET lock_timeout = '3s';
DROP INDEX CONCURRENTLY IF EXISTS orders_user_only_idx;

It also cannot run in a transaction and drops only one index at a time; indexes backing a UNIQUE or PRIMARY KEY constraint cannot be dropped this way (and usually should not be dropped at all).

Before dropping:

  1. Confirm that idx_scan has not increased over a long enough period on the primary and on every replica.
  2. Save the creation statement with pg_get_indexdef as your rollback plan:
SELECT pg_get_indexdef('orders_user_only_idx'::regclass);
-- CREATE INDEX orders_user_only_idx ON public.orders USING btree (user_id)

If you drop the wrong one, recreate it with that statement plus CONCURRENTLY — but on a table with tens of millions of rows, “recreate it” takes a while, and the queries that depend on it are slow in the meantime. So be more careful dropping than adding.

Step 5: A safe migration runner in Go

These rules are many; it is best to write them into the migration runner so every release follows them automatically, rather than relying on whoever runs it to remember. The runner has to:

  • Detect statements that must run outside a transaction (CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX ... CONCURRENTLY), not wrap them, and allow only one statement in such a migration.
  • Wrap the other migrations in a transaction and set lock_timeout and statement_timeout with SET LOCAL, so they last only for that transaction.
  • Keep every setting on one connection: *sql.DB is a connection pool, so a SET on it lands on just one of the pooled connections, the next statement may use a different connection, and the setting is gone.
  • Retry on a lock timeout (SQLSTATE 55P03) with exponential backoff and jitter, up to a limit.
  • Before retrying a CONCURRENTLY build, drop any INVALID index of the same name left by the previous failure, so IF NOT EXISTS does not skip it.
  • Use an advisory lock so that only one runner runs at a time — when several Pods start together, two runners will not build the same index at once, nor mistakenly drop an index another runner is still building.

The code follows “database/sql, Transactions and Connection Pools in Go”: it depends only on database/sql, with github.com/jackc/pgx/v5/stdlib as the driver; here it also uses pgx's pgconn.PgError to read the SQLSTATE.

First, the parts that never touch the database and can be tested alone: classifying statements, extracting the index name, recognising a lock timeout and computing the backoff.

internal/platform/migrate/statement.go

package migrate

import (
	"errors"
	"math/rand/v2"
	"regexp"
	"strings"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
)

var (
	lineComment = regexp.MustCompile(`--[^\n]*`)
	// These three kinds of statement cannot run inside a transaction block.
	noTx = regexp.MustCompile(`(?is)^\s*(CREATE\s+(UNIQUE\s+)?INDEX\s+CONCURRENTLY|DROP\s+INDEX\s+CONCURRENTLY|REINDEX\b.*\bCONCURRENTLY)\b`)
	// shortcut: only unquoted names are recognised; extend it when quoted names are used.
	concurrentIndex = regexp.MustCompile(`(?is)^\s*CREATE\s+(?:UNIQUE\s+)?INDEX\s+CONCURRENTLY\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w.]+)\s+ON\b`)
)

func stripComments(stmt string) string {
	return strings.TrimSpace(lineComment.ReplaceAllString(stmt, ""))
}

// NeedsNoTx reports whether this SQL must run outside a transaction.
func NeedsNoTx(stmt string) bool {
	return noTx.MatchString(stripComments(stmt))
}

// IndexName extracts the index name from CREATE INDEX CONCURRENTLY, so that an INVALID index left by a
// failed build can be dropped before a retry.
func IndexName(stmt string) (string, bool) {
	m := concurrentIndex.FindStringSubmatch(stripComments(stmt))
	if m == nil {
		return "", false
	}
	return m[1], true
}

// statementCount roughly counts the statements.
// shortcut: semicolons inside strings and $$ are not handled; use a real SQL parser once migrations define functions.
func statementCount(sql string) int {
	n := 0
	for part := range strings.SplitSeq(stripComments(sql), ";") {
		if strings.TrimSpace(part) != "" {
			n++
		}
	}
	return n
}

// lockNotAvailable is the SQLSTATE raised when lock_timeout fires.
const lockNotAvailable = "55P03"

// IsLockTimeout reports whether the error means "waited too long for a lock".
func IsLockTimeout(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == lockNotAvailable
}

// Backoff returns how long to wait after failed attempt number attempt (from 1): growing
// exponentially, capped at maxDelay, with random jitter in the upper half so that several
// instances do not retry at the same moment.
func Backoff(attempt int, base, maxDelay time.Duration) time.Duration {
	d := maxDelay
	if attempt < 1 {
		attempt = 1
	}
	if attempt <= 30 && base<<(attempt-1) < maxDelay {
		d = base << (attempt - 1)
	}
	return d/2 + rand.N(d/2+1)
}

Backoff picks its jitter in [d/2, d]: several instances that hit a lock timeout together do not all retry at the same moment. statementCount is deliberately simple: it does not recognise semicolons inside strings or $$, and its comment states that limit and when to upgrade.

Next, the part that runs the migrations:

internal/platform/migrate/migrate.go

// Package migrate applies database migrations in order: migrations inside a transaction get a
// statement_timeout, CONCURRENTLY statements run outside a transaction, every step carries a
// lock_timeout, and a step that cannot get its lock backs off and retries.
package migrate

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log/slog"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
)

type Migration struct {
	Version int64
	Name    string
	SQL     string
}

type Options struct {
	LockTimeout      time.Duration // how long ordinary DDL may wait for each lock; a few seconds
	StatementTimeout time.Duration // limit for migrations in a transaction; none for CONCURRENTLY
	// ConcurrentLockTimeout is for CONCURRENTLY statements: they wait for all older transactions
	// to finish, but their lock does not block normal reads and writes, so they can wait longer.
	ConcurrentLockTimeout time.Duration
	MaxAttempts           int
	BaseDelay             time.Duration
	MaxDelay              time.Duration
}

// advisoryKey is an arbitrary fixed number: only one migration runner may run at a time.
const advisoryKey = 240001

func Run(ctx context.Context, db *sql.DB, opts Options, ms []Migration) error {
	if opts.LockTimeout <= 0 || opts.ConcurrentLockTimeout <= 0 || opts.MaxAttempts < 1 {
		return errors.New("migrate: lock timeouts and MaxAttempts must be set")
	}
	// SET applies to one connection only, so the whole run sticks to one connection instead of the pool.
	// The connection goes back to the pool with these settings, so db is the runner's own, not a service's.
	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()

	if _, err := conn.ExecContext(ctx, `SELECT pg_advisory_lock($1)`, advisoryKey); err != nil {
		return err
	}
	defer conn.ExecContext(context.WithoutCancel(ctx), `SELECT pg_advisory_unlock($1)`, advisoryKey)

	if _, err := conn.ExecContext(ctx,
		fmt.Sprintf(`SET lock_timeout = %d`, opts.LockTimeout.Milliseconds())); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (
		version    BIGINT PRIMARY KEY,
		applied_at TIMESTAMPTZ NOT NULL DEFAULT now())`); err != nil {
		return err
	}

	for _, m := range ms {
		var done bool
		if err := conn.QueryRowContext(ctx,
			`SELECT EXISTS (SELECT 1 FROM schema_migrations WHERE version = $1)`, m.Version,
		).Scan(&done); err != nil {
			return err
		}
		if done {
			continue
		}
		if err := applyWithRetry(ctx, conn, opts, m); err != nil {
			return fmt.Errorf("migration %d %s: %w", m.Version, m.Name, err)
		}
		slog.Info("migration applied", "version", m.Version, "name", m.Name)
	}
	return nil
}

func applyWithRetry(ctx context.Context, conn *sql.Conn, opts Options, m Migration) error {
	for attempt := 1; ; attempt++ {
		err := apply(ctx, conn, opts, m)
		if err == nil || !IsLockTimeout(err) || attempt >= opts.MaxAttempts {
			return err
		}
		wait := Backoff(attempt, opts.BaseDelay, opts.MaxDelay)
		slog.Warn("lock timeout, retrying", "version", m.Version, "attempt", attempt, "wait", wait)
		select {
		case <-time.After(wait):
		case <-ctx.Done():
			return ctx.Err()
		}
	}
}

func apply(ctx context.Context, conn *sql.Conn, opts Options, m Migration) error {
	if NeedsNoTx(m.SQL) {
		return applyNoTx(ctx, conn, opts, m)
	}
	tx, err := conn.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// SET LOCAL lasts only until this transaction ends.
	if _, err := tx.ExecContext(ctx,
		fmt.Sprintf(`SET LOCAL lock_timeout = %d`, opts.LockTimeout.Milliseconds())); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx,
		fmt.Sprintf(`SET LOCAL statement_timeout = %d`, opts.StatementTimeout.Milliseconds())); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, m.SQL); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx,
		`INSERT INTO schema_migrations (version) VALUES ($1)`, m.Version); err != nil {
		return err
	}
	return tx.Commit()
}

func applyNoTx(ctx context.Context, conn *sql.Conn, opts Options, m Migration) error {
	// Several statements sent at once run in one implicit transaction, and CONCURRENTLY then fails.
	if statementCount(m.SQL) != 1 {
		return errors.New("a CONCURRENTLY migration must contain exactly one statement")
	}
	// A build may take long, so no statement_timeout; waiting for locks and old transactions is still capped.
	if _, err := conn.ExecContext(ctx,
		fmt.Sprintf(`SET lock_timeout = %d`, opts.ConcurrentLockTimeout.Milliseconds())); err != nil {
		return err
	}
	if _, err := conn.ExecContext(ctx, `SET statement_timeout = 0`); err != nil {
		return err
	}
	if name, ok := IndexName(m.SQL); ok {
		if err := dropInvalid(ctx, conn, name); err != nil {
			return err
		}
	}
	if _, err := conn.ExecContext(ctx, m.SQL); err != nil {
		return err
	}
	// Record the version only after the build; a crash in between reruns it, so write IF NOT EXISTS.
	_, err := conn.ExecContext(ctx,
		`INSERT INTO schema_migrations (version) VALUES ($1)`, m.Version)
	return err
}

// dropInvalid removes an INVALID index of the same name left by a failed build; otherwise
// CREATE INDEX CONCURRENTLY IF NOT EXISTS would think the index exists and skip it.
func dropInvalid(ctx context.Context, conn *sql.Conn, name string) error {
	var invalid bool
	err := conn.QueryRowContext(ctx,
		`SELECT NOT indisvalid FROM pg_index WHERE indexrelid = to_regclass($1)`, name,
	).Scan(&invalid)
	if errors.Is(err, sql.ErrNoRows) || (err == nil && !invalid) {
		return nil
	}
	if err != nil {
		return err
	}
	slog.Warn("dropping invalid index left by a failed build", "index", name)
	_, err = conn.ExecContext(ctx,
		`DROP INDEX CONCURRENTLY IF EXISTS `+pgx.Identifier(strings.Split(name, ".")).Sanitize())
	return err
}

Key points:

  • Two timeouts, set separately. LockTimeout for ordinary DDL should be short (seconds), because while it waits, reads and writes queue behind it; the lock taken by CONCURRENTLY does not block normal reads and writes, but it must wait for every older transaction, so ConcurrentLockTimeout is longer (a minute, say), and statement_timeout is 0 so the build can take as long as it needs.
  • SET LOCAL lasts only for the transaction and is restored when it ends, so it does not affect later migrations. A SET outside a transaction stays on the connection, which is why applyNoTx sets its values every time, and why this *sql.DB belongs to the runner alone.
  • Build first, then record the version. CONCURRENTLY is outside a transaction, so “build the index” and “insert into schema_migrations” cannot succeed or fail together. If the process dies between the two, the next run repeats the same migration, so the migration itself must use IF NOT EXISTS, and dropInvalid makes sure IF NOT EXISTS is not fooled by an INVALID leftover.
  • An advisory lock is an application-level lock provided by PostgreSQL, unrelated to any table; pg_advisory_lock waits until it gets the lock, and closing the connection releases it automatically.

Finally, the composition root. The runner is a separate binary that the deployment runs before the new version of the service starts (for example as a Kubernetes Job, as discussed in “Deploying a Microservice System on Kubernetes”):

cmd/migrate/main.go

package main

import (
	"context"
	"database/sql"
	"log/slog"
	"os"
	"os/signal"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"

	"example.com/shop/internal/platform/migrate"
)

var migrations = []migrate.Migration{
	{Version: 24001, Name: "orders_pending_created_idx", SQL: `
		CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_pending_created_idx
		    ON orders (created_at) WHERE status = 'pending'`},
	{Version: 24002, Name: "orders_user_status_created_idx", SQL: `
		CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_user_status_created_idx
		    ON orders (user_id, status, created_at DESC, id DESC)`},
}

func main() {
	ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt)
	defer stop()

	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		slog.Error("open database", "err", err)
		os.Exit(1)
	}
	defer db.Close()

	err = migrate.Run(ctx, db, migrate.Options{
		LockTimeout:           3 * time.Second,
		StatementTimeout:      time.Minute,
		ConcurrentLockTimeout: time.Minute,
		MaxAttempts:           8,
		BaseDelay:             2 * time.Second,
		MaxDelay:              time.Minute,
	}, migrations)
	if err != nil {
		slog.Error("migrate", "err", err)
		os.Exit(1)
	}
}

Tests

The parts that do not touch the database get table-driven tests covering case, line breaks, keywords inside comments, wrapped errors, and the bounds of the backoff:

internal/platform/migrate/statement_test.go

package migrate

import (
	"fmt"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
)

func TestNeedsNoTx(t *testing.T) {
	tests := []struct {
		sql  string
		want bool
	}{
		{"CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at)", true},
		{"create unique index concurrently if not exists k ON orders (idempotency_key)", true},
		{"-- build the index first\nCREATE INDEX\n  CONCURRENTLY x ON orders (total)", true},
		{"DROP INDEX CONCURRENTLY IF EXISTS orders_user_only_idx", true},
		{"REINDEX INDEX CONCURRENTLY orders_user_id_idx", true},
		{"CREATE INDEX orders_total_idx ON orders (total)", false},
		{"ALTER TABLE orders ADD COLUMN note TEXT", false},
		{"-- CREATE INDEX CONCURRENTLY comes later\nALTER TABLE orders ADD COLUMN note TEXT", false},
	}
	for _, tt := range tests {
		if got := NeedsNoTx(tt.sql); got != tt.want {
			t.Errorf("NeedsNoTx(%q) = %v, want %v", tt.sql, got, tt.want)
		}
	}
}

func TestIndexName(t *testing.T) {
	tests := []struct {
		sql    string
		want   string
		wantOK bool
	}{
		{"CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at)", "orders_pending_idx", true},
		{"CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS shop.orders_key ON shop.orders (k)", "shop.orders_key", true},
		{"DROP INDEX CONCURRENTLY orders_pending_idx", "", false},
		{"CREATE INDEX orders_total_idx ON orders (total)", "", false},
	}
	for _, tt := range tests {
		got, ok := IndexName(tt.sql)
		if got != tt.want || ok != tt.wantOK {
			t.Errorf("IndexName(%q) = %q, %v; want %q, %v", tt.sql, got, ok, tt.want, tt.wantOK)
		}
	}
}

func TestStatementCount(t *testing.T) {
	if n := statementCount("CREATE INDEX CONCURRENTLY a ON t (x);\n-- done;\n"); n != 1 {
		t.Errorf("got %d, want 1", n)
	}
	if n := statementCount("CREATE INDEX CONCURRENTLY a ON t (x); CREATE INDEX CONCURRENTLY b ON t (y);"); n != 2 {
		t.Errorf("got %d, want 2", n)
	}
}

func TestIsLockTimeout(t *testing.T) {
	lock := fmt.Errorf("apply: %w", &pgconn.PgError{Code: "55P03"})
	cancelled := &pgconn.PgError{Code: "57014"} // statement_timeout
	if !IsLockTimeout(lock) {
		t.Error("wrapped 55P03 should be a lock timeout")
	}
	if IsLockTimeout(cancelled) || IsLockTimeout(nil) {
		t.Error("only 55P03 is a lock timeout")
	}
}

func TestBackoff(t *testing.T) {
	base, maxDelay := 500*time.Millisecond, 30*time.Second
	for attempt := 1; attempt <= 40; attempt++ {
		ceil := min(base<<min(attempt-1, 30), maxDelay)
		for range 100 {
			d := Backoff(attempt, base, maxDelay)
			if d < ceil/2 || d > ceil {
				t.Fatalf("attempt %d: %v outside [%v, %v]", attempt, d, ceil/2, ceil)
			}
		}
	}
}

The real behaviour — the INVALID leftover, rebuilding after cleanup, timing out while waiting for old transactions and retrying, ordinary DDL giving up when it cannot get the lock — can only be tested against a real PostgreSQL. This integration test is skipped when MIGRATE_TEST_DSN is not set; in CI it can run against a throwaway database container (“Testing in Go: Table-Driven Tests, httptest and Integration Tests” described this split):

internal/platform/migrate/migrate_integration_test.go

package migrate

import (
	"context"
	"database/sql"
	"os"
	"testing"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

// Needs a PostgreSQL where the test may create and drop tables:
// MIGRATE_TEST_DSN=postgres://... go test -race ./internal/platform/migrate/
func TestRunAgainstPostgres(t *testing.T) {
	dsn := os.Getenv("MIGRATE_TEST_DSN")
	if dsn == "" {
		t.Skip("MIGRATE_TEST_DSN not set")
	}
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	for _, s := range []string{
		`DROP TABLE IF EXISTS mig_orders, schema_migrations`,
		`CREATE TABLE mig_orders (id BIGINT PRIMARY KEY, k TEXT)`,
		`INSERT INTO mig_orders VALUES (1, 'a'), (2, 'a')`, // duplicate keys: the unique index build fails
	} {
		if _, err := db.ExecContext(ctx, s); err != nil {
			t.Fatal(err)
		}
	}
	opts := Options{
		LockTimeout:           200 * time.Millisecond,
		StatementTimeout:      5 * time.Second,
		ConcurrentLockTimeout: 200 * time.Millisecond,
		MaxAttempts:           4,
		BaseDelay:             50 * time.Millisecond,
		MaxDelay:              200 * time.Millisecond,
	}
	unique := Migration{Version: 1, Name: "k", SQL: `CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS mig_orders_k_uidx ON mig_orders (k)`}

	if err := Run(ctx, db, opts, []Migration{unique}); err == nil {
		t.Fatal("unique index on duplicate keys should fail")
	}
	var valid bool
	if err := db.QueryRowContext(ctx,
		`SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass('mig_orders_k_uidx')`,
	).Scan(&valid); err != nil || valid {
		t.Fatalf("want an INVALID leftover, got valid=%v err=%v", valid, err)
	}

	// Fix the data and run again: the INVALID leftover must be dropped and rebuilt, not skipped by IF NOT EXISTS.
	if _, err := db.ExecContext(ctx, `UPDATE mig_orders SET k = 'b' WHERE id = 2`); err != nil {
		t.Fatal(err)
	}
	if err := Run(ctx, db, opts, []Migration{unique}); err != nil {
		t.Fatal(err)
	}
	if err := db.QueryRowContext(ctx,
		`SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass('mig_orders_k_uidx')`,
	).Scan(&valid); err != nil || !valid {
		t.Fatalf("want a valid index, got valid=%v err=%v", valid, err)
	}

	// A write transaction still open: CONCURRENTLY waits for it, times out, drops the INVALID index and
	// retries, and succeeds once the transaction has ended.
	writer, err := db.BeginTx(ctx, nil)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := writer.ExecContext(ctx, `INSERT INTO mig_orders VALUES (3, 'c')`); err != nil {
		t.Fatal(err)
	}
	time.AfterFunc(300*time.Millisecond, func() { _ = writer.Rollback() })
	byID := Migration{Version: 2, Name: "id k", SQL: `CREATE INDEX CONCURRENTLY IF NOT EXISTS mig_orders_id_k_idx ON mig_orders (id, k)`}
	if err := Run(ctx, db, opts, []Migration{byID}); err != nil {
		t.Fatalf("should succeed after the writer finishes: %v", err)
	}

	// Another transaction holds a lock: ordinary DDL gives up after lock_timeout, retries, then reports the lock timeout.
	holder, err := db.BeginTx(ctx, nil)
	if err != nil {
		t.Fatal(err)
	}
	defer holder.Rollback()
	if _, err := holder.ExecContext(ctx, `LOCK TABLE mig_orders IN ACCESS SHARE MODE`); err != nil {
		t.Fatal(err)
	}
	drop := Migration{Version: 3, Name: "drop k", SQL: `ALTER TABLE mig_orders DROP COLUMN k`}
	if err := Run(ctx, db, opts, []Migration{drop}); !IsLockTimeout(err) {
		t.Fatalf("want a lock timeout, got %v", err)
	}
}

Run:

go vet ./...
go test -race ./...
MIGRATE_TEST_DSN='postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable' \
    go test -race ./internal/platform/migrate/

The integration test's log shows the expected events in order: the first unique index build fails, the INVALID index is dropped and the rebuild succeeds; CONCURRENTLY times out waiting for an old transaction and succeeds after a retry; ALTER TABLE cannot get its lock and reports a lock timeout after three retries.

Tip: if your team uses an existing migration tool, check three things: whether a single migration can run without a transaction, whether lock_timeout and statement_timeout can be set per migration, and whether it checks for INVALID indexes after a failure. Most tools have a way to do the first two; the third is often missing, and you need to add a check to your deployment yourself.

Step 6: How MySQL (InnoDB) differs

The ideas are broadly the same; the details differ. The following assumes InnoDB on MySQL 8.0 or later.

Online DDL. InnoDB can add or drop a secondary index “in place”, without rebuilding the table, and allows concurrent reads and writes during the build. It is good practice to state the algorithm and lock level you expect:

ALTER TABLE orders
    ADD INDEX orders_user_status_created_idx (user_id, status, created_at),
    ALGORITHM=INPLACE, LOCK=NONE;

The benefit of stating them is that if the operation cannot be done with LOCK=NONE, MySQL fails with an error instead of silently switching to a method that locks the table. Concurrent writes during the build are first recorded in an online change log and applied at the end; under heavy write load, the operation fails if that log exceeds its limit (innodb_online_alter_log_max_size). Not every index operation works this way: adding the first FULLTEXT index, or changing the primary key, for example, may rebuild the table or disallow concurrent writes — check the per-operation tables in the official documentation.

Metadata locks are the classic cause of incidents. Online DDL still needs a brief exclusive metadata lock on the table at its start and end. Any unfinished transaction that has touched the table (even with just a SELECT) holds a shared metadata lock, and the DDL must wait for it to end; and a pending exclusive metadata lock blocks every later query on the table. It is the same incident as PostgreSQL's lock queue: SHOW PROCESSLIST shows a long list of Waiting for table metadata lock. The remedy is the same too — cap the wait:

SET SESSION lock_wait_timeout = 5;   -- seconds; the default is very long

performance_schema.metadata_locks shows who holds the lock and who is waiting.

Replication lag on replicas. With classic asynchronous replication, the DDL is written to the binary log only after it completes on the source; the replica then runs it again, taking roughly as long, and later changes may have to wait behind it, so replication lag keeps growing. Account for this when changing a large table.

Online schema change tools. For changes online DDL cannot do, or when you want finer control over load and the moment of switching, gh-ost and pt-online-schema-change are common choices. They share the same idea: create a shadow table with the new structure, copy the data over in batches while also applying the changes made during the copy, and finish by switching with a rename. They differ in how they capture changes: pt-online-schema-change creates triggers on the original table, while gh-ost reads the binary log and uses no triggers. Both need room for a full extra copy of the table, and the switch still takes a brief lock, so the lock wait limits above still apply.

Other differences. MySQL has neither partial indexes nor INCLUDE; InnoDB secondary indexes carry the primary key columns, so a query that uses only the indexed columns plus the primary key can be answered from the index. Version 8.0 supports descending indexes and functional key parts. Version 8.0 also offers invisible indexes: ALTER TABLE orders ALTER INDEX orders_user_only_idx INVISIBLE; stops the optimiser from using the index while still maintaining it; watch for a while, drop it if nothing breaks, and switch it back to VISIBLE at once if something does — a very handy rollback path for dropping indexes, with no built-in equivalent in PostgreSQL. Implicit conversion is just as deadly in MySQL: comparing a VARCHAR column with a number converts the column to a number, and the index can no longer be used.

Step 7: Pre-flight checklist, rollback plan and an incident timeline

Adding an index safely 1. Evidence pg_stat_statements EXPLAIN (ANALYZE, BUFFERS) 2. Design column order, partial, covering no duplicate of an existing one 3. Rehearse on a production-size copy note time, size and plan 4. Pre-flight long transactions, disk, WAL duplicates, off-peak window 5. Run CONCURRENTLY, no transaction lock_timeout, backoff, retry 6. Watch progress_create_index replica lag, error rate 7. Verify the index is VALID EXPLAIN really uses it Rollback plan (if any step goes wrong) during the build: pg_cancel_backend, then drop the INVALID index slower writes or a worse plan: DROP INDEX CONCURRENTLY wrong index dropped: recreate it from pg_get_indexdef replica lag too high: send reads to the primary or degrade MySQL: make it INVISIBLE before dropping

Figure: start from evidence, design and rehearse on data close to production size, check every item before running, run with CONCURRENTLY and timeouts in an off-peak window while watching progress and replication lag, and finally confirm the plan really uses the new index; if any step does not go as expected, follow the rollback plan.

Pre-flight checklist

  • Evidence: pg_stat_statements or the slow query log points at the query to improve, and the EXPLAIN (ANALYZE, BUFFERS) from before the index is saved.
  • Design: column order follows “equality first, then range and sort”; the index does not duplicate an existing one or become a prefix of one; the impact on writes and HOT updates is assessed.
  • Rehearsal: built once on a copy close to production size, noting how long it took, how big the index is and whether the plan is as expected.
  • Syntax: uses CONCURRENTLY and IF NOT EXISTS; the migration is not wrapped in a transaction and has only one statement.
  • Timeouts: lock_timeout set on the migration connection (seconds for ordinary DDL); statement_timeout for CONCURRENTLY unlimited or generous.
  • Long transactions: pg_stat_activity shows no long-running transactions; if it does, deal with them first.
  • Space: the disk can hold the new index, temporary sort files and WAL; the replicas too.
  • Timing: an off-peak window, with the on-call engineer informed.
  • Unique indexes: duplicates found and resolved, and the application already checks on write.
  • Monitoring: pg_stat_progress_create_index, pg_stat_replication and the service's latency and error-rate dashboards are ready.
  • Verification: afterwards, the index is VALID and EXPLAIN confirms the query really uses it.

Rollback plan

  • Abort during the build: cancel the statement (SELECT pg_cancel_backend(pid);), then check for and drop the INVALID index it leaves (DROP INDEX CONCURRENTLY).
  • Writes slower after the build: if the new index adds too much write cost or makes some query's plan worse, drop it with DROP INDEX CONCURRENTLY, with lock_timeout ready.
  • Queries slower after dropping an index: recreate it from the saved pg_get_indexdef statement with CONCURRENTLY, knowing how long that takes (measured in the rehearsal). In MySQL, make it INVISIBLE first and watch, which minimises the risk of the drop.
  • Replica lag too high: the build cannot be paused, but reads that depend on replicas can temporarily go to the primary or degrade; prepare that switch in advance.

An incident timeline without lock_timeout (a fictional illustration)

The following scenario is fictional, to show how a lock queue can bring a service down within seconds. Times are minutes:seconds:

TimeWhat happens
00:00An operations analyst runs a month-end report on the primary; its query on orders is expected to take 15 minutes and holds an ACCESS SHARE lock.
03:00The deployment runs a migration that removes an unused index, written as DROP INDEX orders_user_only_idx;, with no CONCURRENTLY and no lock_timeout.
03:00DROP INDEX needs ACCESS EXCLUSIVE, conflicts with the report, and starts waiting.
03:01Every later request that reads or writes orders queues behind the waiting DROP INDEX. The order service's connection pool fills with waiting queries in a second or two.
03:05Calls from api-gateway to order start timing out; retries double the request volume. payment callbacks that depend on orders start failing too.
03:30The health check queries the database, so the order Pods are judged unhealthy and restarted; the new connections after the restart get stuck in the same queue.
09:00The on-call engineer sees many connections with wait_event_type = Lock in pg_stat_activity and follows pg_blocking_pids() to the DROP INDEX and the report behind it.
09:30The report (or the DROP INDEX) is cancelled; DROP INDEX completes or gives up within milliseconds, the queue drains, and the service recovers within a minute or two.

Had the same migration been written as SET lock_timeout = '3s'; DROP INDEX CONCURRENTLY ...: CONCURRENTLY itself never takes a lock that blocks reads and writes; and even for ordinary DDL, the requests behind it would wait an extra 3 seconds before it gave up with an error and the runner retried later. The worst case changes from “ordering stops for over six minutes” to “latency rises for a few seconds and the migration finishes a little later”. The follow-up improvements are clear too: move the report to a read replica, make the migration runner enforce lock_timeout, and check in CI for CREATE INDEX and DROP INDEX without CONCURRENTLY.

A query that shows who is blocking whom, useful when on call:

SELECT pid,
       pg_blocking_pids(pid)  AS blocked_by,
       wait_event_type,
       now() - query_start    AS waiting_for,
       left(query, 60)        AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY query_start;

Common pitfalls

  • Adding indexes on a hunch. Without evidence from pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS), the index may go unused and only add write cost.
  • Not verifying afterwards. Without running EXPLAIN again, you do not know whether the planner uses the new index at all.
  • Composite columns in the wrong order. A range column placed before equality columns makes the later columns useless; a sort direction that does not match the ORDER BY needs an extra sort.
  • Functions or casts on the column. created_at::date = ... or comparing with a different type keeps the index from being used.
  • Full indexes on low-selectivity or frequently updated columns. The planner will not use them, and updates lose HOT. Consider a partial index.
  • Plain CREATE INDEX on a large table. Every write is blocked for the whole build.
  • The migration tool wrapping CONCURRENTLY in a transaction. It fails outright; sending several statements at once fails the same way.
  • No lock_timeout. A DDL statement waiting for a lock makes all reads and writes on the table queue, and the connection pool runs dry.
  • Ignoring INVALID indexes. The failed leftover keeps costing writes, and IF NOT EXISTS skips the rebuild because of it.
  • Not resolving duplicates before a unique index. The build fails, and the application may receive uniqueness violations during the build.
  • Dropping an index based only on the primary's idx_scan. Queries on replicas count only in the replicas' statistics.
  • Forgetting disk and replicas. The index, temporary files and WAL fill the disk, or replica lag serves stale data to reads.
  • Using DROP INDEX instead of DROP INDEX CONCURRENTLY. Dropping needs ACCESS EXCLUSIVE and triggers a lock queue more easily than adding.

Summary

  • A B-tree is good at equality, ranges and sorting; every index costs write amplification, storage, cache, VACUUM work and HOT updates. Low selectivity, small tables, functions or casts on the column, leading wildcards and OR can all leave an index useless.
  • Decide from evidence: pg_stat_statements finds the heaviest queries by total time, the slow query log and auto_explain add the parameters, and EXPLAIN (ANALYZE, BUFFERS) is read inside out, looking at actual rows, loops, Rows Removed by Filter and Buffers; verify again after adding the index.
  • Composite indexes: equality first, then range and sort, with sort directions matching the ORDER BY of keyset pagination; INCLUDE for covering indexes; partial indexes index only what you need; expression indexes must be IMMUTABLE; unique indexes guard idempotency keys; regularly find unused, duplicate and redundant-prefix indexes, and check every replica.
  • A plain CREATE INDEX blocks writes; a DDL statement waiting for a lock makes later reads and writes queue. Set lock_timeout for all DDL.
  • CREATE INDEX CONCURRENTLY does not block reads or writes, but cannot run in a transaction, scans twice, waits for old transactions, and leaves an INVALID index on failure; DROP INDEX CONCURRENTLY before retrying, and beware of IF NOT EXISTS being fooled by a leftover. For unique indexes, resolve duplicates first, then promote the index with ADD CONSTRAINT ... USING INDEX.
  • Before running, check long transactions, disk, maintenance_work_mem and replica lag; during the build, watch pg_stat_progress_create_index; fix bloat with REINDEX CONCURRENTLY, and drop with DROP INDEX CONCURRENTLY after saving the creation statement.
  • The Go migration runner hard-codes the rules: one fixed connection, CONCURRENTLY outside a transaction with a single statement, timeouts via SET LOCAL, backoff and retry on lock timeouts, cleanup of INVALID leftovers, and an advisory lock against concurrent runs; the pure logic gets table tests and the behaviour an integration test against a real database.
  • MySQL: state ALGORITHM=INPLACE, LOCK=NONE explicitly; metadata lock waits are the classic incident, so set lock_wait_timeout; account for replica lag when changing large tables, and use gh-ost or pt-online-schema-change when needed; invisible indexes make dropping reversible.

Next

Indexes are the cheapest way to make queries faster, but they do not make the data any smaller. The orders table grows year after year and its indexes grow with it; most queries only touch the last few months, yet backups, VACUUM and every new index have to process all of it. The next article, “Hot and Cold Data, Sharding and Splitting Databases: What to Do When the Data Grows”, carries on from here: separate hot and cold data and archive old rows first, then, when it is really needed, split databases vertically and shard horizontally, and move the data over without downtime.