The Safe Way to Add a NOT NULL Column to a Large Postgres Table

#postgres #sql #database #webdev

If you're adding a brand-new column and can give it a constant default, this is already fast and safe in modern Postgres:

ALTER TABLE users ADD COLUMN plan text NOT NULL DEFAULT 'free';

Since Postgres 11, this is a single statement — no table rewrite, and no scan to verify existing rows either. Postgres knows every existing row will read back as the default value, and a non-null default can't violate NOT NULL, so there's nothing to check. This works at any table size.

The actual risk: NOT NULL on a column that already has data

The dangerous case is different: a column that already exists in a populated table, where you now want to enforce NOT NULL retroactively — a field that's been optional since launch and now needs to be required, for example.

ALTER TABLE users ALTER COLUMN plan SET NOT NULL;

Run this directly and Postgres has to scan every existing row to prove none of them are null, holding an ACCESS EXCLUSIVE lock for the entire scan. On a small table that's instant. On a table with tens of millions of rows, that scan can run for minutes — and every read and write against the table queues up behind it.

The pattern: separate "add the constraint" from "prove it holds"

1. Backfill any remaining NULLs, in batches:

UPDATE users SET plan = 'free'
WHERE plan IS NULL
  AND id BETWEEN 1 AND 50000;
-- repeat for the next id range

A single unbatched UPDATE over millions of rows is its own problem — one long transaction holding row locks and generating a pile of WAL/vacuum work all at once.

2. Add the constraint as NOT VALID first:

ALTER TABLE users
  ADD CONSTRAINT users_plan_not_null CHECK (plan IS NOT NULL) NOT VALID;

With NOT VALID, Postgres adds the constraint without scanning existing rows — it's the usual ACCESS EXCLUSIVE lock, but only briefly, since there's no data to check.

3. Validate it separately:

ALTER TABLE users VALIDATE CONSTRAINT users_plan_not_null;

This is the step that actually scans the table — but per Postgres's own documentation it takes only a SHARE UPDATE EXCLUSIVE lock, which doesn't block normal reads or writes. Traffic keeps flowing.

4. Set NOT NULL for real:

ALTER TABLE users ALTER COLUMN plan SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_plan_not_null;

Since Postgres 12, if a validated CHECK constraint already guarantees the column can't be null, this step is metadata-only — Postgres trusts the existing constraint instead of re-scanning.

The same distinction shows up with DROP COLUMN

The database-level operation is nearly free in Postgres — metadata-only, the column is marked dropped but not physically removed, and disk space isn't reclaimed by routine autovacuum on its own, only by later row rewrites or a forced VACUUM FULL/CLUSTER. MySQL/InnoDB used to require a full table rebuild for DROP COLUMN, same as any other lock-holding ALTER — but MySQL 8.0.29 extended instant DDL to cover it too, under similar conditions to instant ADD COLUMN.

The real risk with DROP COLUMN usually isn't the database — it's the application layer: old instances mid-rollout, SELECT * queries, ORM models that still list the field, and queued background jobs can all still be reaching for that column the instant it disappears. The fix there is sequencing — remove every application reference first, confirm via logs/APM that nothing's touching it, and only then run the drop.

Correction

I got two things wrong in the original version of this post and I'd rather flag it than quietly edit it away: I'd claimed ADD COLUMN ... NOT NULL DEFAULT in one statement still needed a table scan under lock — it doesn't, since Postgres 11's fast-default optimization covers the NOT NULL check too, not just the table rewrite. I also overstated MySQL's DROP COLUMN cost as still requiring a rebuild — 8.0.29 fixed that. Thanks to a rigorous fact-check pass for catching both. The reasoning above reflects the correction.

Why I'm writing this

I got tired of re-deriving this pattern from memory (or from old Slack threads) every time a migration like this came up, so I built Migration Checker — paste an ALTER TABLE statement and it flags dangerous patterns like NOT NULL without a default, DROP COLUMN, DROP PRIMARY KEY, and a handful of others, before you run anything for real. It correctly tells apart ADD COLUMN ... NOT NULL DEFAULT (safe) from SET NOT NULL on an existing column (dangerous). It's part of SQL Toolkit, a set of free, browser-only SQL tools (formatter, JSON/CSV/Excel → SQL, schema docs) — nothing leaves your browser, no signup.

If you want the deeper walkthroughs, I wrote them up as standalone guides:

Curious what migration foot-guns you've hit — always looking for more patterns to add.

DEV Community