Changing big tables safely

Module 4 gave you the rule for changing a schema while two versions of the app run side by side: expand and contract. Add the new thing, backfill, switch, then remove the old thing. That rule keeps the code safe. It says nothing about what the database does while each step runs.

On a table with a few thousand rows, nothing interesting happens. Every ALTER TABLE finishes before you notice. On Tiffin's orders table, with 80 million rows and 2,000 queries a second hitting it at dinner, the same statements can freeze the whole app. This lesson is about the gap between "the migration is correct" and "the migration is safe to run at 3pm on a Tuesday".

Every result below is real: we ran it on Postgres 18 with a 5-million-row copy of orders (472 MB) on a laptop. Tiffin's real table is 16 times bigger, so its slow steps are 16 times slower.

Every ALTER takes a lock

Postgres protects a table's structure with locks. Ordinary queries take weak ones: a SELECT takes ACCESS SHARE, an UPDATE takes ROW EXCLUSIVE, and any number of those can hold the table at once.

Most ALTER TABLE forms take the strongest lock there is, ACCESS EXCLUSIVE. While it's held, nothing else can touch the table, not even a SELECT. That sounds alarming, but for many changes the lock is held for a millisecond, because the change only touches the table's description, not its rows:

ALTER TABLE orders ADD COLUMN is_gift boolean NOT NULL DEFAULT false;
Time: 0.881 ms

Since Postgres 11, adding a column with a constant default doesn't write anything into the 5 million rows. The default is stored once, in the table's metadata, and filled in when rows are read.

Change one word and the picture changes completely:

ALTER TABLE orders ADD COLUMN public_id uuid DEFAULT gen_random_uuid();
Time: 7567.817 ms (00:07.568)

gen_random_uuid() gives a different value for every row, so Postgres has to rewrite the whole table and all its indexes, holding ACCESS EXCLUSIVE the entire time. Seven and a half seconds of nobody reading or writing orders. On the real table, about two minutes. At dinner, that's an outage.

So the first skill is knowing which changes rewrite. The common ones:

(now() counts as stable, not volatile, because it returns the same value for the whole transaction. Every existing row gets one timestamp, stored once.)

The lock queue

Here's the trap that catches teams who have read the table above. Even a millisecond change can take down the app, because of how locks wait.

We started a slow report that reads orders for 20 seconds. That's an ordinary SELECT holding a weak lock. Then we ran the harmless ADD COLUMN, and then a normal one-row lookup, the kind the order status screen sends hundreds of times a second:

 pid | wait_event_type | query                                    | running_for
-----+-----------------+------------------------------------------+-------------
 136 | Timeout         | BEGIN; SELECT count(*) FROM orders; ...  | 00:00:04
 141 | Lock            | ALTER TABLE orders ADD COLUMN delivery_n | 00:00:03
 144 | Lock            | SELECT status FROM orders WHERE id = 42  | 00:00:01

 pid | blocked_by | query
-----+------------+--------------------------------------------------
 141 | {136}      | ALTER TABLE orders ADD COLUMN delivery_note text
 144 | {141}      | SELECT status FROM orders WHERE id = 42

The ALTER waits for the report, which is fair. But the one-row lookup waits for the ALTER. Postgres queues lock requests in order, and a new SELECT can't jump ahead of a waiting ACCESS EXCLUSIVE request. So every query that arrives after the ALTER lines up behind it. That lookup, normally under a millisecond, took this long:

Time: 18054.000 ms (00:18.054)

%% caption: The ALTER only needs a moment, but it waits for the report, and every new query waits behind the ALTER.
flowchart LR
  R["Slow report<br/>holds a weak lock, 20 s"] --> A["ALTER TABLE<br/>waiting for the strongest lock"]:::hot
  A --> Q1["SELECT status ..."]
  A --> Q2["UPDATE orders ..."]
  A --> Q3["INSERT INTO orders ..."]

At 2,000 queries a second, 18 seconds is 36,000 queries stuck in line. The app's connection pools run dry within a second, and every screen in the Tiffin app spins.

lock_timeout: fail the migration, not the app

The fix is one setting. lock_timeout tells Postgres how long a statement may wait for a lock before giving up. Same experiment, with a 2-second limit on the migration:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN rider_note text;
ERROR:  canceling statement due to lock timeout
Time: 2005.895 ms (00:02.006)

The one-row lookup now waited 1.5 seconds instead of 18, and the app kept going. The migration failed, which is exactly what you want: try again in a minute, when the report has finished.

Every migration that touches a busy table should start with this:

SET lock_timeout = '2s';        -- give up rather than block everyone
SET statement_timeout = '30s';  -- and never run for ages holding a lock

Then let the migration runner retry a few times, with a pause in between. Most lock waits are a single slow query, and the second or third attempt goes through. If it keeps failing, look for what's holding the table: pg_blocking_pids in the output above names it.

Pick the number from what the app can absorb. Two seconds of queued queries is a blip on most dashboards. Ten seconds is an incident.

Indexes without blocking writes

A plain CREATE INDEX lets reads continue but blocks every INSERT, UPDATE and DELETE on the table until it finishes. On our copy it took one second. On 80 million rows, with the table not all in memory, several minutes of no new orders.

CREATE INDEX CONCURRENTLY builds the index while writes carry on. It's slower, because it scans the table twice and waits for older transactions to finish:

CREATE INDEX orders_restaurant_plain ON orders (restaurant_id);
Time: 1045.132 ms (00:01.045)

CREATE INDEX CONCURRENTLY orders_restaurant_idx ON orders (restaurant_id);
Time: 1629.042 ms (00:01.629)

Two catches, both of which bite in real migration tools.

It can't run inside a transaction.

BEGIN;
CREATE INDEX CONCURRENTLY orders_restaurant_idx ON orders (restaurant_id);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Many migration frameworks wrap each migration in a transaction by default, so you have to opt out for this one. Rails has disable_ddl_transaction!, Django has atomic = False. Keep a concurrent index in a migration of its own, so nothing else loses its transaction.

A failed build leaves a broken index behind. We tried a unique index on a column that had duplicates:

CREATE UNIQUE INDEX CONCURRENTLY orders_customer_uniq ON orders (customer_id);
ERROR:  could not create unique index "orders_customer_uniq"
DETAIL:  Key (customer_id)=(20616) is duplicated.

Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_uniq" UNIQUE, btree (customer_id) INVALID

The index is still there, marked INVALID. Queries won't use it, but every write still updates it, so it costs you on every insert. Rerunning the migration fails with "already exists". Drop it with DROP INDEX CONCURRENTLY and try again, and have your migration check for invalid indexes first.

Constraints in two steps

Say Tiffin wants every order to have a delivery_note, even an empty one. SET NOT NULL would scan 80 million rows under the strongest lock. Instead, split adding the rule from checking it.

-- 1. Backfill in batches (see below), so no row is NULL.

-- 2. Add the rule for new rows only. Metadata, instant.
ALTER TABLE orders
  ADD CONSTRAINT orders_delivery_note_nn NOT NULL delivery_note NOT VALID;

-- 3. Check the old rows. Scans the table, but with a weak lock:
--    reads and writes carry on while it runs.
ALTER TABLE orders VALIDATE CONSTRAINT orders_delivery_note_nn;

On our copy, step 2 took 2 ms and step 3 took 5.2 seconds, during which the app could keep inserting orders. VALIDATE CONSTRAINT takes a SHARE UPDATE EXCLUSIVE lock, which doesn't conflict with normal reads and writes.

NOT VALID on a not-null constraint is new in Postgres 18. On older versions the same idea works with a CHECK (delivery_note IS NOT NULL) NOT VALID constraint: validate it, then SET NOT NULL skips its scan because the check already proves there are no nulls. Foreign keys have worked this way for years: add them NOT VALID, then VALIDATE.

Backfills that don't hurt

The backfill step of expand and contract is just UPDATE, but on a big table one statement is the wrong tool. We filled delivery_note for all 5 million rows in one go to see what happens:

UPDATE orders SET delivery_note = '' WHERE delivery_note IS NULL;
UPDATE 5000000
Time: 37042.156 ms (00:37.042)

For 37 seconds, those 5 million rows were locked against other updates. A rider marking an old order "delivered" would have waited. It also wrote a burst of change log that replicas had to replay, so replica lag (Module 5) climbed. On the real table it would be ten minutes of both.

Batch it instead, and pause between batches:

UPDATE orders
SET delivery_note = ''
WHERE id > :last_id AND id <= :last_id + 10000
  AND delivery_note IS NULL;
-- commit, sleep 100 ms, move :last_id on, repeat

Each batch holds its locks for a few milliseconds. The job is slower in total, around 20 minutes for 80 million rows at 10,000 rows per batch, but nobody notices it. Good backfill jobs also watch replica lag and slow down when it rises, and can stop and resume from the last id.

Migrations and the deploy

The last piece is when each migration runs relative to the code. During a rolling deploy, old and new code run together, so:

• Expand migrations run before the deploy that needs them. Adding delivery_note must finish before any server runs code that writes it.
• Contract migrations run after the deploy that stops using the old thing has finished everywhere, and after you're sure you won't roll back. Dropping phone while a v1 server still reads it is the classic outage.

Tiffin's pipeline used to run migrations when each app server started up. With 12 servers, that's 12 copies of the migration racing each other, and an expand migration that starts only when the first new server boots. The fix is to make migrations a separate step in the pipeline: one task runs the pending expand migrations, with lock_timeout set and retries on, and only when it succeeds does the rolling deploy begin.

Two more habits that save real outages:

• Connect the migration runner straight to the database, not through the connection pooler. Settings like SET lock_timeout live on the connection, and in a pooler's transaction mode they leak to other clients or get lost (running a pooler shows it happening). Several migration tools, Rails among them, also take a session-level lock to stop two runs at once, which a transaction-mode pooler breaks.
• Review migrations for locks, not just for correctness. A checklist in code review, or a linter that flags ADD COLUMN ... DEFAULT <volatile>, SET NOT NULL and plain CREATE INDEX, catches most of these before they reach production.

BEGIN;
ALTER TABLE orders ADD COLUMN source text NOT NULL DEFAULT 'app';
ALTER TABLE orders ALTER COLUMN total_paise TYPE bigint;
CREATE INDEX orders_source_idx ON orders (source);
COMMIT;