Learning center

What a PostgreSQL column default does to existing rows

Adding a column with a constant default is instant on PostgreSQL 11 and later. A volatile default such as gen_random_uuid() rewrites the table. A stable default such as now() is instant, and it gives every existing row the same value: the time of the migration. Decide which value existing rows should hold before you choose the default.

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;
ColumnDefaultTimeTable rewritten
plan'free'1.1 msNo
created_atnow()1.0 msNo
public_idgen_random_uuid()406 msYes

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_createddistinct_ids
1200000

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 NULL without 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.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading