Field guide 01

Add a NOT NULL column without turning one migration into the whole rollout.

Treat the invariant as a sequence: expand the schema, make new writes compatible, repair old rows, validate the evidence, then enforce.

The risky shape

ALTER TABLE users
  ADD COLUMN email_verified boolean NOT NULL;
Release verdict: BLOCK until existing-row and application compatibility are defined.Existing rows need a valid value, while old and new application versions must remain compatible throughout deployment and rollback.

Why production is different

A schema migration is only one part of this change. The release also has to account for existing rows, every writer, background jobs, replicas, long transactions, and the period where old and new application versions run together.

PostgreSQL 11 and newer can add many constant defaults without rewriting every row, but the command still needs to acquire its table lock. Volatile defaults, older versions, or a queued lock can change the operational outcome.

A staged pattern

1. Expand

SET lock_timeout = '2s';
ALTER TABLE users
  ADD COLUMN email_verified boolean;

2. Make writes compatible

Deploy application code that writes the new column while continuing to support the old schema. Decide whether the value is derived, defaulted, or explicitly supplied.

3. Backfill outside the schema transaction

UPDATE users
SET email_verified = false
WHERE id > :last_id
  AND id <= :next_id
  AND email_verified IS NULL;

Use bounded, restartable batches. Record batch size, pause conditions, replication lag, WAL growth, row count, and completion criteria.

4. Validate before enforcement

ALTER TABLE users
  ADD CONSTRAINT users_email_verified_nn
  CHECK (email_verified IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_email_verified_nn;

ALTER TABLE users
  ALTER COLUMN email_verified SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_email_verified_nn;

Evidence gate

  • Target PostgreSQL version and actual generated SQL are recorded.
  • Affected row count, table size, write rate, and longest transaction are measured.
  • Every writer supplies a non-null value before enforcement.
  • The backfill is idempotent, bounded, observable, and safe to pause.
  • Zero NULL rows and application compatibility are verified before SET NOT NULL.

Rollback boundary

Before enforcement, rollback can usually stop new-code usage while leaving the nullable column in place. After old rows have been transformed or old application behavior removed, rollback is an application and data decision, not merely a down migration. Delay column removal until the rollback window has closed.

Primary references