DEV Community

Gaurang
Gaurang

Posted on Originally published at discoveringcode.in

How a One-Line Column Rename Takes Down Production (and the Expand/Contract Fix)

#postgres #devops #database #backend

Deploying new app code is the easy half. A rolling deploy swaps instances a few at a time, and for a few minutes old and new versions serve traffic side by side. That's fine; they're stateless.

The hard half starts when the deploy also changes the database schema. For those same minutes, both versions have to run correct queries against the same database.

The one-liner that breaks things 

ALTER TABLE users RENAME COLUMN email TO email_address;
Enter fullscreen mode Exit fullscreen mode

It looks trivial. But mid-deploy:

  • Old instances still run SELECT email FROM users.
  • New instances already use email_address.

The moment the migration runs, every request hitting an old instance fails with column "email" does not exist. That's a real outage, even though the code on each side is correct on its own.

The fix: expand/contract

Don't require the schema to be right at one single instant. Split one risky change into several safe ones, each compatible with both live versions.

Stage 1: Expand. Add the new column, nullable:

ALTER TABLE users ADD COLUMN email_address TEXT; -- no default, no table rewrite
Enter fullscreen mode Exit fullscreen mode

Old code doesn't know it exists, and nothing breaks.

Stage 2: Dual-write. Deploy code that writes both columns but still reads the old one:

await pool.query(
  `INSERT INTO users (name, email, email_address)
   VALUES ($1, $2, $2) RETURNING id, name, email`,
  [name, email],
);
Enter fullscreen mode Exit fullscreen mode

Stage 3: Backfill. Copy old rows in batches, not one giant UPDATE:

UPDATE users SET email_address = email
WHERE email_address IS NULL AND id BETWEEN 1 AND 10000;
-- repeat per batch until none remain
Enter fullscreen mode Exit fullscreen mode

Stage 4: Switch reads. Once every row has email_address, deploy code that reads (and writes) only the new column.

Stage 5: Contract. Once every instance is on that version:

ALTER TABLE users DROP COLUMN email;
Enter fullscreen mode Exit fullscreen mode

That's five steps instead of one RENAME, and at no point is live code running a query the schema can't answer.

The details that matter

  • The one rule: never require the schema and the live code to change at the same instant.
  • Batch your backfills. One huge UPDATE holds locks and floods the write-ahead log for its whole duration. Small batches take longer overall but don't compete with real traffic.
  • Dual-write is your rollback insurance. If stage 4 goes wrong, rolling back to the stage-2 or stage-3 version still works, because it never stopped writing the old column.
  • Not every change needs all five stages. A purely additive optional column is just stage 1. Save the full pattern for renames, type changes, column splits, or anything live code still depends on.

This is the closing topic of the Infra & Delivery pillar on discoveringCode, a free, ad-free notebook that goes from SSH-ing into a box by hand to containerized CI/CD. Related: deployment strategies: blue-green, canary, rolling.

Top comments (0)