1. Courses
  2. System Design
  3. SQL & Data Modeling
  4. Stop 6

40 minIntermediate

Changing the schema without downtime

A one-line ALTER TABLE can freeze every store's tills for minutes, even though it takes milliseconds on your laptop. This lesson shows why, which changes are safe on a busy table, and the expand-and-contract method for the ones that are not.

By the end you can

  • Explain how one waiting ALTER TABLE blocks every query behind it
  • Sort common schema changes into instant, slow-but-safe, and dangerous
  • Protect every migration with lock_timeout and a retry
  • Rename a column or add NOT NULL safely, across several deploys, with a batched backfill
  • Ship Prisma migrations to production correctly, including CREATE INDEX CONCURRENTLY

The everyday picture

Say a store needs a new shelf layout for the festival range. One way: close the shop for a day, pull everything out, rebuild, restock, reopen. Customers go to the shop next door.

The better way: build the new shelf next to the old one while the shop stays open. Move the products across, a few boxes at a time, in quiet hours. Put up new aisle signs. Once nobody looks at the old shelf any more, take it down. Each step is small, the shop never closes, and at every moment a customer can find what they need.

Changing a live database works the same way. You add the new thing first, move the data in small batches, switch the app over, and remove the old thing last. This lesson is about doing each step without "closing the shop".

Why a tiny ALTER can stop the whole app

Every query takes a lock on the tables it uses. A normal SELECT takes the weakest lock, ACCESS SHARE. Thousands of them can share a table at once, and they also share with inserts and updates. Most forms of ALTER TABLE need the strongest lock, , which allows nothing else at all. In tests on Postgres 16, adding a column, setting a default, renaming a column, dropping a column and adding a CHECK constraint all took ACCESS EXCLUSIVE.

Usually the ALTER gets its lock, finishes in a few milliseconds, and nobody notices. The trouble starts when something else is already using the table. Postgres grants locks in a queue, in arrival order. So:

  1. A slow report has been reading grn (the goods received notes, one row per delivery) for 40 seconds. It holds ACCESS SHARE.
  2. Your migration's ALTER TABLE grn ADD COLUMN … arrives. It needs ACCESS EXCLUSIVE, so it waits for the report.
  3. Every new query on grn, even a simple SELECT, now queues behind the waiting ALTER, because it would conflict with the lock the ALTER is waiting for.

The ALTER itself would take 2 ms. But for the remaining 40 seconds of that report, nothing can touch the table. Requests pile up, the connection pool runs out, and the app goes down.

Lock queue on table grn 1. Monthly GRN report ACCESS SHARE · granted · runs 40 s waits for 1 2. ALTER TABLE grn ADD COLUMN ACCESS EXCLUSIVE · waiting wait for 2 3. SELECT pending GRNs · waiting 4. INSERT new GRN · waiting 5…200. every other query · waiting Fix: SET lock_timeout = '3s' ALTER gives up, queue drains, retry later
Postgres grants locks in order. The ALTER cannot start until the report finishes, and everything that arrives after the ALTER waits behind it. When this was reproduced on Postgres 16, pg_blocking_pids showed three ordinary SELECTs blocked by the ALTER, and the ALTER blocked by the report.

Quick check

A report has been reading grn for 30 seconds and needs 10 more. An ALTER TABLE grn ADD COLUMN note text arrives, then a simple SELECT on grn. When does the SELECT run?

Loading the rest of the lesson…