Migrations & Zero-Downtime Schema Evolution — SQL Course

What you'll learn

Treat schema changes as versioned releases and use expand-contract techniques to stay compatible during rolling deployments. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Migration history to a realistic data question
Apply Transactional DDL to a realistic data question
Apply Expand-contract to a realistic data question
Apply Backfills to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Migration	An ordered versioned transition	Never edit history already applied to shared environments
Expand-contract	Add compatible shape before removing old shape	Assume old and new application versions overlap
Backfill	Populating new data for existing rows	Run in bounded, observable batches with resumable progress

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the compatible schema migration question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Evolve a required column in stages

The expansion adds nullable shape and default, the application dual-writes, a batch job backfills, then a later release enforces NOT NULL.

042_add_order_channel.sql

ALTER TABLE sales.orders ADD COLUMN channel text;
ALTER TABLE sales.orders ALTER COLUMN channel SET DEFAULT 'web';

-- Deploy writers, backfill in bounded batches, verify no NULL remains.
ALTER TABLE sales.orders
  ADD CONSTRAINT orders_channel_valid CHECK (channel IN ('web','mobile','partner')) NOT VALID;
ALTER TABLE sales.orders VALIDATE CONSTRAINT orders_channel_valid;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Adding a required column and immediately depending on it can break old application instances and hold long locks on large tables.

Independent workshop

Build a review-ready compatible schema migration lab against the course commerce dataset.

Your finished workshop must include:

Migration history
Transactional DDL
Expand-contract
Backfills
Concurrent indexes
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Migration: Never edit history already applied to shared environments
Expand-contract: Assume old and new application versions overlap
Backfill: Run in bounded, observable batches with resumable progress

Quick check

1. Which rule best applies to Migration?

2. Which rule best applies to Expand-contract?

3. Which rule best applies to Backfill?

Next: Phase Project: Tune & Secure a Transactional System