← Back to all projects

DATABASE SCHEMA EVOLUTION MASTERY

In the early days of web development, maintenance windows were acceptable. You'd shut down the app, run `ALTER TABLE`, and hope for the best. Today, a 5-minute outage for a major service can cost millions in revenue and irreparable brand damage.

Learn Database Schema Evolution: From Zero to Zero-Downtime Master

Goal: Deeply understand the mechanics of mutating production data structures without interrupting service. You will move from simple SQL scripts to building sophisticated migration engines that handle “expand/contract” patterns, “ghost table” migrations, and high-concurrency data backfills—the exact skills used at companies like Stripe, GitHub, and Shopify to manage massive, live datasets.

Why Database Schema Evolution Matters

In the early days of web development, “maintenance windows” were acceptable. You’d shut down the app, run ALTER TABLE, and hope for the best. Today, a 5-minute outage for a major service can cost millions in revenue and irreparable brand damage.

Database Schema Evolution is the “Heart Surgery” of software engineering: you are changing the internal structure of the system while it is still beating.

Understanding this unlocks:

  • True Continuous Delivery: Shipping code and schema changes 50 times a day.
  • Architectural Seniority: Moving beyond “it works on my machine” to “it works for 100k requests per second.”
  • Data Integrity Mastery: Ensuring that during a transition, no data is lost and “Version A” code can still read “Version B” data.

Core Concept Analysis

1. The Expand/Contract Pattern (The Golden Rule)

This is the fundamental strategy for zero-downtime changes. You never change a column in place; you add, migrate, then remove.

PHASE 1: STABLE      PHASE 2: EXPAND      PHASE 3: MIGRATE     PHASE 4: CONTRACT
┌─────────────┐      ┌─────────────┐      ┌─────────────┐      ┌─────────────┐
│  Table V1   │      │  Table V1   │      │  Table V1   │      │  Table V2   │
│ [Old Col]   │ ───> │ [Old Col]   │ ───> │ [Old Col]   │ ───> │ [New Col]   │
└─────────────┘      │ [New Col]   │      │ [New Col]   │      └─────────────┘
                     └─────────────┘      └─────────────┘
                                                ↑
                                         (Dual Writing)

2. DDL Locking & The “Hidden” Downtime

Many developers think ADD COLUMN is safe. In reality, depending on the database engine (Postgres/MySQL) and the version, certain operations take an ACCESS EXCLUSIVE lock. If a table has 10 million rows, a “fast” operation might still block the entire site for 30 seconds while waiting for active transactions to finish.

Active Trans 1: [SELECT...] (Holds Shared Lock)
Active Trans 2: [UPDATE...] (Holds Shared Lock)
Migration:      [ALTER TABLE...] (Requests Exclusive Lock) -- BLOCKED!
Active Trans 3: [SELECT...] (Requests Shared Lock)    -- BLOCKED by Migration!
...Site goes down as connection pool exhausts...

3. The “Ghost Table” Strategy

For massive tables, you don’t alter the table at all. You create a “ghost” copy, sync data via triggers or binlogs, and then swap the names.

1. Original Table [A]
2. Ghost Table    [A_gh] (New Schema)
3. Shadow Copying: [A] ─── (Triggers) ───> [A_gh]
4. Final Swap:     [A] <─── (Atomic) ───> [A_gh]

Concept Summary Table

Concept Cluster What You Need to Internalize
Backward Compatibility The database must always support the previous version of the application code.
Lock Contention DDL operations block DML. You must know which commands are “online” and which are “offline”.
Idempotency Migrations must be safe to run multiple times or fail halfway without leaving the DB in an unknown state.
Dual-Writing The application writes to both the old and new columns/tables during a transition period.
Backfilling Moving historical data from old structures to new ones without overloading the CPU/IO.

Deep Dive Reading by Concept

Foundational Principles

Concept Book & Chapter
Evolutionary Database Design “Refactoring Databases” by Scott Ambler & Pramod Sadalage — Ch. 1: “Evolutionary Database Development”
Online Schema Changes “Designing Data-Intensive Applications” by Martin Kleppmann — Ch. 4: “Encoding and Evolution”

Engine Specifics (Postgres focus)

Concept Book & Chapter
Locking & Concurrency “PostgreSQL 14 Administration Cookbook” by Simon Riggs — Ch. 6: “Transactions, Locking, and Concurrency”
Table Partitioning “Mastering PostgreSQL 15” by Hans-Jürgen Schönig — Ch. 9: “Partitioning and Logical Replication”

Essential Reading Order

  1. The Core Philosophy (Week 1):
    • Refactoring Databases Ch. 1-2 (The mindset shift).
    • DDIA Ch. 4 (Why backward compatibility is hard).
  2. The Mechanics of Locks (Week 2):
    • PostgreSQL Documentation: “Explicit Locking” (Wait queues and lock modes).

Project 1: The Linear Migration Engine (The Fundamentals)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Go
  • Alternative Programming Languages: Rust, Python, Node.js
  • Coolness Level: Level 2: Practical but Forgettable
  • Business Potential: 1. The “Resume Gold”
  • Difficulty: Level 1: Beginner
  • Knowledge Area: File I/O, SQL Execution, State Management
  • Software or Tool: SQLite or PostgreSQL
  • Main Book: “Database Internals” by Alex Petrov (Ch. 1-2 for context)

What you’ll build: A CLI tool that reads a folder of .sql files, tracks which ones have been executed in a schema_migrations table, and ensures they run in order and within a transaction.

Why it teaches evolution: You learn the “source of truth” problem. How does the system know it’s at Version 5? This is the foundation of all evolution tools.

Core challenges you’ll face:

  • Atomic Execution → Ensuring a migration either completely succeeds or leaves no trace (Transactions).
  • State Tracking → Designing the metadata table that records version, checksum, and execution time.
  • Dependency Management → Preventing Migration #5 from running before Migration #4.

Real World Outcome

You will have a CLI tool called migrator that can “up” or “down” your database schema.

Example Output:

$ ./migrator status
Current Version: 0 (Dirty: false)
Pending: 001_create_users.sql, 002_add_email_index.sql

$ ./migrator up
Executing 001_create_users.sql... SUCCESS
Executing 002_add_email_index.sql... SUCCESS
Database at Version: 2

$ psql -c "SELECT * FROM schema_migrations;"
 version |            name            | applied_at
---------+----------------------------+----------------------------
       1 | 001_create_users.sql       | 2025-12-28 10:00:00
       2 | 002_add_email_index.sql    | 2025-12-28 10:00:05

The Core Question You’re Answering

“How do we ensure that every developer and production server has exactly the same database structure without manual intervention?”

Before you write code, ask: If two migrations modify the same table, can I run them concurrently? Why must I record the checksum of the migration file?

Concepts You Must Understand First

Stop and research these before coding:

  1. ACID Transactions
    • What happens to a CREATE TABLE inside a BEGIN; ... COMMIT; block if it fails?
    • Book Reference: “Operating Systems: Three Easy Pieces” Ch. 30.
  2. Database Metadata
    • How do I check if a table exists using SQL only (information_schema)?

Questions to Guide Your Design

  1. The “Dirty” State
    • If a migration fails halfway, how does your tool prevent future runs?
  2. Version Sequencing
    • Should you use timestamps (202512281000.sql) or integers (001.sql)? What happens if two developers create “005.sql” at the same time?

Thinking Exercise

The Checksum Dilemma

Imagine you run 001_add_column.sql on Production. A week later, you realize you made a typo in the file and change it on your local machine.

Questions:

  • Does the production server know the file changed?
  • If it runs again, what happens?
  • How would a cryptographic hash (SHA256) of the file contents solve this?

The Interview Questions They’ll Ask

  1. “How do you handle a migration that fails in production halfway through?”
  2. “Why is it better to use timestamps than incremental integers for migration versions in a team environment?”
  3. “What is a ‘idempotent’ migration?”
  4. “How do you handle rolling back a migration that dropped a column?” (Trick question: you can’t easily!)
  5. “What is the information_schema?”

Hints in Layers

Hint 1: The Metadata Table Start by creating a table named schema_migrations with two columns: version (int) and applied_at (timestamp).

Hint 2: The Logic Loop Read the files in the directory. For each file, check if its name/version exists in your table. If not, read the string and execute it using your DB driver’s Exec method.

Hint 3: Transactions are Key Wrap the file execution AND the metadata update in the SAME transaction. If the metadata update fails, the table change should roll back.

Books That Will Help

Topic Book Chapter
Transactional DDL “PostgreSQL High Performance” by Gregory Smith Ch. 12
CLI Tool Design “Build Every-day CLI Tools in Go” by Ricardo Gerardi Ch. 1-3

Project 2: The “Dangerous SQL” Linter (Lock Prevention)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Python or Go
  • Alternative Programming Languages: Rust, TypeScript
  • Coolness Level: Level 3: Genuinely Clever
  • Business Potential: 3. The “Service & Support” Model
  • Difficulty: Level 2: Intermediate
  • Knowledge Area: SQL Parsing, AST (Abstract Syntax Trees)
  • Software or Tool: PostgreSQL, pglast (Python) or pg_query_go
  • Main Book: “PostgreSQL 14 Administration Cookbook” (Ch. 6: Locking)

What you’ll build: A static analysis tool that scans migration files and flags “Dangerous” operations that cause table-wide locks or long-running rewrites (e.g., adding a column with a default value in older Postgres, or creating an index without CONCURRENTLY).

Why it teaches evolution: It forces you to learn the internal implementation of DDL commands. You stop treating SQL as magic and start seeing it as a set of instructions that may or may not require a full table rewrite.

Core challenges you’ll face:

  • Parsing SQL → Converting raw strings into an AST to identify commands (e.g., distinguishing ALTER TABLE ADD COLUMN from ALTER TABLE ALTER COLUMN).
  • Engine-Specific Rules → Understanding that ADD COLUMN ... DEFAULT is safe in Postgres 11+ but deadly in Postgres 10.
  • Context Awareness → Detecting if an index is being created on a small table (safe) vs a potentially large table (dangerous).

Real World Outcome

A CI/CD check that blocks pull requests containing unsafe SQL.

Example Output:

$ ./sql-linter migrations/003_add_bio.sql

[ERROR] Line 1: 'ALTER TABLE users ADD COLUMN bio text DEFAULT 'N/A';'
  -> Dangerous on Postgres < 11. This will rewrite the entire table.
  -> Solution: Add column as NULL, then update in batches.

[WARNING] Line 4: 'CREATE INDEX idx_user_email ON users(email);'
  -> Dangerous: This blocks writes to the table.
  -> Solution: Use 'CREATE INDEX CONCURRENTLY'.

The Core Question You’re Answering

“Is this single line of SQL going to crash my production database when it’s under load?”

A 100-row table migrates in 1ms. A 100-million-row table migrates in 2 hours and locks the world. Your tool helps bridge this knowledge gap.

Concepts You Must Understand First

  1. Lock Modes
    • What is the difference between SHARE UPDATE EXCLUSIVE and ACCESS EXCLUSIVE?
  2. Table Rewrites
    • Which operations change the actual file on disk for every row?

Project 3: The Expand/Contract Orchestrator (Application Integration)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Java or C#
  • Alternative Programming Languages: Go, Python
  • Coolness Level: Level 3: Genuinely Clever
  • Business Potential: 2. The “Micro-SaaS / Pro Tool”
  • Difficulty: Level 3: Advanced
  • Knowledge Area: Application State, Multi-Version Compatibility
  • Software or Tool: Redis (for feature flags), PostgreSQL
  • Main Book: “Refactoring Databases” by Ambler & Sadalage (Ch. 5: Structural Refactorings)

What you’ll build: A framework (or a set of middleware) that manages a “Dual Write” phase. It will allow you to transition from a single name column to first_name and last_name columns without stopping the app.

Why it teaches evolution: You learn that the DB change is only 20% of the work. The real difficulty is the application code that must gracefully handle the “In-Between” state where both old and new structures exist.

Core challenges you’ll face:

  • Dual Writing → Updating your ORM/DAO to write to both the old and new columns.
  • Graceful Degradation → Code that tries to read from the new column, but falls back to the old one if it’s null.
  • Consistency → Ensuring that if one write fails, the entire operation rolls back.

Real World Outcome

An application that continues to function perfectly while you are fundamentally changing its data structure.

Scenario Trace:

  1. App V1: Reads/Writes name.
  2. App V2 (Deploying): Writes to name AND first_name/last_name. Reads from name.
  3. App V3: Reads from first_name/last_name. If missing, reads from name.
  4. App V4: Only reads/writes first_name/last_name.

Thinking Exercise

The Split Name Problem

You have a full_name column. You want to split it into first_name and last_name.

Questions:

  • What happens if a user updates their name between the “Expand” phase and the “Backfill” phase?
  • How do you handle names that don’t fit the First Last pattern (e.g., “Prince” or “Jean-Luc Picard”)?
  • At what point is it safe to delete the full_name column?

Project 4: The Batch Backfiller (Safe Data Migration)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Go
  • Alternative Programming Languages: Elixir, Python
  • Coolness Level: Level 2: Practical but Forgettable
  • Business Potential: 1. The “Resume Gold”
  • Difficulty: Level 2: Intermediate
  • Knowledge Area: Concurrency, Throttling, Batch Processing
  • Software or Tool: PostgreSQL
  • Main Book: “High Performance MySQL” by Baron Schwartz (Ch. 11: Scaling)

What you’ll build: A tool that migrates data from an old column to a new column in chunks of 1000 rows, sleeping between chunks to allow the DB to “breathe” and prevent replication lag.

Why it teaches evolution: It teaches you how to manage the “MIGRATE” phase of the Expand/Contract pattern. You learn about throughput vs. latency and the impact of long-running transactions.

Core challenges you’ll face:

  • Iterative Updates → Using primary key ranges (WHERE id > ? AND id <= ?) instead of OFFSET/LIMIT (which is slow for large datasets).
  • Throttling → Monitoring DB load (CPU/Replication Lag) and slowing down if the DB is stressed.
  • Resumability → If the process crashes at row 5,000,000, it should restart from 5,000,001.

Real World Outcome

A script that can migrate 100 million rows over several hours without ever causing a “Database is busy” error for users.

Example Output:

$ ./backfill --table users --source full_name --target first_name,last_name
[INFO] Total rows to process: 1,500,000
[PROGRESS] 1000/1,500,000 (0.1%) - Latency: 45ms - DB Load: 12%
[PROGRESS] 2000/1,500,000 (0.2%) - Latency: 38ms - DB Load: 15%
...
[INFO] Migration Complete. 0 errors.

Project 5: The Ghost-Table Migrator (Shadow Migration)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: C or Go
  • Alternative Programming Languages: Rust
  • Coolness Level: Level 5: Pure Magic (Super Cool)
  • Business Potential: 5. The “Industry Disruptor”
  • Difficulty: Level 4: Expert
  • Knowledge Area: Database Internals, Logical Replication, Triggers
  • Software or Tool: MySQL or PostgreSQL
  • Main Book: “MySQL High Availability” by Charles Bell (Ch. 10: Replication)

What you’ll build: A tool that implements the “Online Schema Change” pattern. It creates a shadow table, copies existing data, and uses database triggers (or binlog streaming) to keep the shadow table in sync while the copy is happening. Finally, it performs an atomic swap.

Why it teaches evolution: This is the “God Tier” of database evolution. You deal with the most extreme concurrency issues: what if a row is updated while you are copying it? How do you swap tables without losing a single transaction?

Core challenges you’ll face:

  • Trigger-Based Syncing → Creating INSERT/UPDATE/DELETE triggers on the original table that propagate changes to the ghost table immediately.
  • Atomic Cutover → Using a single SQL command (like RENAME TABLE A TO A_old, A_gh TO A) to swap the tables in a way that the application never sees an empty table.
  • Handling Foreign Keys → Managing dependencies that point to the old table.

Real World Outcome

A tool capable of altering a 1TB table without taking the site offline or locking the table for more than a few milliseconds.

The “Magic” Swap:

  1. App is reading from users.
  2. You run ./ghost_migrate --table users --alter "ADD COLUMN age INT".
  3. Tool builds users_gh, copies data, triggers keep it fresh.
  4. Swap!
  5. App is now reading from users (which was previously users_gh) and it has the age column.

The Core Question You’re Answering

“How can I perform a 10-hour migration without locking the table for 10 hours?”

Before you write code, research how gh-ost (GitHub) and pt-online-schema-change (Percona) handle the final “swap” to ensure it’s truly atomic.

Project 6: Zero-Downtime Indexer (Concurrent Operations)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Go or Python
  • Alternative Programming Languages: Bash (using CLI tools)
  • Coolness Level: Level 3: Genuinely Clever
  • Business Potential: 3. The “Service & Support” Model
  • Difficulty: Level 2: Intermediate
  • Knowledge Area: Concurrency, Database Locks
  • Software or Tool: PostgreSQL
  • Main Book: “PostgreSQL 14 Administration Cookbook” (Ch. 11: Performance and Tuning)

What you’ll build: A specialized runner that manages the lifecycle of CREATE INDEX CONCURRENTLY.

Why it teaches evolution: CONCURRENTLY is special because it cannot be run inside a transaction. This breaks most standard migration tools. You’ll learn how to handle “Non-Transactional” migrations and how to recover if the index creation fails (leaving an “invalid” index behind).

Core challenges you’ll face:

  • Transaction Management → Explicitly opening/closing connections because the command requires its own session.
  • Cleanup Logic → Detecting “Invalid” indexes in Postgres metadata and dropping them before retrying.
  • Status Monitoring → Polling the DB to see if the index build is still in progress.

Real World Outcome

A robust script that adds indexes to production tables with zero write-blocking.

Example Output:

$ ./index-tool add --table orders --column created_at
[STEP 1] Checking for existing indexes... None.
[STEP 2] Running CREATE INDEX CONCURRENTLY... (This may take time)
[STEP 3] Monitoring progress... 45% complete.
[STEP 4] Monitoring progress... 100% complete.
[SUCCESS] Index 'idx_orders_created_at' is valid and ready.

Project 8: Blue-Green Database Deployer (Replica Promotion)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Bash or Python
  • Alternative Programming Languages: Go
  • Coolness Level: Level 4: Hardcore Tech Flex
  • Business Potential: 4. The “Open Core” Infrastructure
  • Difficulty: Level 3: Advanced
  • Knowledge Area: DevOps, Replication, Traffic Switching
  • Software or Tool: PostgreSQL, HAProxy or PgBouncer
  • Main Book: “Continuous Delivery” by Jez Humble & David Farley (Ch. 12: Managing Data)

What you’ll build: A system that performs a blue-green migration. It creates a read-replica, applies migrations to the replica (where it doesn’t matter if tables are locked), and then promotes the replica to “Primary” and switches application traffic.

Why it teaches evolution: You learn how to use infrastructure to solve database problems. This is common in cloud environments (AWS RDS/Aurora) where you can “Clone” a database.

Core challenges you’ll face:

  • Data Catch-up → Ensuring the replica has all the data that was written to the primary while the migration was running.
  • Traffic Switching → Moving app connections from DB A to DB B without causing 500 errors (using a connection pooler like PgBouncer).
  • The “Split Brain” Problem → Preventing writes from going to both databases at the same time.

Project 9: The “Self-Healing” Migration (Dry-Run & Rollback)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Go
  • Alternative Programming Languages: Rust, Python
  • Coolness Level: Level 3: Genuinely Clever
  • Business Potential: 2. The “Micro-SaaS / Pro Tool”
  • Difficulty: Level 2: Intermediate
  • Knowledge Area: Testing, Transactions, Shadow Environments
  • Software or Tool: Docker, PostgreSQL
  • Main Book: “Database Reliability Engineering” by Laine Campbell (Ch. 5: Schema Management)

What you’ll build: A migration runner that automatically spins up a Docker container with a clone of the production schema, runs the migration there, and checks for success/errors before ever touching the real database.

Why it teaches evolution: It teaches the “Safety First” mindset. You learn that the best way to handle a failed migration in production is to never run a failing migration in production.

Core challenges you’ll face:

  • Environment Management → Orchestrating Docker containers and database snapshots.
  • Verification Logic → Automatically checking that the new schema matches the “Expected” schema.

Project 10: Chaos Migration Engine (Failure Injection)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Go or Python
  • Alternative Programming Languages: Bash
  • Coolness Level: Level 4: Hardcore Tech Flex
  • Business Potential: 1. The “Resume Gold”
  • Difficulty: Level 3: Advanced
  • Knowledge Area: Chaos Engineering, Resilience Testing
  • Software or Tool: PostgreSQL
  • Main Book: “Chaos Engineering” by Casey Rosenthal (Ch. 1-2)

What you’ll build: A tool that kills the database connection or shuts down the migrator at random points during a migration.

Why it teaches evolution: It forces you to build idempotent and transactional migration tools. If your migrator can’t handle a power failure during a 10-minute migration, it’s not production-ready.

Project Comparison Table

Project Difficulty Time Depth of Understanding Fun Factor
1. Linear Runner Level 1 Weekend Medium ⭐⭐⭐
2. SQL Linter Level 2 1 Week High (Internals) ⭐⭐⭐⭐
3. Expand/Contract Level 3 1-2 Weeks High (App Logic) ⭐⭐⭐⭐
5. Ghost Migrator Level 4 1 Month+ Master (Protocols) ⭐⭐⭐⭐⭐
7. Schema Proxy Level 4 1 Month+ Master (Network) ⭐⭐⭐⭐⭐
10. Chaos Engine Level 3 1 Week High (Resilience) ⭐⭐⭐⭐

Recommendation

If you are a Backend Engineer: Start with Project 3 (Expand/Contract Orchestrator). It solves the most common day-to-day friction in professional teams.

If you want to be a Database Reliability Engineer (DBRE): Focus on Project 2 (Linter) and Project 5 (Ghost Migrator). These are the tools that save companies from multi-million dollar outages.

Final Overall Project: The “Zero-Downtime Pipeline”

The Challenge: Build a complete CI/CD pipeline for database evolution.

  1. A developer submits a SQL migration.
  2. The SQL Linter (Project 2) checks for dangerous locks.
  3. The Self-Healing Runner (Project 9) tests the migration against a clone of production data.
  4. The Batch Backfiller (Project 4) generates a plan for moving data.
  5. The Ghost Migrator (Project 5) performs the change on production.
  6. The Chaos Engine (Project 10) is used in a staging environment to ensure the whole pipeline is indestructible.

Summary

This learning path covers Database Schema Evolution through 10 hands-on projects.

# Project Name Main Language Difficulty Time Estimate
1 Linear Migration Engine Go Beginner Weekend
2 “Dangerous SQL” Linter Python Intermediate 1 Week
3 Expand/Contract Orchestrator Java/C# Advanced 2 Weeks
4 Batch Backfiller Go Intermediate 1 Week
5 Ghost-Table Migrator C/Go Expert 1 Month+
6 Zero-Downtime Indexer Go Intermediate Weekend
7 The Schema Proxy Go Expert 1 Month+
8 Blue-Green DB Deployer Bash/Python Advanced 1 Week
9 Self-Healing Migration Go Intermediate 1 Week
10 Chaos Migration Engine Go Advanced 1 Week

Expected Outcomes

After completing these projects, you will:

  • Understand exactly how database locks (DDL vs DML) function.
  • Be able to architect schema changes that require zero downtime.
  • Know how to write application code that bridges the gap between DB versions.
  • Master the use of triggers and replication for live data migration.
  • Be capable of building production-grade tooling for large-scale engineering teams.

You’ll have built 10 working projects that demonstrate deep understanding of Database Schema Evolution from first principles.

Project 7: The Schema Proxy (Dynamic Translation)

  • File: DATABASE_SCHEMA_EVOLUTION_MASTERY.md
  • Main Programming Language: Go or Node.js
  • Alternative Programming Languages: Rust
  • Coolness Level: Level 4: Hardcore Tech Flex
  • Business Potential: 5. The “Industry Disruptor”
  • Difficulty: Level 4: Expert
  • Knowledge Area: Network Proxies, SQL Rewriting
  • Software or Tool: PostgreSQL Wire Protocol
  • Main Book: “Designing Data-Intensive Applications” (Ch. 12: The Future of Data Systems)

What you’ll build: A TCP proxy that sits between your app and the database. It intercepts SELECT queries and rewrites them on the fly based on the current migration state. (e.g., If the app asks for SELECT name FROM users, the proxy rewrites it to SELECT concat(first_name, ' ', last_name) AS name FROM users).

Why it teaches evolution: This explores the “Contract” part of Expand/Contract at the infrastructure level. It decouples the application code from the database structure completely.

Core challenges you’ll face:

  • Protocol Implementation → Handling the binary PostgreSQL wire protocol (startup packets, query packets, row data).
  • SQL Translation → Identifying which tables and columns are being accessed and applying rewrite rules without breaking complex JOINs.
  • Performance Overhead → Ensuring the proxy adds minimal latency to every query.