Renaming one column on a live table, all the way through

I had a writer loop hammering a table — a single-row UPDATE in a tight while, nothing clever — and from a second connection I renamed the column it was writing to. Postgres 17, Supabase, five million rows.

RENAME COLUMN took 2.0 ms on the server
18 writes succeeded before the rename, 18 failed after
first error: 42703 column "full_name" does not exist

Two milliseconds. The rename is not slow, and it does not lock anything for long. It is still the most destructive thing in this article, because the instant it commits, every process holding a plan or a query string with the old name starts throwing 42703. Your deploy drains over minutes. The rename commits in one statement. There is no window in which both the old and the new code work, which is the entire problem.

So this is one column, users.full_name becoming users.display_name, carried through expand, backfill, dual-write, cutover and contract — with what each step actually costs, measured against a live Postgres 17.6 instance, and the specific thing that broke when I skipped a step. All the timings come from a throwaway table shaped like a users table: five million rows, 475 MB heap, bigserial primary key, text columns for name and email. Where a number came from the smaller 200,000-row walkthrough table I say so.

One thing up front, because the client round-trip to this instance has a median of 146.8 ms over twenty SELECT 1s: anything faster than that I measured server-side, inside a DO block with clock_timestamp() deltas. Client wall-clock numbers below 200 ms are mostly network.

The lock that hurts is not the one you are holding

Every migration guide tells you ALTER TABLE takes ACCESS EXCLUSIVE. True, and mostly irrelevant, because the dangerous part is who queues up behind it.

Three connections. A runs a long read inside a transaction, so it holds ACCESS SHARE for twenty seconds. B runs the rename and waits, because ACCESS EXCLUSIVE conflicts with A. C arrives a second and a half later and runs SELECT id FROM users WHERE id = 1 — a lookup that is compatible with A and would normally return instantly.

Here is pg_locks while that is happening, read from a fourth connection:

4126874  AccessShareLock        granted=true     <- conn A, the long reader
4126873  AccessExclusiveLock    granted=false    <- conn B, the DDL, waiting
4126872  AccessShareLock        granted=false    <- conn C, stuck behind the DDL

C waited 18,237 ms. It was blocked not by the reader it is compatible with, but by the DDL sitting between them. One slow analytics query plus one innocuous ALTER is a site-wide stall, and nothing in your migration log will say so.

The fix is one line, and it goes on the connection running the DDL:

SET lock_timeout = '3s';

Re-running the same three connections with lock_timeout = '2s' on B: the DDL gave up at 2,150 ms with 55P03 canceling statement due to lock timeout, and C's trivial lookup came back in 657 ms. You retry the migration. You do not take the site down while you wait for someone's report to finish.

Expand

Add the new column. This is the step everyone is nervous about and it is the cheapest one in the sequence.

ALTER TABLE users ADD COLUMN display_name text;

Server-side: 156.8 ms on the 5M-row table, 49.6 ms on the 200,000-row one. No rewrite in either case — I checked relfilenode before and after and it did not change.

The interesting version is with a default, because a lot of advice still says this rewrites the table. On Postgres 17 it does not, as long as the default is non-volatile:

now() is STABLE, so it gets evaluated once and stored in the catalog. random() is VOLATILE, so every row needs its own value and the table gets rewritten into a new relfilenode — which also means you need disk for both copies at once, so "rewrite in place" is the wrong mental model. The Postgres 17 ALTER TABLE notes say the same thing: with a non-volatile default "the default is evaluated at the time of the statement and the result stored in the table's metadata… In neither case is a rewrite of the table required."

What you cannot do is add it NOT NULL with no default at all:

ERROR:  column "b_nn" of relation "users" contains null values
SQLSTATE 23502

Which is why NOT NULL is a later step, not this one.

Dual-write, and why application code is not enough

The standard advice is to ship code that writes both columns. Do that. It is also not sufficient, because the set of things that write to your users table is larger than the set of things in your deploy: psql sessions, the admin panel, a backfill script someone wrote last quarter, another service that was given credentials in 2023.

A BEFORE trigger closes the gap for all of them:

CREATE FUNCTION sync_display_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.display_name IS DISTINCT FROM OLD.display_name THEN
    NEW.full_name := NEW.display_name;
  ELSE
    NEW.display_name := NEW.full_name;
  END IF;
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER sync_display_name_trg
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_display_name();

Both directions verified on the walkthrough table: inserting with full_name = 'Ada Lovelace' left both columns reading Ada Lovelace; writing display_name = 'Grace Hopper' left both reading Grace Hopper.

CREATE TRIGGER took 156 ms and held ShareRowExclusiveLock — it blocks writers, not readers, which is a gentler lock than the ACCESS EXCLUSIVE the ALTERs take.

The cost per row I could not pin down. Server-side, five thousand single-row updates in a loop: median 195.1 ms without the trigger, 222.8 ms with it, so roughly 39 µs against 45 µs per row. But the five repetitions without the trigger ranged from 146 ms to 631 ms on this shared instance, which is an order of magnitude wider than the effect I am trying to measure. Single-digit microseconds per row is the honest answer, and I would not build a capacity plan on it. Measuring it from the client was worse than useless — 149.3 ms against 147.4 ms per write, which is the network, not the trigger.

Backfill

UPDATE users SET display_name = full_name;

Do not. On five million rows that statement outran a 120-second window twice, and when the client died the whole thing rolled back — no partial progress, nothing to resume from. Two full-table backfills also took that table's heap from 475 MB to 1441 MB, with 449,956 dead tuples waiting on autovacuum. A backfill is a table rewrite performed slowly and with extra steps.

Batch it by primary key range:

UPDATE users SET display_name = full_name
WHERE id >= $1 AND id < $1 + 50000 AND display_name IS NULL;

Five batches of 50,000 covered the 200,000-row walkthrough table in 7,993 ms total, leaving zero NULLs. On the 5M-row table, 50,000-row batches settled at 2.0–2.9 s each after the first few warmed the cache.

The batching mistake worth naming is the obvious one:

-- costs 18-25 s per batch instead of 2-3 s
UPDATE users SET display_name = full_name
WHERE id IN (SELECT id FROM users WHERE display_name IS NULL ORDER BY id LIMIT 50000);

Same fifty thousand rows, measured at 24,782 / 22,017 / 18,337 ms against 2,037 / 2,185 / 2,878 ms for the id-range form. Every batch rescans looking for the NULLs it has not reached yet, and as the backfill progresses it rescans further each time. Keep a cursor, not a predicate.

Making it NOT NULL without a 20-second stall

SET NOT NULL scans the whole table under ACCESS EXCLUSIVE. On five million rows: 19,511 ms in which nothing else could touch the table. That is not a migration, that is an outage with a ticket number.

Split it:

ALTER TABLE users
  ADD CONSTRAINT display_name_not_null CHECK (display_name IS NOT NULL) NOT VALID;
 
ALTER TABLE users VALIDATE CONSTRAINT display_name_not_null;
 
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

19.5 seconds of hard blocking becomes 0.7. The scan still happens, it just happens under a lock that readers and writers can share. And the last step is fast because Postgres skips its own scan when a valid CHECK already proves no NULL can exist — the 17 docs spell this out under SET/DROP NOT NULL.

Cutover and contract

Ship the read change. Then wait — longer than your deploy takes, longer than your longest-lived worker, longer than whatever cached a query plan. This is the step with no measurement, because the thing you are waiting on is your own organisation.

Then drop the column. It is fast: 143 ms on the walkthrough table, 35.6 ms server-side on the 5M-row one, AccessExclusiveLock, no rewrite. Postgres marks the attribute dropped in the catalog and reclaims the space lazily. If you have read that DROP COLUMN locks a big table for hours, that is not what this instance does.

What did bite me is that the trigger outlives the column:

DROP COLUMN full_name  ->  143 ms, succeeded
trigger still installed?  [ { tgname: 'sync_display_name_trg' } ]
INSERT ...             ->  42703 record "new" has no field "full_name"

The drop succeeds. Postgres does not track column references inside a plpgsql function body, so nothing warns you. Every single write to the table then fails, and it fails at runtime, from a trigger nobody is looking at, in the step you thought was the cleanup. Drop the trigger and the function in the same transaction as the column, or drop them first.

Where this stops being true

Everything above is one Postgres 17.6 instance, one table, one column type, and a 146.8 ms round-trip that I had to measure around rather than through. A table with foreign keys pointing at it, or partitions, or logical replication downstream, will behave differently at the contract step in ways I did not test. VALIDATE CONSTRAINT under concurrent write load is the number I trust least — my writers were single-row updates in a loop, not a real workload, so the 11 seconds is close to a floor.

The ALTER COLUMN … TYPE case I only touched glancingly, and the little I measured says the folklore is backwards in both directions. text to varchar(400) on five million rows: 29.5 seconds under ACCESS EXCLUSIVE, with a second AccessExclusiveLock taken on the primary key index while it rebuilt. But varchar(100) to varchar(400) was 15.9 ms with the relfilenode unchanged, and varchar(400) to text was 0.4 ms — while varchar(400) down to varchar(50) took 40.2 s and moved the relfilenode from 35802 to 35890. Widening is catalog-only, narrowing rewrites, and neither of those is what "changing a column type" sounds like it should cost. Which pairs fall on which side of that line is a table I do not have and would want before —