PostgreSQL in Practice #1 Schema Migrations: Changing Tables Without Stopping the Service

Infrastructure PostgreSQL Database
4 min read

If the basics series built a developer who understands databases, the practice series aims at a developer who can handle a database in production. The first topic is the most frequent operational task: schema changes. In a development environment, any ALTER TABLE finishes instantly; on a production table taking hundreds of queries per second, the same statement can stop the service. Knowing that difference is the entrance to this series.

Migrations are code #

Let’s set the premise first. Schema changes are not typed by hand into psql — they are version-controlled migration files (Flyway, dbmate, Django, Rails, Alembic, whatever fits). When every environment (local, staging, production) walks the same changes in the same order, “it worked on staging” disappears. And here PostgreSQL hands you a major advantage: DDL is transactional. Wrap one migration (create table + index + grants) in BEGIN/COMMIT and a mid-flight failure leaves no half-applied state behind. Plenty of databases can’t do this; it’s the feature people arriving from them envy.

Dangerous changes vs safe changes #

An ALTER TABLE’s danger level is set by two questions: how strong a lock is and how long it is held. Most ALTER TABLE forms take an exclusive lock on the whole table (ACCESS EXCLUSIVE). Taking the lock is unavoidable — what matters is whether the work done while holding it finishes instantly.

ChangeDangerWhy
Adding a column (even with DEFAULT)SafeMetadata-only, instant (since PostgreSQL 11, DEFAULT needs no rewrite)
CREATE INDEXDangerousBlocks writes for the whole build
Adding NOT NULLDangerousHolds the lock while validating every row
Column type change (rewrite required)Very dangerousRewrites the entire table

There’s one more trap on top: waiting for the lock is itself an outage. If an ALTER TABLE queues behind one long SELECT, then every query after it — including plain SELECTs — queues behind the ALTER. So the standard practice is to cap the migration session with something like SET lock_timeout = '3s': if the lock can’t be acquired, the migration fails instead of the service stalling. (The mechanics of locking get their own chapter in practice #6.)

Reshaping changes into safe forms #

Indexes are built with CONCURRENTLY.

Create an index concurrently 
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

It builds the index without blocking writes. Two rules come with it: it can’t run inside a transaction (your migration tool needs its “run without transaction” option), and a failure leaves behind an INVALID index — check for it, DROP it, retry.

NOT NULL is split into stages. Running ALTER TABLE ... SET NOT NULL directly on a production table holds the lock while every row is validated. The safe sequence:

Add NOT NULL in three steps 
-- 1) add the constraint without validating (instant)
ALTER TABLE orders ADD CONSTRAINT orders_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;

-- 2) validate existing rows (runs under a weaker lock; takes time, service keeps running)
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null;

-- 3) SET NOT NULL now finishes instantly, using the validated CHECK as proof
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_email_not_null;

The core idea: push the long-running work (validation) into the weak-lock phase, and leave only instant work in the strong-lock phase. Adding foreign keys follows the same shape (ADD CONSTRAINT ... NOT VALID → VALIDATE).

Expand-contract: changes you can roll back #

Changes like column renames or type changes — where doing it in one shot would force the app and the database to change simultaneously — are solved with the expand-contract pattern. ① Add the new column (expand), ② have the app write to both while a backfill runs (migrate), ③ once the app reads only the new column, drop the old one (contract). The pattern’s value is that every stage can be deployed and rolled back independently. Run the backfill UPDATE not as one shot but in batches of a few thousand rows (a single giant UPDATE is, per chapter 6’s MVCC, also a new version of the entire table — a dead-tuple bomb whose cleanup is practice #5’s topic).

Summary #

  • Migrations are version-controlled code. PostgreSQL’s transactional DDL means no half-applied states.
  • Judge danger by “how strong a lock, how long.” Column adds are safe; indexes, NOT NULL, and type changes need care.
  • Set lock_timeout on migration sessions. The real shape of the outage is the queue behind a waiting lock.
  • Indexes get CONCURRENTLY; NOT NULL and FKs get the two-stage NOT VALID → VALIDATE.
  • Renames and type changes use expand-contract, with backfills in batches. Next chapter: connection pooling.
X