An Instant ADD COLUMN Waiting on One Slow SELECT Blocks Every Query Behind It
- Book: Database Playbook
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
---
Your migration adds one nullable column. That has always been a catalog change with no table rewrite, and since PostgreSQL 11 even a column with a constant default skips the rewrite. The ALTER TABLE docs say so directly: "In neither case is a rewrite of the table required." Then the deploy goes out and your API stops answering queries on that table until something gives.
GoCardless wrote up the same lock queue with a different DDL: "around 15 seconds of unexpected API downtime during a planned database migration." They were adding foreign keys, and their post explains that this also locks the referenced tables, which were "some of the most heavily used." The ALTER itself "was fast to execute." It had to wait for a lock behind a long-running read, and "read/write queries issued by calls to our API piled up behind it, and clients timed out."
You can build that pile-up on your laptop in five minutes, then cap it at a two-second stall.
Why an instant ALTER still waits
Two facts from the Postgres docs combine into the outage.
First, the lock. The ALTER TABLE page says: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted." ADD COLUMN has no note, so it takes the strongest lock there is. The explicit locking chapter says ACCESS EXCLUSIVE "conflicts with locks of all modes," including the ACCESS SHARE lock that every plain SELECT takes. Fast or not, the ALTER cannot start while any transaction holds even a read lock on the table.
Second, the queue. Lock requests wait in line. The docs for pg_blocking_pids() define two kinds of blocking: a "hard block" from a session that holds a conflicting lock, and a "soft block" from a session that "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue."
That soft block is the whole story. A new SELECT wants ACCESS SHARE, which is perfectly compatible with the slow SELECT already running. It still waits, because the ALTER is ahead of it in the queue and the ALTER's ACCESS EXCLUSIVE conflicts with everything. So does every INSERT, UPDATE and DELETE that arrives after it. And the docs say the ALTER will wait for as long as it takes: a transaction seeking a lock "will wait indefinitely for conflicting locks to be released" unless a deadlock is detected.
!A long line of small delivery carts stalled behind one large truck stopped at a narrow gate, while a single slow cart sits inside the gate
One slow reader holds the gate. The ALTER waits at it, and everything behind the ALTER waits too.
Reproduce it with four psql sessions
You need Docker and psql, which the image already ships. Start a throwaway Postgres 18:
docker run -d --name pglock \
-e POSTGRES_PASSWORD=pg postgres:18
Open four terminals and run docker exec -it pglock psql -U postgres in each. Three of them build the pile-up. The fourth watches.
In terminal 1, create a table with some rows:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
total numeric(10,2) NOT NULL
);
INSERT INTO orders (total)
SELECT (random() * 100)::numeric(10,2)
FROM generate_series(1, 100000);
Still in terminal 1, start the slow reader. This stands in for a report query, an analytics export or a forgotten console session. It holds ACCESS SHARE on orders for five minutes:
SELECT pg_sleep(300) FROM orders LIMIT 1;
In terminal 2, run the migration:
ALTER TABLE orders ADD COLUMN note text;
It hangs. In terminal 3, play the app:
SELECT count(*) FROM orders;
That hangs too, even though it has no conflict with terminal 1. Every new statement that touches orders, reads and writes alike, now waits for a five-minute sleep it has nothing to do with.
Watch the queue in pg_locks and pg_blocking_pids()
In terminal 4, list who is waiting on whom. The pg_locks docs recommend pg_blocking_pids() over a hand-written self-join of pg_locks, so run it against pg_stat_activity:
SELECT pid,
pg_blocking_pids(pid) AS blocked_by,
wait_event_type,
left(query, 45) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
You should get two rows. The ALTER's pid lists terminal 1's pid in blocked_by: that is the hard block. The count(*) lists the ALTER's pid, not terminal 1's: that is the soft block, and it is the row that explains the outage. Both show a wait_event_type of Lock.
Now look at the raw locks on the table:
SELECT pid, mode, granted, waitstart
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY granted DESC, waitstart;
Terminal 1 holds AccessShareLock with granted true. The ALTER wants AccessExclusiveLock with granted false, and the count(*) wants AccessShareLock, also not granted. The waitstart column (added in Postgres 14) tells you how long each one has been stuck. One caveat from the docs: pg_locks "does not expose information about which processes are ahead of which others in lock wait queues," so use pg_blocking_pids() when you need the order.
Go back to terminal 1 and press Ctrl+C to cancel the sleep. The ALTER gets its lock, finishes, and releases it, and the count(*) in terminal 3 returns right after. The migration was never the slow part.
Fix one: lock_timeout plus a retry loop
The ALTER cannot skip the line, but it can refuse to stand in it for long. lock_timeout aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock," and the limit "applies separately to each lock acquisition attempt." Put it at the top of the migration file:
-- 0042_add_order_note.sql
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS note text;
Re-run the reproduction: start the slow reader in terminal 1 again, then paste those two lines into terminal 2 instead of the bare ALTER. The count(*) in terminal 3 still waits, but for at most two seconds. Then the ALTER fails with SQLSTATE 55P03, which the error code table names lock_not_available, leaves the queue, and the reads go through.
Pick the number as the longest pause your app can absorb, because that is what every query behind the ALTER pays on each attempt. Published defaults vary a lot: Squawk's example uses 1s, strong_migrations suggests 10.seconds, and PostgresAI's retry write-up uses 50ms.
The migration still has to land, so wrap it in a retry loop with a growing, jittered delay. This wrapper belongs in your deploy pipeline, where psql and $DATABASE_URL point at the real database, not in the Docker repro:
#!/usr/bin/env bash
# retry-ddl.sh FILE: run a migration until it gets its lock
set -u
file=$1
for attempt in $(seq 1 10); do
psql "$DATABASE_URL" -X -1 -v ON_ERROR_STOP=1 -f "$file"
rc=$?
[ "$rc" -eq 0 ] && exit 0
[ "$rc" -ne 3 ] && exit "$rc" # not a SQL error: stop
delay=$(( attempt * 2 + RANDOM % 3 ))
echo "attempt $attempt failed, retry in ${delay}s" >&2
sleep "$delay"
done
echo "gave up after 10 attempts" >&2
exit 1
-1 wraps the file in one transaction, so a failed attempt rolls back cleanly. Per the psql docs, exit status 3 means "an error occurred in a script and the variable ON_ERROR_STOP was set," so connection failures stop the loop instead of retrying. The loop does retry any SQL error, not only lock timeouts, which is why the file uses IF NOT EXISTS and why it gives up after ten tries. If you would rather keep the retry inside the database, the PostgresAI post has a PL/pgSQL DO block that catches lock_not_available with exponential backoff.
!A padlock on a gate with a stopwatch beside it and a circular arrow looping back to the start of a short line
Wait two seconds, step out of line, retry later. Each attempt costs the reads behind it at most two seconds.
One trap: lock_timeout is per lock, not per migration. If one transaction alters orders and then waits on customers, it holds ACCESS EXCLUSIVE on orders the whole time it waits. Keep one table's DDL per file.
Fix two: put a ceiling on long readers
The retry loop keeps the app alive while a slow reader exists. It does not help the migration land if the reader runs for an hour. Cap long readers per role instead of globally, since the docs warn that setting statement_timeout in postgresql.conf "would affect all sessions":
ALTER ROLE reporting SET statement_timeout = '30s';
ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
statement_timeout cancels the five-minute pg_sleep kind of reader. idle_in_transaction_session_timeout ends the other common culprit: a session that ran a SELECT inside BEGIN and never committed, which holds its ACCESS SHARE lock until the transaction ends. Both apply to new sessions for that role.
Before a deploy, look for anything old enough to stall you:
SELECT pid, now() - xact_start AS age, state,
left(query, 45) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 minute'
ORDER BY xact_start;
The one line to require in generated migrations
ORMs and coding agents are good at writing the ALTER TABLE. Neither knows what else is running against your database at deploy time, and nothing about an ADD COLUMN statement looks dangerous in a diff. So make the safety line mandatory and check for it mechanically. Every migration file starts with:
SET lock_timeout = '2s';
Put that rule in the instructions file your coding agent reads, run the file through the retry wrapper, and enforce it in CI with Squawk, which has a rule for exactly this, require-lock-timeout, and flags a bare alter table t add column c boolean;. On Rails, strong_migrations sets the same timeout for every migration from one config line.
Then run the reproduction once more with the line in place and watch terminal 3. Two seconds of waiting, then the reads come back.
---
If this was useful
How a store behaves while you change its schema under live traffic belongs in the decision about which store to use. That is the kind of trade-off I wrote the Database Playbook around. If you are picking a store for a new system, or about to let an ORM or an agent own your migrations, it is meant to help you ask those questions early.
1 reaction · 1 comment
Your Agent's RENAME COLUMN Breaks Every Old Pod. Expand-Contract in 4 Steps
- Book: Database Playbook
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
---
You ask a coding agent to rename users.name to full_name. It updates the model, fixes every query, and writes a migration with one line in it: ALTER TABLE users RENAME COLUMN name TO full_name;. The diff is tidy and the tests pass, because the tests run against one version of the code. Production runs two.
A Kubernetes Deployment rolls out by scaling the new ReplicaSet up while it scales the old one down, and both maxUnavailable and maxSurge default to 25%. For the length of the rollout, old Pods and new Pods serve traffic side by side. Your migration runs once, just before or somewhere inside that window. Every old Pod still alive after the rename sends SELECT name ... to a table that no longer has a name column.
Why one RENAME breaks the rollout
There are two separate failures packed into that one line.
The first is the code-schema mismatch. Run the rename before the new code and the old Pods fail. Ship the code first and the new Pods fail until the migration lands. No ordering makes both versions happy, which is why the strong_migrations README puts it bluntly: "Renaming a column that's in use will cause errors in your application." The Squawk linter has a renaming-column rule for the same reason.
The second is the lock. A rename touches no data; the Postgres docs say "there is no effect on the stored data." But ALTER TABLE takes an ACCESS EXCLUSIVE lock "unless explicitly noted," and that mode conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. If a long transaction is holding the table, the rename waits. While it waits, as Xata's write-up on the lock queue explains, every later statement that needs a lock on that table queues behind it. The table is blocked for reads and writes until the rename finishes or gives up.
The fix is old. Danilo Sato described it as Parallel Change in 2014: expand the interface to support old and new, migrate the clients, then contract back to only the new. For a column rename in Postgres, that is four steps.
!Old and new application servers connected to one database, with the old servers' cables cut
During a rolling deploy, two versions of your code share one schema.
The lab: one table, two app versions
Start a throwaway Postgres 18 and seed a table:
docker run -d --name pg -e POSTGRES_PASSWORD=pw postgres:18
# wait a few seconds for it to accept connections
docker exec -i pg psql -U postgres <<'SQL'
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
INSERT INTO users (name)
SELECT 'user ' || g FROM generate_series(1, 200000) g;
SQL
The "app" is two files, one per image version. Each does what a request handler would do: write a row, read it back.
-- old_app.sql: what the old image runs
INSERT INTO users (name) VALUES ('Old Pod Writer');
SELECT id, name FROM users ORDER BY id DESC LIMIT 1;
-- new_app.sql: what the new image runs
INSERT INTO users (full_name) VALUES ('New Pod Writer');
SELECT id, full_name FROM users ORDER BY id DESC LIMIT 1;
Run either one with docker exec -i pg psql -U postgres < old_app.sql. Right now old_app.sql works and new_app.sql fails. Apply the agent's rename and the result flips: old_app.sql fails with a column-does-not-exist error. That flip is your rolling deploy. Rename the column back to name before you go on.
You can also watch the lock queue. Open three terminals with docker exec -it pg psql -U postgres:
1. Terminal A: BEGIN; SELECT count(*) FROM users; and leave the transaction open.
2. Terminal B: ALTER TABLE users RENAME COLUMN name TO full_name; hangs, waiting for A.
3. Terminal C: SELECT id FROM users LIMIT 1; hangs too, queued behind B.
Cancel B with Ctrl-C and C returns at once. Now run SET lock_timeout = '2s'; in B and try the rename again. B gives up after two seconds with a lock-timeout error instead of holding everyone hostage. lock_timeout "applies separately to each lock acquisition attempt," and it is the setting every migration below starts with. Roll back A when you're done.
Step 1: expand without a long lock
The first deploy is a migration only. No application code changes. It adds the new column and a trigger that keeps the two columns in sync, whichever one a writer touches.
-- 001_expand.sql (deploy 1, app code unchanged)
SET lock_timeout = '2s';
SET statement_timeout = '10s';
BEGIN;
ALTER TABLE users ADD COLUMN full_name text;
CREATE FUNCTION users_sync_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.full_name := coalesce(NEW.full_name, NEW.name);
NEW.name := coalesce(NEW.name, NEW.full_name);
ELSIF NEW.name IS DISTINCT FROM OLD.name THEN
NEW.full_name := NEW.name;
ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
NEW.name := NEW.full_name;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER users_sync_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION users_sync_name();
COMMIT;
Apply it with docker exec -i pg psql -U postgres -v ON_ERROR_STOP=1 < 001_expand.sql. If the lock wait times out, psql exits with status 3, the transaction rolls back, and you run it again: two seconds lost, instead of every query on the table stuck behind your ALTER.
Three details make this safe. Adding a column with no default gives existing rows NULL, and the ALTER TABLE page says "in neither case is a rewrite of the table required," so the exclusive lock is held only briefly. The trigger is a BEFORE row trigger, so it fills name before the NOT NULL check runs, which is what lets new_app.sql insert without naming the old column. And the UPDATE branch copies in one direction only, from whichever column changed, so a writer on either version never has its value overwritten by the other column.
Run both app files now. Both succeed, and the last two rows have name and full_name filled. Old and new code can live together, which is the whole point of the expand step.
Step 2: backfill in batches that give up fast
The 200,000 seed rows still have full_name empty. Don't fix that with one UPDATE users SET full_name = name. On a big table that is one long transaction holding row locks on every row, and strong_migrations names the three keys to doing it safely: "batching, throttling, and running it outside a transaction."
One batch, keyed on the primary key so each batch starts where the last one stopped:
-- backfill.sql: one batch of 1,000 rows
SET lock_timeout = '2s';
SET statement_timeout = '30s';
WITH batch AS (
SELECT id FROM users
WHERE id > :last
ORDER BY id
LIMIT 1000
), upd AS (
UPDATE users u
SET full_name = u.name
FROM batch b
WHERE u.id = b.id AND u.full_name IS NULL
)
SELECT coalesce(max(id), 0) FROM batch;
The upd step has no RETURNING and nothing reads it, and it still runs: Postgres executes data-modifying statements in WITH "exactly once, and always to completion." The query prints the last id it handled, or 0 when nothing is left.
A short loop drives it. psql runs in autocommit mode, so each batch is its own transaction:
#!/usr/bin/env bash
# backfill.sh: run after deploy 1, before deploy 2
last=0
while :; do
next=$(docker exec -i pg psql -U postgres -qtA \
-v ON_ERROR_STOP=1 -v last="$last" < backfill.sql) \
|| { echo "batch failed, retrying"; sleep 1; continue; }
[ "$next" -eq 0 ] && break
echo "backfilled through id $next"
last=$next
sleep 0.2
done
Size the batch so each one finishes far below the 30-second statement_timeout. When a batch hits a timeout, it rolls back on its own and the loop retries the same range. Then check that nothing drifted:
SELECT count(*) FROM users
WHERE full_name IS DISTINCT FROM name;
That count has to be zero before you go on.
!An accordion that widens in the middle and narrows again at the end
Expand, migrate, contract: the schema gets wider before it gets narrower.
Steps 3 and 4: switch, then contract
Step 3 is a normal code deploy (deploy 2): the image that runs new_app.sql, reading and writing only full_name. During that rollout, old Pods still write name and the trigger copies it across. New Pods write full_name and the trigger copies it back, so an old Pod reading name still sees rows the new Pods wrote. Until step 4, rolling back to the old image loses nothing.
If the column should be NOT NULL, Postgres 18 lets you add that without a long lock. The ALTER TABLE docs allow NOT VALID on not-null constraints, and validation runs as a separate statement:
SET lock_timeout = '2s';
SET statement_timeout = '15min';
ALTER TABLE users
ADD CONSTRAINT users_full_name_nn NOT NULL full_name NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_full_name_nn;
The statement_timeout is longer than the 10 seconds used elsewhere because VALIDATE scans the whole table. It only takes a SHARE UPDATE EXCLUSIVE lock while it does, so reads and writes keep going.
Step 4 waits until no released code reads name and you are past the point where you would roll back. Then it removes the old column in its own deploy:
-- 002_contract.sql (deploy 3, nothing reads name)
SET lock_timeout = '2s';
SET statement_timeout = '10s';
BEGIN;
DROP TRIGGER users_sync_name ON users;
DROP FUNCTION users_sync_name();
ALTER TABLE users DROP COLUMN name;
COMMIT;
DROP COLUMN is quick too. Per the same page, Postgres "does not physically remove the column"; it makes the column invisible to SQL, and the space comes back on the next table rewrite. One catch: some ORMs cache the column list at boot. strong_migrations notes that Active Record "caches database columns at runtime," so on Rails you add the column to ignored_columns and deploy that before you run the drop.
The rule to give your agent
The agent wrote the one-liner because nothing told it about the second version of the code. Tell it. Claude Code loads path-scoped rules from .claude/rules/ when it works on matching files, so this file only shows up when the agent touches a migration:
---
paths:
- "migrations/**/*.sql"
---
# Postgres migration rules
- Never RENAME COLUMN or RENAME TABLE on a table the
running app reads. Propose expand-contract instead.
- Expand migration: ADD COLUMN (nullable, no volatile
default) plus a BEFORE INSERT OR UPDATE trigger
that keeps the old and new columns in sync.
- Backfill is a separate batched script keyed on the
primary key, never part of the migration.
- Every migration file starts with SET lock_timeout
and SET statement_timeout.
- DROP COLUMN goes in its own migration, in a later
deploy, after no released code reads the column.
- When asked for a rename, write out the 4-step plan
and put only step 1 in this change.
Other coding agents in the repo read AGENTS.md, so copy the bullets there too. Claude Code keeps loading .claude/rules/ either way, and the same docs page covers when it reads AGENTS.md at all.
A rule file is advice. The docs say so directly: Claude "treats them as context, not enforced configuration." So back it with a check that does not care who wrote the SQL. Run squawk 'migrations/*.sql' in CI, or the squawk-action on pull requests. Its renaming-column rule catches the one-liner, and require-lock-timeout and require-statement-timeout catch a migration that forgot its timeouts. It will also flag the drops in step 4, which is fine: that is the one migration a human should read before it ships.
If you want the expand-contract bookkeeping automated, pgroll does the add-column, backfill and sync-trigger work for you and serves old and new schema versions through views at the same time. Knowing the four steps by hand is still what lets you review what it, or your agent, is about to run.
---
If this was useful
The four steps here are what a schema change costs once a store has live traffic and more than one version of your code talking to it. I wrote the Database Playbook for the decision that comes before all of this: which store to pick for a system, knowing you will be changing its schema under real load for years. If this post made you look harder at your own migrations, the book is the wider version of that look.
SQL-Migration-Dryrun-Guard: Why Regex-Based Static Analysis Fails to Prevent Production Database Lockups
When performing zero-downtime database migrations on tables containing millions or tens of millions of rows, an unconsidered DDL statement can immediately lead to catastrophic exclusive locks (ACCESS EXCLUSIVE in PostgreSQL), grinding the entire user-facing service to a halt.
To tackle this problem, we designed an experimental standalone CLI checker named SQL-Migration-Dryrun-Guard. The goal was simple: analyze raw SQL migration files against table row-count metadata in under 10 seconds before hitting production.
However, after running realistic failure scenarios and edge-case evaluations, the tool was deprecated and discarded.
This post serves as a technical post-mortem and an architectural anti-pattern case study, dissecting why naive string matching and external metadata models fundamentally fall short in mission-critical database safety checks.
---
1. System Architecture and Design Objectives
SQL-Migration-Dryrun-Guard was architected with a strict minimal-dependency philosophy while guaranteeing strict type safety and schema validation.
Technology Stack
- Runtime / Language: Python 3.10+
- Schema Validation: Pydantic v2 (for robust deserialization, strict types, and validation models)
- JSON Processing: orjson (high-throughput Rust-backed serializer, configured with a resilient standard library json fallback)
- Detection Engine: Case-normalized keyword inspection coupled with tabular cardinality threshold comparison (targeting CREATE INDEX without CONCURRENTLY, unoptimized ADD COLUMN WITH DEFAULT, etc.)
Architecture Flow
flowchart TD
subgraph InputStage ["Input Processing"]
SQLFile["SQL Migration Script (.sql)"]
MetaFile["Table Cardinality Metadata (.json)"]
end
subgraph GuardEngine ["SQL-Migration-Dryrun-Guard Core"]
PydanticSchema["Pydantic v2 Input Parser"]
StringNormalizer["Case-Normalization & Token Matching"]
ThresholdEvaluator["Row Count vs Risk Matrix"]
end
subgraph OutputStage ["Reporting"]
Report["Structured DangerPoint Report (JSON/CLI)"]
end
SQLFile -- "Read SQL Text" --> PydanticSchema
MetaFile -- "Read Metadata" --> PydanticSchema
PydanticSchema -- "Validated Input" --> StringNormalizer
StringNormalizer -- "Extracted Keywords" --> ThresholdEvaluator
ThresholdEvaluator -- "Trigger DangerPoint" --> Report
Core Engine Implementation
Below is the foundational logic of the validation engine:
from typing import List
from pydantic import BaseModel, Field
class TableMetadata(BaseModel):
table_name: str
row_count: int
class MigrationInput(BaseModel):
sql_content: str
tables: List[TableMetadata]
class DangerPoint(BaseModel):
statement: str
risk_level: str
reason: str
suggested_sql: str
class MigrationReport(BaseModel):
dangers: List[DangerPoint]
is_safe: bool
class MigrationGuard:
THRESHOLD_ROWS: int = 100_000 # Row count threshold where exclusive locks risk incidents
def __init__(self, migration_input: MigrationInput):
self.input_data = migration_input
def analyze(self) -> MigrationReport:
dangers: List[DangerPoint] = []
sql_upper = self.input_data.sql_content.upper()
row_counts = {t.table_name.upper(): t.row_count for t in self.input_data.tables}
# Validate CREATE INDEX statements for CONCURRENTLY clause
if "CREATE INDEX" in sql_upper:
for t_name, rows in row_counts.items():
if t_name in sql_upper and rows >= self.THRESHOLD_ROWS:
if "CONCURRENTLY" not in sql_upper:
dangers.append(DangerPoint(
statement=self.input_data.sql_content.strip(),
risk_level="HIGH",
reason=(
f"Creating an index on table '{t_name}' ({rows:,} rows) "
f"without CONCURRENTLY will acquire an exclusive lock, blocking writes."
),
suggested_sql=self.input_data.sql_content.replace(
"CREATE INDEX", "CREATE INDEX CONCURRENTLY"
)
))
# Additional heuristics (e.g., ADD COLUMN WITH DEFAULT) follow the same pattern...
return MigrationReport(
dangers=dangers,
is_safe=len(dangers) == 0
)
---
2. Why It Failed: The Three Structural Bottlenecks
While the core logic appeared mathematically straightforward and Pydantic v2 eliminated basic runtime schema errors, practical integration immediately surfaced fundamental architectural limitations.
① Static Analysis Limits: Syntax Permutations and Context Blindness
The utility relied on substring occurrences and naive keyword matching (CREATE INDEX, ADD COLUMN). However, real-world DDL migrations in mature codebases are rarely flat single-statement scripts:
- False Positives in Comments and Procedures: Keywords within inline comments (-- DO NOT RUN: CREATE INDEX ...) or dynamic query literals inside PL/pgSQL stored routines triggered false alarms.
- Complex Multi-line / Transactional DDL: Multi-statement chains spanning dozens of lines, subtransactions (SAVEPOINT), or statements encapsulated within BEGIN ... COMMIT blocks could not be parsed safely.
- Absence of AST (Abstract Syntax Tree): Attempting to infer semantic intent from raw strings without an AST-driven parser is fundamentally flawed. Relying solely on regex matching alongside static row count thresholds leads to uncontrollable false positive and false negative rates.
② The Dilemma of Metadata Stagnation in CLI Workflows
In an attempt to deliver a lightweight, standalone zero-network CLI, the tool required table row counts to be passed externally via a pre-generated JSON metadata file.
In large-scale production environments containing thousands of partitioned tables and relations:
- Maintaining up-to-date table statistics inside developer environments became a maintenance bottleneck.
- Generating, sanitizing, and injecting current statistical snapshots into CI/CD pipelines created more developer friction than the migration risk itself.
- High barrier to entry: the operational cost of preparing input data eclipsed the utility of the tool, leading to workflow abandonment.
③ Operational Friction: LLM-Assisted Code Pipelines and Quality Assurance Failures
During final regression test cycles, foreign artifacts and formatting noise (such as Markdown delimiters and chat transcript formatting tags) inadvertently entered the codebase artifact pipelines.
Because strict file-level sanitation was absent in the pipeline, the Python interpreter encountered invalid full-width characters:
SyntaxError: invalid character '）' (U+FF09)
This crashed execution before the runtime engine could inspect a single line of SQL. Although the algorithmic hypothesis was structurally sound, it exposed how vulnerable loosely audited generative pipelines are without automated syntactic verification gates.
---
3. Engineering Post-Mortem and Anti-Pattern Takeaways
From this decommissioned initiative, we established three non-negotiable architectural lessons for database reliability tooling:
1. Never Gate SQL Safety on String Matching Heuristics
Heuristic regex matching on SQL strings is an anti-pattern. If you are building a custom linter, build upon an established AST parser such as sqlglot, pglast, or libpg_query. AST inspection allows you to isolate token types (distinguishing comments and string literals from actual DDL commands) and inspect syntactic trees deterministically.
2. Do Not Offload Production State to Static Files
Decoupling metadata from the data source to simplify tooling usually inverts the operational burden onto developers. A robust verification layer must either:
- Safely query statistics directly from read replicas or non-blocking system catalogs (e.g., querying pg_stat_user_tables or pg_class.reltuples).
- Execute real dry-runs against an ephemeral copy of the database using transactional rollbacks (BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;) or database clone technologies.
3. Static Code Does Not Equal Runtime Environment Realities
Whether an index creation or schema alteration blocks concurrent operations depends on dynamic environmental variables:
- The target database engine and patch level (e.g., PostgreSQL 11+ handles non-volatile ADD COLUMN ... DEFAULT without table rewrites, whereas earlier versions rewrite the entire table).
- Active connection pools, lock queues, and long-running analytical queries.
Static CLI linters operating without engine version awareness cannot reliably predict real-world lock contention.
---
4. Conclusion & Recommended Alternatives
Rather than re-inventing SQL regex linters, engineering teams should leverage mature, industry-tested alternatives:
- Migration Framework Hooks: Utilize pre-commit validations within native migration tools (such as Alembic, Flyway, or dbmate).
- Ast-Driven Linters: Adopt dedicated linters like Squawk or strong_migrations designed specifically to flag dangerous DDL operations in PostgreSQL.
- Ephemeral DB Branching: Spin up temporary database branch clones in CI (via Docker or storage-level copy-on-write tools) to execute actual migrations against production-like schemas before deployment.
Treating schema safety as a compilation and runtime verification problem—rather than a static string search—is the only sustainable approach to zero-downtime engineering.
---
If this engineering log saved your production server (and your sanity), consider supporting our architecture on GitHub Sponsors.
1 reaction
Mentions
An Instant ADD COLUMN Waiting on One Slow SELECT Blocks Every Query Behind It - Book: Database Playbook - Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go - My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools - Me: xgabriel.com | GitHub --- Your migration adds one nullable column. That has always been a catalog change with no table rewrite, and since PostgreSQL 11 even a column with a constant default skips the rewrite. The ALTER TABLE docs say so directly: "In neither case is a rewrite of the table required." Then the deploy goes out and your API stops answering queries on that table until something gives. GoCardless wrote up the same lock queue with a different DDL: "around 15 seconds of unexpected API downtime during a planned database migration." They were adding foreign keys, and their post explains that this also locks the referenced tables, which were "some of the most heavily used." The ALTER itself "was fast to execute." It had to wait for a lock behind a long-running read, and "read/write queries issued by calls to our API piled up behind it, and clients timed out." You can build that pile-up on your laptop in five minutes, then cap it at a two-second stall. Why an instant ALTER still waits Two facts from the Postgres docs combine into the outage. First, the lock. The ALTER TABLE page says: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted." ADD COLUMN has no note, so it takes the strongest lock there is. The explicit locking chapter says ACCESS EXCLUSIVE "conflicts with locks of all modes," including the ACCESS SHARE lock that every plain SELECT takes. Fast or not, the ALTER cannot start while any transaction holds even a read lock on the table. Second, the queue. Lock requests wait in line. The docs for pg_blocking_pids() define two kinds of blocking: a "hard block" from a session that holds a conflicting lock, and a "soft block" from a session that "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue." That soft block is the whole story. A new SELECT wants ACCESS SHARE, which is perfectly compatible with the slow SELECT already running. It still waits, because the ALTER is ahead of it in the queue and the ALTER's ACCESS EXCLUSIVE conflicts with everything. So does every INSERT, UPDATE and DELETE that arrives after it. And the docs say the ALTER will wait for as long as it takes: a transaction seeking a lock "will wait indefinitely for conflicting locks to be released" unless a deadlock is detected. !A long line of small delivery carts stalled behind one large truck stopped at a narrow gate, while a single slow cart sits inside the gate One slow reader holds the gate. The ALTER waits at it, and everything behind the ALTER waits too. Reproduce it with four psql sessions You need Docker and psql, which the image already ships. Start a throwaway Postgres 18: docker run -d --name pglock \ -e POSTGRES_PASSWORD=pg postgres:18 Open four terminals and run docker exec -it pglock psql -U postgres in each. Three of them build the pile-up. The fourth watches. In terminal 1, create a table with some rows: CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total numeric(10,2) NOT NULL ); INSERT INTO orders (total) SELECT (random() * 100)::numeric(10,2) FROM generate_series(1, 100000); Still in terminal 1, start the slow reader. This stands in for a report query, an analytics export or a forgotten console session. It holds ACCESS SHARE on orders for five minutes: SELECT pg_sleep(300) FROM orders LIMIT 1; In terminal 2, run the migration: ALTER TABLE orders ADD COLUMN note text; It hangs. In terminal 3, play the app: SELECT count(*) FROM orders; That hangs too, even though it has no conflict with terminal 1. Every new statement that touches orders, reads and writes alike, now waits for a five-minute sleep it has nothing to do with. Watch the queue in pg_locks and pg_blocking_pids() In terminal 4, list who is waiting on whom. The pg_locks docs recommend pg_blocking_pids() over a hand-written self-join of pg_locks, so run it against pg_stat_activity: SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 45) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; You should get two rows. The ALTER's pid lists terminal 1's pid in blocked_by: that is the hard block. The count(*) lists the ALTER's pid, not terminal 1's: that is the soft block, and it is the row that explains the outage. Both show a wait_event_type of Lock. Now look at the raw locks on the table: SELECT pid, mode, granted, waitstart FROM pg_locks WHERE relation = 'orders'::regclass ORDER BY granted DESC, waitstart; Terminal 1 holds AccessShareLock with granted true. The ALTER wants AccessExclusiveLock with granted false, and the count(*) wants AccessShareLock, also not granted. The waitstart column (added in Postgres 14) tells you how long each one has been stuck. One caveat from the docs: pg_locks "does not expose information about which processes are ahead of which others in lock wait queues," so use pg_blocking_pids() when you need the order. Go back to terminal 1 and press Ctrl+C to cancel the sleep. The ALTER gets its lock, finishes, and releases it, and the count(*) in terminal 3 returns right after. The migration was never the slow part. Fix one: lock_timeout plus a retry loop The ALTER cannot skip the line, but it can refuse to stand in it for long. lock_timeout aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock," and the limit "applies separately to each lock acquisition attempt." Put it at the top of the migration file: -- 0042_add_order_note.sql SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN IF NOT EXISTS note text; Re-run the reproduction: start the slow reader in terminal 1 again, then paste those two lines into terminal 2 instead of the bare ALTER. The count(*) in terminal 3 still waits, but for at most two seconds. Then the ALTER fails with SQLSTATE 55P03, which the error code table names lock_not_available, leaves the queue, and the reads go through. Pick the number as the longest pause your app can absorb, because that is what every query behind the ALTER pays on each attempt. Published defaults vary a lot: Squawk's example uses 1s, strong_migrations suggests 10.seconds, and PostgresAI's retry write-up uses 50ms. The migration still has to land, so wrap it in a retry loop with a growing, jittered delay. This wrapper belongs in your deploy pipeline, where psql and $DATABASE_URL point at the real database, not in the Docker repro: #!/usr/bin/env bash # retry-ddl.sh FILE: run a migration until it gets its lock set -u file=$1 for attempt in $(seq 1 10); do psql "$DATABASE_URL" -X -1 -v ON_ERROR_STOP=1 -f "$file" rc=$? [ "$rc" -eq 0 ] && exit 0 [ "$rc" -ne 3 ] && exit "$rc" # not a SQL error: stop delay=$(( attempt * 2 + RANDOM % 3 )) echo "attempt $attempt failed, retry in ${delay}s" >&2 sleep "$delay" done echo "gave up after 10 attempts" >&2 exit 1 -1 wraps the file in one transaction, so a failed attempt rolls back cleanly. Per the psql docs, exit status 3 means "an error occurred in a script and the variable ON_ERROR_STOP was set," so connection failures stop the loop instead of retrying. The loop does retry any SQL error, not only lock timeouts, which is why the file uses IF NOT EXISTS and why it gives up after ten tries. If you would rather keep the retry inside the database, the PostgresAI post has a PL/pgSQL DO block that catches lock_not_available with exponential backoff. !A padlock on a gate with a stopwatch beside it and a circular arrow looping back to the start of a short line Wait two seconds, step out of line, retry later. Each attempt costs the reads behind it at most two seconds. One trap: lock_timeout is per lock, not per migration. If one transaction alters orders and then waits on customers, it holds ACCESS EXCLUSIVE on orders the whole time it waits. Keep one table's DDL per file. Fix two: put a ceiling on long readers The retry loop keeps the app alive while a slow reader exists. It does not help the migration land if the reader runs for an hour. Cap long readers per role instead of globally, since the docs warn that setting statement_timeout in postgresql.conf "would affect all sessions": ALTER ROLE reporting SET statement_timeout = '30s'; ALTER ROLE app SET idle_in_transaction_session_timeout = '60s'; statement_timeout cancels the five-minute pg_sleep kind of reader. idle_in_transaction_session_timeout ends the other common culprit: a session that ran a SELECT inside BEGIN and never committed, which holds its ACCESS SHARE lock until the transaction ends. Both apply to new sessions for that role. Before a deploy, look for anything old enough to stall you: SELECT pid, now() - xact_start AS age, state, left(query, 45) AS query FROM pg_stat_activity WHERE xact_start < now() - interval '1 minute' ORDER BY xact_start; The one line to require in generated migrations ORMs and coding agents are good at writing the ALTER TABLE. Neither knows what else is running against your database at deploy time, and nothing about an ADD COLUMN statement looks dangerous in a diff. So make the safety line mandatory and check for it mechanically. Every migration file starts with: SET lock_timeout = '2s'; Put that rule in the instructions file your coding agent reads, run the file through the retry wrapper, and enforce it in CI with Squawk, which has a rule for exactly this, require-lock-timeout, and flags a bare alter table t add column c boolean;. On Rails, strong_migrations sets the same timeout for every migration from one config line. Then run the reproduction once more with the line in place and watch terminal 3. Two seconds of waiting, then the reads come back. --- If this was useful How a store behaves while you change its schema under live traffic belongs in the decision about which store to use. That is the kind of trade-off I wrote the Database Playbook around. If you are picking a store for a new system, or about to let an ORM or an agent own your migrations, it is meant to help you ask those questions early. 
Your Agent's RENAME COLUMN Breaks Every Old Pod. Expand-Contract in 4 Steps - Book: Database Playbook - Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go - My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools - Me: xgabriel.com | GitHub --- You ask a coding agent to rename users.name to full_name. It updates the model, fixes every query, and writes a migration with one line in it: ALTER TABLE users RENAME COLUMN name TO full_name;. The diff is tidy and the tests pass, because the tests run against one version of the code. Production runs two. A Kubernetes Deployment rolls out by scaling the new ReplicaSet up while it scales the old one down, and both maxUnavailable and maxSurge default to 25%. For the length of the rollout, old Pods and new Pods serve traffic side by side. Your migration runs once, just before or somewhere inside that window. Every old Pod still alive after the rename sends SELECT name ... to a table that no longer has a name column. Why one RENAME breaks the rollout There are two separate failures packed into that one line. The first is the code-schema mismatch. Run the rename before the new code and the old Pods fail. Ship the code first and the new Pods fail until the migration lands. No ordering makes both versions happy, which is why the strong_migrations README puts it bluntly: "Renaming a column that's in use will cause errors in your application." The Squawk linter has a renaming-column rule for the same reason. The second is the lock. A rename touches no data; the Postgres docs say "there is no effect on the stored data." But ALTER TABLE takes an ACCESS EXCLUSIVE lock "unless explicitly noted," and that mode conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. If a long transaction is holding the table, the rename waits. While it waits, as Xata's write-up on the lock queue explains, every later statement that needs a lock on that table queues behind it. The table is blocked for reads and writes until the rename finishes or gives up. The fix is old. Danilo Sato described it as Parallel Change in 2014: expand the interface to support old and new, migrate the clients, then contract back to only the new. For a column rename in Postgres, that is four steps. !Old and new application servers connected to one database, with the old servers' cables cut During a rolling deploy, two versions of your code share one schema. The lab: one table, two app versions Start a throwaway Postgres 18 and seed a table: docker run -d --name pg -e POSTGRES_PASSWORD=pw postgres:18 # wait a few seconds for it to accept connections docker exec -i pg psql -U postgres <<'SQL' CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL ); INSERT INTO users (name) SELECT 'user ' || g FROM generate_series(1, 200000) g; SQL The "app" is two files, one per image version. Each does what a request handler would do: write a row, read it back. -- old_app.sql: what the old image runs INSERT INTO users (name) VALUES ('Old Pod Writer'); SELECT id, name FROM users ORDER BY id DESC LIMIT 1; -- new_app.sql: what the new image runs INSERT INTO users (full_name) VALUES ('New Pod Writer'); SELECT id, full_name FROM users ORDER BY id DESC LIMIT 1; Run either one with docker exec -i pg psql -U postgres < old_app.sql. Right now old_app.sql works and new_app.sql fails. Apply the agent's rename and the result flips: old_app.sql fails with a column-does-not-exist error. That flip is your rolling deploy. Rename the column back to name before you go on. You can also watch the lock queue. Open three terminals with docker exec -it pg psql -U postgres: 1. Terminal A: BEGIN; SELECT count(*) FROM users; and leave the transaction open. 2. Terminal B: ALTER TABLE users RENAME COLUMN name TO full_name; hangs, waiting for A. 3. Terminal C: SELECT id FROM users LIMIT 1; hangs too, queued behind B. Cancel B with Ctrl-C and C returns at once. Now run SET lock_timeout = '2s'; in B and try the rename again. B gives up after two seconds with a lock-timeout error instead of holding everyone hostage. lock_timeout "applies separately to each lock acquisition attempt," and it is the setting every migration below starts with. Roll back A when you're done. Step 1: expand without a long lock The first deploy is a migration only. No application code changes. It adds the new column and a trigger that keeps the two columns in sync, whichever one a writer touches. -- 001_expand.sql (deploy 1, app code unchanged) SET lock_timeout = '2s'; SET statement_timeout = '10s'; BEGIN; ALTER TABLE users ADD COLUMN full_name text; CREATE FUNCTION users_sync_name() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'INSERT' THEN NEW.full_name := coalesce(NEW.full_name, NEW.name); NEW.name := coalesce(NEW.name, NEW.full_name); ELSIF NEW.name IS DISTINCT FROM OLD.name THEN NEW.full_name := NEW.name; ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN NEW.name := NEW.full_name; END IF; RETURN NEW; END $$; CREATE TRIGGER users_sync_name BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION users_sync_name(); COMMIT; Apply it with docker exec -i pg psql -U postgres -v ON_ERROR_STOP=1 < 001_expand.sql. If the lock wait times out, psql exits with status 3, the transaction rolls back, and you run it again: two seconds lost, instead of every query on the table stuck behind your ALTER. Three details make this safe. Adding a column with no default gives existing rows NULL, and the ALTER TABLE page says "in neither case is a rewrite of the table required," so the exclusive lock is held only briefly. The trigger is a BEFORE row trigger, so it fills name before the NOT NULL check runs, which is what lets new_app.sql insert without naming the old column. And the UPDATE branch copies in one direction only, from whichever column changed, so a writer on either version never has its value overwritten by the other column. Run both app files now. Both succeed, and the last two rows have name and full_name filled. Old and new code can live together, which is the whole point of the expand step. Step 2: backfill in batches that give up fast The 200,000 seed rows still have full_name empty. Don't fix that with one UPDATE users SET full_name = name. On a big table that is one long transaction holding row locks on every row, and strong_migrations names the three keys to doing it safely: "batching, throttling, and running it outside a transaction." One batch, keyed on the primary key so each batch starts where the last one stopped: -- backfill.sql: one batch of 1,000 rows SET lock_timeout = '2s'; SET statement_timeout = '30s'; WITH batch AS ( SELECT id FROM users WHERE id > :last ORDER BY id LIMIT 1000 ), upd AS ( UPDATE users u SET full_name = u.name FROM batch b WHERE u.id = b.id AND u.full_name IS NULL ) SELECT coalesce(max(id), 0) FROM batch; The upd step has no RETURNING and nothing reads it, and it still runs: Postgres executes data-modifying statements in WITH "exactly once, and always to completion." The query prints the last id it handled, or 0 when nothing is left. A short loop drives it. psql runs in autocommit mode, so each batch is its own transaction: #!/usr/bin/env bash # backfill.sh: run after deploy 1, before deploy 2 last=0 while :; do next=$(docker exec -i pg psql -U postgres -qtA \ -v ON_ERROR_STOP=1 -v last="$last" < backfill.sql) \ || { echo "batch failed, retrying"; sleep 1; continue; } [ "$next" -eq 0 ] && break echo "backfilled through id $next" last=$next sleep 0.2 done Size the batch so each one finishes far below the 30-second statement_timeout. When a batch hits a timeout, it rolls back on its own and the loop retries the same range. Then check that nothing drifted: SELECT count(*) FROM users WHERE full_name IS DISTINCT FROM name; That count has to be zero before you go on. !An accordion that widens in the middle and narrows again at the end Expand, migrate, contract: the schema gets wider before it gets narrower. Steps 3 and 4: switch, then contract Step 3 is a normal code deploy (deploy 2): the image that runs new_app.sql, reading and writing only full_name. During that rollout, old Pods still write name and the trigger copies it across. New Pods write full_name and the trigger copies it back, so an old Pod reading name still sees rows the new Pods wrote. Until step 4, rolling back to the old image loses nothing. If the column should be NOT NULL, Postgres 18 lets you add that without a long lock. The ALTER TABLE docs allow NOT VALID on not-null constraints, and validation runs as a separate statement: SET lock_timeout = '2s'; SET statement_timeout = '15min'; ALTER TABLE users ADD CONSTRAINT users_full_name_nn NOT NULL full_name NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT users_full_name_nn; The statement_timeout is longer than the 10 seconds used elsewhere because VALIDATE scans the whole table. It only takes a SHARE UPDATE EXCLUSIVE lock while it does, so reads and writes keep going. Step 4 waits until no released code reads name and you are past the point where you would roll back. Then it removes the old column in its own deploy: -- 002_contract.sql (deploy 3, nothing reads name) SET lock_timeout = '2s'; SET statement_timeout = '10s'; BEGIN; DROP TRIGGER users_sync_name ON users; DROP FUNCTION users_sync_name(); ALTER TABLE users DROP COLUMN name; COMMIT; DROP COLUMN is quick too. Per the same page, Postgres "does not physically remove the column"; it makes the column invisible to SQL, and the space comes back on the next table rewrite. One catch: some ORMs cache the column list at boot. strong_migrations notes that Active Record "caches database columns at runtime," so on Rails you add the column to ignored_columns and deploy that before you run the drop. The rule to give your agent The agent wrote the one-liner because nothing told it about the second version of the code. Tell it. Claude Code loads path-scoped rules from .claude/rules/ when it works on matching files, so this file only shows up when the agent touches a migration: --- paths: - "migrations/**/*.sql" --- # Postgres migration rules - Never RENAME COLUMN or RENAME TABLE on a table the running app reads. Propose expand-contract instead. - Expand migration: ADD COLUMN (nullable, no volatile default) plus a BEFORE INSERT OR UPDATE trigger that keeps the old and new columns in sync. - Backfill is a separate batched script keyed on the primary key, never part of the migration. - Every migration file starts with SET lock_timeout and SET statement_timeout. - DROP COLUMN goes in its own migration, in a later deploy, after no released code reads the column. - When asked for a rename, write out the 4-step plan and put only step 1 in this change. Other coding agents in the repo read AGENTS.md, so copy the bullets there too. Claude Code keeps loading .claude/rules/ either way, and the same docs page covers when it reads AGENTS.md at all. A rule file is advice. The docs say so directly: Claude "treats them as context, not enforced configuration." So back it with a check that does not care who wrote the SQL. Run squawk 'migrations/*.sql' in CI, or the squawk-action on pull requests. Its renaming-column rule catches the one-liner, and require-lock-timeout and require-statement-timeout catch a migration that forgot its timeouts. It will also flag the drops in step 4, which is fine: that is the one migration a human should read before it ships. If you want the expand-contract bookkeeping automated, pgroll does the add-column, backfill and sync-trigger work for you and serves old and new schema versions through views at the same time. Knowing the four steps by hand is still what lets you review what it, or your agent, is about to run. --- If this was useful The four steps here are what a schema change costs once a store has live traffic and more than one version of your code talking to it. I wrote the Database Playbook for the decision that comes before all of this: which store to pick for a system, knowing you will be changing its schema under real load for years. If this post made you look harder at your own migrations, the book is the wider version of that look. 
SQL-Migration-Dryrun-Guard: Why Regex-Based Static Analysis Fails to Prevent Production Database Lockups When performing zero-downtime database migrations on tables containing millions or tens of millions of rows, an unconsidered DDL statement can immediately lead to catastrophic exclusive locks (ACCESS EXCLUSIVE in PostgreSQL), grinding the entire user-facing service to a halt. To tackle this problem, we designed an experimental standalone CLI checker named SQL-Migration-Dryrun-Guard. The goal was simple: analyze raw SQL migration files against table row-count metadata in under 10 seconds before hitting production. However, after running realistic failure scenarios and edge-case evaluations, the tool was deprecated and discarded. This post serves as a technical post-mortem and an architectural anti-pattern case study, dissecting why naive string matching and external metadata models fundamentally fall short in mission-critical database safety checks. --- 1. System Architecture and Design Objectives SQL-Migration-Dryrun-Guard was architected with a strict minimal-dependency philosophy while guaranteeing strict type safety and schema validation. Technology Stack - Runtime / Language: Python 3.10+ - Schema Validation: Pydantic v2 (for robust deserialization, strict types, and validation models) - JSON Processing: orjson (high-throughput Rust-backed serializer, configured with a resilient standard library json fallback) - Detection Engine: Case-normalized keyword inspection coupled with tabular cardinality threshold comparison (targeting CREATE INDEX without CONCURRENTLY, unoptimized ADD COLUMN WITH DEFAULT, etc.) Architecture Flow flowchart TD subgraph InputStage ["Input Processing"] SQLFile["SQL Migration Script (.sql)"] MetaFile["Table Cardinality Metadata (.json)"] end subgraph GuardEngine ["SQL-Migration-Dryrun-Guard Core"] PydanticSchema["Pydantic v2 Input Parser"] StringNormalizer["Case-Normalization & Token Matching"] ThresholdEvaluator["Row Count vs Risk Matrix"] end subgraph OutputStage ["Reporting"] Report["Structured DangerPoint Report (JSON/CLI)"] end SQLFile -- "Read SQL Text" --> PydanticSchema MetaFile -- "Read Metadata" --> PydanticSchema PydanticSchema -- "Validated Input" --> StringNormalizer StringNormalizer -- "Extracted Keywords" --> ThresholdEvaluator ThresholdEvaluator -- "Trigger DangerPoint" --> Report Core Engine Implementation Below is the foundational logic of the validation engine: from typing import List from pydantic import BaseModel, Field class TableMetadata(BaseModel): table_name: str row_count: int class MigrationInput(BaseModel): sql_content: str tables: List[TableMetadata] class DangerPoint(BaseModel): statement: str risk_level: str reason: str suggested_sql: str class MigrationReport(BaseModel): dangers: List[DangerPoint] is_safe: bool class MigrationGuard: THRESHOLD_ROWS: int = 100_000 # Row count threshold where exclusive locks risk incidents def __init__(self, migration_input: MigrationInput): self.input_data = migration_input def analyze(self) -> MigrationReport: dangers: List[DangerPoint] = [] sql_upper = self.input_data.sql_content.upper() row_counts = {t.table_name.upper(): t.row_count for t in self.input_data.tables} # Validate CREATE INDEX statements for CONCURRENTLY clause if "CREATE INDEX" in sql_upper: for t_name, rows in row_counts.items(): if t_name in sql_upper and rows >= self.THRESHOLD_ROWS: if "CONCURRENTLY" not in sql_upper: dangers.append(DangerPoint( statement=self.input_data.sql_content.strip(), risk_level="HIGH", reason=( f"Creating an index on table '{t_name}' ({rows:,} rows) " f"without CONCURRENTLY will acquire an exclusive lock, blocking writes." ), suggested_sql=self.input_data.sql_content.replace( "CREATE INDEX", "CREATE INDEX CONCURRENTLY" ) )) # Additional heuristics (e.g., ADD COLUMN WITH DEFAULT) follow the same pattern... return MigrationReport( dangers=dangers, is_safe=len(dangers) == 0 ) --- 2. Why It Failed: The Three Structural Bottlenecks While the core logic appeared mathematically straightforward and Pydantic v2 eliminated basic runtime schema errors, practical integration immediately surfaced fundamental architectural limitations. ① Static Analysis Limits: Syntax Permutations and Context Blindness The utility relied on substring occurrences and naive keyword matching (CREATE INDEX, ADD COLUMN). However, real-world DDL migrations in mature codebases are rarely flat single-statement scripts: - False Positives in Comments and Procedures: Keywords within inline comments (-- DO NOT RUN: CREATE INDEX ...) or dynamic query literals inside PL/pgSQL stored routines triggered false alarms. - Complex Multi-line / Transactional DDL: Multi-statement chains spanning dozens of lines, subtransactions (SAVEPOINT), or statements encapsulated within BEGIN ... COMMIT blocks could not be parsed safely. - Absence of AST (Abstract Syntax Tree): Attempting to infer semantic intent from raw strings without an AST-driven parser is fundamentally flawed. Relying solely on regex matching alongside static row count thresholds leads to uncontrollable false positive and false negative rates. ② The Dilemma of Metadata Stagnation in CLI Workflows In an attempt to deliver a lightweight, standalone zero-network CLI, the tool required table row counts to be passed externally via a pre-generated JSON metadata file. In large-scale production environments containing thousands of partitioned tables and relations: - Maintaining up-to-date table statistics inside developer environments became a maintenance bottleneck. - Generating, sanitizing, and injecting current statistical snapshots into CI/CD pipelines created more developer friction than the migration risk itself. - High barrier to entry: the operational cost of preparing input data eclipsed the utility of the tool, leading to workflow abandonment. ③ Operational Friction: LLM-Assisted Code Pipelines and Quality Assurance Failures During final regression test cycles, foreign artifacts and formatting noise (such as Markdown delimiters and chat transcript formatting tags) inadvertently entered the codebase artifact pipelines. Because strict file-level sanitation was absent in the pipeline, the Python interpreter encountered invalid full-width characters: SyntaxError: invalid character '）' (U+FF09) This crashed execution before the runtime engine could inspect a single line of SQL. Although the algorithmic hypothesis was structurally sound, it exposed how vulnerable loosely audited generative pipelines are without automated syntactic verification gates. --- 3. Engineering Post-Mortem and Anti-Pattern Takeaways From this decommissioned initiative, we established three non-negotiable architectural lessons for database reliability tooling: 1. Never Gate SQL Safety on String Matching Heuristics Heuristic regex matching on SQL strings is an anti-pattern. If you are building a custom linter, build upon an established AST parser such as sqlglot, pglast, or libpg_query. AST inspection allows you to isolate token types (distinguishing comments and string literals from actual DDL commands) and inspect syntactic trees deterministically. 2. Do Not Offload Production State to Static Files Decoupling metadata from the data source to simplify tooling usually inverts the operational burden onto developers. A robust verification layer must either: - Safely query statistics directly from read replicas or non-blocking system catalogs (e.g., querying pg_stat_user_tables or pg_class.reltuples). - Execute real dry-runs against an ephemeral copy of the database using transactional rollbacks (BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;) or database clone technologies. 3. Static Code Does Not Equal Runtime Environment Realities Whether an index creation or schema alteration blocks concurrent operations depends on dynamic environmental variables: - The target database engine and patch level (e.g., PostgreSQL 11+ handles non-volatile ADD COLUMN ... DEFAULT without table rewrites, whereas earlier versions rewrite the entire table). - Active connection pools, lock queues, and long-running analytical queries. Static CLI linters operating without engine version awareness cannot reliably predict real-world lock contention. --- 4. Conclusion & Recommended Alternatives Rather than re-inventing SQL regex linters, engineering teams should leverage mature, industry-tested alternatives: - Migration Framework Hooks: Utilize pre-commit validations within native migration tools (such as Alembic, Flyway, or dbmate). - Ast-Driven Linters: Adopt dedicated linters like Squawk or strong_migrations designed specifically to flag dangerous DDL operations in PostgreSQL. - Ephemeral DB Branching: Spin up temporary database branch clones in CI (via Docker or storage-level copy-on-write tools) to execute actual migrations against production-like schemas before deployment. Treating schema safety as a compilation and runtime verification problem—rather than a static string search—is the only sustainable approach to zero-downtime engineering. --- If this engineering log saved your production server (and your sanity), consider supporting our architecture on GitHub Sponsors. 