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

Sayonil Mitra — Fri, 11 Sep 2026 09:23:41 +0000

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.

Simple SQL Tool

Sayonil Mitra — Sat, 30 May 2026 14:05:41 +0000

I am a full stack dev but randomly got interested in SQL. Something about combining queries felt like an interesting challenge, like piecing together a puzzle. Then I thought, why not make a tool which uses SQL somehow. At that time I did not know what tool to make or what my tool should do. Later I discovered, a converter can be handy. It is not life changing, ground breaking tool that will blow someone's mind. I am a fan of little utility tools which make small mundane tasks easy on a daily basis. So I built one as such. Is it useful for a lot of people in a large scale? Only time will tell I guess...
(I will keep adding more complex features, just to see how far this project can go)

If you have any feedback on these tools or want to check them out:
https://sqltoolkit.dev/json-to-sql
May sound very simple but looked really cool to me that json can be converted to an sql insertion query.

https://sqltoolkit.dev/csv-to-sql
This idea I got from my seeing my project managers. They have a massive CSV file and want to import that into some database (I know every major platform has easy import for CSVs, but still, wanted to build it)

Update: this little side project grew way more than I expected when I wrote this. It's now a full SQL Toolkit (https://sqltoolkit.dev) with a bunch more free tools - the original SQL formatter/beautifier, plus SQL to JSON, SQL to CSV, Excel to SQL, a schema doc generator, and a migration risk analyzer. Still no signup, still runs entirely in your browser. Would love feedback if you try it out.