Zero-downtime schema change (expand / contract)

Prerequisites

• ▹The ability to deploy application code and schema changes separately, in sequence.
• ▹A batched-backfill mechanism that can throttle and respect replication lag.
• ▹Enough visibility (pg_stat_activity / slow-query log) to watch for locks during the change.

Procedure

1. 1

   Expand — add the new shape additively, without a rewrite

   Add the new column nullable and with no default. On Postgres, adding a `NOT NULL DEFAULT` to a populated table rewrites it under a lock; a nullable column is metadata-only and instant. Build any new index `CONCURRENTLY` so it doesn't block writes.

   -- Instant: nullable, no default => no table rewrite.
   ALTER TABLE orders ADD COLUMN total_minor BIGINT NULL;
   
   -- CONCURRENTLY builds the index without blocking writes (run outside a txn).
   CREATE INDEX CONCURRENTLY idx_orders_total_minor ON orders (total_minor);

2. 2

   Dual-write — new code writes both old and new

   Deploy code that writes the old and the new column together. From this point every new and updated row is correct in both shapes; only historical rows still need backfilling. This phase is safe to run on its own for as long as you like.

   $order->total = $total;                 // old column
   $order->total_minor = $total->cents();  // new column
   $order->save();

3. 3

   Backfill history in throttled batches

   Copy old to new in bounded chunks with a pause between them, so the backfill never holds a long lock or saturates I/O, and replicas keep up. Never one giant `UPDATE` — that locks the table and blows replication lag.

   -- Repeat until zero rows updated; sleep between iterations.
   UPDATE orders
   SET    total_minor = ROUND(total * 100)
   WHERE  id IN (
       SELECT id FROM orders
       WHERE  total_minor IS NULL
       ORDER BY id
       LIMIT 5000
   );

4. 4

   Switch reads to the new column

   Once the backfill reports zero remaining NULLs, deploy code that reads the new column. Old and new are still both written, so this switch is instantly reversible — if anything looks wrong, revert the read path and you're back on the proven column.

5. 5

   Contract — stop writing the old shape, then drop it

   After a bake period on the new column, remove the dual-write, then drop the old column (and add `NOT NULL` now that every row is populated). Set a short `lock_timeout` so a DDL statement that can't immediately acquire its lock fails fast instead of queuing and stalling every query behind it.

   SET lock_timeout = '2s';   -- don't queue behind a long transaction
   
   ALTER TABLE orders ALTER COLUMN total_minor SET NOT NULL;
   ALTER TABLE orders DROP COLUMN total;