Expand and Contract: Changing a Column While Both Versions Run

A schema change and an application deploy are two separate events, and between them the database and the code disagree. If a column is renamed in one step, there is a window, seconds in a single-server deploy and minutes across a fleet, in which some processes issue queries naming the old column and some name the new one. One of those groups fails. No locking strategy fixes this, because the conflict is between the schema and the code rather than between two database sessions.

The remedy is to split every breaking change into a sequence of changes that are each compatible with both versions of the code. The pattern has a standard name, expand and contract, and a small number of rules that determine whether a particular change can be decomposed at all.

The invariant each step must preserve

At every moment during the rollout, the schema must satisfy two properties: old code runs correctly against it, and new code runs correctly against it. That is stronger than “both can connect”. A rename violates it because the old name no longer resolves. A dropped column violates it because the old code selects it. A new NOT NULL column without a default violates it because old code inserts rows without supplying a value.

Every step in the sequence is chosen so that the union of the two code versions’ requirements is satisfied by the schema at that point. The sequence therefore moves from a schema that satisfies both, through states that satisfy both, to a schema that satisfies only the new code, with the old code retired before the last step.

The four phases for a rename are: add the new column, write both, read the new, remove the old. For a type change they are: add a column of the new type, dual-write with conversion, backfill, switch reads, drop the old. For a split of one column into two, they are: add both new columns, dual-write, backfill with a decomposition function, switch reads, drop the original.

Phase one: expand

Expansion adds the new structure without removing anything. A new column must be nullable or have a default, because rows written by old code will not include it; if it is declared NOT NULL without a default, old code’s inserts fail immediately. This is where the fast-default mechanism matters operationally: adding a nullable column to a large table is a catalogue edit, while adding one with a non-null default may or may not be, depending on the engine and the position in the row.

The phase also covers the write path. Dual-writing is application code, not schema: the new code writes both the old and the new column, converting between them. The conversion must be total over the domain, including nulls and empty strings, and it must be idempotent, because the same row may be written twice during a retry. Where the conversion is lossy, as when truncating a text column to a shorter varchar or parsing a free-form string into a structured type, the lossy case needs an explicit decision recorded somewhere, because after the old column is dropped the information is gone.

A constraint on the new column cannot be added in this phase if old code can violate it. A uniqueness constraint on the new column, for instance, fails for old-code inserts that write duplicates into it, and a NOT NULL constraint fails for every old-code insert that omits it. Both are deferred to the phase where all writers populate the column:

-- expand: nullable, unconstrained, no index needed yet
ALTER TABLE invoices ADD COLUMN billing_currency char(3);

-- once every writer populates it, enforce the constraint
ALTER TABLE invoices ALTER COLUMN billing_currency SET NOT NULL;

The second statement is not free even though it reads like a catalogue edit. Adding NOT NULL to a column requires the engine to establish that no existing row violates it, and on PostgreSQL versions before that check could reuse a validated constraint it meant a full table scan under an exclusive lock. The portable technique avoids the scan: add a CHECK (billing_currency IS NOT NULL) constraint marked not valid, which takes the exclusive lock only for the catalogue write, then VALIDATE CONSTRAINT, which scans the table under a lock that permits writes.

Phase two: backfill

Existing rows have no value in the new column. The backfill writes it, in batches, with a throttle, while the table serves traffic. This is the longest phase and the one with the most operational risk; its mechanics (batch sizing, key ranges, avoiding long transactions) are the subject of batched backfills and throttling.

Two properties make the backfill safe. It must be restartable, because it will be interrupted, and it must be idempotent, because a row can be written by the backfill and by the application concurrently. Both follow from processing by primary key range and writing values computed from the row’s own contents rather than from a snapshot taken at the start.

The subtle hazard is a row updated by new code between the backfill’s read and its write. The dual-write path writes the converted value, the backfill overwrites it with a conversion of the old value that it read before the update, and the row ends with the pre-update value in the new column. The repair is either to write only where the new column is still null, or to re-run the backfill over recently modified rows until the two agree, or to make the write conditional on the old column’s value being unchanged. All three are correct; the third is the most expensive.

Phase three: switch reads, then stop writing the old column

Once every row has a value in the new column, the read path can move. This is a code change with no schema change, and it is the point at which the new column becomes authoritative. Reads that still need the old column’s data get it from the new one; queries that filter or sort on the old column switch to the new one, which may require an index that did not exist.

Indexes are the part most often skipped. A query filtering on the new column needs an index on the new column, and creating it is a separate operation with its own lock behaviour. Creating it during the expand phase is usually better than during the switch, because the expand phase has more slack and because a concurrent build, the mechanism described in online index builds and their failure states, leaves an invalid index behind if it fails, which must be cleaned up before retrying.

After reads have moved and the old column is no longer read, the write path can stop writing it. This is a second code change, and it must be verified rather than assumed: a column that is still written by one forgotten job, or by a trigger, or by a replication tool that maps columns by name, keeps the old column alive and its removal breaks that path.

Phase four: contract

The final step removes the old structure. This is the only step that is a breaking schema change, and it is safe only because no running code references the old column. The check is mechanical: search the codebase, the views, the stored procedures, the trigger definitions, and the column lists of any external tooling that reads the table. The database itself can help: a column referenced by a view cannot be dropped without dropping the view first, which is a useful forcing function, since the error names the dependency.

Dropping a column is a catalogue operation that leaves the bytes in place, as described in the article on rewriting alters, so the table does not shrink. If the table is large and the dead column was wide, a rewrite may be needed afterwards to reclaim the space; that rewrite has the full lock cost of a table copy and is a separate maintenance operation.

The contract phase should also remove the compatibility scaffolding: the dual-write branch, the conversion function, the feature flag that selected the read path, and any temporary index added only for the transition. Scaffolding that survives the migration becomes permanent debt, and the next person to read the code cannot tell which branch is the intended one.

Where the pattern does not decompose

Some changes have no sequence of compatible states, and recognising them early avoids a rollout that stalls halfway.

Changing a primary key is the hardest. Every foreign key referencing the table must change with it, and there is no way for two versions of the code to reference the same row by different keys without either a mapping table or a period in which both keys exist and are kept in sync. The mapping table approach works but the dual-key period is long, and the foreign keys on dependent tables have their own expand-and-contract cycles.

Changing a column’s semantics without changing its name, such as reinterpreting a stored integer from cents to dollars or a timestamp from local time to UTC, cannot be decomposed by adding a column alone, because the old code writes values that are valid under the old interpretation and the new code writes values valid under the new one, into the same column. Two columns are required, with the old code’s writes converted on the way in, which means the conversion has to live in the database (a trigger) rather than in the application, since the old code will not be changed.

Dropping a table is the simplest case and still needs the contract phase: the table must stop being written and read before it is dropped, and on a replicated system the drop propagates to standbys and to any downstream consumer that reads the table directly.

The rollout discipline that makes it work

The sequence has a fixed relationship to deploys: expand and code that writes both go out together, or expand goes out first; backfill runs as a job between deploys; the read switch goes out with the next deploy; the contract goes out at least one deploy after the read switch has been observed working. The rule is that no deploy may depend on a schema state that a previous deploy has not yet established everywhere.

Two practices support that. Every migration must be backward compatible with the immediately previous release, which is a property that can be checked in review and, for the schema, approximated by running the previous release’s test suite against the new schema. And the deploy must be observable: the number of processes still running the old code is the number of processes that would break at the contract step, and it needs to be zero, not nearly zero, before that step runs.