How to Run Zero-Downtime Schema Migrations on PostgreSQL 18 and Block Unsafe ALTER TABLE Statements With a Python Lint Gate
Measure what 17 common PostgreSQL 18 statements lock on a 2,000,000-row table, watch one idle transaction freeze it, then migrate safely with lock_timeout, NOT VALID constraints and concurrent...
A schema migration is a change to the structure of a database: add a column, add an index, tighten a rule. On a small table it takes a few milliseconds and nobody notices. On a table with millions of rows, the same statement can make every query against that table wait, and a whole application looks down even though the database never stopped running. In this tutorial you will see why that happens in PostgreSQL 18, run the common migrations without the stall, and build a small Python program, a lint gate, that refuses the unsafe ones before they ship.
Table Of Content
- The ideas you need first
- Locks and the waiting line
- Three kinds of work a statement can do
- Words used in the rest of the tutorial
- Prerequisites
- Step 1: Create a database with 2,000,000 orders
- Step 2: Measure what each statement locks, rewrites and holds
- How to read the table
- A note on foreign keys
- Step 3: See how a one-millisecond statement freezes a table
- The fix: a short lock_timeout and a retry loop
- Which statements queue everyone, and which do not
- Step 4: Add NOT NULL without a long lock
- Step 5: Build indexes without blocking writes
- Step 6: Change a column type with expand, backfill and contract
- Step 7: Write a lint gate that refuses unsafe migrations
- Step 8: Apply the approved migration while traffic runs
- Common mistakes and how to spot them
- Check that everything works end to end
- Where to go next
- Sources
You do not need to know anything about PostgreSQL locks to start. Each step gives you a command to run, shows the output I got, and explains what the output means. Everything was run against PostgreSQL 18.6 on a 2,000,000-row table, so the timings are measurements from one machine, not promises about yours.
The ideas you need first
Locks and the waiting line
A lock is PostgreSQL’s way of keeping two statements from stepping on each other. Every statement asks for a lock on each table it touches, in a mode that says what it intends to do. Reading a table asks for ACCESS SHARE. Changing rows asks for ROW EXCLUSIVE. Two statements can hold their locks together only if their modes do not conflict, and the PostgreSQL documentation lists the conflicts: ACCESS SHARE, for example, “Conflicts with the ACCESS EXCLUSIVE lock mode only.” Most forms of ALTER TABLE ask for that strongest mode: “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted. When multiple subcommands are given, the lock acquired will be the strictest one required by any subcommand.”
When a lock cannot be granted yet, the request does not fail. It waits in a queue, and that is the part that surprises people. The GoCardless engineering team described it after a planned migration took their API down for about 15 seconds: “When a lock can’t be acquired because of a lock held by another transaction, it goes into a queue. Any locks that conflict with the queued lock will queue up behind it.” So a statement that needs only a millisecond of work can still freeze a table, because everything that arrives after it lines up behind the wait.
Three kinds of work a statement can do
Once the lock is granted, a statement does one of three things, and the difference decides how long everyone else waits.
A metadata change edits the catalog and touches no rows, so it took about a millisecond on both of my tables. A scan reads every row to check a rule, so its time grows with the table. A rewrite builds a new copy of the table and its indexes, which is the slowest of the three. In step 2 you will measure all three kinds instead of taking my word for it.
Words used in the rest of the tutorial
A backfill fills a new column for the rows that already exist. Expand and contract is the habit of changing a database in two safe stages: first add the new structure next to the old one (expand), and only after no running code uses the old structure, remove it (contract). A lint gate is a program that reads a migration file, applies rules, and exits with a failure code if a rule is broken, so that a deploy pipeline can refuse the file.
Prerequisites
You need a PostgreSQL 18 server you can connect to as a superuser, because the lab creates a database and runs CHECKPOINT. I used PostgreSQL 18.6 on Windows 11 with the server listening on port 55432; any PostgreSQL 18 server should work. You need Python 3.10 or newer (psycopg 3.3.6 and pytest 9.2.0 both declare Requires-Python >=3.10; I used 3.13.14), about 1 GB of free disk, and five to ten minutes for the full run. You should know basic SQL (SELECT, INSERT, CREATE TABLE) and be able to read short Python scripts.
Create a folder, a virtual environment and install the two packages. Then tell the PostgreSQL client library where your server is. The scripts read the standard PGHOST, PGPORT and PGUSER variables (add PGPASSWORD if your server wants one).
python -m venv .venv
.venv/bin/python -m pip install "psycopg[binary]" pytest
export PGHOST=localhost PGPORT=5432 PGUSER=postgresOn Windows the interpreter is .venv\Scripts\python.exe, and in PowerShell you set the variables with $env:PGPORT = "5432". Every file below goes in that one folder, and the first line of each is a comment with its own name so you can tell them apart.
Step 1: Create a database with 2,000,000 orders
The lab needs a table big enough that slow statements are visibly slow. lab.py builds a customers table with 1,000 rows and an orders table with 2,000,000 rows, plus a 200,000-row copy named orders_small. The copy exists for one reason: if a statement takes about ten times longer on the table with ten times the rows, its time grows with the table and it will hurt more as your data grows. That is the test you will use in step 2.
The file also holds two helpers you will reuse. connect() opens an autocommit connection (no hidden transactions, so what you see is what runs). Probe is a thread that runs one cheap statement over and over, dozens of times a second, and records how long each call took. That is how an application feels a migration: not as a lock name, but as calls that suddenly take seconds. The seed() function rebuilds the tables if an earlier run left them changed, and its last act is a CHECKPOINT so that a disk flush from loading the data is not mistaken for a lock later.
# lab.py
"""lab.py: shared helpers for the migration lab: connections, seed data and a latency probe."""
import threading
import time
import psycopg
ADMIN_DSN = "dbname=postgres"
DSN = "dbname=migratelab"
BIG, SMALL = 2_000_000, 200_000
BASELINE = ("id:bigint:NO,customer_id:integer:NO,status:text:YES,"
"amount_cents:integer:NO,created_at:timestamp with time zone:NO")
LOCKS = ["AccessShareLock", "RowShareLock", "RowExclusiveLock", "ShareUpdateExclusiveLock",
"ShareLock", "ShareRowExclusiveLock", "ExclusiveLock", "AccessExclusiveLock"]
def say(text=""):
print(text, flush=True)
def connect(name="lab", autocommit=True):
"""libpq reads PGHOST, PGPORT and PGUSER from the environment, so the DSN stays short."""
return psycopg.connect(DSN, autocommit=autocommit, application_name=name)
def strongest(modes):
return max(modes, key=LOCKS.index) if modes else None
def build_orders(conn, table, rows):
conn.execute(f"""CREATE TABLE {table} (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text,
amount_cents integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now())""")
conn.execute("SELECT setseed(0.42)")
conn.execute(f"""INSERT INTO {table} (customer_id, status, amount_cents, created_at)
SELECT 1 + floor(random() * 1000)::int,
(ARRAY['new', 'paid', 'shipped', 'cancelled'])[1 + floor(random() * 4)::int],
floor(random() * 100000)::int,
timestamptz '2026-01-01' + random() * 300 * interval '1 day'
FROM generate_series(1, {rows})""")
conn.execute(f"CREATE INDEX {table}_amount_idx ON {table} (amount_cents)")
conn.execute(f"VACUUM ANALYZE {table}")
def is_baseline(conn):
if not conn.execute("SELECT to_regclass('orders') IS NOT NULL "
"AND to_regclass('orders_small') IS NOT NULL").fetchone()[0]:
return False
shape = conn.execute("""SELECT string_agg(column_name || ':' || data_type || ':' || is_nullable, ','
ORDER BY ordinal_position)
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'""").fetchone()[0]
extras = conn.execute("""SELECT (SELECT count(*) FROM pg_indexes WHERE tablename = 'orders')
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal)
+ (SELECT count(*) FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype IN ('c', 'f', 'u'))""").fetchone()[0]
return shape == BASELINE and extras == 2 # the primary key and the amount index
def seed():
"""Create the lab database once, then make sure orders is the 2,000,000-row baseline."""
with psycopg.connect(ADMIN_DSN, autocommit=True) as admin:
if not admin.execute("SELECT 1 FROM pg_database WHERE datname = 'migratelab'").fetchone():
admin.execute("CREATE DATABASE migratelab")
with connect("seed") as conn:
if is_baseline(conn):
conn.execute("DELETE FROM orders WHERE status = 'probe'")
return
started = time.perf_counter()
conn.execute("DROP TABLE IF EXISTS orders, orders_small, customers CASCADE")
conn.execute("CREATE TABLE customers (id integer PRIMARY KEY, name text NOT NULL)")
conn.execute("INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 1000) AS g")
build_orders(conn, "orders", BIG)
build_orders(conn, "orders_small", SMALL)
conn.execute("CHECKPOINT") # start from a quiet server, so a flush is not mistaken for a lock
say(f"seeded {BIG:,} + {SMALL:,} rows in {time.perf_counter() - started:.1f} s")
class Probe(threading.Thread):
"""Run one cheap statement over and over and record how long each call took, as an app would feel it."""
def __init__(self, label, sql, args=None, period=0.01):
super().__init__(daemon=True)
self.label, self.sql, self.args, self.period = label, sql, args, period
self.samples, self.errors = [], 0
self.halt = threading.Event()
self.t0 = time.perf_counter()
def run(self):
conn = connect(self.label)
conn.execute("SET lock_timeout = '60s'")
n = 0
while not self.halt.is_set():
args = self.args(n) if callable(self.args) else self.args
started = time.perf_counter()
try:
conn.execute(self.sql, args)
except psycopg.Error:
self.errors += 1
self.samples.append((started - self.t0, time.perf_counter() - started))
n += 1
time.sleep(self.period)
conn.close()
def finish(self):
self.halt.set()
self.join()
taken = sorted(s[1] for s in self.samples)
return {"calls": len(taken), "median_ms": taken[len(taken) // 2] * 1000,
"max_ms": taken[-1] * 1000, "over_100ms": sum(t > 0.1 for t in taken),
"errors": self.errors, "worst_at": max(self.samples, key=lambda s: s[1])[0]}
def fmt(s):
return (f"{s['calls']} calls, median {s['median_ms']:.1f} ms, max {s['max_ms']:.0f} ms, "
f"{s['over_100ms']} slower than 100 ms, {s['errors']} errors")You will run every step through a small runner. run_all.py runs the steps you name (or all of them, in order), saves each step’s output in an out folder, and stops at the first failure. It uses the PG* variables you exported and falls back to the values shown if they are missing. Save it next to lab.py:
# run_all.py
"""run_all.py [step ...]: run the steps in order with this venv's python and save out/NAME.txt."""
import os
import subprocess
import sys
LAB = os.path.dirname(os.path.abspath(__file__))
ORDER = ["step01_matrix", "step02_queue", "step03_notnull", "step04_index", "step05_bigint", "pytest",
"step06_gate", "step07_apply"]
wanted = sys.argv[1:] or ORDER
os.makedirs(f"{LAB}/out", exist_ok=True)
env = dict(os.environ, COLUMNS="170")
for name, value in {"PGHOST": "localhost", "PGPORT": "5432", "PGUSER": "postgres"}.items():
env.setdefault(name, value) # the variables you exported win; these are only fallbacks
for name in wanted:
if name == "pytest":
cmd = [sys.executable, "-X", "utf8", "-m", "pytest", "-q", "-p", "no:cacheprovider"]
else:
cmd = [sys.executable, "-X", "utf8", f"{LAB}/{name}.py"]
result = subprocess.run(cmd, cwd=LAB, capture_output=True, text=True, encoding="utf-8", env=env)
with open(f"{LAB}/out/{name}.txt", "w", encoding="utf-8", newline="\n") as fh:
fh.write(result.stdout + result.stderr)
print(f"== {name} rc={result.returncode}", flush=True)
if result.returncode != 0:
print((result.stdout + result.stderr)[-1500:], flush=True)
breakThe data is created the first time a step needs it, so you will meet the seeding in step 2, when you run python run_all.py step01_matrix. The first line of that output tells you how long the seeding took, and it depends on your disk:
seeded 2,000,000 + 200,000 rows in 9.2 sStep 2: Measure what each statement locks, rewrites and holds
This script runs seventeen common statements, each inside a transaction that it rolls back, so the tables never change. For each one it reads three facts while the transaction is still open. The first is the strongest lock the statement holds on the table, taken from the pg_locks system view. The second is whether the table was rewritten, which you can tell because pg_relation_filenode() returns a new number when PostgreSQL writes a new copy of the file. The third is how long the statement took, which is also how long it holds its lock, because nothing else happens before the rollback. It runs every statement twice on each table and keeps the faster run, to damp one-off noise.
Some statements need a setup step, for example a VALIDATE needs a constraint that was added NOT VALID first. Those setups are committed, the statement under test is measured, and a cleanup statement puts the table back.
# step01_matrix.py
"""step01_matrix.py: what does each statement lock, does it rewrite the table, and how long does it hold the
lock? Every statement runs inside a transaction that is rolled back, so the tables never change."""
import json
import os
import re
import time
from lab import connect, say, seed, strongest
BLOCKS_READS = {"AccessExclusiveLock"}
BLOCKS_WRITES = {"ShareLock", "ShareRowExclusiveLock", "ExclusiveLock", "AccessExclusiveLock"}
CK_NOT_VALID = "ALTER TABLE orders ADD CONSTRAINT orders_status_ck CHECK (status IS NOT NULL) NOT VALID"
CK_VALIDATE = "ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck"
CK_DROP = "ALTER TABLE orders DROP CONSTRAINT orders_status_ck"
NN_NOT_VALID = "ALTER TABLE orders ADD CONSTRAINT orders_status_nn NOT NULL status NOT VALID"
FK_NOT_VALID = ("ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) "
"REFERENCES customers (id) NOT VALID")
FK_DROP = "ALTER TABLE orders DROP CONSTRAINT orders_customer_fk"
CASES = [ # (label, setup statements committed first, statement under test, cleanup statements)
("ADD COLUMN, no default", [], "ALTER TABLE orders ADD COLUMN note text", []),
("ADD COLUMN, constant default", [],
"ALTER TABLE orders ADD COLUMN is_test boolean NOT NULL DEFAULT false", []),
("ADD COLUMN, volatile default", [],
"ALTER TABLE orders ADD COLUMN touched_at timestamptz NOT NULL DEFAULT clock_timestamp()", []),
("ALTER COLUMN TYPE bigint", [], "ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint", []),
("SET NOT NULL, plain", [], "ALTER TABLE orders ALTER COLUMN status SET NOT NULL", []),
("ADD CHECK ... NOT VALID", [], CK_NOT_VALID, []),
("VALIDATE the CHECK", [CK_NOT_VALID], CK_VALIDATE, [CK_DROP]),
("SET NOT NULL after a valid CHECK", [CK_NOT_VALID, CK_VALIDATE],
"ALTER TABLE orders ALTER COLUMN status SET NOT NULL", [CK_DROP]),
("ADD NOT NULL ... NOT VALID (PG 18)", [], NN_NOT_VALID, []),
("VALIDATE the NOT NULL (PG 18)", [NN_NOT_VALID],
"ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn",
["ALTER TABLE orders DROP CONSTRAINT orders_status_nn"]),
("CREATE INDEX", [], "CREATE INDEX orders_status_idx ON orders (status)", []),
("ADD FOREIGN KEY", [],
"ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id)", []),
("ADD FOREIGN KEY ... NOT VALID", [], FK_NOT_VALID, []),
("VALIDATE the FOREIGN KEY", [FK_NOT_VALID], "ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk",
[FK_DROP]),
("ADD UNIQUE (id, amount_cents)", [],
"ALTER TABLE orders ADD CONSTRAINT orders_amount_uq UNIQUE (id, amount_cents)", []),
("RENAME COLUMN", [], "ALTER TABLE orders RENAME COLUMN status TO state", []),
("DROP COLUMN", [], "ALTER TABLE orders DROP COLUMN created_at", []),
]
def measure(table, setup, sql, cleanup):
sub = lambda text: re.sub(r"\borders\b", table, text) # noqa: E731 run the same statement on the small copy
admin = connect("matrix-admin")
for statement in setup:
admin.execute(sub(statement))
try:
with connect("matrix", autocommit=False) as conn:
before = conn.execute(f"SELECT pg_relation_filenode('{table}')").fetchone()[0]
started = time.perf_counter()
conn.execute(sub(sql))
ms = (time.perf_counter() - started) * 1000
after = conn.execute(f"SELECT pg_relation_filenode('{table}')").fetchone()[0]
held = conn.execute("""SELECT c.relname, l.mode FROM pg_locks l JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = pg_backend_pid() AND l.granted AND l.locktype = 'relation'
AND c.relname IN (%s, 'customers')""", (table,)).fetchall()
conn.rollback()
finally:
for statement in cleanup:
admin.execute(sub(statement))
admin.close()
modes = {}
for relation, mode in held:
modes.setdefault(relation, []).append(mode)
return {"ms": ms, "rewrote": before != after, "table_lock": strongest(modes.get(table, [])),
"customers_lock": strongest(modes.get("customers", []))}
def best_of_two(table, setup, sql, cleanup):
runs = [measure(table, setup, sql, cleanup) for _ in range(2)]
return min(runs, key=lambda r: r["ms"])
def short(mode):
return mode[:-4] if mode else "-"
def main():
seed()
results = []
say(f"{'statement':36} {'lock on orders':22} {'also locks':19} {'blocks':13} {'rewrote':8}"
f" {'ms 2M':>8} {'ms 200k':>8} {'ratio':>6}")
for label, setup, sql, cleanup in CASES:
big = best_of_two("orders", setup, sql, cleanup)
small = best_of_two("orders_small", setup, sql, cleanup)
locks = {big["table_lock"], big["customers_lock"]}
blocks_writes = bool(locks & BLOCKS_WRITES)
blocks = "reads+writes" if locks & BLOCKS_READS else "writes" if blocks_writes else "nothing"
ratio = big["ms"] / small["ms"]
say(f"{label:36} {short(big['table_lock']):22} "
f"{('customers ' + short(big['customers_lock'])) if big['customers_lock'] else '-':19} "
f"{blocks:13} {'yes' if big['rewrote'] else 'no':8} {big['ms']:8.1f} {small['ms']:8.1f} {ratio:6.1f}")
results.append({"label": label, "setup": setup, "sql": sql, "table_lock": big["table_lock"],
"customers_lock": big["customers_lock"], "rewrote": big["rewrote"],
"blocks_writes": blocks_writes, "ms_big": big["ms"], "ms_small": small["ms"],
"ratio": ratio})
os.makedirs("out", exist_ok=True)
with open("out/matrix.json", "w", encoding="utf-8", newline="\n") as fh:
json.dump(results, fh, indent=1)
if __name__ == "__main__":
main()Run it. This is the table I got:
statement lock on orders also locks blocks rewrote ms 2M ms 200k ratio
ADD COLUMN, no default AccessExclusive - reads+writes no 0.6 0.6 1.0
ADD COLUMN, constant default AccessExclusive - reads+writes no 1.4 1.2 1.1
ADD COLUMN, volatile default AccessExclusive - reads+writes yes 2637.1 275.3 9.6
ALTER COLUMN TYPE bigint AccessExclusive - reads+writes yes 2452.7 300.3 8.2
SET NOT NULL, plain AccessExclusive - reads+writes no 80.8 9.5 8.5
ADD CHECK ... NOT VALID AccessExclusive - reads+writes no 1.0 0.8 1.2
VALIDATE the CHECK ShareUpdateExclusive - nothing no 66.9 7.2 9.3
SET NOT NULL after a valid CHECK AccessExclusive - reads+writes no 0.9 0.8 1.2
ADD NOT NULL ... NOT VALID (PG 18) AccessExclusive - reads+writes no 1.0 0.8 1.2
VALIDATE the NOT NULL (PG 18) ShareUpdateExclusive - nothing no 79.8 8.8 9.0
CREATE INDEX Share - writes no 659.9 92.5 7.1
ADD FOREIGN KEY ShareRowExclusive customers ShareRowExclusive writes no 105.4 12.8 8.2
ADD FOREIGN KEY ... NOT VALID ShareRowExclusive customers ShareRowExclusive writes no 1.1 1.2 0.9
VALIDATE the FOREIGN KEY ShareUpdateExclusive customers RowShare nothing no 105.4 12.2 8.6
ADD UNIQUE (id, amount_cents) AccessExclusive - reads+writes no 613.2 72.3 8.5
RENAME COLUMN AccessExclusive - reads+writes no 0.4 0.5 0.9
DROP COLUMN AccessExclusive - reads+writes no 1.0 0.8 1.2How to read the table
Read each row left to right. Lock on orders is the mode. Blocks says which other statements must wait while it is held: reads+writes means even a plain SELECT waits, writes means INSERT, UPDATE and DELETE wait but reads continue, and nothing means ordinary reads and writes carry on. Rewrote is the filenode test. The last three columns are the milliseconds on 2,000,000 rows, on 200,000 rows, and the ratio between them.
Four groups stand out.
Metadata only. Adding a column with no default or with a constant default, adding a CHECK constraint as NOT VALID, renaming, dropping a column, adding a not-null constraint as NOT VALID, and SET NOT NULL after a validated CHECK all took about a millisecond on both tables, with a ratio near 1. They still ask for ACCESS EXCLUSIVE, so they are only safe if they can get the lock quickly. That is the subject of step 3. The documentation explains why adding a column is cheap: “the default value is evaluated at the time of the statement and the result stored in the table’s metadata, where it will be returned when any existing rows are accessed.”
Rewrites. A volatile default (clock_timestamp()) took 2.6 seconds and a type change to bigint took 2.5 seconds, both with the filenode changing and ratios of 9.6 and 8.2 for ten times the rows. During that time every read and write waits. The documentation lists what triggers it: adding a column with a volatile default, a stored generated column, an identity column, or a domain with constraints “will cause the entire table and its indexes to be rewritten”, and “Changing the type of an existing column will normally cause the entire table and its indexes to be rewritten.”
Scans under a blocking lock. A plain SET NOT NULL (81 ms), CREATE INDEX (660 ms), ADD FOREIGN KEY (105 ms) and ADD UNIQUE (613 ms) read the whole table while they hold a lock that blocks writes, or reads too. Their ratios, 7.1 to 8.5 for ten times the rows, say that the time grows with the table. If it keeps growing in proportion, the 81 ms scan would hold its lock for roughly 8 seconds on 200 million rows. That is my arithmetic, not a measurement.
Scans that do not block. The three VALIDATE CONSTRAINT rows scan the whole table too (67, 80 and 105 ms), but their lock is SHARE UPDATE EXCLUSIVE, which does not block ordinary reads and writes. The documentation says so directly: “This command acquires a SHARE UPDATE EXCLUSIVE lock.” This is the trick the safe patterns in the rest of this tutorial rely on: split a check into a quick NOT VALID step that only takes a brief lock and a slow VALIDATE step that does the scan without blocking reads or writes.
Two more details are worth noticing. A SET NOT NULL that follows a validated CHECK took 0.9 ms instead of 81 ms, because, in the documentation’s words, “if a valid CHECK constraint exists (and is not dropped in the same command) which proves no NULL can exist, then the table scan is skipped.” And the two rows marked PG 18 exist because that release added a new option: its release notes say “Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints.” The ALTER TABLE page now says NOT VALID “is currently only allowed for foreign-key, CHECK, and not-null constraints.”
A note on foreign keys
The GoCardless post, whose current page says it was last edited in June 2024, says in a footnote that adding a foreign key, even as NOT VALID, makes PostgreSQL “briefly hold an AccessExclusive lock against both tables while it adds constraint triggers to them.” The row for ADD FOREIGN KEY with NOT VALID in my table shows something different on 18.6: SHARE ROW EXCLUSIVE on both orders and customers, which blocks writes but not reads. That matches the PostgreSQL 18 documentation: “ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock.” The 9.5 release notes contain an entry, “Reduce lock levels of some create and alter trigger and foreign key commands”, which is the likely origin of the change, though the entry does not name the lock modes, so I cannot say which statement in it covers this one. Step 3 tests the practical consequence.
Step 3: See how a one-millisecond statement freezes a table
Here is the experiment behind the kind of outage GoCardless described. Session A is an ordinary transaction that read some rows and then went idle without committing, like a console somebody left open. Session B runs a metadata-only ALTER TABLE that needs ACCESS EXCLUSIVE, so it must wait for A. Meanwhile two probes, a reader and a writer, keep calling the table. The script takes a look at pg_stat_activity 1.5 seconds in, using pg_blocking_pids() to show who waits for whom, and A commits at 3 seconds.
The same file runs four experiments. The first is the dangerous case. The second runs the same statement through the polite runner from migrate.py, which you will read in a moment. The third and fourth test the foreign-key claim from step 2 with two different sessions A.
# step02_queue.py
"""step02_queue.py: a metadata-only ALTER TABLE can still freeze a table, because it waits in the lock queue."""
import random
import threading
import time
from lab import DSN, Probe, connect, fmt, say, seed
from migrate import run_ddl
HOLD = 3.0 # seconds that session A keeps its transaction open
READ_ORDERS = ("reader", "SELECT id FROM orders WHERE id = 1")
WRITE_ORDERS = ("writer", "INSERT INTO orders (customer_id, status, amount_cents) VALUES (1, 'probe', 100)")
READ_CUSTOMERS = ("reader", "SELECT name FROM customers WHERE id = 2")
WRITE_CUSTOMERS = ("writer", "UPDATE customers SET name = name WHERE id = 2")
def experiment(title, a_sql, ddl, probes, cleanup, use_runner=False):
seed()
say(f"=== {title}")
a = connect("A", autocommit=False) # the open transaction, like an analyst's console left idle
a.execute(a_sql)
t0 = time.perf_counter()
running = [Probe(*spec) for spec in probes]
for probe in running:
probe.start()
result = {}
def migrate():
if use_runner:
result["attempts"] = run_ddl(DSN, ddl, lock_timeout_ms=200, rng=random.Random(7),
log=lambda m: say(f" migration {m}"))
else:
with connect("B") as b:
b.execute(ddl)
result["done"] = time.perf_counter() - t0
time.sleep(0.3)
worker = threading.Thread(target=migrate)
worker.start()
time.sleep(1.2)
with connect("watcher") as w:
rows = w.execute("""SELECT application_name, pid, state, wait_event_type, wait_event, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE datname = 'migratelab' AND application_name IN ('A', 'B', 'migration', 'reader', 'writer')
ORDER BY application_name""").fetchall()
names = {pid: name for name, pid, *_ in rows}
for name, pid, state, kind, event, blockers in rows:
by = ",".join(names.get(b, str(b)) for b in blockers) or "-"
say(f" at t=1.5 s {name:10} {state:20} waiting on {kind or '-'}:{event or '-'} blocked by {by}")
time.sleep(HOLD - 1.5)
a.commit()
say(f" A commits at t={time.perf_counter() - t0:.1f} s")
worker.join()
time.sleep(0.2)
for probe in running:
say(f" {probe.label:8} {fmt(probe.finish())}")
attempts = f" after {result['attempts']} attempts" if "attempts" in result else ""
say(f" the DDL finished at t={result['done']:.1f} s{attempts}")
a.close()
with connect("cleanup") as c:
for statement in cleanup:
c.execute(statement)
ADD_NOTE = "ALTER TABLE orders ADD COLUMN note text"
DROP_NOTE = "ALTER TABLE orders DROP COLUMN IF EXISTS note"
ADD_FK = ("ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) "
"REFERENCES customers (id) NOT VALID")
DROP_FK = "ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_fk"
if __name__ == "__main__":
experiment("1. ADD COLUMN behind an open transaction, no lock_timeout",
"SELECT count(*) FROM orders WHERE customer_id = 7", ADD_NOTE,
[READ_ORDERS, WRITE_ORDERS], [DROP_NOTE])
experiment("2. the same ADD COLUMN with lock_timeout 200 ms and retries",
"SELECT count(*) FROM orders WHERE customer_id = 7", ADD_NOTE,
[READ_ORDERS, WRITE_ORDERS], [DROP_NOTE], use_runner=True)
experiment("3. ADD FOREIGN KEY while A holds a plain read on customers",
"SELECT name FROM customers WHERE id = 1", ADD_FK,
[READ_CUSTOMERS, WRITE_CUSTOMERS], [DROP_FK])
experiment("4. ADD FOREIGN KEY while A holds an uncommitted write on customers",
"UPDATE customers SET name = name WHERE id = 1", ADD_FK,
[READ_CUSTOMERS, WRITE_CUSTOMERS], [DROP_FK])Experiment 1 first, the plain ALTER TABLE orders ADD COLUMN with no timeout:
=== 1. ADD COLUMN behind an open transaction, no lock_timeout
at t=1.5 s A idle in transaction waiting on Client:ClientRead blocked by -
at t=1.5 s B active waiting on Lock:relation blocked by A
at t=1.5 s reader active waiting on Lock:relation blocked by B
at t=1.5 s writer active waiting on Lock:relation blocked by B
A commits at t=3.0 s
reader 47 calls, median 0.3 ms, max 2710 ms, 1 slower than 100 ms, 0 errors
writer 47 calls, median 0.6 ms, max 2709 ms, 1 slower than 100 ms, 0 errors
the DDL finished at t=3.0 sRead the snapshot lines. A is idle in transaction and blocked by nobody, exactly as intended. B is waiting on Lock:relation, blocked by A. And the reader and the writer, which do not conflict with A at all, are blocked by B. A single wait made a wall: the reader’s and the writer’s calls took 2,710 and 2,709 ms instead of under a millisecond, and the ALTER TABLE itself did no work until A committed. Nothing is wrong with the statement. The lock queue turned it into an outage.
The fix: a short lock_timeout and a retry loop
PostgreSQL has a setting for exactly this. The documentation describes lock_timeout as: “Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object.” A statement that gives up leaves the queue, and everything behind it moves again. GoCardless gave the same advice: “Set lock_timeout in your migration scripts to a pause your app can tolerate. It’s better to abort a deploy than take your application down.”
Set it in the migration session, not globally: the documentation says “Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions.” migrate.py wraps one statement in that policy. It opens its own connection, sets a short lock_timeout and a long statement_timeout, runs the statement, and on a lock timeout (SQLSTATE 55P03, which psycopg raises as LockNotAvailable) waits with an exponentially growing, jittered pause and tries again. Statements containing CONCURRENTLY run outside a transaction block because PostgreSQL requires that, as you will see in step 5.
# migrate.py
"""migrate.py: run one DDL statement politely: short lock_timeout, retry with jitter, never queue for long."""
import random
import time
import psycopg
from psycopg import errors
def run_ddl(dsn, sql, lock_timeout_ms=200, statement_timeout_ms=120_000, attempts=10,
base_delay=0.2, max_delay=2.0, rng=None, log=None):
"""Run sql on its own connection and return how many attempts it took.
A statement that cannot get its lock within lock_timeout_ms gives the lock queue back and retries
after a jittered pause. Any other error is raised at once."""
rng = rng or random.Random()
for attempt in range(1, attempts + 1):
try:
with psycopg.connect(dsn, autocommit=True, application_name="migration") as conn:
conn.execute(f"SET lock_timeout = {int(lock_timeout_ms)}")
conn.execute(f"SET statement_timeout = {int(statement_timeout_ms)}")
if "CONCURRENTLY" in sql.upper():
conn.execute(sql) # these cannot run inside a transaction block
else:
with conn.transaction():
conn.execute(sql)
return attempt
except errors.LockNotAvailable:
delay = min(max_delay, base_delay * 2 ** (attempt - 1)) * rng.uniform(0.5, 1.0)
if log:
log(f"attempt {attempt}: no lock within {lock_timeout_ms} ms, retrying in {delay:.2f} s")
if attempt == attempts:
raise
time.sleep(delay)Experiment 2 runs the same ADD COLUMN through run_ddl with a 200 ms timeout:
=== 2. the same ADD COLUMN with lock_timeout 200 ms and retries
migration attempt 1: no lock within 200 ms, retrying in 0.13 s
migration attempt 2: no lock within 200 ms, retrying in 0.23 s
migration attempt 3: no lock within 200 ms, retrying in 0.66 s
at t=1.5 s A idle in transaction waiting on Client:ClientRead blocked by -
at t=1.5 s reader idle waiting on Client:ClientRead blocked by -
at t=1.5 s writer idle waiting on Client:ClientRead blocked by -
migration attempt 4: no lock within 200 ms, retrying in 0.86 s
A commits at t=3.0 s
reader 237 calls, median 0.3 ms, max 212 ms, 4 slower than 100 ms, 0 errors
writer 231 calls, median 0.5 ms, max 211 ms, 4 slower than 100 ms, 0 errors
the DDL finished at t=3.2 s after 5 attemptsThe migration gave up four times, then succeeded on the fifth attempt, 0.2 seconds after A committed. The reader’s and the writer’s worst calls were 212 and 211 ms instead of 2.7 seconds, and 4 calls in each probe were slower than 100 ms. Each failed attempt still blocks the table for up to the timeout, so the damage per attempt is bounded by the number you choose and not removed. Pick a timeout your application can absorb, and let the pauses between attempts grow, as run_ddl does, so the table gets quiet time between attempts.
Which statements queue everyone, and which do not
Experiments 3 and 4 add a foreign key as NOT VALID, which needs SHARE ROW EXCLUSIVE on customers. In experiment 3, session A only read a customers row. In experiment 4, A updated one without committing, which holds ROW EXCLUSIVE.
=== 3. ADD FOREIGN KEY while A holds a plain read on customers
at t=1.5 s A idle in transaction waiting on Client:ClientRead blocked by -
at t=1.5 s reader idle waiting on Client:ClientRead blocked by -
at t=1.5 s writer idle waiting on Client:ClientRead blocked by -
A commits at t=3.0 s
reader 303 calls, median 0.3 ms, max 1 ms, 0 slower than 100 ms, 0 errors
writer 285 calls, median 0.5 ms, max 64 ms, 0 slower than 100 ms, 0 errors
the DDL finished at t=0.3 s
=== 4. ADD FOREIGN KEY while A holds an uncommitted write on customers
at t=1.5 s A idle in transaction waiting on Client:ClientRead blocked by -
at t=1.5 s B active waiting on Lock:relation blocked by A
at t=1.5 s reader idle waiting on Client:ClientRead blocked by -
at t=1.5 s writer active waiting on Lock:relation blocked by B
A commits at t=3.2 s
reader 292 calls, median 0.9 ms, max 2 ms, 0 slower than 100 ms, 0 errors
writer 47 calls, median 0.6 ms, max 2815 ms, 1 slower than 100 ms, 0 errors
the DDL finished at t=3.2 sIn experiment 3 the foreign key was added at t=0.3 s, immediately, and nothing queued behind it: the reader’s slowest call was 1 ms and the writer’s was 64 ms, a single blip that I did not trace. A plain read does not conflict with SHARE ROW EXCLUSIVE, so the example in the GoCardless post, a long read on the referenced table, does not queue anything on 18.6. In experiment 4, A’s uncommitted write conflicts, so B waited, and the writer probe stalled for 2,815 ms behind it while the reader stayed at 2 ms. The lock mode decides who queues. The lesson is the same: a long transaction that has written to a table can hold up a migration, and then every write that arrives behind the migration waits too.
For the application side, the PostgreSQL documentation offers idle_in_transaction_session_timeout: “This option can be used to ensure that idle sessions do not hold locks for an unreasonable amount of time.” It ends sessions like A so they cannot sit on locks for hours.
Step 4: Add NOT NULL without a long lock
Suppose orders.status allows NULLs and you want to forbid them. The obvious statement, SET NOT NULL, scans the table while it holds ACCESS EXCLUSIVE. This script tries three ways, each from the baseline table, with a reader and a writer probe running, and reports what the probes felt.
Way A is the obvious statement. Way B adds a CHECK (status IS NOT NULL) as NOT VALID (instant), validates it (a scan that does not block reads or writes), then runs SET NOT NULL, which now skips its scan, and finally drops the helper constraint. Way C is the PostgreSQL 18 shortcut: add the not-null constraint as NOT VALID, then validate it. For way C the script also prints two catalog columns after each statement so you can see the constraint become valid.
The last part of the file shows what happens when validation fails. A NOT VALID constraint is not a promise about old rows, only about new ones. The documentation says so: “Once the constraint is in place, no new violations can be inserted, and the existing problems can be corrected at leisure until VALIDATE CONSTRAINT finally succeeds.”
# step03_notnull.py
"""step03_notnull.py: three ways to make orders.status NOT NULL, and what a reader and a writer feel."""
import random
import time
import psycopg
from lab import DSN, Probe, connect, fmt, say, seed
from migrate import run_ddl
WAYS = {
"A. plain SET NOT NULL": (
["ALTER TABLE orders ALTER COLUMN status SET NOT NULL"],
["ALTER TABLE orders ALTER COLUMN status DROP NOT NULL"]),
"B. CHECK NOT VALID, VALIDATE, SET NOT NULL": (
["ALTER TABLE orders ADD CONSTRAINT orders_status_ck CHECK (status IS NOT NULL) NOT VALID",
"ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck",
"ALTER TABLE orders ALTER COLUMN status SET NOT NULL",
"ALTER TABLE orders DROP CONSTRAINT orders_status_ck"],
["ALTER TABLE orders ALTER COLUMN status DROP NOT NULL"]),
"C. NOT NULL NOT VALID, VALIDATE (PG 18)": (
["ALTER TABLE orders ADD CONSTRAINT orders_status_nn NOT NULL status NOT VALID",
"ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn"],
["ALTER TABLE orders DROP CONSTRAINT orders_status_nn"]),
}
def try_way(title, statements, cleanup):
seed()
say(f"=== {title}")
rng = random.Random(3)
reader = Probe("reader", "SELECT id FROM orders WHERE id = %s", lambda n: (rng.randint(1, 2_000_000),))
writer = Probe("writer", "INSERT INTO orders (customer_id, status, amount_cents) VALUES (1, 'probe', 100)")
reader.start()
writer.start()
time.sleep(0.5)
for statement in statements:
started = time.perf_counter()
run_ddl(DSN, statement)
say(f" {statement[:78]:78} {time.perf_counter() - started:5.2f} s")
if "orders_status_nn" in statement:
with connect("peek") as c:
valid = c.execute("SELECT convalidated FROM pg_constraint WHERE conname = 'orders_status_nn'").fetchone()[0]
flag = c.execute("SELECT attnotnull FROM pg_attribute "
"WHERE attrelid = 'orders'::regclass AND attname = 'status'").fetchone()[0]
say(f" convalidated = {valid}, attnotnull = {flag}")
time.sleep(0.3)
say(f" reader: {fmt(reader.finish())}")
say(f" writer: {fmt(writer.finish())}")
with connect("cleanup") as c:
for statement in cleanup:
c.execute(statement)
def failing_validation():
seed()
say("=== VALIDATE fails when an old row breaks the rule, and new rows are already refused")
with connect("fix") as c:
row_id = c.execute("INSERT INTO orders (customer_id, status, amount_cents) "
"VALUES (1, NULL, 100) RETURNING id").fetchone()[0]
c.execute("ALTER TABLE orders ADD CONSTRAINT orders_status_ck CHECK (status IS NOT NULL) NOT VALID")
for label, statement in [("a new NULL row", "INSERT INTO orders (customer_id, status, amount_cents) "
"VALUES (1, NULL, 100)"),
("VALIDATE with one old NULL row", "ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck")]:
try:
c.execute(statement)
except psycopg.Error as exc:
say(f" {label}: SQLSTATE {exc.sqlstate}: {exc.diag.message_primary}")
c.execute("UPDATE orders SET status = 'new' WHERE status IS NULL")
c.execute("ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck")
say(" after fixing the row, VALIDATE succeeds")
c.execute("ALTER TABLE orders DROP CONSTRAINT orders_status_ck")
c.execute("DELETE FROM orders WHERE id = %s", (row_id,))
if __name__ == "__main__":
for title, (statements, cleanup) in WAYS.items():
try_way(title, statements, cleanup)
failing_validation()=== A. plain SET NOT NULL
ALTER TABLE orders ALTER COLUMN status SET NOT NULL 0.11 s
reader: 73 calls, median 0.5 ms, max 82 ms, 0 slower than 100 ms, 0 errors
writer: 73 calls, median 0.7 ms, max 81 ms, 0 slower than 100 ms, 0 errors
=== B. CHECK NOT VALID, VALIDATE, SET NOT NULL
ALTER TABLE orders ADD CONSTRAINT orders_status_ck CHECK (status IS NOT NULL) 0.03 s
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck 0.09 s
ALTER TABLE orders ALTER COLUMN status SET NOT NULL 0.14 s
ALTER TABLE orders DROP CONSTRAINT orders_status_ck 0.02 s
reader: 86 calls, median 0.5 ms, max 1 ms, 0 slower than 100 ms, 0 errors
writer: 97 calls, median 0.6 ms, max 3 ms, 0 slower than 100 ms, 0 errors
=== C. NOT NULL NOT VALID, VALIDATE (PG 18)
ALTER TABLE orders ADD CONSTRAINT orders_status_nn NOT NULL status NOT VALID 0.03 s
convalidated = False, attnotnull = True
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn 0.10 s
convalidated = True, attnotnull = True
reader: 97 calls, median 0.6 ms, max 3 ms, 0 slower than 100 ms, 0 errors
writer: 97 calls, median 0.7 ms, max 3 ms, 0 slower than 100 ms, 0 errors
=== VALIDATE fails when an old row breaks the rule, and new rows are already refused
a new NULL row: SQLSTATE 23514: new row for relation "orders" violates check constraint "orders_status_ck"
VALIDATE with one old NULL row: SQLSTATE 23514: check constraint "orders_status_ck" of relation "orders" is violated by some row
after fixing the row, VALIDATE succeedsWay A held its lock for the length of the scan: the reader’s and the writer’s slowest calls were 82 and 81 ms. Ways B and C kept both probes at 1 to 3 ms. On 2,000,000 rows the difference is small in absolute terms, which is the honest reading of this table, but the scan time grows with the table while the metadata steps do not. Way C needs two statements instead of four and leaves no helper constraint behind. After the second statement, convalidated flips from False to True while attnotnull was already True.
The failure demo at the bottom shows the safety net. With the NOT VALID check in place, a new NULL row is refused at once (SQLSTATE 23514), and VALIDATE fails while an old NULL row exists, with a message that names the constraint. You fix the data with an ordinary UPDATE and validate again. Readers and writers were not blocked while you did.
Step 5: Build indexes without blocking writes
A plain CREATE INDEX takes the SHARE lock, which the documentation says is “Acquired by CREATE INDEX (without CONCURRENTLY)” and which conflicts with the row-changing lock modes. This script builds the same index both ways while a writer probe inserts rows, then shows two ways a concurrent build can go wrong.
# step04_index.py
"""step04_index.py: CREATE INDEX versus CREATE INDEX CONCURRENTLY while a writer keeps inserting."""
import random
import time
import psycopg
from lab import DSN, Probe, connect, fmt, say, seed
from migrate import run_ddl
def build(title, statement):
seed()
say(f"=== {title}")
writer = Probe("writer", "INSERT INTO orders (customer_id, status, amount_cents) VALUES (1, 'probe', 100)")
writer.start()
time.sleep(0.5)
started = time.perf_counter()
run_ddl(DSN, statement, rng=random.Random(7))
say(f" {statement}: {time.perf_counter() - started:.2f} s")
time.sleep(0.3)
say(f" writer: {fmt(writer.finish())}")
with connect("cleanup") as c:
c.execute("DROP INDEX IF EXISTS orders_status_idx")
def failure_modes():
seed()
say("=== a concurrent build that fails leaves an INVALID index behind")
with connect("failure") as c:
try:
c.execute("CREATE UNIQUE INDEX CONCURRENTLY orders_status_uq ON orders (status)")
except psycopg.Error as exc:
say(f" SQLSTATE {exc.sqlstate}: {exc.diag.message_primary}")
say(f" DETAIL: {exc.diag.message_detail}")
invalid = c.execute("""SELECT c.relname, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'orders'::regclass ORDER BY 1""").fetchall()
say(f" indexes on orders: {invalid}")
c.execute("DROP INDEX CONCURRENTLY orders_status_uq")
say(" DROP INDEX CONCURRENTLY orders_status_uq: done")
try:
with c.transaction():
c.execute("CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status)")
except psycopg.Error as exc:
say(f" inside a transaction block: SQLSTATE {exc.sqlstate}: {exc.diag.message_primary}")
if __name__ == "__main__":
build("plain CREATE INDEX", "CREATE INDEX orders_status_idx ON orders (status)")
build("CREATE INDEX CONCURRENTLY", "CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status)")
failure_modes()=== plain CREATE INDEX
CREATE INDEX orders_status_idx ON orders (status): 0.71 s
writer: 72 calls, median 0.8 ms, max 680 ms, 1 slower than 100 ms, 0 errors
=== CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status): 0.84 s
writer: 136 calls, median 0.7 ms, max 4 ms, 0 slower than 100 ms, 0 errors
=== a concurrent build that fails leaves an INVALID index behind
SQLSTATE 23505: could not create unique index "orders_status_uq"
DETAIL: Key (status)=(paid) is duplicated.
indexes on orders: [('orders_amount_idx', True), ('orders_pkey', True), ('orders_status_uq', False)]
DROP INDEX CONCURRENTLY orders_status_uq: done
inside a transaction block: SQLSTATE 25001: CREATE INDEX CONCURRENTLY cannot run inside a transaction blockThe plain build took 0.71 s and the writer’s slowest call was 680 ms: writes simply waited for the build. The concurrent build took 0.84 s, and the writer’s slowest call was 4 ms. On this idle machine the two builds took about the same time. The documentation explains why that is not the general case: “In a concurrent index build, the index is actually entered as an ‘invalid’ index into the system catalogs in one transaction, then two table scans occur in two more transactions”, and it must wait for other transactions along the way, so expect it to be slower on a busy system.
Two failure modes are in the output. If a concurrent build fails, here a unique index on a column that has duplicates, it leaves an invalid index behind. The listing shows orders_status_uq with indisvalid False. Queries ignore the index, but according to the documentation it still consumes update overhead, so its advice applies: “The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY.” And a concurrent build cannot run in a transaction at all: “a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.” PostgreSQL reports it as SQLSTATE 25001. (The DETAIL line of the failed unique build names one duplicated value, and which value it names can differ from run to run.) Some migration tools wrap each migration in a transaction, so check yours before you rely on this.
Step 6: Change a column type with expand, backfill and contract
Changing amount_cents from integer to bigint is the classic hard case, because the statement is a rewrite. The Strong Migrations project, a Ruby library that catches unsafe Rails migrations in development, says it plainly: “Changing the type of a column causes the entire table to be rewritten. During this time, reads and writes are blocked in Postgres”. This script measures that, then does the same job in small steps while three probes run: a reader, a writer, and an updater that changes amount_cents on random rows.
The small steps have a fixed order. Expand: add a nullable amount_cents_v2 bigint column (instant), add a trigger so every insert or update of the old column also fills the new one, copy the existing rows in batches of 10,000 with one short transaction per batch, build an index on the new column concurrently, and check that the two columns agree. Release: deploy application code that uses the new column. I do not simulate that step; a rolling deploy has its own care, such as writing both columns until the old version is gone. Contract: when only code that knows the new column is running, drop the not-null rule on the old column, the trigger, the function and finally the old column.
The last function, too_early(), repeats the contract step at the wrong moment on purpose: it renames the old column for one second while release N is still running.
# step05_bigint.py
"""step05_bigint.py: change orders.amount_cents from integer to bigint, the naive way and in small steps."""
import random
import time
from lab import DSN, Probe, connect, fmt, say, seed
from migrate import run_ddl
BATCH = 10_000
COPY_FUNCTION = """CREATE OR REPLACE FUNCTION orders_copy_amount() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.amount_cents_v2 := NEW.amount_cents;
RETURN NEW;
END
$$"""
COPY_TRIGGER = ("CREATE TRIGGER orders_copy_amount BEFORE INSERT OR UPDATE OF amount_cents ON orders "
"FOR EACH ROW EXECUTE FUNCTION orders_copy_amount()")
def release(column):
"""Probes that behave like one release of the application: they only know the column they are given."""
rng = random.Random(1)
return [Probe("reader", "SELECT id FROM orders WHERE id = %s", lambda n: (rng.randint(1, 2_000_000),)),
Probe("writer", f"INSERT INTO orders (customer_id, status, {column}) VALUES (1, 'probe', 100)"),
Probe("updater", f"UPDATE orders SET {column} = {column} + 1 WHERE id = %s",
lambda n: (rng.randint(1, 2_000_000),))]
def size_mb():
with connect("size") as c:
return c.execute("SELECT pg_total_relation_size('orders')").fetchone()[0] / 1_000_000
def start(running):
for probe in running:
probe.start()
time.sleep(0.5)
def summarize(running):
time.sleep(0.3)
for probe in running:
say(f" {probe.label:8} {fmt(probe.finish())}")
def naive():
seed()
say("=== naive: ALTER COLUMN TYPE bigint, rolled back right after it finishes")
running = release("amount_cents")
start(running)
with connect("naive", autocommit=False) as conn:
started = time.perf_counter()
conn.execute("ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint")
say(f" the ALTER held its lock for {time.perf_counter() - started:.2f} s")
conn.rollback()
summarize(running)
def backfill():
with connect("backfill") as conn:
last = conn.execute("SELECT max(id) FROM orders").fetchone()[0]
low, batches = 0, 0
while low < last:
conn.execute("UPDATE orders SET amount_cents_v2 = amount_cents "
"WHERE id > %s AND id <= %s AND amount_cents_v2 IS NULL", (low, low + BATCH))
low, batches = low + BATCH, batches + 1
return f"{batches} batches"
def once(sql):
with connect("migration") as c:
c.execute(sql)
return ""
def check():
with connect("check") as c:
bad = c.execute("SELECT count(*) FROM orders WHERE amount_cents_v2 IS DISTINCT FROM amount_cents").fetchone()[0]
return f"{bad} rows disagree"
def run_steps(steps):
for label, step in steps:
started = time.perf_counter()
note = step()
if isinstance(note, int):
note = "" if note == 1 else f"({note} attempts)"
say(f" {label:48} {time.perf_counter() - started:6.2f} s {note}")
def playbook():
seed()
before = size_mb()
with connect("setup") as c: # a vacuum on the table would compete for the same locks and make the timings vary
c.execute("ALTER TABLE orders SET (autovacuum_enabled = false)")
say("=== expand and backfill while release N keeps running (autovacuum is off for this table)")
running = release("amount_cents")
start(running)
run_steps([
("expand: ADD COLUMN amount_cents_v2 bigint",
lambda: run_ddl(DSN, "ALTER TABLE orders ADD COLUMN amount_cents_v2 bigint")),
("dual write: CREATE FUNCTION", lambda: once(COPY_FUNCTION)),
("dual write: CREATE TRIGGER", lambda: run_ddl(DSN, COPY_TRIGGER)),
("backfill in batches of 10,000 rows", backfill),
("CREATE INDEX CONCURRENTLY on the new column",
lambda: run_ddl(DSN, "CREATE INDEX CONCURRENTLY orders_amount_v2_idx ON orders (amount_cents_v2)")),
("check that old and new columns agree", check),
])
summarize(running)
say("=== contract once only the release that knows amount_cents_v2 is running")
run_steps([
("DROP NOT NULL on the old column", lambda: run_ddl(DSN, "ALTER TABLE orders ALTER COLUMN amount_cents DROP NOT NULL")),
("DROP TRIGGER", lambda: run_ddl(DSN, "DROP TRIGGER orders_copy_amount ON orders")),
("DROP FUNCTION", lambda: once("DROP FUNCTION orders_copy_amount()")),
])
running = release("amount_cents_v2")
start(running)
run_steps([("DROP COLUMN amount_cents", lambda: run_ddl(DSN, "ALTER TABLE orders DROP COLUMN amount_cents"))])
summarize(running)
with connect("types") as c:
kind = c.execute("SELECT data_type FROM information_schema.columns "
"WHERE table_name = 'orders' AND column_name = 'amount_cents_v2'").fetchone()[0]
c.execute("ALTER TABLE orders RESET (autovacuum_enabled)")
say(f" amount_cents_v2 is now {kind}; table plus indexes grew from {before:.0f} MB to {size_mb():.0f} MB")
def too_early():
seed() # the playbook changed the table, so this rebuilds it
say("=== the mistake: rename the old column while release N still runs")
running = release("amount_cents")[1:]
start(running)
with connect("oops") as c:
c.execute("ALTER TABLE orders RENAME COLUMN amount_cents TO amount_cents_gone")
time.sleep(1.0)
c.execute("ALTER TABLE orders RENAME COLUMN amount_cents_gone TO amount_cents")
summarize(running)
if __name__ == "__main__":
naive()
playbook()
too_early()First the naive statement, held inside a transaction that is rolled back as soon as it finishes:
=== naive: ALTER COLUMN TYPE bigint, rolled back right after it finishes
the ALTER held its lock for 2.48 s
reader 78 calls, median 0.3 ms, max 2477 ms, 1 slower than 100 ms, 0 errors
writer 74 calls, median 0.5 ms, max 2481 ms, 1 slower than 100 ms, 0 errors
updater 74 calls, median 0.6 ms, max 2475 ms, 1 slower than 100 ms, 0 errorsThe rewrite held its lock for 2.48 seconds, and in that single stall all three probes waited 2.48 seconds. Now the step-by-step version:
=== expand and backfill while release N keeps running (autovacuum is off for this table)
expand: ADD COLUMN amount_cents_v2 bigint 0.03 s
dual write: CREATE FUNCTION 0.02 s
dual write: CREATE TRIGGER 0.02 s
backfill in batches of 10,000 rows 14.59 s 201 batches
CREATE INDEX CONCURRENTLY on the new column 1.28 s
check that old and new columns agree 0.13 s 0 rows disagree
reader 1554 calls, median 0.4 ms, max 141 ms, 1 slower than 100 ms, 0 errors
writer 1409 calls, median 1.0 ms, max 213 ms, 2 slower than 100 ms, 0 errors
updater 1395 calls, median 1.0 ms, max 214 ms, 2 slower than 100 ms, 0 errors
=== contract once only the release that knows amount_cents_v2 is running
DROP NOT NULL on the old column 0.09 s
DROP TRIGGER 0.05 s
DROP FUNCTION 0.03 s
DROP COLUMN amount_cents 0.03 s
reader 72 calls, median 0.7 ms, max 1 ms, 0 slower than 100 ms, 0 errors
writer 72 calls, median 0.8 ms, max 5 ms, 0 slower than 100 ms, 0 errors
updater 71 calls, median 0.8 ms, max 2 ms, 0 slower than 100 ms, 0 errors
amount_cents_v2 is now bigint; table plus indexes grew from 186 MB to 371 MBThe short statements took 0.02 to 0.03 seconds, the concurrent index build 1.28 seconds, and the backfill 14.6 seconds across 201 batches, and no probe logged an error. The worst calls in the whole expand phase were 141, 213 and 214 ms, from 1 or 2 calls per probe slower than 100 ms, against 2.48 seconds for the one-statement version. I only tried a batch of 10,000 rows. The stall comes from rows locked by a batch that the updater probe happened to need, so a smaller batch should shorten it at the price of more transactions; I did not measure that. The contract phase caused no stall: no probe saw anything slower than 5 ms.
The price is disk. The table and its indexes grew from 186 MB to 371 MB, because every updated row is a new row version. Dropping the old column does not shrink the table. The documentation says dropping a column “will not immediately reduce the on-disk size of your table”, and that the space “will be reclaimed over time as existing rows are updated.” Plan for the extra space before you start.
The last output block shows the mistake too_early() makes on purpose:
=== the mistake: rename the old column while release N still runs
writer 169 calls, median 0.5 ms, max 3 ms, 0 slower than 100 ms, 93 errors
updater 168 calls, median 0.5 ms, max 2 ms, 0 slower than 100 ms, 93 errorsFor one second of the old name being gone, the writer and the updater, which still use amount_cents, failed 93 times each. The reader does not use that column and was fine. The rename itself took a millisecond; the cost came from code that still needed the old name. Strong Migrations says it in one line: “Renaming a column that’s in use will cause errors in your application.” The same goes for dropping a column. Contract only after the release that used the old column is gone.
Step 7: Write a lint gate that refuses unsafe migrations
You now know enough to write rules, and every rule in gate.py comes from something you measured or read in the steps above. The gate splits a migration file into statements, normalizes each one, and applies these checks.
M001 flags adding a column whose default might be volatile, or that is serial, identity or stored generated (rewrites). M002 flags a column type change (rewrite). M003 flags SET NOT NULL unless the same file already validated a CHECK (col IS NOT NULL) for that column. M004 flags CREATE INDEX without CONCURRENTLY. M005 flags a check, foreign key or not-null constraint added without NOT VALID. M006 flags a unique or primary key constraint that builds its own index instead of using one built concurrently. M009 flags CREATE INDEX CONCURRENTLY inside a transaction. Those are errors: the gate exits with code 1.
Three more rules are warnings because they are not lock problems but need a human: M007 for renames, M008 for dropped columns (both break running code), and M011 for an UPDATE or DELETE without a WHERE, which changes every row in one transaction and should be batched. Tables created in the same file are exempt from the lock rules because a new table is empty and nobody is using it yet.
Two design choices are worth explaining. The default check is conservative: only literals and a short list of stable built-ins such as now() pass, because the gate cannot know whether a custom function is volatile, and a false alarm costs one line in the allow list while a missed rewrite costs an outage. And the gate remembers what earlier statements in the file proved, which is how it accepts the safe SET NOT NULL only after a validated check for the same table and column.
# gate.py
"""gate.py: a lint gate for PostgreSQL migrations. Each rule is backed by a measurement from steps 1 to 5.
Usage: python gate.py migration.sql [RULE ...] (rules named after the file are allowed)
Exit code 0 = no errors, 1 = at least one error, 2 = unreadable input."""
import re
import sys
from dataclasses import dataclass
from pathlib import Path
IDENT = r'(?:"[^"]+"|[\w.]+)'
STABLE = {"now", "current_timestamp", "current_date", "current_time", "localtimestamp", "localtime",
"transaction_timestamp", "statement_timestamp"}
CLAUSE_STARTS = ("not null", "null", "check", "references", "unique", "primary key", "constraint",
"generated", "collate")
CONSTRAINT_ADD = re.compile(r"add (?:constraint (" + IDENT + r") )?"
r"(check|foreign key|unique|primary key|exclude|not null)\b(.*)$")
@dataclass
class Finding:
rule: str
severity: str # "error" fails the gate, "warn" asks for a human look
index: int # statement number in the file, starting at 1
message: str
fix: str
def split_statements(sql):
"""Split on semicolons that sit outside quotes, comments and dollar-quoted bodies."""
out, buf, i, n = [], [], 0, len(sql)
while i < n:
c = sql[i]
if c in "'\"":
j = i + 1
while j < n:
if sql[j] == c:
if j + 1 < n and sql[j + 1] == c:
j += 2
continue
break
j += 1
buf.append(sql[i:j + 1])
i = j + 1
elif sql.startswith("--", i):
j = sql.find("\n", i)
i = n if j < 0 else j
elif sql.startswith("/*", i):
depth, j = 1, i + 2
while j < n and depth:
if sql.startswith("/*", j):
depth, j = depth + 1, j + 2
elif sql.startswith("*/", j):
depth, j = depth - 1, j + 2
else:
j += 1
buf.append(" ")
i = j
elif c == "$" and re.match(r"\$(?:[A-Za-z_]\w*)?\$", sql[i:]):
tag = re.match(r"\$(?:[A-Za-z_]\w*)?\$", sql[i:]).group(0)
j = sql.find(tag, i + len(tag))
j = n if j < 0 else j + len(tag)
buf.append(sql[i:j])
i = j
elif c == ";":
out.append("".join(buf))
buf, i = [], i + 1
else:
buf.append(c)
i += 1
out.append("".join(buf))
return [s.strip() for s in out if s.strip()]
def normalize(statement):
"""Hide string contents, collapse white space, lower-case: rules then match plain keywords."""
statement = re.sub(r"'(?:[^']|'')*'", "''", statement)
return re.sub(r"\s+", " ", statement.strip()).lower().rstrip(";")
def top_level_split(text, sep=","):
parts, depth, start = [], 0, 0
for i, ch in enumerate(text):
if ch == "(":
depth += 1
elif ch == ")":
depth -= 1
elif ch == sep and depth == 0:
parts.append(text[start:i].strip())
start = i + 1
parts.append(text[start:].strip())
return [p for p in parts if p]
def key(name):
return name.replace('"', "").removeprefix("public.")
def default_expr(action):
found = re.search(r"\bdefault\b", action)
if not found:
return None
rest, depth = action[found.end():].strip(), 0
for i, ch in enumerate(rest):
if ch == "(":
depth += 1
elif ch == ")":
depth -= 1
elif depth == 0 and ch == " " and rest[i + 1:].startswith(CLAUSE_STARTS):
return rest[:i]
return rest
def default_is_safe(expr):
"""True for literals and the stable built-ins; anything else might be volatile."""
expr = re.sub(r"::[a-z_ ]+(\(\d+(,\d+)?\))?(\[\])?", "", expr).strip("() ")
if re.fullmatch(r"-?\d+(\.\d+)?|''|true|false|null", expr):
return True
named = re.fullmatch(r"([a-z_]+)(\(\))?", expr)
return bool(named and named.group(1) in STABLE)
def check_column_add(action):
out = []
if re.search(r"\b(small|big)?serial\b", action):
out.append(("M001", "error", "a serial column gets a volatile nextval() default",
"add a plain bigint column, backfill in batches, then attach the sequence default"))
if re.search(r"generated (always|by default) as identity", action) or \
re.search(r"generated always as \(.*\) stored", action):
out.append(("M001", "error", "an identity or stored generated column rewrites the whole table",
"add a plain column and fill it with batched updates"))
expr = default_expr(action)
if expr is not None and not default_is_safe(expr):
out.append(("M001", "error", f"DEFAULT {expr} is volatile or not provably stable, so the table is rewritten",
"add the column without a default, set the value in batches, then SET DEFAULT"))
return out
def lint(sql, allow=(), in_transaction=False):
findings = []
state = {"created": set(), "pending": {}, "proved": set(), "in_block": False}
for index, raw in enumerate(split_statements(sql), 1):
statement = normalize(raw)
found = []
if re.match(r"(begin|start transaction)\b", statement):
state["in_block"] = True
elif re.match(r"(commit|end|rollback|abort)\b", statement):
state["in_block"] = False
created = re.match(r"create table (?:if not exists )?(" + IDENT + r")", statement)
if created:
state["created"].add(key(created.group(1)))
make_index = re.match(r"create (?:unique )?index (concurrently )?(?:if not exists )?"
r"(?:(?!on )" + IDENT + r" )?on (?:only )?(" + IDENT + r")", statement)
if make_index:
table = key(make_index.group(2))
if make_index.group(1) and (state["in_block"] or in_transaction):
found.append(("M009", "error", "CREATE INDEX CONCURRENTLY cannot run inside a transaction block",
"run it outside BEGIN and COMMIT, in autocommit mode"))
elif not make_index.group(1) and table not in state["created"]:
found.append(("M004", "error", f"CREATE INDEX on {table} blocks every write until it finishes",
"use CREATE INDEX CONCURRENTLY"))
alter = re.match(r"alter table (?:if exists )?(?:only )?(" + IDENT + r") (.*)$", statement)
if alter:
table = key(alter.group(1))
fresh = table in state["created"]
for action in top_level_split(alter.group(2)):
found.extend(check_action(table, action, fresh, state))
if re.match(r"(update " + IDENT + r" set|delete from " + IDENT + r")\b", statement) \
and " where " not in statement:
found.append(("M011", "warn", "one statement changes every row in one transaction",
"run it in batches by primary-key range, one transaction per batch"))
findings.extend(Finding(rule, severity, index, message, fix) for rule, severity, message, fix in found)
return [f for f in findings if f.rule not in allow]
def check_action(table, action, fresh, state):
found = []
constraint = CONSTRAINT_ADD.match(action)
column = re.match(r"add (?:column )?(?:if not exists )?(" + IDENT + r") (.+)$", action)
if constraint:
name, kind, tail = constraint.groups()
not_valid = action.endswith("not valid")
if kind in ("unique", "primary key", "exclude") and "using index" not in tail and not fresh:
found.append(("M006", "error", f"ADD {kind.upper()} builds an index while holding ACCESS EXCLUSIVE",
"CREATE UNIQUE INDEX CONCURRENTLY first, then ADD CONSTRAINT ... USING INDEX"))
if kind in ("check", "foreign key", "not null") and not not_valid and not fresh:
found.append(("M005", "error", f"ADD {kind.upper()} scans the table while holding a blocking lock",
"add it NOT VALID, then VALIDATE CONSTRAINT in a later statement"))
if name:
proof = re.match(r"\s*\(\s*\(?\s*(" + IDENT + r")\s*\)?\s+is not null\s*\)", tail)
if kind == "check" and proof:
state["pending"][(table, key(name))] = key(proof.group(1))
elif kind == "not null" and re.match(r"\s*(" + IDENT + r")", tail):
state["pending"][(table, key(name))] = key(re.match(r"\s*(" + IDENT + r")", tail).group(1))
elif column:
found.extend([] if fresh else check_column_add(action))
elif re.match(r"alter (?:column )?" + IDENT + r" (?:set data )?type\b", action):
found.append(("M002", "error", "changing a column type rewrites the table and its indexes",
"expand and contract: new column, dual write, batched backfill, switch, drop"))
elif re.match(r"alter (?:column )?" + IDENT + r" set not null$", action):
col = key(re.match(r"alter (?:column )?(" + IDENT + r")", action).group(1))
if not fresh and (table, col) not in state["proved"]:
found.append(("M003", "error", f"SET NOT NULL on {col} scans the table while holding ACCESS EXCLUSIVE",
"add CHECK (col IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL"))
elif re.match(r"validate constraint " + IDENT + r"$", action):
name = key(action.split()[-1])
if (table, name) in state["pending"]:
state["proved"].add((table, state["pending"][(table, name)]))
elif re.match(r"rename (?!constraint\b)(?:column )?" + IDENT + r" to ", action) or action.startswith("rename to "):
found.append(("M007", "warn", "a rename breaks every running copy of the application that uses the old name",
"add the new name, copy the data, move the code, drop the old name later"))
elif re.match(r"drop (?!constraint\b)(?:column )?(?:if exists )?" + IDENT, action):
found.append(("M008", "warn", "dropping a column breaks any code that still reads or writes it",
"ship the code change first, drop the column in a later release"))
return found
def main(argv):
if len(argv) < 2:
print(__doc__)
return 2
try:
text = Path(argv[1]).read_text(encoding="utf-8")
except OSError as exc:
print(f"cannot read {argv[1]}: {exc}")
return 2
findings = lint(text, allow=set(argv[2:]))
for f in findings:
print(f"{argv[1]}: statement {f.index}: {f.severity.upper()} {f.rule}: {f.message}\n fix: {f.fix}")
errors = [f for f in findings if f.severity == "error"]
print(f"{len(errors)} errors, {len(findings) - len(errors)} warnings")
return 1 if errors else 0
if __name__ == "__main__":
sys.exit(main(sys.argv))A linter is code, so test it. The test file covers the statement splitter (semicolons inside strings, comments, nested block comments and dollar-quoted function bodies must not split), each unsafe statement, each safe statement, the warnings, multi-action statements, the order rules, the allow list and the command-line exit codes.
# test_gate.py
import subprocess
import sys
from pathlib import Path
import pytest
from gate import lint, split_statements
HERE = Path(__file__).parent
def errors(sql, **kw):
return [f.rule for f in lint(sql, **kw) if f.severity == "error"]
def warnings(sql, **kw):
return [f.rule for f in lint(sql, **kw) if f.severity == "warn"]
@pytest.mark.parametrize("sql, expected", [
("SELECT 1; SELECT 2;", ["SELECT 1", "SELECT 2"]),
("SELECT 'a;b'; SELECT 2", ["SELECT 'a;b'", "SELECT 2"]),
("SELECT 'it''s; fine'; SELECT 2", ["SELECT 'it''s; fine'", "SELECT 2"]),
('SELECT "a;b" FROM t; SELECT 2', ['SELECT "a;b" FROM t', "SELECT 2"]),
("-- one; two\nSELECT 1", ["SELECT 1"]),
("/* a; /* b; */ c; */ SELECT 1", ["SELECT 1"]),
("CREATE FUNCTION f() RETURNS int AS $$ BEGIN RETURN 1; END $$ LANGUAGE plpgsql; SELECT 2",
["CREATE FUNCTION f() RETURNS int AS $$ BEGIN RETURN 1; END $$ LANGUAGE plpgsql", "SELECT 2"]),
("SELECT $tag$ a; b $tag$; SELECT 2", ["SELECT $tag$ a; b $tag$", "SELECT 2"]),
("", []),
])
def test_split_statements(sql, expected):
assert split_statements(sql) == expected
UNSAFE = [
("ALTER TABLE orders ADD COLUMN touched_at timestamptz NOT NULL DEFAULT clock_timestamp()", "M001"),
("ALTER TABLE orders ADD COLUMN token uuid DEFAULT gen_random_uuid()", "M001"),
("ALTER TABLE orders ADD COLUMN seq bigserial", "M001"),
("ALTER TABLE orders ADD COLUMN total bigint GENERATED ALWAYS AS (amount_cents * 2) STORED", "M001"),
("ALTER TABLE orders ADD COLUMN n bigint GENERATED ALWAYS AS IDENTITY", "M001"),
("ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint", "M002"),
("ALTER TABLE orders ALTER COLUMN amount_cents SET DATA TYPE bigint USING amount_cents::bigint", "M002"),
("ALTER TABLE orders ALTER COLUMN status SET NOT NULL", "M003"),
("CREATE INDEX orders_status_idx ON orders (status)", "M004"),
("CREATE UNIQUE INDEX ON orders (id, amount_cents)", "M004"),
("ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (customer_id) REFERENCES customers (id)", "M005"),
("ALTER TABLE orders ADD CONSTRAINT ck CHECK (amount_cents >= 0)", "M005"),
("ALTER TABLE orders ADD CONSTRAINT nn NOT NULL status", "M005"),
("ALTER TABLE orders ADD CONSTRAINT uq UNIQUE (id, amount_cents)", "M006"),
("ALTER TABLE orders ADD PRIMARY KEY (id)", "M006"),
("ALTER TABLE public.orders ADD COLUMN token uuid DEFAULT uuid_generate_v4()", "M001"),
('ALTER TABLE "orders" ALTER COLUMN "status" SET NOT NULL', "M003"),
("ALTER TABLE IF EXISTS ONLY orders ADD COLUMN n int DEFAULT random()", "M001"),
]
@pytest.mark.parametrize("sql, rule", UNSAFE)
def test_unsafe_statements_are_refused(sql, rule):
assert errors(sql) == [rule]
SAFE = [
"ALTER TABLE orders ADD COLUMN note text",
"ALTER TABLE orders ADD COLUMN is_test boolean NOT NULL DEFAULT false",
"ALTER TABLE orders ADD COLUMN created timestamptz DEFAULT now()",
"ALTER TABLE orders ADD COLUMN stamp timestamptz NOT NULL DEFAULT current_timestamp",
"ALTER TABLE orders ADD COLUMN label text DEFAULT 'none'",
"ALTER TABLE orders ADD COLUMN price numeric(10,2) NOT NULL DEFAULT 0.00",
"ALTER TABLE orders ADD COLUMN kind text DEFAULT 'a'::text NOT NULL",
"CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status)",
"ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID",
"ALTER TABLE orders ADD CONSTRAINT ck CHECK (amount_cents >= 0) NOT VALID",
"ALTER TABLE orders VALIDATE CONSTRAINT ck",
"ALTER TABLE orders ADD CONSTRAINT uq UNIQUE USING INDEX orders_uq_idx",
"ALTER TABLE orders ADD CONSTRAINT nn NOT NULL status NOT VALID",
"ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new'",
"ALTER TABLE orders DROP CONSTRAINT ck",
"ALTER TABLE orders RENAME CONSTRAINT a TO b",
"UPDATE orders SET status = 'x' WHERE id < 10",
"SELECT 1",
]
@pytest.mark.parametrize("sql", SAFE)
def test_safe_statements_pass(sql):
assert errors(sql) == []
@pytest.mark.parametrize("sql, rule", [
("ALTER TABLE orders RENAME COLUMN status TO state", "M007"),
("ALTER TABLE orders RENAME status TO state", "M007"),
("ALTER TABLE orders RENAME TO purchases", "M007"),
("ALTER TABLE orders DROP COLUMN created_at", "M008"),
("ALTER TABLE orders DROP created_at", "M008"),
("UPDATE orders SET status = 'x'", "M011"),
("DELETE FROM orders", "M011"),
])
def test_compatibility_and_batch_warnings(sql, rule):
assert warnings(sql) == [rule]
assert errors(sql) == []
def test_every_action_in_a_multi_action_statement_is_checked():
sql = "ALTER TABLE orders ADD COLUMN a text, ADD COLUMN b int DEFAULT random(), ALTER COLUMN c TYPE bigint"
assert errors(sql) == ["M001", "M002"]
def test_text_inside_a_string_is_not_sql():
assert errors("COMMENT ON TABLE orders IS 'ALTER TABLE orders ALTER COLUMN a TYPE bigint'") == []
CHECK = "ALTER TABLE orders ADD CONSTRAINT ck CHECK (status IS NOT NULL) NOT VALID;"
VALIDATE = "ALTER TABLE orders VALIDATE CONSTRAINT ck;"
SET_NN = "ALTER TABLE orders ALTER COLUMN status SET NOT NULL;"
def test_set_not_null_is_fine_after_a_validated_check():
assert errors(CHECK + VALIDATE + SET_NN) == []
def test_set_not_null_needs_the_validate_step():
assert errors(CHECK + SET_NN) == ["M003"]
def test_validating_a_different_constraint_proves_nothing():
other = "ALTER TABLE orders ADD CONSTRAINT other CHECK (amount_cents > 0) NOT VALID; " \
"ALTER TABLE orders VALIDATE CONSTRAINT other;"
assert errors(CHECK + other + SET_NN) == ["M003"]
def test_the_proof_belongs_to_one_table():
assert errors(CHECK + VALIDATE + "ALTER TABLE customers ALTER COLUMN status SET NOT NULL;") == ["M003"]
def test_the_proof_belongs_to_one_column():
assert errors(CHECK + VALIDATE + "ALTER TABLE orders ALTER COLUMN amount_cents SET NOT NULL;") == ["M003"]
def test_pg18_not_null_constraint_counts_as_proof_after_validate():
sql = ("ALTER TABLE orders ADD CONSTRAINT nn NOT NULL status NOT VALID; "
"ALTER TABLE orders VALIDATE CONSTRAINT nn; ALTER TABLE orders ALTER COLUMN status SET NOT NULL")
assert errors(sql) == []
def test_tables_created_in_the_same_file_are_exempt():
sql = ("CREATE TABLE audit (id bigint, note text); CREATE INDEX audit_idx ON audit (note); "
"ALTER TABLE audit ALTER COLUMN note SET NOT NULL; "
"ALTER TABLE audit ADD COLUMN token uuid DEFAULT gen_random_uuid()")
assert errors(sql) == []
def test_concurrent_index_inside_begin_commit_is_refused():
sql = "BEGIN; CREATE INDEX CONCURRENTLY i ON orders (status); COMMIT;"
assert errors(sql) == ["M009"]
assert errors("CREATE INDEX CONCURRENTLY i ON orders (status); BEGIN; COMMIT;") == []
def test_concurrent_index_is_refused_when_the_runner_wraps_a_transaction():
assert errors("CREATE INDEX CONCURRENTLY i ON orders (status)", in_transaction=True) == ["M009"]
def test_allow_list_silences_a_rule():
assert errors("ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint", allow={"M002"}) == []
def test_findings_carry_the_statement_number():
sql = "SELECT 1; ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint; SELECT 2"
assert [(f.rule, f.index) for f in lint(sql)] == [("M002", 2)]
def test_cli_exit_codes(tmp_path):
bad = tmp_path / "bad.sql"
bad.write_text("CREATE INDEX i ON orders (status);\n", encoding="utf-8")
good = tmp_path / "good.sql"
good.write_text("CREATE INDEX CONCURRENTLY i ON orders (status);\n", encoding="utf-8")
run = lambda *args: subprocess.run([sys.executable, str(HERE / "gate.py"), *args], # noqa: E731
capture_output=True, text=True).returncode
assert run(str(bad)) == 1
assert run(str(good)) == 0
assert run(str(bad), "M004") == 0
assert run(str(tmp_path / "missing.sql")) == 2.................................................................. [100%]
66 passed in 0.33sNow run the gate on two migration files that express the same intent. The first is written the obvious way:
-- 0007_naive.sql: the obvious way to write the change
ALTER TABLE orders ADD COLUMN touched_at timestamptz NOT NULL DEFAULT clock_timestamp();
ALTER TABLE orders ALTER COLUMN amount_cents TYPE bigint;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
CREATE INDEX orders_status_idx ON orders (status);
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE orders ADD CONSTRAINT orders_amount_uq UNIQUE (id, amount_cents);
ALTER TABLE orders RENAME COLUMN status TO state;
UPDATE orders SET touched_at = created_at;The second is the rewrite with the safe patterns from steps 2 to 5 (the column type change is left out because it needs the multi-release plan from step 6):
-- 0007_safe.sql: the same intent, written so that no statement holds a blocking lock for long
ALTER TABLE orders ADD COLUMN touched_at timestamptz;
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
ALTER TABLE orders ADD CONSTRAINT orders_status_ck CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
CREATE UNIQUE INDEX CONCURRENTLY orders_amount_uq ON orders (id, amount_cents);
ALTER TABLE orders ADD CONSTRAINT orders_amount_uq UNIQUE USING INDEX orders_amount_uq;This script runs the gate on both files, and then does the check that matters most: it compares the gate’s verdict with the measurements you saved in step 2. A statement counts as measured-unsafe when its lock blocks writes, it took at least 20 ms, and it was at least twice as slow on ten times the rows.
# step06_gate.py
"""step06_gate.py: run the gate on two migration files, then compare its verdicts with the measurements of step 1."""
import json
from gate import lint, main as gate_main
def show(path):
print(f"$ python gate.py {path}")
code = gate_main(["gate.py", path])
print(f"exit code {code}\n")
def agreement():
with open("out/matrix.json", encoding="utf-8") as fh:
rows = json.load(fh)
agree = 0
print(f"{'statement':36} {'measured':10} {'gate':10} verdict")
for row in rows:
statements = row["setup"] + [row["sql"]]
gate_errors = [f for f in lint(";\n".join(statements))
if f.severity == "error" and f.index == len(statements)]
measured_unsafe = row["blocks_writes"] and row["ms_big"] >= 20 and row["ratio"] >= 2
same = bool(gate_errors) == measured_unsafe
agree += same
print(f"{row['label']:36} {'unsafe' if measured_unsafe else 'ok':10} "
f"{gate_errors[0].rule if gate_errors else 'ok':10} {'agree' if same else 'DISAGREE'}")
print(f"the gate agrees with the measurement on {agree} of {len(rows)} statements")
if __name__ == "__main__":
show("0007_naive.sql")
show("0007_safe.sql")
agreement()$ python gate.py 0007_naive.sql
0007_naive.sql: statement 1: ERROR M001: DEFAULT clock_timestamp() is volatile or not provably stable, so the table is rewritten
fix: add the column without a default, set the value in batches, then SET DEFAULT
0007_naive.sql: statement 2: ERROR M002: changing a column type rewrites the table and its indexes
fix: expand and contract: new column, dual write, batched backfill, switch, drop
0007_naive.sql: statement 3: ERROR M003: SET NOT NULL on status scans the table while holding ACCESS EXCLUSIVE
fix: add CHECK (col IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL
0007_naive.sql: statement 4: ERROR M004: CREATE INDEX on orders blocks every write until it finishes
fix: use CREATE INDEX CONCURRENTLY
0007_naive.sql: statement 5: ERROR M005: ADD FOREIGN KEY scans the table while holding a blocking lock
fix: add it NOT VALID, then VALIDATE CONSTRAINT in a later statement
0007_naive.sql: statement 6: ERROR M006: ADD UNIQUE builds an index while holding ACCESS EXCLUSIVE
fix: CREATE UNIQUE INDEX CONCURRENTLY first, then ADD CONSTRAINT ... USING INDEX
0007_naive.sql: statement 7: WARN M007: a rename breaks every running copy of the application that uses the old name
fix: add the new name, copy the data, move the code, drop the old name later
0007_naive.sql: statement 8: WARN M011: one statement changes every row in one transaction
fix: run it in batches by primary-key range, one transaction per batch
6 errors, 2 warnings
exit code 1
$ python gate.py 0007_safe.sql
0 errors, 0 warnings
exit code 0statement measured gate verdict
ADD COLUMN, no default ok ok agree
ADD COLUMN, constant default ok ok agree
ADD COLUMN, volatile default unsafe M001 agree
ALTER COLUMN TYPE bigint unsafe M002 agree
SET NOT NULL, plain unsafe M003 agree
ADD CHECK ... NOT VALID ok ok agree
VALIDATE the CHECK ok ok agree
SET NOT NULL after a valid CHECK ok ok agree
ADD NOT NULL ... NOT VALID (PG 18) ok ok agree
VALIDATE the NOT NULL (PG 18) ok ok agree
CREATE INDEX unsafe M004 agree
ADD FOREIGN KEY unsafe M005 agree
ADD FOREIGN KEY ... NOT VALID ok ok agree
VALIDATE the FOREIGN KEY ok ok agree
ADD UNIQUE (id, amount_cents) unsafe M006 agree
RENAME COLUMN ok ok agree
DROP COLUMN ok ok agree
the gate agrees with the measurement on 17 of 17 statementsThe obvious file fails with six errors and two warnings, each with a fix; the safe file passes. The gate agrees with the measurement on all 17 statements, which is the point of building rules from measurements: the rules and the database tell the same story. Treat that as a check on the rules, not a proof. The gate reads text. It cannot see your data, your traffic, a custom function’s volatility, partitioned tables or triggers, so it complements a rehearsal on a production-sized copy and does not replace one.
Step 8: Apply the approved migration while traffic runs
The last script reads 0007_safe.sql, refuses to continue if the gate finds an error, and applies the nine statements one by one through run_ddl while a reader and a writer probe run. It also reports which statement was running when the slowest call started, so a stall can be traced to a statement instead of guessed at.
# step07_apply.py
"""step07_apply.py: gate the safe migration, then apply it statement by statement while traffic runs."""
import random
import time
from pathlib import Path
from gate import lint, split_statements
from lab import DSN, Probe, connect, fmt, say, seed
from migrate import run_ddl
def main():
text = Path("0007_safe.sql").read_text(encoding="utf-8")
refused = [f for f in lint(text) if f.severity == "error"]
if refused:
raise SystemExit(f"the gate refused the migration: {[f.rule for f in refused]}")
say("the gate accepted 0007_safe.sql")
seed()
rng = random.Random(5)
reader = Probe("reader", "SELECT id FROM orders WHERE id = %s", lambda n: (rng.randint(1, 2_000_000),))
writer = Probe("writer", "INSERT INTO orders (customer_id, status, amount_cents) VALUES (1, 'probe', 100)")
reader.start()
writer.start()
time.sleep(0.5)
windows = []
for number, statement in enumerate(split_statements(text), 1):
started = time.perf_counter()
attempts = run_ddl(DSN, statement, rng=random.Random(number))
ended = time.perf_counter()
windows.append((number, started - reader.t0, ended - reader.t0))
flat = " ".join(statement.split())
say(f" {number}. {flat[:66]:66} {ended - started:5.2f} s attempts={attempts}")
time.sleep(0.3)
for label, probe in (("reader", reader), ("writer", writer)):
summary = probe.finish()
during = [n for n, begin, end in windows if begin <= summary["worst_at"] <= end]
say(f" {label}: {fmt(summary)}")
say(f" slowest call started at t={summary['worst_at']:.2f} s, during statement "
f"{during[0] if during else 'none (between statements)'}")
with connect("final") as c:
say(" constraints: " + str(c.execute("SELECT conname, contype, convalidated FROM pg_constraint "
"WHERE conrelid = 'orders'::regclass AND contype IN ('c', 'f', 'u', 'p') "
"ORDER BY conname").fetchall()))
say(" status NOT NULL: " + str(c.execute("SELECT attnotnull FROM pg_attribute "
"WHERE attrelid = 'orders'::regclass AND attname = 'status'").fetchone()[0]))
say(" invalid indexes: " + str(c.execute("SELECT count(*) FROM pg_index WHERE NOT indisvalid").fetchone()[0]))
if __name__ == "__main__":
main()the gate accepted 0007_safe.sql
1. ALTER TABLE orders ADD COLUMN touched_at timestamptz 0.03 s attempts=1
2. CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status) 0.94 s attempts=1
3. ALTER TABLE orders ADD CONSTRAINT orders_status_ck CHECK (status I 0.14 s attempts=1
4. ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck 0.09 s attempts=1
5. ALTER TABLE orders ALTER COLUMN status SET NOT NULL 0.02 s attempts=1
6. ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY ( 0.02 s attempts=1
7. ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk 0.13 s attempts=1
8. CREATE UNIQUE INDEX CONCURRENTLY orders_amount_uq ON orders (id, a 0.64 s attempts=1
9. ALTER TABLE orders ADD CONSTRAINT orders_amount_uq UNIQUE USING IN 0.02 s attempts=1
reader: 262 calls, median 0.3 ms, max 1 ms, 0 slower than 100 ms, 0 errors
slowest call started at t=0.02 s, during statement none (between statements)
writer: 254 calls, median 0.5 ms, max 6 ms, 0 slower than 100 ms, 0 errors
slowest call started at t=2.28 s, during statement 8
constraints: [('orders_amount_uq', 'u', True), ('orders_customer_fk', 'f', True), ('orders_pkey', 'p', True), ('orders_status_ck', 'c', True)]
status NOT NULL: True
invalid indexes: 0Every statement ran on its first attempt. The two index builds took 0.94 and 0.64 seconds, the validations took 0.09 and 0.13 seconds, and everything else took 0.14 seconds or less. The reader’s worst call was 1 ms and the writer’s was 6 ms across the whole migration. The final listing shows what you wanted: every constraint validated, status not null, and no invalid index left behind.
Common mistakes and how to spot them
Trusting a fast test database. On the 200,000-row table the 81 ms scan took 9.5 ms. A migration that looks instant on a laptop can hold a lock for seconds in production. Rehearse on a copy of the real size.
Looking at the statement instead of the queue. If a deploy hangs, ask who holds the lock, not what the migration does. The pg_stat_activity query in step 3 prints it: the migration waiting on Lock:relation and, from pg_blocking_pids(), the session it is waiting for. The usual culprit is a transaction that is idle and open.
Setting a timeout and forgetting the retries. A lock_timeout without a retry loop turns a stall into a failed deploy. A retry loop without jitter and a cap hammers the table in lock-step. run_ddl does both, and gives up after ten attempts instead of retrying forever.
Leaving an invalid index. After any failed CREATE INDEX CONCURRENTLY, query pg_index.indisvalid and drop the leftover with DROP INDEX CONCURRENTLY.
Contracting too early. The two 93-error counts in step 6 are what happens when the old release is still running. Rename and drop last.
Autovacuum in the way. My first complete run of the step 6 script failed at the index build: ten attempts, every one ending in a lock timeout at 200 ms. Index builds with CONCURRENTLY take SHARE UPDATE EXCLUSIVE, the lock a vacuum holds, and a backfill of 2,000,000 rows leaves a lot to vacuum. The table’s statistics showed an autovacuum of orders that finished within half a minute after the failure, so a vacuum was probably running during the attempts, but I did not capture the worker in pg_stat_activity at the time, so treat that as likely, not proven. The documentation says “If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum”, and it defines deadlock_timeout (1 second by default) as “the amount of time to wait on a lock before checking to see if there is a deadlock condition.” My reading, which I did not test, is that a request that gives up after 200 ms never waits long enough for that to happen. For the lab I turned autovacuum off for the table while the demo runs, which is why the output says so. In production, look for a vacuum of the table first, and decide whether the statement may wait longer.
Check that everything works end to end
Run python run_all.py. It should print rc=0 for all eight steps, and you should be able to confirm these results in the saved outputs: the lock table shows the VALIDATE rows with SHARE UPDATE EXCLUSIVE and the PG 18 NOT VALID row with ACCESS EXCLUSIVE at about a millisecond; experiment 1 shows the reader and writer waiting about 2.7 seconds while experiment 2 stays near 0.2 seconds; the expand and contract phases of the type-change playbook have zero probe errors, while the deliberate mistake after them logs dozens; pytest says 66 passed; the gate agrees with the measurement on all rows; and the final step ends with no invalid indexes. Run python gate.py 0007_naive.sql and check that its exit code is 1, then python gate.py 0007_safe.sql and check that it is 0. Your timings will differ. The lock modes, the rewrite column, the error counts and the pass and fail results should not.
Where to go next
Run the gate in your deploy pipeline or as a pre-commit hook on your migration folder, and add a rule each time a migration surprises you. Put the lock timeout in whatever runs your migrations, and make the runner print the blocker when a retry fails. Try different lock_timeout values and batch sizes on your own table. If you write Rails migrations, Strong Migrations already does this job for them, so use it instead of this gate; the tutorial gives you the reasons behind its rules and a way to apply them to plain SQL files. If you want to keep learning on this site, the same habits appear in evolving a Protobuf schema without breaking old clients, where a CI gate blocks unsafe edits, and in building a plan gate for generated SQL and enforcing guardrails on generated Terraform. For what else PostgreSQL does with locks, read stopping lost updates and write skew with isolation levels and finding a slow query with EXPLAIN ANALYZE.
Sources
PostgreSQL 18 documentation: ALTER TABLE, explicit locking, CREATE INDEX, client connection defaults (lock_timeout and idle_in_transaction_session_timeout), routine vacuuming, lock management settings and the release 18 notes; the release 9.5 notes. GoCardless, Zero-downtime Postgres migrations, the hard parts. The Strong Migrations README. Python packages: psycopg and pytest.
No Comment! Be the first one.