Skip to main content
← PROBLEMS / ENGINEERING
An end-to-end domain learning journey

Zero-Downtime Column Renames
with Expand-and-Contract.

A 40-pod Kubernetes deployment (maxSurge: 25%, maxUnavailable: 0) running a Python/SQLAlchemy service against MySQL 8.0 executes a rolling update from v2.0 to v2.1. An initContainer on the first v2.1 pod runs ALTER TABLE customers RENAME COLUMN user_phone TO phone_e164; on a 25M-row table. Immediately, all 40 existing v2.0 pods fail with OperationalError: (1054, "Unknown column 'user_phone' in 'field list'"), halting the readiness-gated rollout and leaving the cluster in a split-brain outage. Design a zero-downtime column rename workflow for rolling deployments on MySQL 8.0.
Start the journey5 stages
Loading diagram…
Zero-Downtime Column Renames with Expand-and-Contract architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

A 40-pod Kubernetes deployment (`maxSurge: 25%`, `maxUnavailable: 0`) running a Python/SQLAlchemy service against MySQL 8.0 executes a rolling update from v2.0 to v2.1. An initContainer on the first v2.1 pod runs `ALTER TABLE customers RENAME COLUMN user_phone TO phone_e164;` on a 25M-row table. Immediately, all 40 existing v2.0 pods fail with `OperationalError: (1054, "Unknown column 'user_phone' in 'field list'")`, halting the readiness-gated rollout and leaving the cluster in a split-brain outage. Design a zero-downtime column rename workflow for rolling deployments on MySQL 8.0.

From patterns to decisions.

A 40-pod Kubernetes deployment (`maxSurge: 25%`, `maxUnavailable: 0`) running a Python/SQLAlchemy service against MySQL 8.0 executes a rolling update from v2.0 to v2.1. An initContainer on the first v2.1 pod runs `ALTER TABLE customers RENAME COLUMN user_phone TO phone_e164;` on a 25M-row table. Immediately, all 40 existing v2.0 pods fail with `OperationalError: (1054, "Unknown column 'user_phone' in 'field list'")`, halting the readiness-gated rollout and leaving the cluster in a split-brain outage. Design a zero-downtime column rename workflow for rolling deployments on MySQL 8.0.

01
ENGINEERING · STAGE 01L4 · FUNDAMENTALS

Metadata speed does not solve.

Metadata speed does not solve application-level schema skew: v2.0 pods still issue SQL referencing user_phone, which immediately ceases to exist.

ChoiceWhat it buysWhat it costs
Trade-off 1Zero-downtime rolling updates require N and N-1 schema compatibility

Explain that `maxSurge

25%, maxUnavailable: 0` guarantees v2.0 and v2.1 overlap.

Step 2

Show how initContainer execution raced and broke all 40 live v2.0 pods.

Step 3

Note that failed readiness probes halt the rolling update mid-flight.

Alternative 1

Take a maintenance window and scale replicas to 0 before renaming (violates zero-downtime constraint)

Failure Mode 1

initContainer migrations running concurrently across multiple surging pods

-- Immediate error on all 40 v2.0 pods:
SELECT id, user_phone FROM customers WHERE id = 123;
-- ERROR 1054 (42S22): Unknown column 'user_phone' in 'field list'
02
ENGINEERING · STAGE 02L5 · APPLIED DESIGN

Implementation & Guardrails (2).

Add phone_e164 with ALGORITHM=INSTANT, synchronize both columns via BEFORE INSERT/UPDATE triggers using <=>, backfill in batches, and drop user_phone in v2.2.

ChoiceWhat it buysWhat it costs
Trade-off 1Requires two releases (v2.1 + v2.2) to complete the rename cleanly

Step 1

Run Expand in a single K8s pre-upgrade Job with lock_wait_timeout = 2.

Step 2

Use <=> in BEFORE UPDATE to detect whether v2.0 or v2.1 changed the value.

Step 3

Backfill 25M rows in 2,000-row batches and add indexes with LOCK=NONE.

Alternative 1

If phone_e164 is only read and never written directly until v2.2, use a MySQL VIRTUAL generated column instead of triggers

Failure Mode 1

Using != instead of <=> in the UPDATE trigger fails when either column transitions from NULL to non-null

SET SESSION lock_wait_timeout = 2;
ALTER TABLE customers ADD COLUMN phone_e164 VARCHAR(20) NULL, ALGORITHM=INSTANT;
-- Backfill in 2k batches, then:
ALTER TABLE customers ADD INDEX idx_phone_e164 (phone_e164), ALGORITHM=INPLACE, LOCK=NONE;
03
ENGINEERING · STAGE 03L6 · SCALE & TRADE-OFFS

A VIRTUAL generated column avoids.

A VIRTUAL generated column avoids triggers and backfill entirely if v2.1 only reads phone_e164, but cannot be written to directly by INSERT/UPDATE.

ChoiceWhat it buysWhat it costs
Trade-off 1Virtual columns are instant and trigger-free, but force v2.1 to still write to the old column name

Step 1

Virtual generated columns compute phone_e164 on read with zero storage overhead.

Step 2

However, MySQL rejects INSERT INTO customers (phone_e164) VALUES (...) on generated columns.

Step 3

Therefore, triggers are required when v2.1 must write to phone_e164 while v2.0 still writes to user_phone.

Alternative 1

Use an updatable SQL VIEW (customers_v2) if the application can target a versioned table/view name

Failure Mode 1

Attempting to write to a generated column throws ERROR 3105: The value specified for generated column is not allowed

-- Valid ONLY if v2.1 reads phone_e164 but still writes user_phone:
ALTER TABLE customers
  ADD COLUMN phone_e164 VARCHAR(20) GENERATED ALWAYS AS (user_phone) VIRTUAL;
04
ENGINEERING · STAGE 04L6 · SCALE & TRADE-OFFS

MySQL's Metadata Lock (MDL) queue.

MySQL's Metadata Lock (MDL) queue gives ALTER TABLE priority over new SELECT/UPDATEs; without lock_wait_timeout, all 3,000 QPS queue behind the analytics query.

ChoiceWhat it buysWhat it costs
Trade-off 1The migration job may need 2–3 automated retries during busy hours, which is far safer than an MDL outage

Trace the MDL pileup

Analytics SELECT -> ALTER TABLE waits -> all 3,000 QPS OLTP queries block.

Step 2

Set SET SESSION lock_wait_timeout = 2; so the DDL fails fast instead of blocking traffic.

Step 3

Retry the DDL with exponential backoff until the analytics query finishes.

Alternative 1

Use gh-ost or pt-online-schema-change for non-instant schema changes on large tables

Failure Mode 1

Default MySQL lock_wait_timeout is 31,536,000 seconds (1 year), guaranteeing an outage if an ALTER blocks

SET SESSION lock_wait_timeout = 2;
ALTER TABLE customers ADD COLUMN phone_e164 VARCHAR(20) NULL, ALGORITHM=INSTANT;
05
ENGINEERING · STAGE 05L7 · STAFF / PRINCIPAL

Execute migrations in a dedicated.

Execute migrations in a dedicated single-pod Kubernetes Job (Helm pre-upgrade hook or ArgoCD PreSync hook) before updating the Deployment.

ChoiceWhat it buysWhat it costs
Trade-off 1Separates migration lifecycle from pod lifecycle, requiring CI/CD hook support

Step 1

Remove Alembic/Flyway from application initContainers.

Create a Kubernetes `Job` with `completions

1, parallelism: 1, and backoffLimit: 2`.

Step 3

Gate the Deployment rollout on Job completion.

Alternative 1

Use a distributed advisory lock (GET_LOCK in MySQL) if migrations must remain in-app

Failure Mode 1

If ttlSecondsAfterFinished is omitted, old migration Jobs accumulate in the namespace

apiVersion: batch/v1
kind: Job
metadata:
  name: customers-db-migrate-v21
  annotations:
    argocd.argoproj.io/hook: PreSync
spec:
  backoffLimit: 2
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: alembic
          image: payments-api:v2.1
          command: ["alembic", "upgrade", "head"]

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Never rename a live column in place during a rolling deploy; use additive columns, generated/triggered sync, or updatable views across two releases.
  • 02Trade-off tip: Virtual columns are instant and trigger-free, but force v2.1 to still write to the old column name
  • 03Watch out for: Default MySQL lock_wait_timeout is 31,536,000 seconds (1 year), guaranteeing an outage if an ALTER blocks
Behavioral Progression · Expand Contract Column Renames

Expand Contract Column Renames — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Never rename a live column in place during a rolling deploy; use additive columns, generated/triggered sync, or updatable views across two releases.

Synthesis / One connected design

Putting Zero-Downtime Column Renames with Expand-and-Contract Together

Never rename a live column in place during a rolling deploy; use additive columns, generated/triggered sync, or updatable views across two releases.

A candidate architecture, not a universal answer

Putting Zero-Downtime Column Renames with Expand-and-Contract Together

Step 1
Step 2
Step 3
  1. Run Expand in a single K8s pre-upgrade Job with lock_wait_timeout = 2.
  2. Use <=> in BEFORE UPDATE to detect whether v2.0 or v2.1 changed the value.
  3. Backfill 25M rows in 2,000-row batches and add indexes with LOCK=NONE.
MySQL's Metadata Lock (MDL) queue gives `ALTER TABLE` priority over new `SELECT/UPDATE`s; without `lock_wait_timeout`, all 3,000 QPS queue behind the analytics query.
Interview Readiness Check

MySQL's Metadata Lock (MDL) queue gives ALTER TABLE priority over new SELECT/UPDATEs; without lock_wait_timeout, all 3,000 QPS queue behind the analytics query.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

Zero-Downtime Column Renames with Expand-and-Contract

Never rename a live column in place during a rolling deploy; use additive columns, generated/triggered sync, or updatable views across two releases.