Changing a table that is in use

The schema is not finished, and the table is busy

The orders table has forty million rows, a web application reading from it every second, and it needs a new column. ALTER TABLE orders ADD COLUMN channel text; looks like nothing. Whether it is nothing depends on what the database has to do to every existing row, and on who is holding a lock at the moment you run it.

What is instant and what rewrites the table

Adding a nullable column with no default is a metadata change in every major engine: the catalogue is updated, existing rows are untouched and read as NULL for the new column, and it completes in milliseconds regardless of table size.

Adding a column with a constant DEFAULT used to rewrite every row in Postgres, minutes to hours on a big table. Since Postgres 11 a constant default is also metadata only, with the default applied on read for old rows. A default that is an expression, such as DEFAULT now(), still rewrites. Changing a column’s type rewrites unless the new type is a strict widening, varchar(50) to text for instance. Adding NOT NULL to an existing column scans the whole table to prove that no NULL exists, and holds a lock while it does.

The pattern for a new required column on a live table is therefore three steps rather than one:

ALTER TABLE orders ADD COLUMN channel text;             -- instant, nullable
-- backfill in batches of 10,000 with UPDATE ... WHERE channel IS NULL LIMIT-style loops
ALTER TABLE orders ADD CONSTRAINT orders_channel_nn CHECK (channel IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_nn;   -- scans without the heavy lock
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;       -- instant, since the check proves it

NOT VALID adds the rule for new rows immediately; VALIDATE checks old rows while allowing reads and writes to continue. Postgres 12 and later recognises the validated check and sets NOT NULL without rescanning.

The lock queue

ALTER TABLE takes the strongest lock there is, ACCESS EXCLUSIVE, which waits for every current reader and writer to finish, and blocks every new one until it is done. On a busy table the danger is not the ALTER itself but the queue behind it: a long-running report holds a share lock, the ALTER waits behind it, and every ordinary query that arrives waits behind the ALTER. The site appears to be down. It is not; it is queued behind a metadata change that would have taken two milliseconds if it had been allowed to start.

Set a lock timeout before any schema change on a live database: SET lock_timeout = '3s';. If the ALTER cannot get its lock in three seconds, it fails, nothing is queued, and you try again when the report has finished. Creating an index has its own version of the problem and its own fix, CREATE INDEX CONCURRENTLY, which builds the index without blocking writes, at the cost of taking longer and needing to be run outside a transaction.

Rename nothing that is in use

Renaming a column breaks every query, view and application that uses the old name at the instant the rename commits. The safe sequence is additive: add the new column, have the application write both, backfill, switch reads to the new column, stop writing the old one, and drop it in a later release. It is more steps and it means no moment at which anything is broken. The same applies to dropping a column: first confirm nothing reads it, which a quick grep of the code and a look at the views that depend on it will tell you.

Migrations are files, not commands

A schema change typed into a GUI against production is a change nobody can review, repeat on the test database, or reverse. The discipline is a numbered file per change, 0042_add_orders_channel.sql, applied in order, with a table in the database recording which have run. The free tools that do exactly this are Flyway, Liquibase, dbmate and sqitch, and most application frameworks ship their own; the earlier lesson on Postgres transactions is why they can apply a multi-statement migration atomically there and not in MySQL. The file is also the documentation: six months later, the reason a column exists is the commit message on the migration that added it.

The habit

Before a schema change, answer three questions in writing: does this rewrite the table, what lock does it take, and what breaks if the old name disappears. Then set a lock timeout, and run it from a migration file that has already run on a copy.