Blog

Migration safety insights for Postgres teams.

Start here

The migration safety essentials

Reference Postgres lock mode cheat sheet

See what each table lock blocks and which DDL statements acquire it.

 Playbook Expand and contract migrations

Split risky one-shot schema changes into deployable, reversible stages.

 Incident guide Diagnose a stuck ALTER TABLE

Find the blocker, understand the queue, and restore traffic safely.

September 20, 2026

My Postgres migration linter analyzed nothing for four and a half months

From 0.5.0 through 0.7.0, pgfence exited 0 after analyzing nothing when launched through a symlinked path, including npx, node_modules/.bin, pnpm, global installs, and its own GitHub Action. A user found it. Here is the bug and the fail-closed fix.

September 20, 2026

Postgres ALTER COLUMN TYPE Without a Table Rewrite

ALTER COLUMN TYPE normally rewrites the table, but varchar widening, text-family changes, and numeric precision increases can be metadata-only. They still take ACCESS EXCLUSIVE, so an instant change can still queue and block every SELECT.

September 20, 2026

Postgres Migration Timeouts: What Values to Use and Where

Use lock_timeout = 2s before any dangerous DDL, keep statement_timeout longer than lock_timeout, and add idle_in_transaction_session_timeout for abandoned transactions. The value matters, but placement is what protects the first lock.

September 20, 2026

REFRESH MATERIALIZED VIEW Lock Modes: CONCURRENTLY Still Blocks Writes

REFRESH MATERIALIZED VIEW takes ACCESS EXCLUSIVE and blocks reads and writes for the full refresh. CONCURRENTLY takes EXCLUSIVE, so SELECT keeps working but writes, locking reads, DDL, and another refresh still wait.

September 20, 2026

TRUNCATE CASCADE in Postgres: Preview the Blast Radius

TRUNCATE CASCADE removes every row from the named table and all foreign-key referencing tables it reaches, while taking ACCESS EXCLUSIVE on each one. Preview the dependency graph first, or use explicit batched deletes.

September 20, 2026

Postgres REINDEX and DROP INDEX Without Blocking Writes

A standard REINDEX blocks writes for the full rebuild, while a normal DROP INDEX takes ACCESS EXCLUSIVE on the table. PostgreSQL 12+ supports REINDEX CONCURRENTLY, but both concurrent forms have transaction and recovery caveats.

September 20, 2026

Postgres SET NOT NULL Without a Long Table Scan

ALTER COLUMN ... SET NOT NULL normally scans the table while holding ACCESS EXCLUSIVE. On PostgreSQL 12 and later, a validated CHECK constraint can prove the column is ready so the final metadata change stays brief.

September 17, 2026

PostgreSQL ACCESS EXCLUSIVE Lock: What It Blocks and Why SELECT Waits

ACCESS EXCLUSIVE is the only PostgreSQL table lock that blocks a plain SELECT. Here is the conflict rule, the commands that take it, and how a short DDL statement creates a lock queue.

September 17, 2026

Prisma Migrate and CREATE INDEX CONCURRENTLY: edit the migration before deploy

Prisma generates regular CREATE INDEX statements for PostgreSQL. Use create-only, add CONCURRENTLY, verify there is no transaction wrapper, and check the SQL before prisma migrate deploy.

September 17, 2026

TypeORM CREATE INDEX CONCURRENTLY: transaction false is not enough by itself

TypeORM wraps migrations in one transaction by default, which makes PostgreSQL reject CREATE INDEX CONCURRENTLY. Here is the exact transaction mode and migration shape that works.

September 16, 2026

Postgres ALTER TABLE Hangs: Find the Blocker, Then Decide Who to Cancel

An ALTER TABLE that will not return is almost never slow, it is queued for ACCESS EXCLUSIVE behind an older transaction, and every query that arrived after it is now queued too. Here is the pg_stat_activity query to run first, how to read wait_event, and why cancelling the migration is usually the right first move.

September 16, 2026

Postgres ENABLE ROW LEVEL SECURITY Returns Zero Rows, Not an Error

Enabling row level security with no applicable policy denies every row to every non-owner role, and Postgres reports that as an empty result rather than a permission error. Here is why your migration role still sees everything, what the ACCESS EXCLUSIVE lock costs, and the order that avoids both.

September 16, 2026

ALTER TYPE ADD VALUE Inside a Transaction: The Error Changed in Postgres 12

Before Postgres 12, ALTER TYPE ... ADD VALUE is rejected with "cannot run inside a transaction block". From Postgres 12 the statement succeeds and you get "unsafe use of new value" instead, when something uses the value before the transaction commits. Two errors, two different fixes.

September 5, 2026

REFRESH MATERIALIZED VIEW CONCURRENTLY Runs Fine Inside a Transaction Block

REFRESH MATERIALIZED VIEW CONCURRENTLY does not throw "cannot run inside a transaction block", unlike CREATE INDEX CONCURRENTLY. Here is the source-verified reason why, what lock it actually takes, and why disabling your migration's transaction wrapper for it is the wrong fix.

September 5, 2026

PostgreSQL VALIDATE CONSTRAINT: What Lock Mode It Actually Takes

ALTER TABLE ... VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE, not ACCESS EXCLUSIVE. Here is exactly what that blocks, what keeps running, and the same-transaction mistake that quietly undoes it.

June 5, 2026

CREATE INDEX CONCURRENTLY IF NOT EXISTS does not fix a broken index

IF NOT EXISTS only checks whether a relation with that name exists, not whether it is valid. A failed CREATE INDEX CONCURRENTLY leaves an invalid index behind, and a retry with IF NOT EXISTS will silently skip rebuilding it.

June 2, 2026

pgfence 0.6.1: Trust Contract fixes after the audit pass

pgfence 0.6.1 closes several false-negative paths in ORM extraction, inline foreign key scoring, constrained-domain analysis, stats-source precedence, and public boundary linting.

May 31, 2026

Inline foreign keys need both table sizes

A foreign key added through ADD COLUMN can look like a column change, but the referenced table matters too. Size-aware risk scoring has to include both sides of the relationship.

May 28, 2026

ORM migration extraction has to fail closed

TypeORM, Knex, Sequelize, Prisma, and Drizzle all expose SQL differently. A migration safety tool has to keep valid statements, warn on dynamic pieces, and never let destructive SQL disappear from coverage.

May 26, 2026

Unknown SQL is a product signal, not a footnote

A migration safety tool earns trust by saying what it could not prove. Unknown statements should show up in coverage, reports, and review policy instead of disappearing behind a green check.

May 25, 2026

pgfence 0.6: explain, RULES.md, and five footguns no other linter catches

v0.6 ships a paste-and-run statement explainer, a single-file rule catalog for in-editor coding assistants, seven new rules covering REPLICA IDENTITY FULL, CLUSTER, RLS toggles and INHERIT, plus a Trust Contract polish that surfaces unanalyzable line numbers in every reporter.

May 24, 2026

Teaching in-editor coding assistants Postgres lock semantics with a single rules file

In-editor coding assistants are fluent in SQL syntax and blind to lock modes. Drop one curated file at the repo root and the assistant stops suggesting ACCESS EXCLUSIVE DDL and starts writing the expand/contract sequence. Here is what to put in the file and a concrete before/after.

May 23, 2026

Five Postgres migration footguns that no linter catches today

Squawk, Eugene, pgrubic, and strong_migrations together catch most of the obvious dangers. These five operations slip past every one of them, and each has taken down a production system this year.

May 22, 2026

Prisma now documents pgfence for pre-deploy migration checks

Prisma's deployment docs now show pgfence as a pre-deploy migration safety check before prisma migrate deploy. Here is what that check catches and why it belongs in CI.

May 22, 2026

REPLICA IDENTITY FULL is the silent CDC killer

One line of DDL, no lock contention, no rewrite, no warning from any safety guide. Three weeks later your WAL volume has doubled and Debezium is melting. Here is what REPLICA IDENTITY FULL actually costs and how to avoid it.

May 21, 2026

What lock does each DDL statement actually take? A cheat sheet verified against the PostgreSQL source

Every Postgres DDL statement takes a lock. Most cheat sheets on the internet get at least one wrong. This one is verified line by line against tablecmds.c, lockcmds.c, and indexcmds.c in PostgreSQL 17.

May 19, 2026

ADD COLUMN with a DEFAULT: sometimes instant, sometimes catastrophic

On PostgreSQL 11 and newer, the same shape of statement can finish in 8ms on a 200GB table or lock the table for 90 minutes. The difference is the volatility class of the default expression, and most production teams still believe the pre-PG11 rule.

May 18, 2026

ADD CONSTRAINT lock modes are not one-size-fits-all

Foreign keys, CHECK constraints, UNIQUE constraints, EXCLUDE constraints, USING INDEX, and VALIDATE CONSTRAINT do not all take the same lock. pgfence v0.6 fixed the map against PostgreSQL source.

April 30, 2026

pgfence stays free and open source

Quick positioning update: pgfence is, and stays, a free open-source Postgres migration safety tool. The CLI, GitHub Action, LSP, ORM extractors, lock-mode rules, and safe rewrite recipes are all free forever.

April 24, 2026

pgfence 0.5: fail-closed migration analysis

pgfence 0.5 tightens ORM extraction, coverage reporting, editor diagnostics, and release boundaries so unknown migration SQL is surfaced instead of silently treated as safe.

April 21, 2026

What a Postgres migration audit log needs to prove

A useful migration audit log is not just an activity feed. It needs to prove what changed, what risk was found, who approved it, and which policy applied at the time.

April 18, 2026

CREATE INDEX CONCURRENTLY in a transaction is a silent footgun

CREATE INDEX CONCURRENTLY is the right fix for blocking index builds, but it fails inside a transaction block. Here is why that happens and how to catch it before deploy.

March 20, 2026

The lock_timeout Death Spiral: Why Every Postgres Migration Needs a Timeout

Your migration grabs an ACCESS EXCLUSIVE lock. It queues behind a long-running query. Every new connection piles up behind it. In 30 seconds, your entire database is frozen. Here's the fix.

March 16, 2026

pgfence 0.4.1: Trust Contract Hardening, 22 New Tests

We audited every rule, extractor, and reporter in pgfence and fixed 18 silent failure paths, 6 bugs, and 11 stale comments. Here is what we found and what we fixed.

March 14, 2026

pgfence 0.4: Trace Mode, Verified Lock Analysis Against Real Postgres

pgfence can now execute migrations against a real Postgres instance and verify lock predictions against observed behavior. It spins up a disposable Docker container and traces statements one by one.

March 10, 2026

False Negatives: The Silent Killer of Migration Safety Tools

Your migration linter says everything is safe. It's wrong. Here's why false negatives are more dangerous than false positives, and what we do about it.

March 6, 2026

pgfence 0.3: VS Code Extension, LSP Server, and 5 New Rules

pgfence now runs inside your editor. Real-time diagnostics, quick fixes for supported safe rewrites, and hover info for SQL migrations. Plus new rules for char fields, serial columns, DROP DATABASE, and domain constraints.

March 5, 2026

The Expand/Contract Pattern: Five Zero-Downtime Migration Recipes

Step-by-step SQL sequences for the five most common dangerous migrations. No downtime, no blocked queries, no surprises.

February 27, 2026

PGLT + pgfence: Catch SQL Errors and Lock Dangers in One CI Pipeline

Postgres Language Server validates SQL correctness. pgfence adds lock and migration-safety analysis. Here's how to run both in CI for broader migration coverage.

February 25, 2026

TypeORM Migrations Are Dangerous (Here's How to Check)

TypeORM's migration generator doesn't understand Postgres lock modes. Here's what that means for your production database and how to catch problems before they ship.

February 23, 2026

The Postgres Lock Mode Cheat Sheet Nobody Gave You (All 8, Ranked)

All 8 PostgreSQL lock modes explained, plus the exact lock level for every ALTER TABLE, CREATE INDEX, and VALIDATE CONSTRAINT command.

February 22, 2026

How one ADD COLUMN migration took down our 12M-row table

A war story about a volatile-default footgun, why ORMs hide it from you, and the staged rollout pattern that prevents it.