The ALTER TABLE That Can Take Your App Down: A Field Guide to Safe Postgres Migrations

A migration file with zero statements hit the front page of Hacker News this week. Not a clever query, not a new Postgres feature. An empty file, fed into a browser tool called safe-not-safe, and the comment thread filled up with engineers trading war stories about migrations that looked harmless and took production down. The post sat at 89 points within hours, which tells you how raw this nerve still is.

Here is the uncomfortable truth the tool exists to highlight: the same SQL statement can be a no-op on a 500-row staging table and an outage on a 50-million-row production table. Postgres does not warn you. The statement parses fine, runs fine, and then holds a lock that blocks every other query on the table until it finishes. If you have never been the person watching an app-wide error rate spike because of a one-line schema change, this article is the cheap way to learn the failure modes.

Full disclosure: I have not used safe-not-safe itself. I have been on the receiving end of a bad migration, and I have written the linter rules below into CI pipelines I maintain. Everything about the tool here comes from its public page. The lock mechanics and the patterns are standard Postgres behavior, and I will point you at primary sources for each one.

Why an ALTER TABLE Can Freeze Your Whole App

Postgres uses table-level locks, and the one that matters for migrations is the ACCESS EXCLUSIVE lock. When a DDL statement like ALTER TABLE runs, it takes that lock, and it is the strongest lock Postgres has: no other session can read from or write to the table until it is released. Not "writes are slow". Blocked. Every SELECT queues up behind it.

Two details make this dangerous in ways that surprise people:

Locks queue, and queued locks block everything behind them. A fast ALTER TABLE on a busy table can still cause an outage. The statement grabs the lock for a millisecond, but while it waits for its turn, it sits in the lock queue. Every new query that arrives queues behind it, including plain reads. Your app does not see a slow migration. It sees a table that stopped responding entirely. This is why the standard advice is to set lock_timeout before any DDL: the statement gives up and fails fast instead of poisoning the queue. Something like:

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN gift_note TEXT;

If the lock is not available within five seconds, the migration errors, you retry later, and your traffic never queues behind it.

Whether the lock is instant or an outage depends on the rewrite rule. Some ALTER TABLE variants are metadata-only: Postgres updates the catalog and returns, no matter how big the table is. Others trigger a full table rewrite, where Postgres copies the entire table under that exclusive lock. Adding a nullable column with no default is metadata-only. Adding a column with a volatile default function like now() is a rewrite on every supported version. The SQL looks nearly identical. The runtime behavior is an order of magnitude apart.

Five Patterns That Break Production

These are the recurring offenders from Postgres docs, migration linter rule lists, and a decade of postmortems. Each one has a safe rewrite.

1. CREATE INDEX without CONCURRENTLY. A normal CREATE INDEX blocks all writes on the table for the entire build, which on a large table is minutes to hours. The fix is well known and still missed constantly:

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

Two gotchas: CONCURRENTLY cannot run inside a transaction block, so your migration tool must not wrap it, and if the build fails it leaves an invalid index behind that you need to drop before retrying. (Postgres docs on CREATE INDEX)

2. ADD COLUMN with a volatile DEFAULT. ADD COLUMN created_at TIMESTAMPTZ DEFAULT now() reads like a trivial change. Because now() returns a different value per row, Postgres must rewrite the whole table to materialize it, under ACCESS EXCLUSIVE. On Postgres 11 and later, a constant default (a fixed string, a number, false) is metadata-only and fast. The volatile case never is. Safe path: add the column with no default, backfill in batches by primary key, then set the default.

3. SET NOT NULL as a one-liner. ALTER TABLE users ALTER COLUMN email SET NOT NULL scans the entire table to verify no nulls exist, holding the exclusive lock for the scan. The standard workaround has two steps. First add a CHECK constraint that is not validated, which is instant:

ALTER TABLE users
  ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

Then validate it in a second statement, which only takes a weaker lock, and only after that declare the column NOT NULL. On Postgres 12 and later, once the CHECK is validated, the SET NOT NULL step skips the table scan entirely. (Postgres docs on ALTER TABLE)

4. Adding a foreign key or UNIQUE constraint the naive way. ADD CONSTRAINT ... FOREIGN KEY and ADD CONSTRAINT ... UNIQUE both validate existing data while holding ACCESS EXCLUSIVE. For foreign keys the fix is the same NOT VALID / VALIDATE two-step. For UNIQUE constraints, build the unique index with CREATE UNIQUE INDEX CONCURRENTLY first, then attach it with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX, which is instant because the index already exists.

5. Statement timeouts left at zero. The default statement_timeout for a migration session is unlimited, so a rewrite that would take 40 minutes will calmly take 40 minutes while your application times out at the connection pool. Set lock_timeout and statement_timeout at the top of every migration file. The numbers depend on your traffic, but "fail in 5 seconds" beats "succeed in 40 minutes while everything burns".

What safe-not-safe Actually Does

The tool that sparked this thread is a single-purpose web app at safenotsafe.dev. You paste a migration into the browser, it parses the SQL with libpg_query compiled to WebAssembly, and it flags statements that take strong locks or trigger rewrites. Three details are worth noting:

• Nothing leaves the tab. The parsing runs in a web worker client-side, so you can paste a migration from a private codebase without it touching a server.
• It is a checker, not a fixer. It tells you a statement is risky; the safe rewrite is still your job.
• There is a CLI shape. The page shows an npx safe-not-safe check migration.sql invocation, which is the form you would actually wire into CI.

It is also brand new and lightly proven. Treat it as a convenience layer. The durable protection is the rule set, and that is portable.

The Rules You Can Steal Today

However you apply them, these six rules catch the overwhelming majority of migration outages:

• CREATE INDEX must be CONCURRENTLY on any table above trivial size, and must never sit inside a transaction block.
• No volatile DEFAULT on ADD COLUMN. Constant defaults are fine on Postgres 11+. Volatile functions mean a table rewrite.
• Never SET NOT NULL directly on a large table. Use the NOT VALID CHECK constraint, then VALIDATE, then SET NOT NULL.
• Foreign keys and UNIQUE constraints go in as NOT VALID first, validated in a separate migration.
• Every migration file starts with a lock_timeout and a statement_timeout.
• DROP COLUMN waits two deploys. Ship the code that stops reading and writing the column first, drop it in a later release. Dropping a column your running code still selects is an instant application error, not a lock problem.

Wiring It Into CI

The linter landscape is more mature than the new tool: squawk is a Rust linter with roughly 40 rules, around 1,150 GitHub stars, and about 1.4 million npm downloads a month, and newer entries like MigrationPilot advertise 112 rules with lock classification. Pick one and enforce it. A minimal GitHub Actions gate with squawk looks like:

name: migration-safety
on:
  pull_request:
    paths:
      - "migrations/**"
jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: sbdchd/squawk-action@v1
        with:
          pattern: "migrations/*.sql"

One honest caveat from practice: linters produce false positives, usually around statements touching tables that were just created in the same migration. Do not respond by blanket-disabling rules. Allow targeted exceptions with a comment explaining why, or split the migration so the new-table case is explicit. A linter your team routes around is worth less than nothing, because it teaches people that the red X is noise.

The Part Tooling Cannot Cover

Here is what I would tell a team adopting this whole practice, based on watching these failures happen. The expensive mistakes are rarely exotic SQL. They are a developer who tested a migration against a 2,000-row copy of the table, shipped it to a 50-million-row table, and nobody asked about size because the statement ran in 40 milliseconds locally. A linter catches known syntax patterns. It cannot know your table has 50 million rows unless you tell it.

So before any DDL on a core table, three questions in the PR description:

• How many rows does this table have in production? (Not staging. Production.)
• What lock does each statement take, and how long can it plausibly hold it at that size?
• What is the rollout order between this migration and the application code that depends on it?

If the answer to the first one is "I do not know", that is the finding. Go find out before merging, not after.

The takeaway checklist: concurrent indexes, no volatile defaults, NOT VALID before VALIDATE, lock timeouts on every file, two-deploy column drops, and a row-count question on every schema PR. Print it, paste it into your CONTRIBUTING.md, and make the linter the enforcer rather than memory.

I write about databases, backend engineering, and AI infrastructure every week. Subscribe, it is free.

Have you been burned by a migration that looked safe in staging and broke production? What does your team's review checklist look like? I read every comment.