Expand and Contract: Changing a Payments Column Without Downtime
Changing the shape of a payments table is trivial in a migration file and hard in production. The rows are being written to while the migration runs, the previous release and the new one serve traffic side by side for the length of a rollout, and the two statements a migration tool will happily generate for you — a table-rewriting ALTER and a NOT NULL on a populated column — both take a lock that queues every payment in the system behind them.
This is the ladder we use when a money column has to change: five deploys instead of one, each additive, each resumable, each leaving rollback as nothing more dramatic than shipping the previous image. The worked example below is one we have done in more than one payment system: a table that stores amount numeric(18,2) and silently assumes a single currency, which has to become amount_minor bigint plus an explicit currency code, because minor units are the only representation that survives reconciliation without a rounding argument.
The constraint: old code and new code run at the same time#
During a rolling deploy, the N-1 release and the N release are both writing. That single fact kills the obvious plan. If the new release renames a column, the old one starts throwing on every insert until the last pod is replaced. If the new release adds a NOT NULL column, the old release inserts rows without it and fails. So the schema has to be simultaneously valid for both releases, which is exactly what expand/contract buys: expand the schema so both shapes work, move the data, switch the readers, and only then contract — in a separate release, after the rollback window has closed.
Our review gate refuses a migration that is not additive and idempotent. Drops, type narrowing, and NOT NULL on a populated column are not migrations in our vocabulary; they are their own ticket, scheduled after the old code is provably gone, and they need a human to approve them.
Step 1: expand, with a lock timeout#
The addition itself is cheap on any modern Postgres — a nullable column, or a column with a constant default, is a catalogue change and not a rewrite. What is not cheap is waiting for the lock.
SET lock_timeout = '2s';
SET statement_timeout = '5s';
ALTER TABLE payment
ADD COLUMN IF NOT EXISTS amount_minor bigint,
ADD COLUMN IF NOT EXISTS currency char(3);The lock_timeout is the whole point of that snippet. ALTER TABLE wants an ACCESS EXCLUSIVE lock. If any long-running transaction is holding a conflicting lock — an analytics query, an idle transaction left open by a client library — the ALTER waits, and every statement that arrives after it queues behind it, including the ones taking payments. Without a lock timeout, a metadata change that takes a millisecond of work can produce a full outage of the money path. With one, the migration fails fast and gets retried; failing is fine, blocking is not.
Indexes on the new column go in their own step, outside a transaction:
CREATE INDEX CONCURRENTLY IF NOT EXISTS payment_currency_idx
ON payment (currency)
WHERE currency IS NOT NULL;CONCURRENTLY cannot run inside a transaction block, so in Django this means a migration with atomic = False and AddIndexConcurrently. It also means the operation can fail and leave an invalid index behind, so the step is written to drop-concurrently-then-recreate if pg_index.indisvalid is false. That check is what makes the step re-runnable, and re-runnability is what lets a deploy be retried at three in the morning by whoever is on call rather than by whoever wrote the migration.
Step 2: dual-write from the application#
The next release writes both representations. We do this in application code rather than in a database trigger, because the trigger is invisible to code review, invisible to the test suite, and has to be removed by yet another migration later.
from decimal import Decimal
MINOR_UNITS = {'JPY': 1, 'KWD': 1000}
def to_minor(amount: Decimal, currency: str) -> int:
scale = MINOR_UNITS.get(currency, 100)
quantized = amount.quantize(Decimal(1) / scale)
return int(quantized * scale)
def record_payment(account, amount: Decimal):
currency = account.currency
return Payment.objects.create(
account=account,
amount=amount,
amount_minor=to_minor(amount, currency),
currency=currency,
)Two things are worth stating plainly here. The first is that the currency comes from a source of truth — the account — and not from a constant in the migration; a backfill that guesses currency is a data corruption incident with a delay fuse. The second is that reads still use amount. Dual-write is not a cutover, it is the thing that stops the backlog growing while the backfill catches up.
Step 3: backfill in batches that stay out of the way#
The backfill runs as a job, not as a migration. A migration that takes hours holds a connection and a transaction, blocks the deploy pipeline behind it, and cannot be resumed after a network blip.
BATCH = 2000
SQL = """
WITH batch AS (
SELECT p.id, a.currency
FROM payment p
JOIN account a ON a.id = p.account_id
WHERE p.id > %(cursor)s
AND p.amount_minor IS NULL
ORDER BY p.id
LIMIT %(limit)s
)
UPDATE payment p
SET amount_minor = (p.amount * 100)::bigint,
currency = batch.currency
FROM batch
WHERE p.id = batch.id
RETURNING p.id
"""
def backfill(conn, sleep=0.2):
cursor_id = 0
while True:
with conn.cursor() as cur:
cur.execute('SET lock_timeout = %s', ('2s',))
cur.execute(SQL, {'cursor': cursor_id, 'limit': BATCH})
ids = [row[0] for row in cur.fetchall()]
conn.commit()
if not ids:
return
cursor_id = max(ids)
time.sleep(sleep)The amount_minor IS NULL predicate is what makes the job idempotent: killing it and starting it again costs nothing, and rows written by the dual-writing application are skipped rather than recomputed. The keyset cursor keeps each batch bounded regardless of table size. The sleep is not cosmetic — on a replicated cluster we drive it from replication lag, because a backfill that outruns the replicas turns into stale reads on every service that reads from a follower, and those services have no idea a migration is happening.
The hardcoded * 100 in the SQL is deliberate: the batch is scoped to currencies with two minor units, and the exotic ones are backfilled by a second pass with their own scale. A single clever expression that handles every currency is the kind of thing that is right in review and wrong in production.
Step 4: enforce without a full-table lock#
Once the backfill has drained, the new column has to become mandatory. SET NOT NULL scans the whole table under an ACCESS EXCLUSIVE lock, which is the outage we spent four steps avoiding. The two-phase constraint does the same job with a lock that lets writes through.
ALTER TABLE payment
ADD CONSTRAINT payment_amount_minor_present
CHECK (amount_minor IS NOT NULL AND currency IS NOT NULL) NOT VALID;
-- separate transaction, minutes or hours later
ALTER TABLE payment VALIDATE CONSTRAINT payment_amount_minor_present;NOT VALID enforces the rule for new and updated rows immediately and takes only a brief lock. VALIDATE CONSTRAINT then scans the existing rows under SHARE UPDATE EXCLUSIVE, which does not block inserts, updates, or reads. If the validation fails, it fails on a row that tells you exactly which assumption in the backfill was wrong, and nothing is broken while you go and fix it.
Step 5: switch reads, then contract#
Before the read switch, the verification is a reconciliation, not a spot check — the same discipline as any other money-path work: classify the mismatches, do not repair them silently.
SELECT count(*) AS mismatched
FROM payment
WHERE amount_minor IS DISTINCT FROM (amount * 100)::bigint;That count has to be zero across the whole table, not a sample, and it gets re-run after the last batch because dual-write is still filling the tail. Only then does the read path move to amount_minor, behind a flag so the switch is a configuration change rather than a deploy.
The contract step — dropping amount, deleting the dual-write branch — is a different release entirely, and it waits until the previous image is no longer something anyone would roll back to. Until that moment, the old column is our rollback plan, and a rollback plan you have already deleted is not one.
What it costs to run#
Five deploys instead of one, a job that has to be watched while it drains, a few extra bytes per row for the length of the transition, and a branch in the write path that lives for a couple of weeks. The real cost is organisational: contract steps are boring and nobody volunteers for them, so we schedule the drop as a ticket at the same time as the expand, or the table quietly accumulates columns nobody dares to remove two years later.
What the ladder buys is a single property, and it is worth all five steps: at every point in the sequence, the database is valid for both the release that is running and the release before it. That is what makes the rollback plan the same one sentence throughout — deploy the previous image — and it is why a schema change on a money table stops being an event that needs a window, a night shift, and an audience.