DEV Community: Sayonil Mitra

The 4 kinds of ALTER TABLE that cause production incidents (and a 5-minute review for each)

Sayonil Mitra — Thu, 15 Oct 2026 12:55:45 +0000

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

Tag every statement: destructive, data-dependent, code-breaking, or lock-taking.
For destructive ones, confirm the backup and that the data is unused.
For data-dependent ones, run a check query first, e.g. SELECT COUNT(*) FROM orders WHERE region IS NULL.
For code-breaking ones, search the codebase for the old name and plan the deploy order.
Rehearse on production-sized data if the table is large.
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.

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.