--- title: "Changing Schema in Production With Zero Downtime" description: "Running migrations without causing downtime in production: backward-compatible steps, multi-phase column changes, and avoiding table locks" url: https://sade.dev/en/notes/zero-downtime-database-migrations/ lang: en author: "Muhammet Şafak" published: 2026-08-29 updated: 2026-09-06 section: Note tags: ["postgresql","database","migrations","production"] summary: "A schema change is not what is dangerous; doing it in one step is. Only operations taking an ACCESS EXCLUSIVE lock queue up reads as well as writes, and on a fifty-million-row table that queue is the outage. Break the change into expand-contract steps: add the constraint NOT VALID and run VALIDATE separately, build indexes CONCURRENTLY, and give the migration session a short lock_timeout." --- # Changing Schema in Production With Zero Downtime > A schema change is not what is dangerous; doing it in one step is. Only operations taking an ACCESS EXCLUSIVE lock queue up reads as well as writes, and on a fifty-million-row table that queue is the outage. Break the change into expand-contract steps: add the constraint NOT VALID and run VALIDATE separately, build indexes CONCURRENTLY, and give the migration session a short lock_timeout. A deploy ran a single `ALTER TABLE` on a table with 50 million rows and locked it for four minutes. For four minutes, every request that touched that table waited; the site was effectively down. The migration itself was correct — the problem was doing it in one step. The dangerous thing is not the schema change. The dangerous thing is making a schema change all at once, without thinking about backward compatibility. ## Who takes the lock? Not every schema change costs the same. In modern PostgreSQL, adding a column with a constant `DEFAULT` is a metadata operation — it's fast. The real danger is in operations that lock the table for a long time: - `CREATE INDEX` — without `CONCURRENTLY`, it closes the table to writes. - `ALTER COLUMN ... TYPE` changes that rewrite the table. - `NOT NULL`, `CHECK`, or foreign key additions that scan the whole table. These operations take a strong lock; the ones that take it in `ACCESS EXCLUSIVE` mode — a table rewrite, or adding `NOT NULL`/`CHECK` — queue up every query touching the table, reads included. The rest block writes only. If the table is large, the queue grows. ## The dangerous part is the single step The solution is not to avoid migrations; it's to break every dangerous migration into small steps, each of which is safe and backward-compatible on its own. This is called the **expand-contract** pattern. Suppose you want to rename a column. A single-step `RENAME COLUMN` instantly breaks running code that reads the old column. Instead, three deploys: 1. **Expand.** Add the new column. Have the code write to both old and new, still reading from old. 2. **Migrate.** Move the old data into the new column in batches. Now have the code read from new. 3. **Contract.** Drop the old column. Each step works with both the code from the previous release and the new code. At no moment is the running code incompatible with the schema. ## Safe recipes The zero-downtime versions of common changes: **A new `NOT NULL` column.** Adding `NOT NULL` in one step scans the table. Split it: ```sql -- 1. Add it as nullable first ALTER TABLE orders ADD COLUMN status text; -- 2. Backfill existing rows in batches (on the application side) -- 3. Add the constraint NOT VALID first, then validate in a separate step ALTER TABLE orders ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null; ``` A constraint added with `NOT VALID` applies immediately to new rows but does not scan existing ones; `VALIDATE` then scans the table with only a `SHARE UPDATE EXCLUSIVE` lock — it doesn't block writes. **An index.** Always `CONCURRENTLY`: ```sql CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status); ``` **A foreign key.** The same two-step pattern: first `ADD CONSTRAINT ... NOT VALID`, then `VALIDATE CONSTRAINT`. **`lock_timeout`.** While a migration waits for a lock behind a long-running query, it blocks everything behind it too. To prevent this, give the migration session a short `lock_timeout` — if the lock can't be taken immediately, let the migration fail instead of waiting, and retry it yourself. ## Code and schema must be compatible together The essence of expand-contract is one rule: at every intermediate step, both the **old code still running** and the **new code** must be able to work with the current schema. The database schema is what I called a "one-way door" in [the cost of just-in-case code](/en/journal/the-cost-of-just-in-case-code) — rolling it back is expensive. So design the change not as one big, irreversible step, but as small steps that can each be rolled back individually. --- Zero-downtime migration is not a tool but a discipline: breaking every schema change into steps small enough that the running code never notices. The dangerous thing is not the change itself, but doing it in one breath. ## Frequently asked **Which schema changes block reads and not just writes?** The ones that take an ACCESS EXCLUSIVE lock: a table rewrite, or adding NOT NULL or CHECK. Only that lock mode conflicts with a plain SELECT. A CREATE INDEX without CONCURRENTLY takes a weaker lock that closes the table to writes but lets reads through. **Why add a constraint NOT VALID first and validate it in a separate step?** A constraint added with NOT VALID applies to new rows immediately without scanning the table, so it commits at once. The separate VALIDATE CONSTRAINT step then scans the table holding only a SHARE UPDATE EXCLUSIVE lock, which does not block writes. ## Sources - [ALTER TABLE: NOT VALID constraints, VALIDATE CONSTRAINT and ADD COLUMN with a DEFAULT](https://www.postgresql.org/docs/current/sql-altertable.html) — PostgreSQL - [Explicit Locking: table-level lock modes](https://www.postgresql.org/docs/current/explicit-locking.html) — PostgreSQL - [CREATE INDEX: building indexes concurrently](https://www.postgresql.org/docs/current/sql-createindex.html) — PostgreSQL - [Client Connection Defaults: lock_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html) — PostgreSQL