--- 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-30 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. > **Tan:** Expand-contract is paid for in calendar time: a change like the rename example is spread over three deploys. If the contract step is forgotten, the old column becomes permanent. Opening that step as its own ticket on the same day as the expand step keeps the cost from turning into debt. ## 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