A migration that runs fine on your laptop's empty database can still fail, or do real damage, in production. Production has data, traffic, and application code that depends on the old schema.

You don't need a heavyweight process to catch most of it. You need to look at each statement and ask which of four categories it falls into.

Here's a migration that looks routine:

ALTER TABLE orders DROP COLUMN legacy_ref; ALTER TABLE orders RENAME COLUMN cust_id TO customer_id; ALTER TABLE orders ADD COLUMN region TEXT NOT NULL; CREATE INDEX idx_orders_customer ON orders (customer_id);

Every one of those four lines belongs to a different category.

1. Destructive and irreversible

DROP COLUMN permanently deletes the data in that column. DROP TABLE deletes the whole table. There's no undo inside the migration; recovery means restoring from a backup.

Before running one: confirm the data is genuinely unused, and confirm a recent backup exists. Dropping a primary key belongs here too, because ORMs, replication and cascading deletes often rely on one.

2. Statements that fail on existing data

These pass on an empty table and fail in production.

  • ADD COLUMN ... NOT NULL with no default fails if the table already has rows, because existing rows would have no value for it. region TEXT NOT NULL above is exactly this case.
  • SET NOT NULL on an existing column fails if any row holds NULL. The docs say it's checked by scanning the entire table, unless a valid CHECK constraint already proves no NULL can exist, in which case the scan is skipped.
  • Adding a foreign key fails if orphaned child rows exist.
  • Changing a column type can fail, or lose precision, if existing values don't fit.

The good news on defaults: since PostgreSQL 11, adding a column with a non-volatile DEFAULT doesn't rewrite the table. The default is stored in metadata. So this is safe and fast:

ALTER TABLE orders ADD COLUMN region TEXT NOT NULL DEFAULT 'unknown';

A volatile default (the docs use clock_timestamp() as the example) still forces a full rewrite.

For SET NOT NULL or a new constraint on a big table, the two-step pattern avoids doing the long scan under the heavy lock:

ALTER TABLE orders ADD CONSTRAINT region_not_null CHECK (region IS NOT NULL) NOT VALID; -- no table scan ALTER TABLE orders VALIDATE CONSTRAINT region_not_null; -- scans, but lighter lock

NOT VALID means the ADD CONSTRAINT doesn't scan the table and commits right away. VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock, which doesn't block normal reads and writes. Once the constraint is valid, a later SET NOT NULL can skip its own scan (PostgreSQL 12 and later). The CHECK constraint has to still exist when SET NOT NULL runs, so drop it in a separate statement afterwards, not in the same command.

On PostgreSQL 18 and later you can skip that workaround. The 18.0 release notes list "Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints", so you can add the NOT NULL constraint itself as NOT VALID and validate it later. Check the exact syntax in the PostgreSQL 18 docs for your minor version before relying on it.

Everything above is available in every currently supported release. The oldest, PostgreSQL 14, reaches end of life on 12 November 2026.

3. Statements that silently break application code

These succeed in the database and fail later, in code the migration never touched.

  • Renaming a column or table. cust_id → customer_id breaks every query, ORM model, view and report still using the old name, including the previous version of your app that's still running during a rolling deploy.
  • Dropping an index. Nothing errors. Queries that relied on it just get slower, sometimes dramatically on a large table.

A safer rename: add the new column, write to both for a while, migrate the readers, and only then drop the old one.

4. Statements that are safe but take a lock

Adding a nullable column or an index won't fail or lose data. But a plain CREATE INDEX locks the table against writes while it builds. Per the docs, other transactions can still read, but inserts, updates and deletes block until the build finishes.

CREATE INDEX CONCURRENTLY avoids blocking writes. The trade-offs, also from the docs:

  • it does more total work and takes significantly longer;
  • it can't run inside a transaction block;
  • if it fails, it leaves behind an INVALID index that still adds update overhead, so you need to drop it and retry.

One more habit worth having for anything that needs a heavy lock: set a lock_timeout first.

SET lock_timeout = '5s';

By default a statement waits indefinitely for a lock. With a timeout, the migration gives up instead of waiting, and you can retry at a quieter moment.

A pre-flight routine

  1. Tag every statement: destructive, data-dependent, code-breaking, or lock-taking.
  2. For destructive ones, confirm the backup and that the data is unused.
  3. For data-dependent ones, run a check query first, e.g. SELECT COUNT(*) FROM orders WHERE region IS NULL.
  4. For code-breaking ones, search the codebase for the old name and plan the deploy order.
  5. Rehearse on production-sized data if the table is large.
  6. Write the rollback step before you run it.

Automating the first pass

I built a free Migration Checker that applies this categorisation to pasted SQL. On the example migration above it rates the DROP COLUMN and the NOT NULL column as danger and the rename as a warning. It reads the statements only: it can't see your data or your application code, and it doesn't flag a standalone CREATE INDEX, so the lock question in section 4 is still yours to check. It runs entirely in your browser, so the schema you paste isn't uploaded.

The longer write-up with more detail is here. If you're about to add a NOT NULL column on a big Postgres table specifically, this guide covers the backfill-and-validate pattern.

What's the migration mistake that bit you hardest? I'd like to add it to the checklist.