Learning center

Which PostgreSQL column type changes rewrite the table

Widening a varchar finishes at once. Changing integer to bigint copies every row while reads wait. Check the table's file node before and after the change to learn which kind your migration is, then rehearse the slow kind at production size.

Why the rewrite decides the rehearsal#

Every ALTER COLUMN ... TYPE takes an ACCESS EXCLUSIVE lock, which blocks reads and writes on the table. When PostgreSQL can reuse the stored values, it releases that lock after a catalog update. When it cannot, it writes a new copy of the table and rebuilds its indexes before it releases the lock. The ALTER TABLE documentation states the rule: no rewrite is needed when the old type is binary coercible to the new one and the USING clause does not change the values. Indexes may still be rebuilt.

The rule is short, but whether a given pair of types is binary coercible is not obvious from reading the migration. Test it.

Watch the file node change#

The file node identifies the file that holds a table's rows. A rewrite gives the table a new one. Run this in a scratch database. We ran it on PostgreSQL 16.15.

CREATE TABLE invoices (
    id integer PRIMARY KEY,
    customer_ref varchar(20) NOT NULL,
    amount numeric(10,2) NOT NULL,
    issued_at timestamp NOT NULL
);
INSERT INTO invoices
SELECT g, 'C' || g, g * 1.5, now() FROM generate_series(1, 100000) g;

SELECT pg_relation_filenode('invoices');

Run one ALTER at a time, and read the file node again after each.

ALTER TABLE invoices ALTER COLUMN customer_ref TYPE varchar(40);
SELECT pg_relation_filenode('invoices');

These are the results we recorded, each compared with the file node just before the statement:

ChangeTable rewritten
varchar(20) to varchar(40)No
varchar(40) to textNo
text to varchar(40)Yes
numeric(10,2) to numeric(12,2)No
numeric(12,2) to numeric(12,4)Yes
integer to bigintYes
timestamp to timestamptz with TimeZone = 'Europe/Berlin'Yes
timestamptz back to timestamp with TimeZone = 'Europe/Berlin'Yes
timestamp to timestamptz with TimeZone = 'UTC'No

The pattern follows the stored representation. A longer length limit and more digits before the decimal point leave the stored values valid as they are. A shorter limit forces PostgreSQL to check each value, and a change of scale or integer width changes the bytes. The time zone row is the surprise: the same statement rewrites the table or not depending on the session setting that the migration runner happens to use. Since PostgreSQL 12, the conversion between timestamp and timestamptz skips the rewrite only when the session time zone is UTC, because then the stored values mean the same instant.

Time the rewrite at production size#

A rewrite holds the lock for as long as it takes to copy the table and build every index on it. Restore a recent snapshot or generate the same row count, then time the statement:

\timing on
SET lock_timeout = '2s';
ALTER TABLE invoices ALTER COLUMN id TYPE bigint;

The rehearsal gives you the number to compare with what the application tolerates. A few hundred milliseconds may fit inside request timeouts. Minutes will not. For a large table, add a new column of the target type, fill it in batches, and switch to it in a later release instead. The migration review checklist describes that expand-and-contract sequence, and the integer ID headroom lab covers the columns that reference a key you widen.

What the rehearsal does not show#

The file node shows whether a rewrite happened, not how long it held a lock under traffic. The statement first waits for its lock behind any open transaction on the table, and queries queue behind it while it waits. Read why a fast migration blocked every query before you run even a change that the table above lists as instant.

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