Lab: change a 5-million-row table without stopping writes

Before you start

You need a throwaway database with about 1.5 GB free and two terminals connected to it. Build the table:

CREATE TABLE payments (
  id          int PRIMARY KEY,
  customer_id int,
  status      varchar(20),
  amount      numeric(10,2),
  created_at  timestamptz
);
INSERT INTO payments
SELECT g, g % 100000, (ARRAY['paid', 'pending', 'refunded'])[1 + g % 3],
       (g % 10000) / 100.0, now() - g * interval '1 second'
FROM generate_series(1, 5000000) g;
VACUUM ANALYZE payments;
SELECT pg_size_pretty(pg_relation_size('payments'));   -- ours: 287 MB
\timing on

Our numbers are medians of three runs on PostgreSQL 18.3, Apple M1 Pro laptop. Yours will differ in milliseconds; which changes rewrite, scan or block should not differ at all.

1. Which changes rewrite the table

A table lives in a file whose number pg_relation_filenode reports. A rewrite writes a new file, so the number changes. Wrap each change like this:

SELECT pg_relation_filenode('payments') AS before \gset
ALTER TABLE payments ADD COLUMN note text;
SELECT CASE WHEN :before = pg_relation_filenode('payments') THEN 'same file' ELSE 'REWRITTEN' END;

Now try each of these the same way, in order, and fill in the table:

ALTER TABLE payments ADD COLUMN currency text NOT NULL DEFAULT 'INR';
ALTER TABLE payments ALTER COLUMN status TYPE varchar(50);
ALTER TABLE payments ALTER COLUMN status TYPE text;
ALTER TABLE payments ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE payments ADD COLUMN imported_at timestamptz DEFAULT clock_timestamp();
ALTER TABLE payments ALTER COLUMN id TYPE bigint;

The SET NOT NULL kept the same file but still took a quarter of a second: it read every row to check for nulls, under ACCESS EXCLUSIVE.

2. Does it block writes?

Time alone does not tell you about the outage; the lock does. In terminal A, start a long change. In terminal B, within a second, insert one row:

-- A
CREATE INDEX pay_cust ON payments (customer_id);

-- B
INSERT INTO payments (id, customer_id, status, amount, created_at)
VALUES (-1, 1, 'paid', 1, now());

Note how long B’s insert took. Then drop the index and repeat with the concurrent version:

-- A
DROP INDEX pay_cust;
CREATE INDEX CONCURRENTLY pay_cust ON payments (customer_id);

-- B
INSERT INTO payments (id, customer_id, status, amount, created_at)
VALUES (-2, 1, 'paid', 1, now());

The concurrent build took longer, and that is the trade: more total work in exchange for never stopping writes. On a table where a normal build takes an hour, that hour is an hour of failed checkouts.

3. Constraints in two steps

Do the same with a CHECK constraint. First the usual way, with B inserting while A runs:

-- A
ALTER TABLE payments ADD CONSTRAINT amount_positive CHECK (amount >= 0);

Then in two steps, inserting from B during the VALIDATE:

-- A
ALTER TABLE payments ADD CONSTRAINT amount_small CHECK (amount < 1000) NOT VALID;
ALTER TABLE payments VALIDATE CONSTRAINT amount_small;

Both versions read the whole table. Only the first stopped writes while it did. On PostgreSQL 18 try the same with NOT NULL: ALTER TABLE payments ADD CONSTRAINT amount_not_null NOT NULL amount NOT VALID; then validate it.

4. Backfill: one statement or many

You have added amount_paise bigint and need to fill it for 5 million rows.

ALTER TABLE payments ADD COLUMN amount_paise bigint;

The one-statement way. In A:

UPDATE payments SET amount_paise = amount * 100;

Five seconds in, from B, update a row near the start of the table, the kind of thing the application does all day:

UPDATE payments SET status = 'refunded' WHERE id = 10;

B waits until A finishes: A has already updated row 10 and holds its lock until it commits. Ours waited 23 s of a 28 s backfill.

The batched way. Rebuild the table from “Before you start” (or set amount_paise back to NULL and vacuum), add the column again, and backfill in chunks of 50,000 rows, committing after each:

CREATE PROCEDURE backfill_paise(batch int) LANGUAGE plpgsql AS $$
DECLARE lo int := 0; hi int;
BEGIN
  SELECT max(id) INTO hi FROM payments;
  WHILE lo < hi LOOP
    UPDATE payments SET amount_paise = amount * 100
    WHERE id > lo AND id <= lo + batch AND amount_paise IS NULL;
    lo := lo + batch;
    COMMIT;
  END LOOP;
END $$;

CALL backfill_paise(50000);   -- in A; repeat B's update five seconds in

Batching took about as long overall, and it did not save space: both more than doubled the table, because every update writes a new row version and the old one stays until vacuum reclaims it. What batching changed is how long anyone else waits: at most one batch, instead of the whole backfill. On a real system you would also pause between batches to let replicas and autovacuum keep up.

The amount_paise IS NULL condition makes the procedure safe to stop and rerun: finished rows are skipped.

What you measured

• Constant defaults and compatible type changes were instant; a volatile default and int to bigint rewrote 287 MB in 8 and 13 seconds.
• A plain index build and a plain CHECK blocked a concurrent insert for their whole duration; the concurrent and NOT VALID versions did not.
• Batching a backfill cut another session’s wait from 23 seconds to 7 milliseconds, for about the same total time.

Clean up

DROP TABLE payments;
DROP PROCEDURE backfill_paise(int);

Check yourself

1. How can you tell, without reading the documentation, whether an ALTER TABLE rewrote the table?

   Show answer

   Compare pg_relation_filenode before and after. A rewrite creates a new file for the table, so the number changes.

   I could explain this without looking

2. The concurrent index build took longer than the plain one. Why is it still the right choice on a busy table?

   Show answer

   The plain build blocks every insert, update and delete for its whole duration. The concurrent one does more work but lets writes continue, so the extra time costs nothing visible to users.

   I could explain this without looking

3. Why did the single-statement backfill block an update to row 10 for 23 seconds?

   Show answer

   The big UPDATE had already changed row 10 and holds that row’s lock until its transaction commits at the end. In batches, each batch commits within a fraction of a second and releases its locks.

   I could explain this without looking