Skip to content
LLD Learn/Distributed & Large-Scale Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Schema Versioning, Migrations & CQRS

5 min read

You'll learn to

  • -Explain why a column rename can crash a live system, and use an additive, multi-step migration instead
  • -Record applied migrations so running one twice is harmless
  • -Describe CQRS as separate write and read models, and the eventual-consistency price you accept

This closing chapter of the module covers a problem every other chapter quietly assumed away: schemas change. Requirements change, and a schema already running in production needs a safe way to evolve without breaking the application that is using it, especially when that application runs on many servers that cannot all update at the same instant.

An Analogy: Renovating a Restaurant That Stays Open

Imagine renovating a restaurant without closing it. You cannot bolt up the door the waiters use and tell them to find the new one afterwards. The safe way is to build the new door first, let waiters use either door for a while, move everyone over, and only then remove the old door. A schema migration is the same.

The Core Problem: Old Code and a New Schema at the Same Time

A rolling deployment updates application servers one at a time. For a while, old code and new code both run against the same database. A migration that renames a column, or makes a nullable column required, breaks the old code the moment it runs, before the rollout even finishes.

Additive, Backward-Compatible Migrations

An additive migration - safe for old and new code to run against simultaneously
-- Adding a new column with a default is safe: old code that doesn't
-- know about it simply never references it, and continues working unchanged.
ALTER TABLE colony_records ADD COLUMN oxygen_reserve_pct DECIMAL(5,2) DEFAULT 100.00;

-- Renaming a column is NOT safe in one step - old code referencing the
-- old name breaks immediately. The safe pattern is a multi-phase migration:
-- 1) add the new column, 2) backfill it and dual-write to both columns,
-- 3) migrate all readers to the new column, 4) only then drop the old one.

The demo plays that out with two versions of the code. First the column is renamed in one step, and the old code crashes at once because the column it asks for no longer exists. Then the new column is added alongside the old one, and both versions of the code keep working.

Press Run: the one-step rename crashes the old code, the additive change does not

The general principle is to prefer additive changes, such as new columns and new tables, that old code can simply ignore, over destructive ones such as renames, drops and type changes, that old code cannot survive. When a destructive change is truly needed, split it into several additive steps spread over several releases: add the new column, backfill it and write to both, move all readers over, and only then drop the old one.

Tracking Schema Version Explicitly

A migrations table recording exactly what has been applied
CREATE TABLE schema_migrations (
    version INTEGER PRIMARY KEY,
    description TEXT NOT NULL,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- every migration script checks this table before running, so
-- re-running the same migration twice is a safe no-op, not a repeated change

Each migration script checks this table first, so re-running one is a harmless no-op and not a repeated change.

CQRS: Splitting the Write Model From the Read Model

Think of a restaurant's kitchen and its menu board. The kitchen is organized for cooking correctly, with ingredients stored once. The board is organized for customers reading quickly, with everything pre-shaped. Command Query Responsibility Segregation (CQRS) does the same for data. The write side is the normalized schema everything in this course has favored, built for correctness. The read side is a separate, denormalized, pre-joined shape built for one specific query, updated asynchronously, meaning a moment after the write, not in the same step.

A write-optimized model and a separately-maintained, read-optimized model
-- Write model: normalized, correctness-focused
CREATE TABLE colony_records (id INTEGER PRIMARY KEY, resource_type TEXT, quantity INTEGER, colony_id INTEGER);

-- Read model: denormalized, pre-shaped exactly for the dashboard query,
-- updated asynchronously (by an event handler, a scheduled job, etc.)
CREATE TABLE colony_dashboard_view (
    colony_id INTEGER PRIMARY KEY,
    colony_name TEXT NOT NULL,          -- duplicated from a colonies table
    total_oxygen DECIMAL(10,2),
    total_food DECIMAL(10,2),
    last_updated TIMESTAMP
);

The demo shows the catch. Two oxygen readings are written to the normalized model. The dashboard is checked straight away and has nothing yet. After the sync runs, it shows the total of 65.

Press Run: the read model is briefly behind the write model

This is denormalization taken to its limit: not just a duplicated column, but a whole separate schema shaped around reads. It is powerful, but it is real added complexity, with two schemas to keep in sync and reads that are only eventually consistent. It needs a genuine reason, like every technique in this module.

CQRS's asynchronous sync means the read model can be briefly stale: a write that just happened might not show on the dashboard for a few seconds. That eventual-consistency trade-off must be an explicit, acceptable choice for the use case, not a surprise discovered later.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design Colony Data, Quantum Data, and the Cooper Station capstone schema in the LLD Lab's Interstellar act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!