agent removed a column in the expand phase before all readers stopped reading it - how to do expand/contract properly

TL;DR

Expand/contract has four phases and the drop belongs in the last one: dropping early breaks readers that have not migrated yet. Re-add the dropped column, backfill it from the new column so both hold the same data, finish migrating every reader to the new column, verify zero old-column traffic, and only then drop it. The phase order is the entire safety of the pattern.

The query

agent removed a column in the expand phase before all readers stopped reading it - how to do expand/contract properly

Use this when

• A column was dropped before all readers stopped reading it
• Need the expand/contract pattern explained as a recovery
• Readers broke after an early column drop
• An agent dropped a column in the expand phase

Not for

• Dropping a column no reader uses (just drop it)
• Adding a column (the expand side; different steps)
• A drop that already completed with no breakage (nothing to recover)

Steps

1. Re-add the dropped column

ALTER TABLE [table] ADD COLUMN [old_name] [type];

Expected output: the old name resolves again and broken readers recover. The column comes back empty: that is what the next step fixes.

2. Backfill it from the new column

UPDATE [table] SET [old_name] = [new_name] WHERE [old_name] IS DISTINCT FROM [new_name];

Batch on large tables. Expected output: both columns hold identical data. Verify with a mismatch count of zero.

3. Keep both columns in sync while readers migrate

Writers must now maintain both columns (application dual-write, or a trigger as a backstop). Migrate readers one by one to the new column, tracking each in the inventory.

Expected output: every reader reads the new column; the old column is maintained but unread.

4. Verify zero old-column traffic

Monitor query logs for references to the old column over a full deploy cycle plus a margin. Expected output: zero reads and zero writes to the old column. "Probably nobody reads it" is not the bar; measured zero is.

5. Contract: drop the old column

ALTER TABLE [table] DROP COLUMN [old_name];

Expected output: the drop completes and nothing breaks, because step 4 proved nothing used it. This is the contract phase, and it comes last by design.

6. Write the phase order into the agent's procedure

Expand (add new), migrate (dual-write, move readers), contract (drop old). The agent's schema-change plan must label which phase each step belongs to, and contract steps require the zero-traffic evidence from step 4.

Expected output: future changes arrive as labeled phases, and drops never precede their evidence.

Tool compatibility

postgres, mysql, cockroachdb. Expand/contract is a process pattern, database-agnostic.

Variant phrasings

expand contract column removal done wrong

Steps 1 through 5: re-add, backfill, sync, verify zero traffic, then drop.

dropped column readers still need it

Steps 1 and 2: re-add the column and backfill from the new one immediately.

expand contract pattern phases

Step 6: expand, migrate, contract, in that order, with evidence gating the contract phase.

Why it happens

Expand/contract exists because readers migrate slower than DDL runs. The expand phase adds the new shape while the old shape keeps working; the migrate phase moves readers; the contract phase removes the old shape only when it is provably unused. Dropping during expand skips the only phase that protects readers, converting a safe pattern into a flag day.

Edge cases

• Backfilling the re-added column from the new column assumes the new column's data is complete: if writes during the outage went only to the new column, that is true; verify rather than assume.
• Triggers as a sync backstop can surprise ORMs and bulk loaders: prefer application dual-write, triggers only when the writers cannot be changed.
• The zero-traffic bar needs a full cycle: weekly cron readers are the classic miss. Check a window longer than your longest reader interval.

Provenance

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

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

Thread query: agent removed a column in the expand phase before all readers stopped reading it - how to do expand/contract properly

TL;DR: Expand/contract has four phases and the drop belongs in the last one: dropping early breaks readers that have not migrated yet. Re-add the dropped column, backfill it from the new column so both hold the same data, finish migrating every reader to the new column, verify zero old-column traffic, and only then drop it. The phase order is the entire safety of the pattern.