Chapter 13: Evolving Schemas Safely in Production

Why this matters

Chapter 1 opened with a schema change that broke every existing record because there was no plan for evolving the schema safely. This chapter is that plan: schema migrations on a live, production system with real traffic and real data are genuinely risky if done naively — locking a large table for a schema change can cause a full application outage, and a change that isn't backward-compatible can break currently-running application code mid-deployment. Both failure modes are well-understood and entirely preventable.

Why naive schema changes are dangerous in production

[SENIOR INSIGHT] Many schema changes that look trivial in a small development database become genuinely dangerous at production scale, because of how the underlying database implements them:

-- This looks simple, but on a LARGE table in older PostgreSQL versions (pre-11),
-- this could require rewriting every single row and holding an exclusive lock
-- on the table for the ENTIRE duration -- blocking every read AND write to this
-- table for however long that rewrite takes, potentially minutes on a huge table.
ALTER TABLE orders ADD COLUMN discount_cents INTEGER NOT NULL DEFAULT 0;

[IN PRACTICE] Modern PostgreSQL (11+) specifically optimizes this exact case (adding a column with a constant default) to be nearly instantaneous, avoiding the table rewrite — but the general principle remains critical and applies to many other kinds of changes (adding a NOT NULL constraint to an existing column with pre-existing data, changing a column's data type, adding certain kinds of indexes without the CONCURRENTLY option): always know, for your specific database version and specific change, whether it requires a blocking rewrite/lock or not — assuming safety without checking is precisely how a routine-seeming migration causes an unplanned outage.

-- Building an index WITHOUT CONCURRENTLY locks the table against writes for the
-- entire build -- dangerous on a large, actively-written table:
CREATE INDEX idx_orders_status ON orders(status);  -- BLOCKS writes during the build

-- WITH CONCURRENTLY: takes longer overall, but does NOT block reads or writes
-- during the build -- the standard, safe choice for production tables:
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

The expand-contract pattern: changing a schema without breaking running code

[BEGINNER] A subtler problem than locking: during a deployment, there's almost always a window where old application code and new application code are both running simultaneously (a rolling deployment updates servers one at a time, not all at once). A schema change that the old code doesn't understand can break it during exactly this window — even if the migration itself doesn't lock anything.

The standard solution is the expand-contract pattern (also called parallel change), splitting a schema change into safe, independently-deployable steps:

Scenario: renaming a column from "email" to "email_address"

NAIVE (unsafe) approach: rename the column directly.
-- The INSTANT this runs, any old application code still running (mid-deployment)
-- that references the OLD column name "email" starts failing immediately.

EXPAND-CONTRACT (safe) approach, as separate deployable steps:

Step 1 (EXPAND): add the NEW column, don't remove the old one yet.
ALTER TABLE customers ADD COLUMN email_address VARCHAR(255);
-- Both "email" and "email_address" now exist. Old code (using "email") still works fine.

Step 2: deploy application code that WRITES to both columns, but still READS
from the old one -- and backfill email_address for existing rows.
UPDATE customers SET email_address = email WHERE email_address IS NULL;

Step 3: deploy application code that READS from and WRITES to the NEW column
only -- at this point, every running instance of the application (old code
should be fully rolled out of the fleet by now) uses "email_address."

Step 4 (CONTRACT): once confident no code anywhere still references the old
column, remove it.
ALTER TABLE customers DROP COLUMN email;

[SENIOR INSIGHT] The core principle underlying every step: never make a change that both old and new application code can't simultaneously tolerate, in a single deployable step. Each step above is independently safe to deploy and roll back, and the sequence is what accomplishes the full migration without ever forcing a moment where running code (of either version) breaks. This exact discipline — never combine changes that require simultaneous, atomic rollout — is the schema-migration-specific instance of a much more general safe-deployment principle covered in the companion Software Development Life Cycle book's discussion of rolling deployments and backward compatibility.

Common mistakes

1. Assuming a schema change is safe on a large production table just because it works fine on a small development database — table size and lock behavior interact in ways that only manifest at real production scale.
2. Renaming or dropping a column directly, without the expand-contract pattern, breaking any application instance still running old code during a rolling deployment window.
3. Not using CONCURRENTLY (or the equivalent for your database) when adding an index to a large, actively-written production table, causing an unplanned, full write-blocking outage for the duration of the index build.
4. Adding a NOT NULL constraint to an existing column with pre-existing data without first ensuring every existing row already has a valid, non-null value — the constraint addition will fail (or, worse in some systems, silently corrupt data) if any existing row violates it.

Checklist: evolving schemas safely

1. Before any schema change on a production table, verify whether it requires a blocking lock/rewrite for your specific database version — don't assume based on how the change behaves on a small development database.
2. Use CREATE INDEX CONCURRENTLY (or equivalent) for any index addition on a live, actively-written table.
3. Use the expand-contract pattern for any change that isn't simultaneously compatible with both old and new application code — column renames, type changes, removing a column still referenced anywhere.
4. Backfill data for a new column before making it NOT NULL, and verify no existing rows would violate the constraint before adding it.
5. Test schema migrations against a realistic copy of production data volume, not just a small development dataset, since lock/rewrite behavior is often exactly what only manifests at real scale.

Discussion / interview questions

1. "Why can adding a column to a large production table be dangerous, even though it works instantly on a small development database?" Strong answer: Depending on the database version and the specific change (e.g., adding a NOT NULL column with a non-constant default, in older PostgreSQL versions), the operation may require rewriting every existing row while holding a blocking lock — a cost that's invisible on a small table but can cause a real, extended outage on a large one.

2. "What is the expand-contract pattern, and what problem does it solve?" Strong answer: Splits a schema change into multiple independently-deployable steps (add new, dual-write, migrate reads, remove old) so that both old and new application code can coexist safely during a rolling deployment — solving the problem of a schema change breaking whichever version of the application code doesn't yet know about it.

3. "Why is CREATE INDEX CONCURRENTLY important on a production table?" Strong answer: A regular CREATE INDEX blocks writes to the table for the entire duration of the index build; CONCURRENTLY avoids this lock (at the cost of a longer overall build time), making it the safe choice for any live, actively-written production table.

4. "You need to rename a column used throughout a large application with rolling deployments. Walk through how you'd do this safely." Strong answer: Describes the expand-contract sequence explicitly — add the new column, deploy code that dual-writes to both while still reading the old one, backfill existing data, deploy code that reads/writes only the new column, then finally drop the old column once confident no code references it anymore.

Chapter summary / cheat sheet

• Not every schema change is instant, even one that looks simple — know whether a specific change requires a blocking lock/table rewrite for your database version before running it on a production table.
• Use CREATE INDEX CONCURRENTLY (or equivalent) for index additions on live tables to avoid blocking writes during the build.
• The expand-contract pattern (add new → dual-write/backfill → migrate reads → remove old) safely evolves a schema across a rolling deployment, where old and new application code run simultaneously for a window.
• Never make a schema change that both currently-running application code versions can't simultaneously tolerate in one atomic step — split it into safe, independently-deployable stages instead.

---