Skip to content
PtahDocs
v0.8.1
Page type: tutorial

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.

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

Section titled “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), Docker for a throwaway PostgreSQL, and about twenty minutes. PostgreSQL is the engine that can prove the most here; what each engine can promise is the rest of the picture.

Terminal window
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

Give it a table with enough rows that a scan is not instant:

Terminal window
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

Section titled “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:

Terminal window
export 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:

Terminal window
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:

Terminal window
docker exec ptah-zero-downtime psql -U ptah -d app -c "SET lock_timeout = '1s'; ALTER TABLE users ADD COLUMN nickname 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:

Terminal window
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:

Terminal window
docker exec ptah-zero-downtime psql -U ptah -d app -c "SET lock_timeout = '2s'; SELECT count(*) FROM users;"
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

Section titled “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:

Terminal window
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

Hold the table again, and apply against it:

Terminal window
docker exec -d ptah-zero-downtime psql -U ptah -d app -c "BEGIN; SELECT count(*) FROM users; SELECT pg_sleep(20);"
Terminal window
ptah migrations up --db-url "$DB" --migrations-dir ./migrations --lock-timeout 2s
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:

Terminal window
ptah migrations status --db-url "$DB" --migrations-dir ./migrations
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:

Terminal window
ptah migrations up --db-url "$DB" --migrations-dir ./migrations --lock-timeout 2s --allow-dirty
✅ 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

Section titled “Ask the linter what the change will cost”

Add the change you actually came for, written the way it comes to mind:

Terminal window
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
Terminal window
ptah migrations lint --dir ./migrations --dialect postgres
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 covers that side.

Rewrite each operation into the form that does not block

Section titled “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:

Terminal window
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

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:

Terminal window
ptah migrations lint --dir ./migrations --dialect postgres
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:

Terminal window
ptah migrations up --db-url "$DB" --migrations-dir ./migrations --lock-timeout 2s
✅ 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

Section titled “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

Section titled “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 is the reference for every rule, what it detects and what it suggests instead.

Clean up

Section titled “Clean up”
Terminal window
docker rm -f ptah-zero-downtime
cd ..
rm -rf ptah-zero-downtime