# Change a schema without downtime
Read what a migration will lock, rewrite it into the form that does not, and cap the wait so a busy table cannot be parked behind it.
Source: https://docs.ptah.run/v0.8.1/operate/zero-downtime-changes/
import { Tabs, TabItem } from '@astrojs/starlight/components';
You have a change to make to a table an application is reading and writing right
now. The question is not whether the SQL is correct. It is whether the database
will hold a lock long enough for requests to pile up behind it.
## What this page can promise
Ptah shapes the SQL and caps how long it waits. That is the whole of it, and it
is the honest way to read every claim below.
Ptah cannot see how many rows the table holds, how much traffic it takes, how
far behind a replica is, or how your application rolls out. Those decide whether
a statement that is safe in shape is also safe today, and they stay yours.
You need a `ptah` binary ([install Ptah](../../start/install/)), Docker for a
throwaway PostgreSQL, and about twenty minutes. PostgreSQL is the engine that
can prove the most here; [what each engine can
promise](#what-each-engine-can-promise) is the rest of the picture.
```bash
mkdir -p ptah-zero-downtime/migrations
cd ptah-zero-downtime
docker run -d --name ptah-zero-downtime \
-e POSTGRES_USER=ptah -e POSTGRES_PASSWORD=ptah -e POSTGRES_DB=app \
-p 55434:5432 postgres:18-alpine
```
```powershell
New-Item -ItemType Directory ptah-zero-downtime/migrations | Out-Null
Set-Location ptah-zero-downtime
docker run -d --name ptah-zero-downtime `
-e POSTGRES_USER=ptah -e POSTGRES_PASSWORD=ptah -e POSTGRES_DB=app `
-p 55434:5432 postgres:18-alpine
```
Give it a table with enough rows that a scan is not instant:
```console
docker exec ptah-zero-downtime psql -U ptah -d app -c "CREATE TABLE users (id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL); INSERT INTO users (email) SELECT 'u' || g || '@example.com' FROM generate_series(1, 50000) g;"
```
## The lock queue is the part that surprises people
`ALTER TABLE ... ADD COLUMN` with no default is a catalog edit. It touches no
rows and finishes in microseconds. It still takes an `ACCESS EXCLUSIVE` lock to
do it, and that is where an outage comes from.
Name the database once:
```bash
export DB='postgres://ptah:ptah@localhost:55434/app?sslmode=disable'
```
```powershell
$DB = 'postgres://ptah:ptah@localhost:55434/app?sslmode=disable'
```
Start a transaction that reads the table and stays open for twenty seconds,
which is what a slow query, a forgotten session or a long report looks like to
the database. `-d` leaves it running in the background:
```console
docker exec -d ptah-zero-downtime psql -U ptah -d app -c "BEGIN; SELECT count(*) FROM users; SELECT pg_sleep(20);"
```
Now ask for the instant change. It does not run, because it cannot get its lock:
```console
docker exec ptah-zero-downtime psql -U ptah -d app -c "SET lock_timeout = '1s'; ALTER TABLE users ADD COLUMN nickname TEXT;"
```
```text
SET
ERROR: canceling statement due to lock timeout
```
That much is ordinary. The part that is not needs an `ALTER` that waits rather
than giving up, so start one with no timeout of its own:
```console
docker exec -d ptah-zero-downtime psql -U ptah -d app -c "ALTER TABLE users ADD COLUMN nickname TEXT;"
```
It is now queued behind the open transaction. Read the table while it waits:
```console
docker exec ptah-zero-downtime psql -U ptah -d app -c "SET lock_timeout = '2s'; SELECT count(*) FROM users;"
```
```text
ERROR: canceling statement due to lock timeout
```
A `SELECT` refused, with no writer anywhere in sight. PostgreSQL's lock queue is
first in, first out, so the waiting `ALTER` sits ahead of every reader and
writer that arrives after it, and one statement that cannot start parks the
whole table. The outage is not the change. It is the queue the change builds
while it waits for a transaction that has nothing to do with it.
The first transaction ends after its twenty seconds, the waiting `ALTER` then
gets its lock, and `users` has its column.
### Cap the wait
`ptah migrations up` takes `--lock-timeout`, and a migration that cannot get its
lock inside that window gives up rather than holding the door open. Write a
migration to prove it on:
```bash
cat > migrations/0000000001_bio.up.sql <<'SQL'
ALTER TABLE users ADD COLUMN bio TEXT;
SQL
cat > migrations/0000000001_bio.down.sql <<'SQL'
ALTER TABLE users DROP COLUMN bio;
SQL
```
```powershell
@'
ALTER TABLE users ADD COLUMN bio TEXT;
'@ | Set-Content migrations/0000000001_bio.up.sql
@'
ALTER TABLE users DROP COLUMN bio;
'@ | Set-Content migrations/0000000001_bio.down.sql
```
Hold the table again, and apply against it:
```console
docker exec -d ptah-zero-downtime psql -U ptah -d app -c "BEGIN; SELECT count(*) FROM users; SELECT pg_sleep(20);"
```
```console
ptah migrations up --db-url "$DB" --migrations-dir ./migrations --lock-timeout 2s
```
```text
error: error running migrations: failed to apply migration 1: failed to execute migration SQL: SQL execution failed: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
SQL: ALTER TABLE users ADD COLUMN bio TEXT
```
That run left something behind, because a migration that started and did not
finish is not the same as one that never started:
```console
ptah migrations status --db-url "$DB" --migrations-dir ./migrations
```
```text
Status: ❌ Dirty migration state detected
Dirty Migration: version=1 state=failed direction=up applied=0/1
Error: failed to execute migration SQL: SQL execution failed: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
SQL: ALTER TABLE users ADD COLUMN bio TEXT
Error Statement: ALTER TABLE users ADD COLUMN bio TEXT
```
`applied=0/1` is the number that decides what to do next: no statement of this
migration ran, so there is nothing to reconcile and the answer is to run it
again once the long transaction is gone. `--allow-dirty` is how you say you read
that line:
```console
ptah migrations up --db-url "$DB" --migrations-dir ./migrations --lock-timeout 2s --allow-dirty
```
```text
✅ Migrations completed successfully!
Database is now at version: 1
```
A migration you retry in a minute is a better outcome than a table nobody can
read, and that is the whole argument for the timeout. `--statement-timeout` caps
the other half, where the statement does get its lock and then runs too long.
Read `applied=` before reaching for anything else. A migration that stopped
partway through several statements did change the database, and
`ptah migrations repair` is the verb for recording what you then fix by hand.
On this row it refuses rather than recording a migration that never ran, and
says the same thing the status hint does.
## Ask the linter what the change will cost
Add the change you actually came for, written the way it comes to mind:
```bash
cat > migrations/0000000002_unsafe.up.sql <<'SQL'
CREATE INDEX idx_users_email ON users (email);
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
SQL
cat > migrations/0000000002_unsafe.down.sql <<'SQL'
DROP INDEX idx_users_email;
SQL
```
```powershell
@'
CREATE INDEX idx_users_email ON users (email);
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
'@ | Set-Content migrations/0000000002_unsafe.up.sql
@'
DROP INDEX idx_users_email;
'@ | Set-Content migrations/0000000002_unsafe.down.sql
```
```console
ptah migrations lint --dir ./migrations --dialect postgres
```
```text
migrations/0000000002_unsafe.down.sql:1 [warning] PG106: DROP INDEX without CONCURRENTLY blocks writes while PostgreSQL removes the index; use DROP INDEX CONCURRENTLY outside a transaction for populated tables (index dropped with a table lock)
migrations/0000000002_unsafe.up.sql:1 [warning] PG101: CREATE INDEX without CONCURRENTLY blocks writes to the table for the whole build; on a populated table use CREATE INDEX CONCURRENTLY outside a transaction (index built with a table lock)
migrations/0000000002_unsafe.up.sql:2 [warning] PG303: SET NOT NULL scans the whole table under an ACCESS EXCLUSIVE lock to check existing rows, and a row holding NULL aborts it (column contains null values); backfill first, then add CHECK (col IS NOT NULL) NOT VALID, validate it under a weaker lock, and SET NOT NULL afterwards, which then scans nothing (not-null validation scans existing rows)
3 finding(s).
```
Read PG303 again: it is the whole recipe, in order, inside the finding. That is
what the catalog is for. The rules name the rewrite rather than only the cost,
and the ones you will meet most are `PG101` and `PG106` for index builds and
drops, `PG104` and `PG105` for primary keys and unique constraints, `PG303` for
`SET NOT NULL`, `PG305` for `CHECK`, and `PG301` for a column type change.
Findings are warnings, so the command still exits zero. `--fail-on any` is what
turns the catalog into a gate, because it fails on every finding rather than on
errors alone; [run checks in CI](../../testing/ci/) covers that side.
## Rewrite each operation into the form that does not block
**An index builds concurrently.** It takes a weaker lock and lets writes
continue, at the cost of two passes over the table and a build that cannot run
inside a transaction. Ptah's directive declares that. Rewrite both directions of
the file the linter named:
```bash
cat > migrations/0000000002_unsafe.up.sql <<'SQL'
-- +ptah no_transaction
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
SQL
cat > migrations/0000000002_unsafe.down.sql <<'SQL'
-- +ptah no_transaction
DROP INDEX CONCURRENTLY idx_users_email;
SQL
```
```powershell
@'
-- +ptah no_transaction
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
'@ | Set-Content migrations/0000000002_unsafe.up.sql
@'
-- +ptah no_transaction
DROP INDEX CONCURRENTLY idx_users_email;
'@ | Set-Content migrations/0000000002_unsafe.down.sql
```
**A constraint arrives unvalidated, then validates.** `NOT VALID` adds it
without reading existing rows, and `VALIDATE CONSTRAINT` reads them under a lock
that lets writes through.
**`SET NOT NULL` goes through a validated `CHECK`,** in the order PG303 prints:
backfill, add `CHECK (col IS NOT NULL) NOT VALID`, validate it, then
`SET NOT NULL`, which now scans nothing because the check already proved it. The
rewrite above drops the `SET NOT NULL` rather than showing all four statements,
which is the other honest answer: a column that has to become `NOT NULL` is its
own migration, after the backfill.
**A type change is a new column, a backfill and a swap.** `ALTER COLUMN TYPE`
rewrites the table and every index on it. Adding a column, backfilling it in
batches and swapping the two takes longer on the clock and never holds the
table.
The directory now has nothing left to report:
```console
ptah migrations lint --dir ./migrations --dialect postgres
```
```text
No lint findings.
```
The rewritten migration still runs under the lock timeout. A migration marked
`no_transaction` commits each statement on its own, so Ptah sets the timeout on
the database session that runs them rather than on a transaction:
```console
ptah migrations up --db-url "$DB" --migrations-dir ./migrations --lock-timeout 2s
```
```text
✅ Migrations completed successfully!
Database is now at version: 2
```
The constraint pair needs that timeout. `ADD CONSTRAINT ... NOT VALID` takes
`ACCESS EXCLUSIVE` for an instant, so it queues behind a long transaction like
any other `ALTER`, and every later reader queues behind it.
A concurrent index build meets the timeout in one more place. After it starts,
it waits for the open transactions that write to the table, and the lock timeout
bounds that wait too. When the wait runs out, the build stops and leaves an
invalid index behind. The next attempt then fails because that index still holds
the name. Drop it with `DROP INDEX CONCURRENTLY`, and run the migration again
with `--allow-dirty`. If the table has long write transactions, give the index
migration a longer timeout of its own with `-- +ptah lock_timeout=`.
## Expand and contract, because deployed code outlives a migration
Adding a column is safe and dropping one is not, and the reason has nothing to
do with the database. Between the migration and the last old process exiting,
code that still names the column is running.
So a change that removes anything is two deploys:
| Deploy | Schema | Code |
| --- | --- | --- |
| First | Add the new column and backfill it, keep the old one | Write both, read the old one |
| Between | Nothing | Switch reads to the new column, still writing both |
| Second | Drop the old column | Stop writing the old one |
A rename is a drop and an add wearing one statement, so it is the same shape:
there is no deploy in which both halves of a rolling fleet agree about the name.
Add, backfill, switch, drop.
## What each engine can promise
| Engine | What it can do |
| --- | --- |
| PostgreSQL | Concurrent index builds, `NOT VALID` constraints, and a lock timeout that fails instead of queueing. The rules prove the most here. |
| MySQL and MariaDB | `ALGORITHM=INPLACE, LOCK=NONE` makes the server refuse a change that would copy the table, which turns a silent outage into an error. The `MY1xx` rules name the operations that cannot take it, and configured online DDL routing sends those to an external tool. |
| SQLite | Rebuilds the table for nearly every change, holding a database-wide write lock while it does. It is the wrong engine for this promise, which is worth knowing rather than working around. |
[Migration lint rules](../../reference/lint-rules/) is the reference for every
rule, what it detects and what it suggests instead.
## Clean up
```bash
docker rm -f ptah-zero-downtime
cd ..
rm -rf ptah-zero-downtime
```
```powershell
docker rm -f ptah-zero-downtime
Set-Location ..
Remove-Item -Recurse -Force ptah-zero-downtime
```