Expand and Contract: Schema Changes That Survive an Overlap

Rolling replacements, blue-green cutovers, and canaries all share one structural property: for a period of time two versions of the application are running, and both are talking to the same database. The old version has the old code and expects the old schema. The new version has the new code and expects the new schema. No deployment technique can avoid this overlap, so the schema itself must be a shape that both versions can use.

A schema change that satisfies that constraint almost never consists of a single statement. It is a sequence of three phases whose names (expand, migrate, contract) describe what the schema looks like at each point, and whose ordering is dictated by which operations can be performed without excluding readers.

Both versions read the same rows

The constraint can be stated precisely. During the overlap the schema must accept every operation either version performs, and every row any version reads must be interpretable by the version reading it. That rules out any change that removes or reinterprets an element the old code depends on: dropping a column, renaming one, changing a type in a way that changes its meaning, adding a NOT NULL constraint on a column the old version does not populate, and splitting one column into two without also writing the old column.

Additive changes are compatible in one direction only, and the direction matters. Adding a nullable column is compatible for old readers, which ignore it, and for new writers, which may or may not populate it. Adding a non-null column without a default is not compatible for old writers, which insert rows that omit it. The asymmetry is why the pattern is expressed in phases: the expand phase adds the new thing in a form nobody is required to use, the migrate phase makes both versions write it and fills in what already exists, and only after no running version needs the old thing does the contract phase remove it.

The middle phase is where the real work lives, and it usually has to be implemented in the application rather than in the database. During migrate, the new version writes both the old and the new representation; the old version writes only the old one; reads must tolerate both. This dual-write window ends when the last old-version instance is gone — which is the same instant that blue-green stops being able to roll back to blue, and the reason data changes belong on the far side of the point of no return discussed in rollback boundaries and irreversibility.

Triggers offer an alternative to application-level dual writes: a trigger on the table keeps the new column synchronised regardless of which version writes. It removes the need to ship dual-write code, at the cost of doing the work inside the database, where it consumes the same connection and lock budget as the rest of the transaction, and where it is invisible to application tests. Triggers also start existing at a moment determined by DDL, so their installation has its own lock cost.

Which DDL statements are cheap and which are not

The expensive operations are the ones that must read or rewrite every row while holding a lock that blocks writers, and the cost of that lock is not only its duration but the queue behind it. A pending request for an exclusive table lock blocks subsequent weaker lock requests, so a slow DDL statement can stall readers that would otherwise be compatible with it. For that reason, an explicit lock timeout before DDL is standard practice: better to fail the migration and retry than to queue every query in the service behind one statement.

The familiar cheap and expensive cases, stated as behaviour rather than as version trivia:

• Adding a nullable column is a catalogue change in the mainstream engines and takes a brief exclusive lock.
• Adding a column with a constant default is metadata-only in modern PostgreSQL, which records the default as a stored expression rather than rewriting rows; engines that lack this must rewrite the table, so the same statement can be instant or catastrophic depending on the server.
• Adding a volatile default, changing a type, or rewriting an expression forces a full rewrite with an exclusive lock.
• Creating an index normally blocks writes for the duration of the build. CREATE INDEX CONCURRENTLY in PostgreSQL avoids that by performing two table scans and waiting for transactions that could observe the index; it cannot run inside a transaction block, and if it fails it leaves an invalid index that must be dropped and rebuilt rather than repaired.
• Adding a NOT NULL constraint requires a scan to verify every row. PostgreSQL permits the verification to be done in advance by a CHECK constraint added as not-yet-validated and then validated separately, which takes a lock that does not block ordinary reads and writes, after which setting the column not-null can consult the validated constraint instead of scanning.
• Dropping a column is usually a catalogue change in modern engines, but in some it rewrites or at least leaves the data physically present, and it is irreversible for readers regardless.

MySQL’s InnoDB has its own taxonomy of in-place and instant operations, documented per statement, with the same general shape: some alterations rebuild the table, some change only metadata, and the difference is not derivable from the syntax. The practical consequence is that the operational cost of a schema change must be looked up per engine and per version, not assumed from the statement.

Filling existing rows without freezing the table

A backfill writes to every existing row. Done as one UPDATE, it is a single transaction that holds row locks for its entire duration, generates enough write-ahead log to affect replication lag, and cannot be paused or resumed. Since none of those properties is acceptable on a table that is serving traffic, the backfill is executed as a sequence of bounded batches:

-- Repeat until no rows are returned. The predicate must be indexable.
UPDATE accounts
   SET display_name = legacy_display_name
 WHERE display_name IS NULL
   AND id IN (
         SELECT id FROM accounts
          WHERE display_name IS NULL
          ORDER BY id
          LIMIT 1000
       );

The batch size is chosen so that each statement’s lock footprint is small relative to the transaction rate of the table, and the loop sleeps between batches so that the backfill does not compete with production traffic for the same workers. Two details are easy to get wrong. First, the predicate used to select unprocessed rows must be driven by an index; a predicate like WHERE display_name IS NULL AND id IN (...) is served by the index on the null-able column only if the batch subquery itself is indexable, and a full scan per batch turns a bounded job into a recurring table scan. Second, a batch that updates a row the application is concurrently updating can deadlock, or can overwrite the application’s newer value with an older one unless the backfill only fills rows that are still null.

Backfills interact badly with long-running read transactions, which hold an old snapshot and keep row versions alive. On an MVCC engine that means the space occupied by superseded rows cannot be reclaimed until the longest transaction ends, so a monitor query left open for hours can turn a modest backfill into a storage incident.

When compatibility cannot be achieved

Some changes cannot be made compatible with both versions, and no ordering fixes them: encrypting a column for which the old code has no key, changing a primary key’s type while both versions write, splitting a table whose old version writes to the unified form, or removing a column that the old version selects explicitly and whose removal makes its queries fail at parse time. These require a deliberate break, and the choices are to stop the world for the migration, to run two schemas side by side with a migration process between them, or to accept that the old version cannot serve during the change.

The overlap that makes this necessary is created by the deployment technique itself. A blue-green cutover can sidestep it entirely by arranging that only one version is ever live against the writable database (the switch happens before any instance of the new version writes) while canary and rolling replacement cannot, because both deliberately run the versions together. That difference in exposure is a reason to choose the technique based on the shape of the change rather than on the topology of the fleet, and it is why blue-green cutover mechanics spends most of its attention on the datastore rather than on the router.