---
title: "Which PostgreSQL column type changes rewrite the table | Proofline"
description: "Widening a varchar finishes at once. Changing integer to bigint copies every row while reads wait."
url: https://proofline.sh/learn/postgres-alter-column-type-rewrite/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 20, 2026 3 min read

## 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](https://www.postgresql.org/docs/16/sql-altertable.html) 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:

| Change | Table rewritten |
| --- | --- |
| `varchar(20)` to `varchar(40)` | No |
| `varchar(40)` to `text` | No |
| `text` to `varchar(40)` | Yes |
| `numeric(10,2)` to `numeric(12,2)` | No |
| `numeric(12,2)` to `numeric(12,4)` | Yes |
| `integer` to `bigint` | Yes |
| `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](https://www.postgresql.org/docs/release/12.0/), 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](/learn/database-migration-review/) describes that expand-and-contract sequence, and [the integer ID headroom lab](/learn/postgres-integer-id-headroom/) 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](/learn/postgres-migration-lock-queue/) 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](/learn/postgres-migration-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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Find the rows a PostgreSQL column type change will reject or misread](/learn/postgres-column-type-conversion-check/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [How much room is left in a PostgreSQL integer ID](/learn/postgres-integer-id-headroom/)
    
    Capacity overruns The Proofline team November 20, 2026 3 min read
