Schema evolution, migrations and compatibility

Why schemas must evolve without stopping the world

Every piece of stored or transmitted data has a schema: the tables and column types of a database, the fields of a JSON API response, the record definition of an event on a stream. Schemas change constantly, because the business does: the bank adds a second account holder, the food-delivery platform starts recording tips, a regulator demands a new field. The architectural question is how to change a schema while the system keeps running, when the old shape is still in use by code, by stored data and by other teams.

Three facts make this hard. First, in any system deployed without downtime, old and new versions of the code run side by side: a rolling deployment replaces instances one at a time, a canary sends a small share of traffic to the new version, and a rollback brings the old version back. Every schema state must therefore work with at least two versions of the code. Second, data outlives code. Rows written five years ago, events in a log retained forever, messages waiting in a queue and backups all keep the old shape. Third, schemas are shared: a column is read by reports, extract jobs and sometimes other services, and an event schema is read by consumers the producer may not even know about. Section 7.1 argued for owning data behind an interface; schema evolution is where that discipline pays off, because a private schema can be changed with one team's agreement.

Backward, forward and full compatibility

Compatibility is defined in terms of readers and writers. A change is backward compatible when code using the new schema can read data written with the old one. Adding an optional field with a default is backward compatible: the new reader supplies the default when the old data lacks the field. A change is forward compatible when code using the old schema can read data written with the new one. That requires old readers to ignore fields they do not know, which is the tolerant reader discipline. A change is fully compatible when it is both, which means readers and writers can be upgraded in any order.

The direction you need follows from who upgrades first. If consumers upgrade before producers (common when a stream has a few consumers that you control), you need backward compatibility. If producers upgrade first, which is the usual case for a database (new code starts writing before every reader has been redeployed) and for public APIs, you need forward compatibility. When you cannot control the order, or need to roll back either side, aim for full compatibility. Confluent Schema Registry, for example, defaults to backward compatibility checked against the previous version, and offers transitive variants that check against every earlier version, which matters when consumers may replay old data from the beginning of a log.

A word of warning on vocabulary. In API design, backward compatible is often used loosely to mean existing clients keep working, which in reader and writer terms is forward compatibility for the clients that read your responses and backward compatibility for the requests you read from them. When a discussion turns on the term, ask which side changes and which side must keep working.

Expand and contract: the core technique

Most breaking changes can be split into a sequence of compatible ones. The pattern is called expand and contract, or parallel change (Danilo Sato's 2014 description on martinfowler.com names the phases expand, migrate and contract). First expand: add the new structure alongside the old, so that both old and new code work. Then migrate: change writers to maintain both, copy existing data across, and move readers to the new structure one by one. Finally contract: once nothing uses the old structure, remove it. Each step is deployed separately, each is compatible with the version before it, and each can be rolled back without losing data.

The cost is time and temporary mess: the change spans several releases, and for days or weeks the schema carries both shapes. The benefit is that no release ever needs downtime, no deployment needs to be coordinated across teams, and every step can be paused. Scott Ambler and Pramod Sadalage's Refactoring Databases (2006) catalogues dozens of database refactorings built on the same idea, with a transition period during which old and new schemas coexist, kept in step by the application or by triggers.

-- Migration V41 (expand): metadata-only change, no table rewrite in PostgreSQL 11+
SET lock_timeout = '3s';            -- fail fast rather than queue behind long queries
ALTER TABLE restaurants ADD COLUMN postal_code text;

-- Backfill job (run repeatedly by a script, not inside one big migration)
UPDATE restaurants
SET    postal_code = postcode
WHERE  id >= :from_id AND id < :from_id + 5000   -- one small batch per transaction
AND    postal_code IS DISTINCT FROM postcode;

-- Verify before switching reads
SELECT count(*) FROM restaurants WHERE postal_code IS DISTINCT FROM postcode;  -- expect 0

-- Migration V44: enforce NOT NULL without a long blocking scan
ALTER TABLE restaurants ADD CONSTRAINT postal_code_nn CHECK (postal_code IS NOT NULL) NOT VALID;
ALTER TABLE restaurants VALIDATE CONSTRAINT postal_code_nn;  -- scans, but allows writes
ALTER TABLE restaurants ALTER COLUMN postal_code SET NOT NULL;  -- PostgreSQL 12+: uses the check, skips scan
ALTER TABLE restaurants DROP CONSTRAINT postal_code_nn;

-- Migration V45 (contract), weeks later, once nothing reads postcode
ALTER TABLE restaurants DROP COLUMN postcode;

Migrations as code in the delivery pipeline

Database changes belong in version control next to the application code, as an ordered series of migrations, each a small script that moves the schema from one version to the next. Pramod Sadalage and Martin Fowler's Evolutionary Database Design (first published 2003, rewritten 2016) set out the practice, and the tools are mature. Flyway runs versioned SQL files such as V41__add_postal_code.sql in order and records each in a flyway_schema_history table. Liquibase describes changes as changesets in SQL, XML, YAML or JSON, tracked in a DATABASECHANGELOG table. Alembic, for Python and SQLAlchemy, generates revision scripts with upgrade() and downgrade() functions and records the current revision in alembic_version. Flyway and Liquibase also store a checksum of every applied migration and stop with an error if one has since been edited, which protects environments from silently diverging.

In a continuous delivery pipeline, the same migrations run against a fresh database in tests, then against staging, then production, so production never receives a change that has not been rehearsed. Good practice adds a few rules. Never edit a migration that has been applied anywhere; write a new one. Keep schema changes and the code that depends on them in separate deployments, with the schema change first for expansions and last for contractions. Test migrations against a copy of production-sized data, because a statement that takes 50 ms on a test database can lock a large table for an hour. Prefer rolling forward with a new migration over down-migrations that drop columns and with them data. And let the database tell you about locks: in PostgreSQL an ALTER TABLE waiting for its lock blocks every query that arrives after it, so set a short lock_timeout and retry rather than queueing behind a long-running report.

Large backfills and online schema changes

A backfill fills a new column or table for existing rows. Done as one UPDATE over 400 million transactions, it holds locks for hours, generates a vast amount of write-ahead log that replicas must replay (causing replication lag), and in PostgreSQL leaves dead row versions that vacuum must clean. The professional approach is a separate, restartable job that processes small batches by primary-key range, commits each batch, records its progress, throttles itself when replication lag or database load rises, and is idempotent so that re-running a batch does no harm. Run it outside the migration tool, so that a deployment never waits on it.

Some changes cannot be made in place without locking. PostgreSQL offers CREATE INDEX CONCURRENTLY (which cannot run inside a transaction block, so migration tools need to be told) and the NOT VALID then VALIDATE CONSTRAINT sequence shown above. For MySQL, tools such as gh-ost and pt-online-schema-change build a shadow copy of the table with the new schema, keep it in step with ongoing writes and swap it in with a brief rename. Changing a column's type is usually best done as an expand and contract onto a new column rather than an in-place ALTER, which in most databases rewrites the whole table.

Schema registries, Avro and Protocol Buffers

For events and messages, the equivalent of a migration tool is a schema registry. Producers register each schema version; the registry checks it against the configured compatibility rule and rejects a breaking change at build or deploy time rather than in production. Each message carries a short schema identifier, so consumers can fetch the exact schema it was written with.

Apache Avro makes the writer's schema explicit: decoding needs both the writer's schema and the reader's schema, and Avro's resolution rules match fields by name, ignore writer fields the reader does not know, and fill reader fields missing from the data with their declared defaults. Hence the practical rule: give every field you might add or remove a default. In the example below, currency and tip_pence were added in later versions with defaults, so old events still decode with new consumers.

Protocol Buffers identify fields by number, not name, on the wire. Adding fields is safe, and old readers keep unknown fields when re-serialising. Renaming a field does not affect the binary encoding (though it does change the JSON form). The cardinal rules are never to change or reuse a field number, and to mark the numbers and names of deleted fields as reserved so that nobody reuses them by accident. Confluent recommends backward-transitive compatibility for Protobuf, because adding a new message type is not forward compatible. JSON Schema can be registered and checked too, but JSON's openness (is an unknown property allowed or not?) makes compatibility rules subtler.

{
  "type": "record",
  "name": "OrderPlaced",
  "namespace": "food.orders.v1",
  "fields": [
    {"name": "order_id",       "type": "string"},
    {"name": "restaurant_id",  "type": "string"},
    {"name": "total_pence",    "type": "long"},
    {"name": "currency",       "type": "string", "default": "GBP"},
    {"name": "tip_pence",      "type": ["null", "long"], "default": null}
  ]
}

Evolving API and event schemas

APIs and events follow the same principles with different tools. For APIs, prefer additive change, require clients to be tolerant readers, and keep breaking changes for a new version run alongside the old one, with usage monitoring to see when the old version can be retired (Section 6.5 covers versioning approaches). Consumer-driven contract tests let each consumer publish the fields it actually relies on, so the provider knows which changes are safe.

Events are harder than APIs because they are stored: a consumer may replay a topic from the beginning, and an event-sourced system (Section 7.5) rebuilds state from events written years ago. Treat a published event schema as a public contract, version it in the registry, and when a change is genuinely breaking, publish a new event type or topic (OrderPlaced.v2) for a period alongside the old one rather than mutating the existing one. Section 6.4 discusses event design more generally.