A 3 ms ALTER can stall Postgres: how to migrate a 5-billion-row table with no downtime
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…
A 3 millisecond ALTER statement can still delay every query on the affected table. In a demonstration, the ALTER took 3 milliseconds. After a long-running transaction blocked the table, the ALTER waited approximately 3 seconds. A subsequent SELECT statement waited 2.7 seconds to execute, despite only requiring a read. This queue of transactions can cause what appears to be downtime for a metadata-only migration. Here's a method for migrating a 5-billion-row table with minimal downtime.
Two scripts were provided to run on the reporter's machine. The first script used PGlite, a lightweight version of Postgres compiled to WebAssembly, containing 1 million rows. The second script utilized a real Postgres 18.6 server with multiple connections.
The first ALTER TABLE statement, ADD COLUMN status text NOT NULL DEFAULT new, was completed in 3 milliseconds. The second ALTER TABLE statement, ALTER COLUMN price TYPE bigint USING (price * 100):: bigint ADD COLUMN price_cents bigint NOT NULL DEFAULT 0, took 3210 milliseconds. The duration of the ALTER statements does not depend on the table size, but the second type of ALTER statement can become slower as the table grows larger.
The key point is that the ADD COLUMN statement requires an ACCESS EXCLUSIVE lock, the strongest lock type. This lock is held until the ALTER statement completes, and other queries waiting on this lock are delayed. Any open transaction that has touched the table, even if idle after a SELECT, will also be affected. The plain SELECT statement waited 2738 milliseconds after the ALTER statement completed.
To minimize downtime during a migration, the reporter recommends several steps. First, ensure that application code can work with both versions of the schema before making any changes. Set a lock_timeout of 300 milliseconds, then add the new column as a nullable with no default value. Next, create a trigger function that calculates the new column value (price_cents) based on the existing price column.
This trigger should be executed before INSERT or UPDATE operations on the price column. Finally, perform backfilling of the new column for existing rows in key ranges, running the updates in short transactions with statement_timeout to prevent locks from being held for extended periods.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
