Chapter 5: DATA IN PRODUCTION - System Design Course: Complete Production Architecture | PlayBook Library

SLIDE 31: Section 3: Data in Production

The Hardest Problems in Production Systems

CODE

#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .error-icon{fill:#552222;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .error-text{fill:#552222;stroke:#552222;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-thickness-normal{stroke-width:1px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-thickness-thick{stroke-width:3.5px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-pattern-solid{stroke-dasharray:0;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .marker{fill:#666;stroke:#666;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .marker.cross{stroke:#666;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a p{margin:0;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .cluster-label text{fill:#333;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .cluster-label span{color:#333;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .cluster-label span p{background-color:transparent;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .label text,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a span{fill:#000000;color:#000000;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node rect,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node circle,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node ellipse,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node polygon,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .rough-node .label text,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node .label text,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .image-shape .label,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .icon-shape .label{text-anchor:middle;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .rough-node .label,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node .label,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .image-shape .label,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .icon-shape .label{text-align:center;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node.clickable{cursor:pointer;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .arrowheadPath{fill:#333333;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .flowchart-link{stroke:#666;fill:none;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edgeLabel{background-color:white;text-align:center;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edgeLabel p{background-color:white;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .cluster text{fill:#333;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .cluster span{color:#333;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a rect.text{fill:none;stroke-width:0;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .icon-shape,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .image-shape{background-color:white;text-align:center;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .icon-shape p,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .image-shape p{background-color:white;padding:2px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .icon-shape .label rect,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a .node .neo-node{stroke:#999;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node rect,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].cluster rect,#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node polygon{stroke:url(#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node path{stroke:url(#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a-gradient);stroke-width:1px;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node circle{stroke:url(#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].icon-shape .icon{fill:url(#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-333b1468-d5e5-4bcf-86e9-5a99a6c8ec6a :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}Schema EvolutionMigrationsReplicationConsistencyHot PartitionsBackups

Why Data is Different:

Aspect	Code	Data
Rollback	Deploy previous version	Cannot "un-migrate" easily
Testing	Unit tests, staging	Production data is unique
Mistakes	Fix and redeploy	Data corruption may be permanent
Scale	Add more servers	Moving data is expensive

Key Insight:
Code is stateless and replaceable. Data is stateful and irreplaceable. Most production incidents involve data problems.

SLIDE 32: Schema Evolution: Why It Matters

The Schema Change Problem

Day 1 Schema:

CODE

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

Day 100 Requirement:
"We need to split name into first_name and last_name."

The Problem:

10 million rows in production
Application servers reading and writing constantly
Cannot take downtime
Old code expects "name", new code expects "first_name" and "last_name"

Interview Answer:
"Just add the new columns and migrate the data."

Production Answer:
"This requires a 4-phase migration over 2 weeks with careful coordination between database changes and code deployments."

SLIDE 33: Schema Evolution: Why It Matters (continued)

What Can Go Wrong:

Mistake	Consequence
Add NOT NULL column without default	INSERT fails for all rows
Drop column still referenced by code	Application crashes
Rename column without coordination	Queries fail
Long-running migration locks table	Writes blocked, timeouts
Migration fails halfway	Inconsistent data state

Production Statistics:

CODE

Database schema changes cause:
- 30% of production incidents at scale
- Average 2-hour resolution time
- Often require manual data fixes

Source: Industry surveys from major tech companies

Key Insight:
Schema changes are the most dangerous deployments. They require more planning than code changes.

SLIDE 34: Schema Evolution: Safe Column Addition

Adding Columns Safely

Safe: Add Nullable Column

CODE

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;

Why Safe:

Existing rows get NULL (no data modification)
Old code ignores new column
New code can start using it
No table lock on most databases

Unsafe: Add NOT NULL Column Without Default

CODE

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
-- ERROR: Cannot add NOT NULL column to table with existing rows

Safe Alternative:

CODE

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;

-- Step 2: Backfill data (in batches)
UPDATE users SET phone = 'unknown' WHERE phone IS NULL LIMIT 10000;

-- Step 3: Add NOT NULL constraint (after all rows have values)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

SLIDE 35: Schema Evolution: Safe Column Addition (continued)

Adding Column With Default:

PostgreSQL 11+ (Safe):

CODE

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

PostgreSQL 11+ stores default in catalog, does not rewrite table.

Older PostgreSQL / MySQL (Unsafe at Scale):

CODE

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- Rewrites entire table
-- 10 million rows = several minutes of locking

Safe Pattern for Older Databases:

CODE

-- Step 1: Add nullable column (instant)
ALTER TABLE users ADD COLUMN status VARCHAR(20) NULL;

-- Step 2: Update application to write 'active' for new rows

-- Step 3: Backfill existing rows in batches
UPDATE users SET status = 'active'
WHERE status IS NULL AND id BETWEEN 1 AND 10000;

-- Step 4: Add default for future rows
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

SLIDE 36: Schema Evolution: Safe Column Removal

Removing Columns Safely

The Danger:

CODE

ALTER TABLE users DROP COLUMN middle_name;

If any application code still references middle_name:

SELECT queries fail
INSERT queries fail
Application crashes

Safe Removal Pattern:

CODE

#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .error-icon{fill:#552222;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .error-text{fill:#552222;stroke:#552222;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-thickness-normal{stroke-width:1px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .marker{fill:#666;stroke:#666;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .marker.cross{stroke:#666;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 p{margin:0;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .cluster-label text{fill:#333;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .cluster-label span{color:#333;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .cluster-label span p{background-color:transparent;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .label text,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 span{fill:#000000;color:#000000;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node rect,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node circle,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node ellipse,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node polygon,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .rough-node .label text,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node .label text,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .image-shape .label,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .icon-shape .label{text-anchor:middle;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .rough-node .label,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node .label,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .image-shape .label,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .icon-shape .label{text-align:center;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node.clickable{cursor:pointer;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .arrowheadPath{fill:#333333;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .flowchart-link{stroke:#666;fill:none;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edgeLabel{background-color:white;text-align:center;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edgeLabel p{background-color:white;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .cluster text{fill:#333;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .cluster span{color:#333;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 rect.text{fill:none;stroke-width:0;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .icon-shape,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .image-shape{background-color:white;text-align:center;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .icon-shape p,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .image-shape p{background-color:white;padding:2px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .icon-shape .label rect,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 .node .neo-node{stroke:#999;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node rect,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].cluster rect,#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node polygon{stroke:url(#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node path{stroke:url(#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9-gradient);stroke-width:1px;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node circle{stroke:url(#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].icon-shape .icon{fill:url(#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-1797c7d6-838b-4574-8cc6-9b7f9db708c9 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}Stop WritingStop ReadingDeploy CodeDrop Column

Step-by-Step:

Phase	Action	Duration
1	Stop writing to column in code	Deploy
2	Stop reading from column in code	Deploy
3	Verify no queries use column	1 week monitoring
4	Drop column from database	Migration

SLIDE 37: Schema Evolution: Safe Column Removal (continued)

Verification Before Drop:

Check for Column Usage:

CODE

-- PostgreSQL: Check if column is referenced in views
SELECT * FROM information_schema.view_column_usage
WHERE column_name = 'middle_name';

-- Check application logs for queries using column
grep "middle_name" /var/log/app/*.log

Safe Drop with Soft Delete First:

CODE

-- Step 1: Rename column (keeps data, breaks nothing if unused)
ALTER TABLE users RENAME COLUMN middle_name TO _deprecated_middle_name;

-- Step 2: Wait 1-2 weeks, monitor for errors

-- Step 3: If no errors, drop
ALTER TABLE users DROP COLUMN _deprecated_middle_name;

Key Insight:
Column removal should take weeks, not minutes. The extra time prevents catastrophic failures.

SLIDE 38: Schema Evolution: Renaming Columns

The Expand-Contract Pattern

Goal: Rename "user_name" to "username"

Wrong Approach:

CODE

ALTER TABLE users RENAME COLUMN user_name TO username;
-- All code referencing user_name breaks instantly

Correct Approach: Expand-Contract

CODE

#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .error-icon{fill:#552222;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .error-text{fill:#552222;stroke:#552222;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-thickness-normal{stroke-width:1px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .marker{fill:#666;stroke:#666;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .marker.cross{stroke:#666;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 p{margin:0;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .cluster-label text{fill:#333;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .cluster-label span{color:#333;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .cluster-label span p{background-color:transparent;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .label text,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 span{fill:#000000;color:#000000;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node rect,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node circle,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node ellipse,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node polygon,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .rough-node .label text,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node .label text,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .image-shape .label,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .icon-shape .label{text-anchor:middle;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .rough-node .label,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node .label,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .image-shape .label,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .icon-shape .label{text-align:center;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node.clickable{cursor:pointer;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .arrowheadPath{fill:#333333;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .flowchart-link{stroke:#666;fill:none;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edgeLabel{background-color:white;text-align:center;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edgeLabel p{background-color:white;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .cluster text{fill:#333;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .cluster span{color:#333;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 rect.text{fill:none;stroke-width:0;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .icon-shape,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .image-shape{background-color:white;text-align:center;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .icon-shape p,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .image-shape p{background-color:white;padding:2px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .icon-shape .label rect,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 .node .neo-node{stroke:#999;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node rect,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].cluster rect,#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node polygon{stroke:url(#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node path{stroke:url(#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701-gradient);stroke-width:1px;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node circle{stroke:url(#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].icon-shape .icon{fill:url(#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-7e4f0582-5ea2-4d6d-8ff7-ee7e543b0701 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}ExpandMigrate CodeContract

Phase 1: Expand (Add New Column)

CODE

ALTER TABLE users ADD COLUMN username VARCHAR(100);
UPDATE users SET username = user_name WHERE username IS NULL;

-- Add trigger to keep columns in sync
CREATE TRIGGER sync_username
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_username_columns();

SLIDE 39: Schema Evolution: Renaming Columns (continued)

Phase 2: Migrate Code

CODE

# Old code (before migration)
user = db.query("SELECT user_name FROM users WHERE id = ?", user_id)

# New code (during migration) - read from new, write to both
user = db.query("SELECT username FROM users WHERE id = ?", user_id)
db.execute("UPDATE users SET username = ?, user_name = ? WHERE id = ?",
           name, name, user_id)

# Final code (after migration) - new column only
user = db.query("SELECT username FROM users WHERE id = ?", user_id)
db.execute("UPDATE users SET username = ? WHERE id = ?", name, user_id)

Phase 3: Contract (Remove Old Column)

CODE

-- Remove trigger
DROP TRIGGER sync_username ON users;

-- Drop old column
ALTER TABLE users DROP COLUMN user_name;

Timeline:

Week	Action
1	Add new column, sync trigger
2-3	Deploy code reading new column
4-5	Deploy code writing only new column
6	Remove trigger and old column

SLIDE 40: Schema Evolution: Changing Column Types

Type Changes Are Dangerous

Scenario: Change user_id from INT to BIGINT

Why Dangerous:

INT max: 2.1 billion
Table has 1.5 billion rows
Approaching limit, need BIGINT
ALTER TABLE rewrites entire table

Wrong Approach:

CODE

ALTER TABLE users ALTER COLUMN id TYPE BIGINT;
-- Locks table for hours
-- All writes blocked
-- Application timeouts

Production Approach:

Strategy	Downtime	Complexity	Risk
Online DDL (pt-online-schema-change)	Minimal	Medium	Medium
Dual-write to new table	None	High	Low
Blue-green database	Minutes	Very High	Low

SLIDE 41: Schema Evolution: Changing Column Types (continued)

Dual-Write Migration Pattern:

CODE

#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .error-icon{fill:#552222;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .error-text{fill:#552222;stroke:#552222;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-thickness-normal{stroke-width:1px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-thickness-thick{stroke-width:3.5px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-pattern-solid{stroke-dasharray:0;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .marker{fill:#666;stroke:#666;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .marker.cross{stroke:#666;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a p{margin:0;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .cluster-label text{fill:#333;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .cluster-label span{color:#333;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .cluster-label span p{background-color:transparent;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .label text,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a span{fill:#000000;color:#000000;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node rect,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node circle,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node ellipse,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node polygon,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .rough-node .label text,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node .label text,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .image-shape .label,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .icon-shape .label{text-anchor:middle;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .rough-node .label,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node .label,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .image-shape .label,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .icon-shape .label{text-align:center;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node.clickable{cursor:pointer;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .arrowheadPath{fill:#333333;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .flowchart-link{stroke:#666;fill:none;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edgeLabel{background-color:white;text-align:center;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edgeLabel p{background-color:white;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .cluster text{fill:#333;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .cluster span{color:#333;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a rect.text{fill:none;stroke-width:0;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .icon-shape,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .image-shape{background-color:white;text-align:center;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .icon-shape p,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .image-shape p{background-color:white;padding:2px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .icon-shape .label rect,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a .node .neo-node{stroke:#999;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node rect,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].cluster rect,#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node polygon{stroke:url(#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node path{stroke:url(#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a-gradient);stroke-width:1px;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node circle{stroke:url(#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].icon-shape .icon{fill:url(#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-26cb0d9b-d67a-4d92-8c48-d494a5c4af0a :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}Create New TableDual WriteBackfillSwitch ReadsStop Old WritesDrop Old Table

Step 1: Create New Table

CODE

CREATE TABLE users_v2 (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

Step 2: Dual Write (Application Code)

CODE

def create_user(name, email):
    # Write to both tables
    user_id = db.insert("INSERT INTO users (name, email) VALUES (?, ?)", name, email)
    db.insert("INSERT INTO users_v2 (id, name, email) VALUES (?, ?, ?)",
              user_id, name, email)
    return user_id

Step 3-6: Backfill historical data, switch reads, stop old writes, drop old table.

SLIDE 42: Database Migrations: Zero-Downtime Principles

The Golden Rules of Production Migrations

Rule 1: Separate Schema Changes from Code Deployments

CODE

#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .error-icon{fill:#552222;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .error-text{fill:#552222;stroke:#552222;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-thickness-normal{stroke-width:1px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-thickness-thick{stroke-width:3.5px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-pattern-solid{stroke-dasharray:0;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .marker{fill:#666;stroke:#666;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .marker.cross{stroke:#666;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e p{margin:0;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .cluster-label text{fill:#333;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .cluster-label span{color:#333;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .cluster-label span p{background-color:transparent;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .label text,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e span{fill:#000000;color:#000000;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node rect,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node circle,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node ellipse,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node polygon,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .rough-node .label text,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node .label text,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .image-shape .label,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .icon-shape .label{text-anchor:middle;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .rough-node .label,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node .label,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .image-shape .label,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .icon-shape .label{text-align:center;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node.clickable{cursor:pointer;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .arrowheadPath{fill:#333333;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .flowchart-link{stroke:#666;fill:none;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edgeLabel{background-color:white;text-align:center;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edgeLabel p{background-color:white;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .cluster text{fill:#333;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .cluster span{color:#333;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e rect.text{fill:none;stroke-width:0;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .icon-shape,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .image-shape{background-color:white;text-align:center;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .icon-shape p,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .image-shape p{background-color:white;padding:2px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .icon-shape .label rect,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e .node .neo-node{stroke:#999;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node rect,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].cluster rect,#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node polygon{stroke:url(#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node path{stroke:url(#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e-gradient);stroke-width:1px;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node circle{stroke:url(#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].icon-shape .icon{fill:url(#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-dfc882bb-e9bc-4326-98a5-3f7df7ae454e :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}Schema ChangeWaitCode Deploy

Never deploy schema change and code change together. If either fails, rollback is impossible.

Rule 2: All Changes Must Be Backward Compatible

Change Type	Backward Compatible?
Add nullable column	Yes
Add NOT NULL with default	Depends on database version
Drop column	No (must remove code references first)
Rename column	No (use expand-contract)
Change type	No (use dual-write)

Rule 3: All Changes Must Be Forward Compatible

New code must work with old schema during rollback window.

SLIDE 43: Database Migrations: Zero-Downtime Principles (continued)

Rule 4: Migrations Must Be Idempotent

CODE

-- Bad: Fails if run twice
CREATE TABLE users (...);

-- Good: Idempotent
CREATE TABLE IF NOT EXISTS users (...);

-- Bad: Fails if column exists
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Good: Check first (PostgreSQL)
DO 
$$

BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name = 'users' AND column_name = 'phone')
    THEN
        ALTER TABLE users ADD COLUMN phone VARCHAR(20);
    END IF;
END 
$$;

Rule 5: Migrations Must Be Reversible

CODE

-- Forward migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Rollback migration (must exist)
ALTER TABLE users DROP COLUMN phone;

SLIDE 44: Database Migrations: Batched Updates

Large Data Migrations

The Problem:

CODE

UPDATE users SET status = 'active' WHERE status IS NULL;
-- 50 million rows
-- Locks table for 30 minutes
-- Transaction log explodes
-- Replication lag spikes

The Solution: Batched Updates

CODE

def backfill_status():
    batch_size = 10000
    last_id = 0

    while True:
        result = db.execute("""
            UPDATE users
            SET status = 'active'
            WHERE status IS NULL
              AND id > ?
              AND id <= ?
        """, last_id, last_id + batch_size)

        if result.rowcount == 0:
            break

        last_id += batch_size
        time.sleep(0.1)  # Pause to reduce load

        print(f"Updated up to id {last_id}")

SLIDE 45: Database Migrations: Batched Updates (continued)

Batching Best Practices:

Parameter	Recommendation	Why
Batch size	1,000 - 10,000 rows	Balance speed and lock duration
Sleep between batches	50-200ms	Allow replication to catch up
Progress tracking	Log every batch	Know where to resume if failed
Resumability	Track last processed ID	Can restart without re-processing

Monitoring During Migration:

CODE

-- Watch replication lag
SHOW SLAVE STATUS;  -- MySQL
SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn();  -- PostgreSQL

-- Watch lock waits
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Abort Conditions:

CODE

if replication_lag > 30_seconds:
    pause_migration()
    alert_oncall()

SLIDE 46: Database Migrations: Online Schema Change Tools

Tools for Large Table Migrations

The Problem With ALTER TABLE:

CODE

ALTER TABLE users ADD COLUMN preferences JSONB;
-- On 100M row table:
-- MySQL 5.6: Locks table for writes (hours)
-- MySQL 8.0: Online DDL (still slow)
-- PostgreSQL: Metadata-only change (instant for ADD COLUMN)

Online Schema Change Tools:

Tool	Database	How It Works
pt-online-schema-change	MySQL	Creates shadow table, copies data, swaps
gh-ost	MySQL	Binary log parsing, no triggers
pg_repack	PostgreSQL	Repacks table without locks
LHM (Large Hadron Migrator)	MySQL	Similar to pt-osc

gh-ost Example:

CODE

gh-ost \\\\
  --host=db.example.com \\\\
  --database=myapp \\\\
  --table=users \\\\
  --alter="ADD COLUMN preferences JSON" \\\\
  --execute

SLIDE 47: Database Migrations: Online Schema Change Tools (continued)

How gh-ost Works:

CODE

#mermaid-05496913-7eec-4fbf-8859-210a54708fd1{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .error-icon{fill:#552222;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .error-text{fill:#552222;stroke:#552222;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-thickness-normal{stroke-width:1px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .marker{fill:#666;stroke:#666;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .marker.cross{stroke:#666;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 p{margin:0;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .cluster-label text{fill:#333;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .cluster-label span{color:#333;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .cluster-label span p{background-color:transparent;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .label text,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 span{fill:#000000;color:#000000;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node rect,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node circle,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node ellipse,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node polygon,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .rough-node .label text,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node .label text,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .image-shape .label,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .icon-shape .label{text-anchor:middle;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .rough-node .label,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node .label,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .image-shape .label,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .icon-shape .label{text-align:center;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node.clickable{cursor:pointer;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .arrowheadPath{fill:#333333;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .flowchart-link{stroke:#666;fill:none;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edgeLabel{background-color:white;text-align:center;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edgeLabel p{background-color:white;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .cluster text{fill:#333;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .cluster span{color:#333;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 rect.text{fill:none;stroke-width:0;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .icon-shape,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .image-shape{background-color:white;text-align:center;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .icon-shape p,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .image-shape p{background-color:white;padding:2px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .icon-shape .label rect,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 .node .neo-node{stroke:#999;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node rect,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].cluster rect,#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node polygon{stroke:url(#mermaid-05496913-7eec-4fbf-8859-210a54708fd1-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node path{stroke:url(#mermaid-05496913-7eec-4fbf-8859-210a54708fd1-gradient);stroke-width:1px;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node circle{stroke:url(#mermaid-05496913-7eec-4fbf-8859-210a54708fd1-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].icon-shape .icon{fill:url(#mermaid-05496913-7eec-4fbf-8859-210a54708fd1-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-05496913-7eec-4fbf-8859-210a54708fd1-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-05496913-7eec-4fbf-8859-210a54708fd1 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}Create Ghost TableCopy Existing RowsStream Binlog ChangesCatch UpCut Over

Detailed Steps:

Step	Action	Duration
1	Create ghost table with new schema	Instant
2	Copy existing rows in chunks	Hours (depends on size)
3	Apply ongoing changes from binlog	Continuous
4	Wait for ghost to catch up	Seconds
5	Rename tables atomically	Instant

Advantages:

No triggers (unlike pt-online-schema-change)
Pausable and resumable
Testable in dry-run mode
Controllable throttling

Production Practice:
Run gh-ost during low-traffic periods. Monitor replication lag. Have rollback plan ready.