Expand, Migrate, Contract: Safely Adding a Non-Nullable Database Column

Adding a column sounds like a small change. Making it required turns that change into a coordination problem: existing records need values, new requests must supply them, and old application versions may still be running.

The useful question is not just whether the final schema is correct. It is whether every intermediate combination of application and database can keep working.

Expand–Migrate–Contract gives that transition a structure. Expand the schema without requiring immediate adoption. Migrate the application and data. Contract the allowed behaviour once every active participant can meet it. Prisma documents the general approach in its expand-and-contract migration guide.

The example: a required display name

Suppose users already has an id primary key and a name column. We want to add display_name, eventually required, so the product can distinguish an account name from the name shown in the interface.

For this example, the product decision is explicit: initially derive the display name from the trimmed existing name, using Member when the old value is null or blank. Display names are not unique identifiers. A real application should choose its own appropriate fallback.

This is a PostgreSQL example for an ordinary, non-partitioned table. The SQL is a sequence of separate rollout steps, not one migration to execute in a single long transaction.

Expand: introduce a column old code can ignore

Start with a nullable column:

ALTER TABLE users ADD COLUMN display_name text;

Do not immediately deploy application code that assumes every row has a value. The new code should tolerate the transition: read display_name when present, otherwise use the agreed derivation from name.

Deploy a compatibility release that populates display_name on inserts and keeps it populated on updates. If a name edit is supposed to change both values during this transition, update both in the same transaction. Apply the same derivation rule in the application and the backfill.

The old name field stays available. Old readers can still use it. Old writers can temporarily leave the new field null, which is why enforcement comes later.

Adding a nullable column still requires a schema lock. PostgreSQL's locking documentation explains how conflicting locks block one another. I would give short schema operations a bounded lock wait and investigate blockers rather than let a migration wait indefinitely behind a long transaction.

Migrate: update writers before repairing history

Inventory every writer: API instances, workers, scheduled jobs, imports, scripts, and external integrations. A web deployment being complete does not prove a background process has adopted the new write contract.

Once the compatibility release is everywhere, backfill the historical records. For a large table, I would use small committed batches rather than a single update covering the entire table.

Here is one batch:

WITH batch AS (
  SELECT id
  FROM users
  WHERE display_name IS NULL
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE users AS u
SET display_name = COALESCE(NULLIF(BTRIM(u.name), ''), 'Member')
FROM batch
WHERE u.id = batch.id
  AND u.display_name IS NULL
RETURNING u.id;

Commit each batch before starting the next. The null predicate makes interrupted work resumable without overwriting a display name that has already been supplied. The batch size is a starting point to tune, not a performance guarantee.

SKIP LOCKED lets this batch bypass rows another transaction has locked. An empty batch therefore does not prove completion: locked rows may remain. PostgreSQL describes that behaviour in the SELECT documentation. Coordinate the workers and check the remaining data separately:

SELECT COUNT(*) AS remaining
FROM users
WHERE display_name IS NULL;

Watch database load, request latency, and replication lag where relevant. If repeatedly locating the next null row becomes expensive, revisit the job's access pattern instead of simply increasing concurrency.

Contract: enforce what the application now guarantees

Before this phase, all writers must obey the new contract and the historical backfill must be complete. Keep the tolerant read behaviour available while checking those gates.

One PostgreSQL approach separates validation from the final column declaration:

ALTER TABLE users
  ADD CONSTRAINT users_display_name_present
  CHECK (display_name IS NOT NULL) NOT VALID;
 
ALTER TABLE users
  VALIDATE CONSTRAINT users_display_name_present;
 
ALTER TABLE users
  ALTER COLUMN display_name SET NOT NULL;
 
ALTER TABLE users
  DROP CONSTRAINT users_display_name_present;

NOT VALID skips the initial check of existing rows; the constraint still applies to new inserts and updates. Validation checks the historical rows. A validated check proving the column contains no nulls lets PostgreSQL skip the usual table scan when setting NOT NULL. Keep that check in place during SET NOT NULL, then remove it separately. See the ALTER TABLE reference for the exact version-specific behaviour and lock requirements.

These operations still acquire locks. Separating a scan from a stronger schema lock reduces one source of disruption; it does not guarantee zero downtime.

Also, NOT NULL does not prohibit an empty string. If the product needs a nonblank value, that is a separate validation rule. PostgreSQL's constraint documentation distinguishes the guarantees provided by different constraints.

Keep rollback compatible with the schema

During expansion and backfill, rolling back to old code may reintroduce nulls. That can be acceptable only while the schema still permits them, with a plan to repair those rows before contracting.

After enforcement, the original writer is no longer a safe rollback target. Retain a compatibility release that satisfies the required column, or deliberately relax the constraint before restoring older code. Do not assume reverting a container image also reverts a database.

Finally, remove the read fallback in a later release when the contract is established. Because this example adds a column, there is no need to drop name. Removing it would be another compatibility transition, including any readers and integrations that still depend on it.

The pattern works because each release has a manageable promise: the schema accepts the next application, the application supplies the next invariant, and the invariant becomes mandatory only when the system is ready.

For how this fits into rolling or blue/green releases, see Deployment Strategies: Choosing How a Release Reaches Production.