0
Challenge · 43Schema as Code

Safe migrations: locks & CONCURRENTLY

Toolingused in ~70% of apps

Your staging database has 40 rows. You run `create index on likes(user_id);`, it takes 4 milliseconds, the PR gets approved, and it ships. In production, `likes` has thirty million rows and real traffic — and for the ninety seconds your index takes to build, every single like, comment and post insert in the entire app queues up and waits. Nobody's query was slow. They were standing in line behind yours.

Core idea. Every DDL statement first has to acquire a lock on the table before it can do anything, and which MODE it asks for decides whether ordinary reads and writes can keep running while it works. A plain `CREATE INDEX` blocks writes; a naive `ALTER COLUMN ... SET NOT NULL` blocks reads and writes both, for as long as the scan takes. `CREATE INDEX CONCURRENTLY`, `NOT VALID` + `VALIDATE CONSTRAINT`, and a short `lock_timeout` are how you make the same schema change cost almost nothing to a live table, at the price of doing it as more than one step.
Playground, this isn't a screenshot
alter table posts add column is_public boolean not null default false;
Lock mode
ACCESS EXCLUSIVE (instant)
Reads
continue
Writes
continue
1 / 8
A constant default is metadata-only since PG11 — no table rewrite.

Every existing row is defined to read back the same literal default, so Postgres never has to touch them. The ACCESS EXCLUSIVE lock is still acquired, but for a few milliseconds — long enough to update the catalog, not to scan anything.

Watch the lock mode, not just the SQL. Two statements that look almost identical — step 4 and step 7 — take the same lock but for wildly different durations, because one of them can prove it doesn't need to scan.

↑ play a little first, then dig in