Zero-Downtime Column Renames
Add new column, dual-write, backfill, switch reads, drop the old — the canonical zero-downtime rename pattern.
Zero-Downtime Column Renames is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Naive Approach Breaks
ALTER TABLE t RENAME COLUMN a TO b; is metadata-only — fast. BUT the app expects column "a"; after the rename, every query reading "a" breaks.
Strategy: Expand → Migrate → Contract
The canonical zero-downtime pattern:
- Expand — add the new column without removing the old
- Have the app write to both for a while
- Migrate — backfill historical data into the new column
- Switch reads to the new column
- Contract — drop the old column
Step-by-Step: Renaming user_name → full_name
Detailed walkthrough:
Step 1 — Add the New Column
Online (metadata only on PG 11+ with constant default):
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);Step 2 — Dual Write
Deploy code that writes BOTH columns:
// App writes to both:
UPDATE users SET user_name = $1, full_name = $1 WHERE id = $2;
INSERT INTO users (..., user_name, full_name) VALUES (..., $name, $name);Step 3 — Backfill
Copy historical data in batches:
UPDATE users SET full_name = user_name
WHERE full_name IS NULL
AND id IN (
SELECT id FROM users WHERE full_name IS NULL LIMIT 10000
);
-- Run in a loop until 0 rows affected.Step 4 — Add NOT NULL (Online)
After backfill, enforce NOT NULL safely:
ALTER TABLE users
ADD CONSTRAINT fn_not_null CHECK (full_name IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT fn_not_null;
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT fn_not_null;Step 5 — Switch Reads
Deploy code that reads full_name instead of user_name.
Step 6 — Stop Dual Writes
Deploy code that only writes full_name.
Step 7 — Drop the Old Column
Online (metadata-only):
ALTER TABLE users DROP COLUMN user_name;Tradeoff: Time and Complexity
A simple rename becomes 4–7 deploys over days/weeks. For non-trivial production systems this is the only safe path.
Renaming Tables
Similar pattern, harder. Use VIEW as a temporary alias:
ALTER TABLE old_name RENAME TO new_name;
CREATE VIEW old_name AS SELECT * FROM new_name;
-- Deploy new code. Then drop the view.Recap
Zero-downtime column renames follow expand → migrate → contract.
- Add new column
- Dual write
- Backfill in batches
- Switch reads
- Stop old writes
- Drop old column
Quick Check
Which phase comes IMMEDIATELY after deploying dual writes to both old and new columns?
Frequently asked questions
Is the “Zero-Downtime Column Renames” lesson free?
Yes — the full text of “Zero-Downtime Column Renames” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Zero-Downtime Column Renames”?
Add new column, dual-write, backfill, switch reads, drop the old — the canonical zero-downtime rename pattern. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Zero-Downtime Column Renames” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.