Rename a Column with Zero Downtime

Introduce the new column, dual-write and backfill, switch reads, then drop the old column in a separate deploy so no release breaks the running app.

~45 mindatabasesmigrationszero-downtime

Task

Perform an expand-contract column rename so old and new code versions work during the rollout.

When to use this

A direct rename breaks the old app version that is still running during a rolling deploy, causing errors between deploys.

Prerequisites

A migration tool that supports expand-contract
Write access to backfill data

Steps

1. Add the new column as nullable in one deploy.

Expected output: Both old and new code can run.

2. Dual-write to both columns and backfill existing rows in batches.

Expected output: The new column has complete, current data.

3. Switch reads to the new column and stop writing the old one.

Expected output: New code no longer depends on the old column.

4. Drop the old column in a later deploy after all instances run new code.

psql -c "UPDATE users SET full_name = name WHERE full_name IS NULL;"

Expected output: The rename completes without downtime.

Troubleshooting

Problem

Backfill locks the table

Solution

Backfill in small batches with a delay, or update via a job that does not hold long transactions.

Problem

Old code still writes the old column after switch

Solution

Only drop the old column after confirming every running instance uses new code.

Limitations

Expand-contract takes multiple deploys and temporary storage, which can be a burden for tiny schemas.
It requires discipline to ensure the old column is not dropped while any old version still runs.

Context

Perform an expand-contract column rename so old and new code versions work during the rollout. A direct rename breaks the old app version that is still running during a rolling deploy, causing errors between deploys.

Steps

Add the new column as nullable in one deploy. Expected: Both old and new code can run.
Dual-write to both columns and backfill existing rows in batches. Expected: The new column has complete, current data.
Switch reads to the new column and stop writing the old one. Expected: New code no longer depends on the old column.
Drop the old column in a later deploy after all instances run new code. Expected: The rename completes without downtime.

-- deploy 1: add + dual write
ALTER TABLE users ADD COLUMN full_name text;
-- deploy 2: backfill (batched), switch reads
-- deploy 3: drop old
ALTER TABLE users DROP COLUMN name;

Verify

Both old and new code can run.
The new column has complete, current data.
New code no longer depends on the old column.
The rename completes without downtime.

Limits

Expand-contract takes multiple deploys and temporary storage, which can be a burden for tiny schemas.
It requires discipline to ensure the old column is not dropped while any old version still runs.