Renaming a Column Safely With Expand and Contract

You need to rename events.kind to events.event_type on a table that absorbs thousands of inserts per second. In a migration review it reads like a formality — one line, ALTER TABLE events RENAME COLUMN kind TO event_type — and on an empty staging database it returns in milliseconds. Ship it to production and it detonates: the instant the rename commits, every application instance still running the previous image queries kind, a column that no longer exists, and your write path collapses into a wall of “column does not exist” errors. You cannot deploy new code to every instance atomically, so there is no single moment at which an in-place rename is safe while a rolling deploy is in flight. The fix is not a better RENAME; it is to stop renaming in place at all. Treat the rename as five ordered, individually reversible steps — add the new column, dual-write both, backfill history, switch reads, then drop the old column — so that at every instant both the old and the new name are valid and no deploy ever has to be atomic. This is the canonical application of the Expand and Contract Methodology, and on a hot table each step needs a lock and lag discipline the naive one-liner never considers.

Symptom / Error Signatures

The failures cluster around the instant an in-place rename commits, and they hit the write path hardest because a hot table’s writers are the first to reference the vanished name.

• PostgreSQL, old code reading the pre-rename name: ERROR: column "kind" does not exist, often with a HINT: Perhaps you meant to reference the column "events.event_type".
• MySQL, the same race: ERROR 1054 (42S22): Unknown column 'kind' in 'field list'.
• ORM-layer wrappers of the above — for example psycopg2.errors.UndefinedColumn or sqlalchemy.exc.ProgrammingError — spiking from application logs the moment the rename deploy lands.
• The mirror-image failure if you rename before the readers are ready: new code queries event_type while the column is still kind, producing the identical column "event_type" does not exist.
• A migration that itself stalls: PostgreSQL ERROR: canceling statement due to lock_timeout, because even a metadata-only RENAME must take an ACCESS EXCLUSIVE lock and queued behind a long-running query on a hot table.
• An application error-rate spike confined to endpoints that write or read the table, beginning at the exact second of the DDL deploy and clearing only when you roll it back.

If the error names the old column, your writers have not caught up; if it names the new one, your readers ran ahead of the schema. Both mean the same thing: code and schema disagreed at an instant a single RENAME created.

Root Cause Analysis

A rename is destructive disguised as trivial. RENAME COLUMN mutates the catalog atomically, but the catalog is shared by every connection and every running application version at once. During a rolling deploy the old image and the new image run simultaneously for minutes; a single atomic rename can satisfy exactly one of them. Whichever name it picks, the other fleet half is instantly wrong. There is no window in which a bare RENAME is safe unless you can freeze all traffic — which on a hot table means an outage.

Expand and contract dissolves that impossibility by never removing a name while code depends on it. You add a second column under the new name, so for a stretch the table carries both kind and event_type. During that compatibility window old code reads and writes kind, new code reads and writes event_type, and a dual-write keeps the two in lockstep so neither fleet half ever sees stale data. Only once every instance has rolled onto the new name — proven, not assumed — do you drop the old column. The five steps are just the two compatibility invariants made concrete: the schema stays backward compatible while you add, and forward compatible while you migrate.

The two engines make the additive steps cheap, but not identically, and the differences dictate how you write each ALTER:

The MySQL “no transactional DDL” row is the subtle one: because each ALTER commits on its own, you cannot bundle the add and the eventual drop into a single rollback unit, so every step must stand alone and be re-runnable — a property idempotent guards give you for free.

Immediate Mitigation

If an in-place RENAME has already broken production, restore service first, then redo it as a proper expansion. The goal of mitigation is to make both names valid again as fast as possible.

1. Reverse the rename to unbreak the majority fleet. If most instances still run the old image, rename back to kind so their writes succeed again. This is itself an atomic swap, so run it fast and at once.

-- PostgreSQL · run on primary as migration role · restores the name old code expects
-- Atomic catalog swap; fail fast rather than queue behind hot-table traffic.
SET lock_timeout = '3s';
ALTER TABLE events RENAME COLUMN event_type TO kind;

2. If the fleet is already mostly new code, expand instead of reversing. Re-add the old name as a nullable column so lagging old instances stop erroring, and backfill it from the new one. Do not fight the deploy direction — add whichever name is missing.

-- MySQL · run on primary as migration role · additive, restores old readers/writers
-- Nullable so new code may omit it; implicit commit per ALTER.
ALTER TABLE events ADD COLUMN kind VARCHAR(64) NULL;

3. Confirm the write-endpoint error rate returns to baseline before touching the schema again. Watch application logs for the column ... does not exist family to fully clear.

4. Cap lock waits on every DDL statement so a contended ALTER on a hot table fails fast instead of stalling the write path behind a long query.

-- PostgreSQL · run as migration role · bound the ACCESS EXCLUSIVE wait
SET lock_timeout = '3s';
-- MySQL equivalent:
-- SET SESSION lock_wait_timeout = 3;

5. Then start the ordered expansion from step one — never retry the in-place rename, no matter how quiet traffic looks.

Permanent Fix / Long-Term Pattern

Run the rename as five separate, individually deployed changes. Each is verified before the next begins; none is atomic across the fleet. The parent Expand and Contract Methodology covers the invariants in depth — here is the rename applied end to end.

Step 1 — Add the new column, additively. Add event_type as nullable so the previous application version, which never writes it, keeps succeeding untouched.

-- PostgreSQL · run on primary as migration role · metadata-only add, brief ACCESS EXCLUSIVE lock
-- Run at a low-write window so the lock does not queue behind a long transaction.
SET lock_timeout = '3s';
ALTER TABLE events ADD COLUMN IF NOT EXISTS event_type VARCHAR(64);

-- MySQL 8.0 · run on primary · add last with no positional clause to keep it INSTANT
ALTER TABLE events ADD COLUMN event_type VARCHAR(64) NULL, ALGORITHM=INSTANT;

Step 2 — Dual-write both columns. Deploy code that writes kind and event_type on every insert and update. Both writes ride the same application transaction so they commit together; if they can drift, follow the safeguards in Preventing Data Loss During Dual-Write Migrations. Gate the new path behind a flag so you can disable it instantly without a deploy, per Feature Flag Rollouts.

-- PostgreSQL · executed by the application per write · keep both columns in sync
-- Both assignments are in one application transaction, so they commit atomically.
INSERT INTO events (id, kind, event_type) VALUES ($1, $2, $2);
-- on update:
UPDATE events SET kind = $2, event_type = $2 WHERE id = $1;

Step 3 — Backfill history in throttled batches. Populate event_type for rows written before dual-write began. Commit per batch so locks release and replicas keep pace; pause if lag climbs, the discipline detailed in Backfill Optimization.

-- PostgreSQL · run as a separate worker, NOT one giant transaction · re-runnable
-- The WHERE guard makes each batch idempotent; advance $start until no rows remain.
UPDATE events
SET event_type = kind
WHERE event_type IS NULL
  AND id BETWEEN $start AND $start + 999;
-- pause the loop whenever replica lag exceeds ~2s.

Step 4 — Switch reads to the new column. Deploy code that reads event_type. The old column is still present and still written, so rolling back to the previous image remains safe at any moment.

Step 5 — Contract: drop the old column. Only after every instance reads the new name and a grace period confirms zero references to kind, drop it. This is the one irreversible step, so gate it behind a verified zero-reference check and a recovery snapshot.

-- PostgreSQL · run on primary as migration role · metadata-only drop, brief ACCESS EXCLUSIVE lock
-- Irreversible without a restore — gate behind a proven zero-reference check.
SET lock_timeout = '3s';
ALTER TABLE events DROP COLUMN IF EXISTS kind;

-- MySQL · run on primary · INSTANT drop on 8.0.29+; older versions rebuild the table off-peak
ALTER TABLE events DROP COLUMN kind, ALGORITHM=INSTANT;

On a hot table the brief locks in steps 1 and 5 still deserve the lock-acquisition care covered in Implementing Expand-Contract for High-Traffic Tables, and the contract mirrors the teardown discipline in Safely Removing a NOT NULL Column With Expand-Contract whenever the renamed column carried a constraint.

Verification Checklist

• event_type exists as a nullable column and its add held its lock for milliseconds, not seconds (checked against pg_stat_activity or performance_schema.metadata_locks).
• Dual-write is live and gated behind a flag, so every new insert and update populates both kind and event_type.
• The backfill has run to completion: SELECT count(*) FROM events WHERE event_type IS NULL returns zero across the whole table.
• Every application instance has rolled onto code that reads event_type, confirmed by deploy status and by application logs showing no reads of kind.
• Query logs or pg_stat_statements show zero references to kind across a full grace period before the drop.
• Each DDL step sets lock_timeout / lock_wait_timeout so a contended ALTER on the hot table fails fast instead of blocking the write path.

Back to the parent topic: Expand and Contract Methodology.

Frequently Asked Questions

Isn’t RENAME COLUMN atomic and instant — why not just use it? The ALTER is genuinely cheap; the cost is not the statement, it is the fleet. A rolling deploy runs old and new code together for minutes, and an atomic rename can satisfy only one of them, so the other half instantly queries a name that no longer exists. Expand and contract trades that one risky instant for five ordered steps in which both names are valid the entire time.

Do I really need a dual-write, or can I just backfill once and switch? On a hot table you need it. Between the moment you backfill and the moment every reader switches, thousands of new rows land, and without dual-write they populate only the old column — leaving the new column stale for exactly the rows most likely to be read. Dual-write keeps kind and event_type identical for every write during the window, so the backfill only has to cover history, not the moving present.

When is it finally safe to drop the old column? Only after every instance reads the new name and a grace period confirms nothing references the old one — at least one full deploy cycle plus time for caches, prepared statements, and scheduled jobs to cycle, often 24 to 48 hours. Prove it from query logs rather than assuming it; the drop is the single irreversible step, so gate it behind a verified zero-reference check and a point-in-time recovery snapshot.