Zero-Downtime Schema Migrations for Core Banking Tables

Every "add a column" migration looks identical in a code review — one line, obviously safe. What that line actually does to a live table under load depends entirely on which lock Postgres decides to take, and that's the part a code review can't see. On a core banking transaction table taking writes continuously, the wrong lock isn't a slow deploy. It's every payment in flight queuing behind a migration until it either finishes or the connection pool empties. This is what actually determines which side of that line a migration falls on, and the sequence that keeps you on the safe one. 

Expand-contract is the governing pattern, not one technique among several 

The single rule worth internalizing before any specific syntax: never make a breaking schema change in one step, on one table, in one deploy. Expand first — add the new structure alongside the old, in a form existing code doesn't need to know about yet. Migrate the application to write to both, or to the new structure only, deployed independently of the schema change. Backfill historical data in the background. Only then contract — remove what's no longer needed, once nothing still depends on it. Every pattern below is a specific instance of that sequence; the sequence is what actually delivers zero downtime, not any individual command. 

Adding a column: the default matters more than the column 

ALTER TABLE transactions ADD COLUMN currency_code TEXT; is fast and safe on Postgres 11 and later — a constant default is stored in the table's catalog metadata rather than written into every existing row, so the operation doesn't rewrite the table and the ACCESS EXCLUSIVE lock it briefly takes is genuinely brief. 

The danger is NOT NULL on an existing column, or a column being added NOT NULL without a default the engine can apply catalog-side. Historically, that forces Postgres to scan every row under an ACCESS EXCLUSIVE lock to verify nothing violates the constraint — on a transaction table with tens of millions of rows, that's minutes of every read and write queuing behind the migration. The safe sequence breaks the check into two steps with two different lock levels: 

-- Step 1: add the constraint unvalidated — brief lock, doesn't scan existing rows 

ALTER TABLE transactions 

  ADD CONSTRAINT currency_code_not_null CHECK (currency_code IS NOT NULL) NOT VALID; 

 

-- Step 2: validate separately — full table scan, but only a SHARE UPDATE 

-- EXCLUSIVE lock, which allows concurrent reads and writes to continue 

ALTER TABLE transactions VALIDATE CONSTRAINT currency_code_not_null; 

 

-- Step 3: now genuinely instant, because the CHECK already proves it 

ALTER TABLE transactions ALTER COLUMN currency_code SET NOT NULL; 

  

Postgres 18 adds a more direct path — ADD COLUMN ... NOT NULL NOT VALID natively — but the three-step CHECK pattern above is worth knowing regardless of version, both because it's what most production databases are still running and because it makes explicit what's actually happening: the expensive part (the scan) and the expensive lock (ACCESS EXCLUSIVE) don't have to happen together. 

Indexes and keys: concurrently, or not at all, on a live table 

CREATE INDEX without CONCURRENTLY takes a lock that blocks all writes for the entire build — minutes on a large table, long enough to matter on anything taking continuous transaction volume. CREATE INDEX CONCURRENTLY builds the index in a way that permits concurrent writes throughout, at the cost of roughly doubling the build time and a real caveat worth planning for: it isn't guaranteed to succeed, and a failed concurrent build leaves an invalid index behind that has to be dropped and retried manually rather than automatically rolled back. 

Primary and unique keys deserve the same two-step treatment as the NOT NULL case, because ADD CONSTRAINT ... PRIMARY KEY on its own builds the backing index under a lock that blocks everything: 

CREATE UNIQUE INDEX CONCURRENTLY transactions_external_ref_key 

  ON transactions (external_reference); 

 

ALTER TABLE transactions 

  ADD CONSTRAINT transactions_external_ref_pkey 

  PRIMARY KEY USING INDEX transactions_external_ref_key; 

  

The first statement does the expensive work without blocking anything. The second attaches the constraint to an index that already exists, which is fast enough that the brief lock it does take is rarely worth worrying about. 

Renaming or retyping a column: there is no safe single step 

A rename or a type change has no version of "just do it carefully" — any direct approach either breaks currently-deployed application code mid-migration or rewrites the entire table under an exclusive lock. The only safe path is the full expand-contract sequence, run as independent deploys: 

Expand: add the new column alongside the old one. 

Dual-write: deploy application code that writes both columns on every mutation, reads from the old one still. 

Backfill: populate the new column for existing rows, in bounded batches — a single UPDATE touching the whole table is the same long-lock problem in a different shape, and on a replicated setup, it's also a replication-lag problem. Chunk it, with a pause between batches: 

DO $$ 

DECLARE 

  batch_size INT := 10000; 

  rows_updated INT; 

BEGIN 

  LOOP 

    UPDATE transactions SET settlement_currency = currency_code 

      WHERE settlement_currency IS NULL 

      AND id IN (SELECT id FROM transactions WHERE settlement_currency IS NULL LIMIT batch_size); 

    GET DIAGNOSTICS rows_updated = ROW_COUNT; 

    EXIT WHEN rows_updated = 0; 

    COMMIT; 

    PERFORM pg_sleep(0.1); 

  END LOOP; 

END $$; 

  

Cut over: deploy application code that reads from the new column. 

Contract: once nothing reads or writes the old column, drop it. 

Five steps sounds like overhead for what a RENAME COLUMN would do in one statement. It's the overhead that guarantees every one of those five deploys can be rolled back independently — a failed step 4 rolls back to a code deploy that still works against the dual-written state from step 2, rather than to a schema that no longer matches what any deployed version of the application expects. 

The rollback plan is part of the migration, not an afterthought 

Every step above needs its own reverse, decided before the migration runs, not improvised if it goes wrong: an added column's rollback is dropping it; a dual-write deploy's rollback is reverting to the previous code, which still works because the old column is untouched. Set lock_timeout on any DDL statement in production — a few seconds is typical — so a migration that would otherwise queue behind a long-running query fails fast and gets retried, instead of silently blocking every other transaction on the table for however long that query takes to finish. On a core banking table, a migration that fails loudly in seconds is a minor annoyance. One that succeeds by blocking real transaction volume for minutes is an outage with a dollar figure attached to it — which is the actual reason none of this is optional, whatever the migration tool's default behavior suggests is fine. 

For the rare case that genuinely needs a full table rewrite — a column type change with no incremental path — pg_repack rewrites a table's physical storage without holding the long exclusive lock a plain ALTER TABLE ... TYPE would. It's a heavier tool than anything above, and worth reaching for only after confirming the expand-contract sequence genuinely can't get you there, not as a default.