Migrations Without Downtime

Every schema change takes a lock, and one lock level decides whether a migration is invisible or an outage. ACCESS EXCLUSIVE conflicts with every other mode, including the ACCESS SHARE that a plain SELECT takes — it is the only lock level that blocks a read. Most forms of ALTER TABLE take it, and most of them hold it for microseconds.

The failure that takes Cartwheel down is not a slow migration. It is a fast one waiting behind a slow query, with every new reader queueing behind the migration. This topic is the procedure for changing a live 40-million-row schema anyway, and it ends with the change Chapter 2 deferred: orders.id is still a 4-byte integer. The sequence stands at 1,352,914,698 of a hard ceiling of 2,147,483,647 — 63% consumed, 794,568,949 values left, and about 210 million spent in the last twelve months. Just under four years of headroom.

The Lock Levels That Matter

Postgres has eight table-level lock modes and four of them carry the argument. ACCESS SHARE is what a SELECT takes and it conflicts with only one other mode. ROW EXCLUSIVE is what INSERT, UPDATE, DELETE and MERGE take. SHARE UPDATE EXCLUSIVE is the friendly maintenance lock — VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY and VALIDATE CONSTRAINT hold it, and it conflicts with neither of the first two, so readers and writers carry on. ACCESS EXCLUSIVE is the one that stops everything: most ALTER TABLE forms, DROP TABLE, TRUNCATE, CLUSTER, VACUUM FULL, non-concurrent REINDEX, and REFRESH MATERIALIZED VIEW without CONCURRENTLY.

The ALTER TABLE reference lists the lock for every form it supports, and ACCESS EXCLUSIVE is the default wherever a weaker one is not explicitly noted. Between them sits SHARE ROW EXCLUSIVE, taken by CREATE TRIGGER and by ALTER TABLE … ADD FOREIGN KEY: it blocks writers and lets readers through.

The Lock Queue Is the Real Danger

A lock request that cannot be granted waits. That much is expected. What surprises people is that requests arriving afterwards do not overtake it: a new request that conflicts with the waiting request queues behind it too. So an analytics query that has been reading orders for twenty minutes holds ACCESS SHARE; an ALTER TABLE asks for ACCESS EXCLUSIVE and joins the queue; and from that instant every new SELECT against orders — checkout included — queues behind the ALTER. The migration itself would have taken three milliseconds. The outage lasts twenty minutes, and the migration tool reports "still running" throughout.

SET lock_timeout = '3s';        -- give up waiting, do not camp in the queue
SET statement_timeout = '30s';  -- must be larger, or it fires first

ALTER TABLE orders ADD COLUMN id_new bigint;
ERROR:  canceling statement due to lock timeout
-- the migration driver sleeps, then tries again

lock_timeout applies separately to each lock acquisition attempt and is zero — disabled — by default, so a migration session that has not set it will wait for as long as the blocking query runs. Three seconds is a reasonable value: long enough to win an uncontended lock, short enough that failing costs nothing. It must stay below statement_timeout, since the statement timeout would otherwise always fire first and you would lose the ability to tell "blocked on a lock" from "slow on its own merits". The pattern is fail fast and retry, five or ten times, with a pause between attempts. A failed attempt costs three seconds and leaves the queue empty behind it.

Which Changes Are Cheap

Cheap means no table rewrite. Adding a nullable column with no default is a catalogue change. Adding a column with a non-volatile default has been metadata-only since 11: the default is evaluated once at the time of the statement, stored in the table's metadata, and returned for every pre-existing row that does not have it stored. Dropping a column is instant, because the column is marked invisible rather than removed, and its space comes back as rows are updated or the table is next rewritten. Renaming has no effect on stored data at all.

ALTER TABLE orders ADD COLUMN note text;                    -- catalogue only
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web';   -- metadata only
ALTER TABLE orders ADD COLUMN opened_at timestamptz
                              DEFAULT now();                -- now() is STABLE:
                                                            -- still metadata only

ALTER TABLE orders ADD COLUMN seen_at timestamptz
                              DEFAULT clock_timestamp();     -- VOLATILE: rewrite

The line that separates them is volatility, not whether the default looks like a function call. now() returns the transaction's start time and is stable, so it is evaluated once and the whole table gets that one value — which is either what you wanted or a fact to establish before you write it. clock_timestamp(), random() and gen_random_uuid() are volatile and produce a different value per row, so Postgres has no choice but to rewrite the table and every index on it. "Cheap" still means the statement takes ACCESS EXCLUSIVE — the queue rule applies to a three-millisecond migration exactly as it applies to a three-hour one. Three other additions rewrite regardless of any default: a stored generated column, an identity column, and a column whose type is a domain carrying constraints. Adding a virtual generated column never rewrites.

Which Changes Rewrite the Table

Changing a column's type normally rewrites the entire table and all of its indexes. There is one exception worth knowing: if the USING clause does not change the contents and the old type is binary-coercible to the new one, no rewrite is needed — although the indexes are still rebuilt unless the system can prove the new index would be logically equivalent. text to varchar with no collation change is the example that qualifies. integer to bigint is not: the values occupy a different number of bytes on the page.

So the honest cost of ALTER TABLE orders ALTER COLUMN id TYPE bigint is a full rewrite of 40 million rows plus every index on the table, holding ACCESS EXCLUSIVE from the first byte to the last, with peak disk usage of roughly twice the table because the new copy exists before the old one is dropped. It cannot be resumed if it is interrupted, and there is no in-place path. A migration framework's change_column_type helper emits exactly that statement.

The Batched Backfill

The way around a rewrite is a new column and a backfill, and the backfill is where the second set of mistakes lives. A single UPDATE orders SET id_new = id writes a new version of all 40 million rows in one transaction: the table roughly doubles in dead tuples, the transaction's snapshot is held for the entire run and pins the vacuum horizon for the whole cluster while it lasts, and the resulting WAL arrives at pg-replica-a as one enormous burst. Chapters 6 and 7 explain why that held snapshot is the expensive part; the practical version is that one statement can make an unrelated table unvacuumable for an hour.

-- the migration driver runs this repeatedly and sleeps between calls
UPDATE orders SET id_new = id
 WHERE id IN (SELECT id FROM orders
               WHERE id_new IS NULL
               ORDER BY id
               LIMIT 10000);

-- and this keeps the driver's query off a sequential scan
CREATE INDEX CONCURRENTLY orders_backfill_todo
    ON orders (id) WHERE id_new IS NULL;

Each batch is its own transaction, so the snapshot is short, the dead tuples it creates are collectable immediately, and autovacuum keeps up rather than falling behind. The partial index shrinks as the backfill progresses, so the driver's own SELECT does not get slower as the remaining work gets smaller — and it is dropped along with the rest of the scaffolding at the end. Ten thousand rows a batch with a short sleep between them is deliberately boring: it can be stopped and restarted at any point, and the two numbers to watch while it runs are dead-tuple growth and replica lag.

Expand and Contract, on orders.id

Expand and contract is the pattern that makes a schema change and an application deploy independent of each other. Expand: add the new structure and start writing to both. Migrate: switch reads to the new structure. Contract: stop writing the old one and remove it. Three deploys instead of one, each individually safe to roll back, and every intermediate state runs in production with old and new code side by side.

ALTER TABLE orders ADD COLUMN id_new bigint;          -- catalogue only

CREATE FUNCTION orders_sync_id_new() RETURNS trigger
  LANGUAGE plpgsql AS $$
BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$;

CREATE TRIGGER orders_sync_id_new BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_id_new();

-- …batched backfill of the 40 million existing rows…

ALTER TABLE orders ADD CONSTRAINT orders_id_new_nn
      CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_nn;
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;

CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_uq ON orders (id_new);

Every statement in that sequence is either instant or takes a lock that blocks nothing. The trigger keeps new and updated rows in step while the backfill walks the old ones, so the two columns converge instead of racing. The NOT NULL arrives through the validated CHECK from the constraints topic, and it has to arrive before the swap: adopting an existing index as a primary key with ADD CONSTRAINT … PRIMARY KEY USING INDEX is a fast operation only when the columns are already marked NOT NULL, and otherwise it triggers the full scan you spent all this effort avoiding. Building the unique index CONCURRENTLY costs two passes over the table and lets writes continue throughout.

What remains is one short transaction, run under lock_timeout and retried until it wins the lock cleanly: drop the old primary key, adopt orders_id_new_uq as the new one, and rename id out of the way and id_new into its place. Renaming has no effect on stored data, so that part costs nothing. The genuinely hard piece is the foreign keys — order_items.order_id and delivery_events.order_id both reference this key, and each gets the same treatment on its own timeline: a new column, a trigger, a batched backfill, then ADD CONSTRAINT … NOT VALID followed by VALIDATE CONSTRAINT. Days of elapsed time, milliseconds of lock, and at no point is there a version of the schema that only works if a deploy finishes in the same second. Chapter 4 turns from changing the schema to querying it, with the SQL that replaces the three round trips the Cartwheel API currently makes to build one screen.