Add three columns to a table of 200,000 rows#
An accounts table gets a plan, a creation time and a public identifier. We ran this on PostgreSQL 16.15 with \timing on, reading the table's file node after each statement to see whether PostgreSQL wrote a new copy of the table.
CREATE TABLE accounts (id integer PRIMARY KEY, email text NOT NULL);
INSERT INTO accounts
SELECT g, 'user' || g || '@example.com' FROM generate_series(1, 200000) g;
SELECT pg_relation_filenode('accounts') AS f0 \gset
ALTER TABLE accounts ADD COLUMN plan text NOT NULL DEFAULT 'free';
ALTER TABLE accounts ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();
ALTER TABLE accounts ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid();
SELECT pg_relation_filenode('accounts') = :f0 AS same_file;
| Column | Default | Time | Table rewritten |
|---|---|---|---|
plan | 'free' | 1.1 ms | No |
created_at | now() | 1.0 ms | No |
public_id | gen_random_uuid() | 406 ms | Yes |
Why gen_random_uuid() rewrites and now() does not#
PostgreSQL 11 stores a non-volatile default once in the catalog and returns it for every row written before the column existed, as the ALTER TABLE documentation describes. A volatile function must give each row its own value, so PostgreSQL computes it row by row and writes a new table while holding an ACCESS EXCLUSIVE lock. On this small table that took 406 ms. The time grows with the table and its indexes, and reads wait the whole time.
now() is stable: it returns the start time of the current transaction. PostgreSQL evaluates it once and stores that one value.
SELECT count(DISTINCT created_at) AS distinct_created,
count(DISTINCT public_id) AS distinct_ids
FROM accounts;
| distinct_created | distinct_ids |
|---|---|
| 1 | 200000 |
All 200,000 existing accounts now report that they were created at the moment the migration ran. Nothing failed, and a report that counts sign-ups per day will show a spike on the deployment date. Each existing account did get its own UUID, at the cost of the rewrite.
Choose the value for existing rows first#
The default answers two separate questions: what new rows get when the application omits the column, and what existing rows get now. Ask the second one in the review.
- When every existing row should hold the same value, a constant or stable default is correct and costs nothing.
- When existing rows need their own values, as with identifiers, add the column as nullable without a default, set the default for new rows, and fill existing rows in batches. Then add
NOT NULLwithout a long scan, as shown in make SET NOT NULL skip the table scan. - When the true value is unknown, as with a creation time for old rows, keep the column nullable or derive the value from data you already have. Do not let the migration timestamp stand in for it.
ALTER TABLE accounts ADD COLUMN public_id uuid;
ALTER TABLE accounts ALTER COLUMN public_id SET DEFAULT gen_random_uuid();
UPDATE accounts SET public_id = gen_random_uuid()
WHERE id IN (
SELECT id FROM accounts
WHERE public_id IS NULL
ORDER BY id
LIMIT 10000
);
-- repeat until it updates 0 rows
What to rehearse#
Read the volatility of each function a default calls:
SELECT proname, provolatile FROM pg_proc
WHERE proname IN ('now', 'clock_timestamp', 'gen_random_uuid');
v means volatile and rewrites the table; s and i do not. A default that calls your own function inherits the volatility you declared for it. If the migration rewrites, time it on a copy at production size, or change it to the batched form above. Before any ALTER TABLE, also read why a fast migration blocked every query, because even the 1 ms statements wait for their lock first.
Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.