agent added a NOT NULL column without a default in a single step and the zero-downtime deploy broke

TL;DR

Old application instances running during the deploy write rows without the new column, and the database rejects them because the column is NOT NULL with no default. Recover by giving the column a default (or making it nullable) immediately, then do it properly in phases: add the column nullable, backfill, deploy code that writes it, then add the NOT NULL constraint once every writer is updated.

The query

agent added a NOT NULL column without a default in a single step and the zero-downtime deploy broke

Use this when

• Zero-downtime deploy broke after adding a NOT NULL column
• Old instances fail writing rows missing the new column
• Need the expand/contract pattern for NOT NULL additions
• An agent added NOT NULL in a single step

Not for

• NOT NULL on an empty table (safe in one step)
• Adding NOT NULL with a default in one step on a compatible deploy (usually safe)
• Constraint violations from bad data rather than deploy ordering (a data problem)

Steps

1. Stop the bleeding

If old instances are erroring on writes, give the column a default right now:

ALTER TABLE [table] ALTER COLUMN [column] SET DEFAULT [default];

Or make it nullable temporarily. Expected output: writes from old code succeed again. This is triage, not the fix.

2. Backfill existing rows

UPDATE [table] SET [column] = [default] WHERE [column] IS NULL;

Do it in batches on large tables (keyset pagination, committing per batch). Expected output: zero nulls remain; verify with a count query.

3. Deploy code that writes the column

Ship the application version that populates the new column on every write. Wait until every running instance is on the new code: with rolling deploys, verify instance versions, do not assume.

Expected output: no writer exists that omits the column.

4. Add the NOT NULL constraint

ALTER TABLE [table] ALTER COLUMN [column] SET NOT NULL;

Expected output: the constraint applies instantly because step 2 eliminated nulls and step 3 eliminated writers that create them. Validate the constraint exists.

5. Teach the agent the phased pattern

The agent's schema-change procedure: no NOT NULL without a default in a single step on a table with live writers. The phases are add-nullable, backfill, deploy writers, constrain.

Expected output: future column additions arrive as phased plans, not single-step surprises.

Tool compatibility

postgres, mysql, and cockroachdb. The phased pattern is database-agnostic; the exact ALTER syntax varies.

Variant phrasings

add NOT NULL column without default broke deploy

Step 1 triages, steps 2-4 do it properly in phases.

zero downtime add not null column pattern

Steps 2 through 4: nullable, backfill, deploy writers, then constrain.

old code fails after NOT NULL migration

Step 1: set a default immediately so old writers survive, then work the phases.

Why it happens

A rolling deploy means two code versions write to one schema simultaneously. The new schema must accept writes from the old code until the old code is gone: that is the entire discipline of zero-downtime schema change. A bare NOT NULL with no default violates it on the first old-code write, because the database enforces the constraint before the deploy finishes.

Edge cases

• On huge tables the backfill in step 2 is the long pole: batch it, and consider doing it before the deploy window opens.
• Some databases validate NOT NULL by scanning the table while holding a lock: on postgres use a CHECK constraint as NOT VALID first, then validate, for the lock-free variant.
• If the column genuinely has no sensible default, the phased pattern still works: nullable first, backfill real values, constrain last. The default in step 1 is triage only.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Uri5PPCfuSl3Kb8Muv8j6Q