An ALTER TABLE that takes 3 ms can still stall every query on the table. In my first demo it took 3 ms. In a second one, on real Postgres, a long transaction held the table, so a similar ALTER waited about 3 seconds, and a plain SELECT that arrived after it waited 2.7 seconds, though it only needed to read. That queue is how a "metadata-only" migration becomes downtime. Here is the method for a 5-billion-row table, with two scripts you can run.
I asked this on X, translated from Portuguese: "How would you do a migration on a 5-billion-row table with no downtime?" It reached 23K views and 33 of the replies had text. 3 gave a method, 9 talked about using an AI for it, and 6 said the hard part is knowing what to ask first: which database, how critical, what the tables look like. The replies are paraphrased and no one is named. This is the answer I would give.
The queue is the downtime
Two scripts, both run on my machine. The first uses PGlite, Postgres compiled to WebAssembly, in memory, with 1 million rows. The second uses a real Postgres 18.6 server with several connections. Read the shape of the numbers and ignore their size.
Does the change rewrite the table, or only change its metadata?
-- metadata only: a constant default (Postgres 11 and later)
ALTER TABLE events ADD COLUMN status text NOT NULL DEFAULT 'new';
-- rewrites the whole table (run on a copy: do not try this on your table)
ALTER TABLE events_copy ALTER COLUMN price TYPE bigint USING (price*100)::bigint;
ADD COLUMN ... DEFAULT constant (no rewrite) 3 ms
ALTER COLUMN TYPE ... (rewrites the table) 3210 ms
The first number does not depend on the table size. The second grows with it. But the first one can still take you down. ADD COLUMN takes an ACCESS EXCLUSIVE lock, the strongest there is, and it waits behind any open transaction that has touched the table, even one sitting idle after a SELECT. Every query that arrives after it waits behind the ALTER. I held a read transaction open for about 3 seconds, ran the ALTER, and sent one plain SELECT after it (output trimmed to the key lines, from single runs):
== 1. ALTER with no lock_timeout
sessions waiting on a lock right now: 2
A: long transaction commits after 3032 ms
B: ALTER finished after 3043 ms
C: a plain SELECT waited 2738 ms
== 2. ALTER with lock_timeout = 1s
B: ALTER gave up after 1016 ms (canceling statement due to lock timeout)
C: a plain SELECT waited 717 ms
A lock_timeout does not remove the stall. It bounds it: with 1 second, the SELECT still waited 717 ms. So use a short value, a few hundred milliseconds, set it on every statement that takes a strong lock, and retry with backoff on error 55P03 (neither demo retries). That means the ALTER ... ADD COLUMN, CREATE TRIGGER, ADD CONSTRAINT ... NOT VALID, SET NOT NULL, DROP TRIGGER and the final DROP COLUMN.
Before you start, look for the session that would hold the table. This is the one in my demo, and I did not run these queries in it:
SELECT pid, state, xact_start FROM pg_stat_activity
WHERE state = 'idle in transaction' ORDER BY xact_start;
-- and make it a rule, so a forgotten session cannot hold a table for hours:
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
The method, step by step
Step 1: expand. Ship application code that works with both versions of the schema first, because ORMs and prepared statements cache column lists.
SET lock_timeout = '300ms';
ALTER TABLE events ADD COLUMN price_cents bigint; -- nullable, no default
Step 2: dual write first, then backfill. Keep the new column right for every new or changed row before you touch the old rows. If you backfill first and start dual writes later, the rows written in between are missed.
-- CREATE TRIGGER takes SHARE ROW EXCLUSIVE, which blocks writes: keep the short lock_timeout
CREATE FUNCTION events_sync() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.price_cents := round(NEW.price * 100)::bigint; RETURN NEW; END $$;
CREATE TRIGGER events_sync_t BEFORE INSERT OR UPDATE OF price ON events
FOR EACH ROW EXECUTE FUNCTION events_sync();
insert after the trigger: {"id":1000001,"price":"12.34","price_cents":1234}
I would not put the dual write in the application. Every writer has to change: other services, jobs, manual SQL, and old app versions still running during the deploy. A trigger covers all of them and sets the value in the same row version. It has a cost: it runs for every row. Change data capture fits copying into a new table. For a column in the same table it needs a second UPDATE per write.
Step 3: backfill in key ranges.
UPDATE events SET price_cents = round(price * 100)::bigint
WHERE id > :lo AND id <= :lo + 50000 AND price_cents IS NULL;
Run it in a loop, in short transactions, with a statement_timeout on that session so one slow batch cannot hold locks for hours, and make it safe to stop and restart.
backfill: 1,000,000 rows in 21 batches, 5133 ms
That is in memory, with no replicas and no other load, so it only gives a floor. A straight line from it says about 7 hours for 5 billion rows with nothing else running. Once you throttle for replicas and vacuum, I would plan for days. Three things will hurt first:
-
Bloat. Every updated row is a new row version, so the table can grow by up to its own size until vacuum catches up. Watch autovacuum, and track
pg_total_relation_size('events')as the backfill runs.pg_repackcan reclaim the space afterwards, but it needs about twice the size of the table and its indexes in free disk, and it takes a brief exclusive lock at the start and the end, so it needs the same lock timeout. -
Replica lag. Pause between batches while replicas fall behind, for example when
now() - pg_last_xact_replay_timestamp()on a physical replica goes past your limit. That expression overstates the lag when the primary is idle, and I did not run it, because the demo has no replica. -
Logical replication and change data capture. Watch the slot in
pg_replication_slots. A stalled slot makes the primary keep log files, and every consumer receives an update event for each backfilled row.
Step 4: verify in ranges. Do not run one count(*) over the whole table. A query that runs for hours holds a snapshot that stops vacuum from cleaning up the dead rows your backfill made, and a count does not catch wrong values. Compare values, one key range at a time, off-peak or on a replica (lag can show false mismatches there, and long queries on a replica can be cancelled):
SELECT count(*) FILTER (WHERE price_cents IS DISTINCT FROM round(price*100)::bigint)
FROM events WHERE id > :lo AND id <= :lo + 50000;
rows where new and old disagree: 0
This proves the copy matches the formula. Compare a sample with the source system or an old report to check the formula itself.
Step 5: make it NOT NULL without a long lock.
ALTER TABLE events ADD CONSTRAINT price_cents_nn CHECK (price_cents IS NOT NULL) NOT VALID;
ALTER TABLE events VALIDATE CONSTRAINT price_cents_nn;
ALTER TABLE events ALTER COLUMN price_cents SET NOT NULL; -- Postgres 12+: skips the scan
ADD CONSTRAINT ... CHECK (...) NOT VALID 1 ms
VALIDATE CONSTRAINT (scans, takes a light lock) 73 ms
SET NOT NULL (uses the validated CHECK) 1 ms
Run VACUUM on the table after the backfill and before VALIDATE, because VALIDATE takes a lock that autovacuum cannot work alongside. NOT VALID takes a brief ACCESS EXCLUSIVE lock. VALIDATE scans under SHARE UPDATE EXCLUSIVE, so reads and writes continue. The CHECK constraint can stay once the column is NOT NULL; the demo drops it. Foreign keys work the same way: add them NOT VALID, then validate. I did not run that one.
Step 6: the index, without blocking writes.
CREATE INDEX CONCURRENTLY events_price_cents_idx ON events (price_cents);
SELECT count(*) FROM pg_index WHERE NOT indisvalid; -- must be 0 (database-wide: filter to your index on a shared database)
CREATE INDEX CONCURRENTLY 956 ms
invalid indexes left behind: 0
It cannot run inside a transaction block, it waits for old transactions, it scans the table twice, and a failed run leaves an invalid index: DROP INDEX CONCURRENTLY it and retry. Most migration tools wrap each migration in a transaction, where CONCURRENTLY errors, so run this one outside it. On a partitioned table it does not work on the parent: build it on each partition, create the parent index with ON ONLY, then attach. The 956 ms means nothing for a busy table, because the demo has no write load.
Step 7: switch, wait, then contract. Switch the reads to the new column. Keep the app writing the old column, and the trigger keeps the new one right. If you later write the new column from the app, add a reverse trigger so the old column keeps up, or flipping the reads back will serve stale data. The one step you cannot undo is dropping the old column, so wait, take a backup, drop the trigger and its function first (DROP COLUMN fails while the trigger depends on the column), then drop the column with the lock timeout. DROP COLUMN frees no space until the rows are rewritten.
For the same idea at table level (copy, keep in sync, swap), MySQL has gh-ost and pt-online-schema-change. On Postgres, pgroll automates expand and contract with triggers.
If an AI gave you the plan
My own reply under the post said an AI helps with the plan, but on a critical table I would not let an agent run it alone. Some companies would not allow it, and many run migrations in separate pipelines. A fluent plan can still be wrong, so ask it:
- What lock does each statement take?
- What happens if the backfill dies halfway?
- How long will it run, and how do you know?
- What stops it when replicas lag?
- How do I go back?
What the demos do not show
The first has one connection and is in memory. The second has no replicas, no live traffic and only 200,000 rows. Rehearse on a copy of production first.
The scripts
I ran both on Node 22. The second downloads Postgres binaries when you install it.
Script 1: the migration steps (PGlite).
// demo.mjs: run with `npm install @electric-sql/pglite` then `node demo.mjs`
import { PGlite } from '@electric-sql/pglite';
const db = new PGlite();
const rows = Number(process.env.ROWS || 1_000_000);
const BATCH = 50_000;
async function timed(label, sql) {
const s = performance.now();
const r = await db.query(sql);
console.log(label.padEnd(46), `${(performance.now() - s).toFixed(0)} ms`);
return r;
}
console.log((await db.query('select version()')).rows[0].version.split(',')[0]);
await db.exec(`
create table events (
id bigint generated always as identity primary key,
price numeric(12,2) not null,
created_at timestamptz not null default now()
);
`);
await timed(`load ${rows.toLocaleString('en-US')} rows`, `insert into events(price) select (random()*1000)::numeric(12,2) from generate_series(1, ${rows})`);
await db.exec('analyze events');
console.log('\n-- 0. The fork: metadata-only or table rewrite?');
await timed('ADD COLUMN ... DEFAULT constant (no rewrite)', `alter table events add column status text not null default 'new'`);
await db.exec('create table events_copy as select * from events');
await timed('ALTER COLUMN TYPE ... (rewrites the table)', 'alter table events_copy alter column price type bigint using (price*100)::bigint');
await db.exec('drop table events_copy');
console.log('\n-- 1. Expand: add the new column, nullable, with a lock timeout');
await db.exec(`set lock_timeout = '300ms'`);
console.log('lock_timeout =', (await db.query('show lock_timeout')).rows[0].lock_timeout);
await timed('ADD COLUMN price_cents bigint (nullable)', 'alter table events add column price_cents bigint');
console.log('\n-- 2. Dual write FIRST: a trigger keeps the new column right for every new or changed row');
await db.exec(`
create function events_sync() returns trigger language plpgsql as $$
begin new.price_cents := round(new.price * 100)::bigint; return new; end $$;
create trigger events_sync_t before insert or update of price on events
for each row execute function events_sync();
`);
const ins = await db.query(`insert into events(price) values (12.34) returning id, price, price_cents`);
console.log('insert after the trigger:', JSON.stringify(ins.rows[0]));
console.log('\n-- 3. Backfill in key-range batches (short transactions)');
const maxId = Number((await db.query('select max(id)::bigint as m from events')).rows[0].m);
let done = 0, batches = 0;
const t0 = performance.now();
for (let lo = 0; lo < maxId; lo += BATCH) {
const r = await db.query(
`update events set price_cents = round(price * 100)::bigint
where id > $1 and id <= $2 and price_cents is null`, [lo, lo + BATCH]);
done += r.affectedRows; batches++;
if (batches % 5 === 0) console.log(` batch ${batches}: ${done.toLocaleString('en-US')} rows filled`);
// in production, pause here while replica lag is above your limit, then continue
}
console.log(`backfill: ${done.toLocaleString('en-US')} rows in ${batches} batches, ${(performance.now() - t0).toFixed(0)} ms`);
console.log('\n-- 4. Verify in key ranges, not with one count(*) over the whole table');
let bad = 0;
for (let lo = 0; lo < maxId; lo += BATCH) {
const r = await db.query(
`select count(*) filter (where price_cents is distinct from round(price*100)::bigint) as bad
from events where id > $1 and id <= $2`, [lo, lo + BATCH]);
bad += Number(r.rows[0].bad);
}
console.log('rows where new and old disagree:', bad);
console.log('\n-- 5. Make it NOT NULL without a long lock: validated CHECK first');
await timed('ADD CONSTRAINT ... CHECK (...) NOT VALID', 'alter table events add constraint price_cents_nn check (price_cents is not null) not valid');
await timed('VALIDATE CONSTRAINT (scans, takes a light lock)', 'alter table events validate constraint price_cents_nn');
await timed('SET NOT NULL (uses the validated CHECK)', 'alter table events alter column price_cents set not null');
await db.exec('alter table events drop constraint price_cents_nn');
console.log('\n-- 6. Index without blocking writes');
await timed('CREATE INDEX CONCURRENTLY', 'create index concurrently events_price_cents_idx on events (price_cents)');
const inv = await db.query(`select count(*) as invalid from pg_index where not indisvalid`);
console.log('invalid indexes left behind:', inv.rows[0].invalid);
console.log('\n-- 7. Later, once reads are switched and you have waited: contract');
await db.exec('drop trigger events_sync_t on events');
console.log('trigger dropped; the old column stays until you are sure');
Script 2: the lock queue (real Postgres).
// lock.mjs: run with `npm install embedded-postgres pg` then `node lock.mjs`
// A short ALTER can still stall every query behind it. Real Postgres, several connections.
import EmbeddedPostgres from 'embedded-postgres';
import pg from 'pg';
import { mkdtempSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
const port = 54329;
const server = new EmbeddedPostgres({
databaseDir: mkdtempSync(join(tmpdir(), 'lockdemo-')),
user: 'postgres', password: 'pw', port, persistent: false,
onLog: () => {}, onError: () => {},
});
await server.initialise();
await server.start();
await server.createDatabase('demo');
const connect = async (name) => {
const c = new pg.Client({ host: 'localhost', port, user: 'postgres', password: 'pw', database: 'demo' });
await c.connect();
c.name = name;
return c;
};
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
const now = () => performance.now();
const admin = await connect('admin');
console.log((await admin.query('select version()')).rows[0].version.split(' on ')[0]);
await admin.query('create table events (id bigint generated always as identity primary key, price numeric(12,2) not null)');
await admin.query('insert into events(price) select (random()*1000)::numeric(12,2) from generate_series(1, 200000)');
async function scenario(label, lockTimeout) {
console.log(`\n== ${label}`);
const A = await connect('A'); // a long transaction that has read the table
const B = await connect('B'); // the migration
const C = await connect('C'); // an ordinary read that arrives after the migration
await A.query('begin');
await A.query('select count(*) from events');
console.log('A: long transaction is open (it only read the table)');
const t0 = now();
const bDone = (async () => {
if (lockTimeout) await B.query(`set lock_timeout = '${lockTimeout}'`);
const s = now();
try {
await B.query(`alter table events add column status_${lockTimeout ? 'b' : 'a'} text not null default 'new'`);
return `B: ALTER finished after ${(now() - s).toFixed(0)} ms`;
} catch (e) {
return `B: ALTER gave up after ${(now() - s).toFixed(0)} ms (${e.message})`;
}
})();
await sleep(300);
const cDone = (async () => {
const s = now();
await C.query('select 1 from events limit 1');
return `C: a plain SELECT waited ${(now() - s).toFixed(0)} ms`;
})();
await sleep(200);
const waiting = await admin.query(
`select count(*) as n from pg_stat_activity where datname='demo' and wait_event_type='Lock'`);
console.log(`sessions waiting on a lock right now: ${waiting.rows[0].n}`);
await sleep(2500);
await A.query('commit');
console.log(`A: long transaction commits after ${(now() - t0).toFixed(0)} ms`);
console.log(await bDone);
console.log(await cDone);
await Promise.all([A.end(), B.end(), C.end()]);
}
await scenario('1. ALTER with no lock_timeout', null);
await admin.query('alter table events drop column if exists status_a');
await scenario('2. ALTER with lock_timeout = 1s', '1s');
await admin.end();
await server.stop();
What is the longest stall a migration has caused on your production database, and what was holding the lock?
I’m Anderson, a software engineer in Europe: 15 years building, 12 of them working remotely. I write about engineering, AI and what works in a career abroad. X: https://x.com/justanderson · Newsletter on getting hired abroad from Brazil (in Portuguese): https://semromantizar.substack.com
Top comments (0)