Postgres ALTER COLUMN TYPE Without a Table Rewrite

ALTER TABLE ... ALTER COLUMN ... TYPE does not always rewrite the table. PostgreSQL can make some changes as catalog updates because every stored value already has a valid representation under the new type.

The trap is that “no rewrite” does not mean “no lock.” ALTER COLUMN TYPE still takes ACCESS EXCLUSIVE, so it must wait for every conflicting transaction and blocks every new read and write once it reaches the front of the lock queue.

A metadata-only type change is fast after the lock is granted. It is not safe to wait for that lock without a timeout.

Does ALTER COLUMN TYPE always rewrite the table?

Type change	Table rewrite	Lock mode	Important condition
varchar(50) to varchar(255)	No	ACCESS EXCLUSIVE	New limit is wider
varchar(50) to text	No	ACCESS EXCLUSIVE	No collation change
varchar(50) to unconstrained varchar	No	ACCESS EXCLUSIVE	Removes the length limit
numeric(10,2) to numeric(18,2)	No	ACCESS EXCLUSIVE	Precision increases, scale stays 2
numeric(10,2) to numeric(18,4)	Yes	ACCESS EXCLUSIVE	Scale changes stored values
integer to bigint	Yes	ACCESS EXCLUSIVE	Physical representation changes
cross-family conversion with USING	Usually yes	ACCESS EXCLUSIVE	Treat as a rewrite until proven otherwise

PostgreSQL’s ALTER TABLE documentation gives the general rule: changing a type normally rewrites the table and its indexes. It can skip the table rewrite when the conversion does not change the stored contents and the old type is binary-coercible to the new type.

That definition is correct and not very usable during review. The table above turns the most common cases into concrete decisions.

Why varchar widening is metadata-only

PostgreSQL stores a varchar(50) value in the same variable-length representation it would use for varchar(255) or text. The length is a constraint on accepted values, not a fixed-width allocation inside every row.

Widening the limit therefore does not require changing existing row bytes:

SET lock_timeout = '2s';
SET statement_timeout = '5min';

ALTER TABLE customers
  ALTER COLUMN external_reference TYPE varchar(255);

The same applies when removing the limit entirely or moving from a varchar type to text, provided the collation does not change.

The reverse direction is not equivalent:

ALTER TABLE customers
  ALTER COLUMN external_reference TYPE varchar(20);

PostgreSQL must inspect and coerce existing values against the narrower limit. Treat that as a rewrite and verify whether any current value exceeds the new bound before planning the deploy.

Numeric precision is safe only when scale stays fixed

Increasing precision while preserving scale gives the type more digits without changing how existing values are represented:

ALTER TABLE invoices
  ALTER COLUMN total TYPE numeric(18,2);

Changing the scale is different:

ALTER TABLE invoices
  ALTER COLUMN total TYPE numeric(18,4);

The stored value 12.30 must become a value with a different scale. PostgreSQL cannot treat that as a catalog-only constraint change. The PostgreSQL developers have explained the distinction directly: precision-only increases can be optimized, while scale changes mutate the datum and require a rewrite.

The conservative rule is simple: only call a numeric change metadata-only when precision increases and scale is unchanged. Everything else needs proof.

Why integer to bigint still rewrites

integer and bigint can represent many of the same values, but they do not use the same physical width. An integer occupies four bytes and a bigint occupies eight. PostgreSQL has to build new tuples with the larger representation.

ALTER TABLE events
  ALTER COLUMN id TYPE bigint;

On a large table this is not an “increase the limit” operation. It is a full table rewrite, index work, extra disk usage, WAL generation, and an ACCESS EXCLUSIVE lock held for the operation.

Use expand and contract instead:

1. Add a new bigint column.
2. Dual-write or keep it synchronized.
3. Backfill in small, restartable batches.
4. Move foreign keys and application reads.
5. Swap only after the old path is no longer needed.

The same caution applies to most cross-family conversions, even when an implicit cast exists.

No rewrite can still cause an outage

This migration can finish in milliseconds on a hundred-million-row table:

ALTER TABLE customers
  ALTER COLUMN external_reference TYPE varchar(255);

It can also wait for minutes before it starts. PostgreSQL’s ALTER TABLE documentation says the default is ACCESS EXCLUSIVE unless a subcommand is explicitly documented with a weaker lock. No weaker lock is documented for SET DATA TYPE.

The sequence that causes an outage is:

1. An older transaction holds ACCESS SHARE on customers.
2. The type change asks for ACCESS EXCLUSIVE and waits.
3. New SELECT, INSERT, UPDATE, and DELETE requests arrive behind the waiting DDL.
4. The pool fills even though the catalog change itself would take milliseconds.

Always bound the acquisition wait:

SET lock_timeout = '2s';
SET statement_timeout = '5min';
SET application_name = 'migrate:widen_customer_reference';

ALTER TABLE customers
  ALTER COLUMN external_reference TYPE varchar(255);

If two seconds is not enough to find a quiet lock window, fail the deploy and retry. Do not let a metadata-only migration sit at the front of a production lock queue indefinitely.

How to prove a rewrite in a disposable database

Do not test this by timing a nearly empty table. Check whether PostgreSQL replaced the table’s physical file.

CREATE TABLE rewrite_probe (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code varchar(50) NOT NULL
);

INSERT INTO rewrite_probe (code)
SELECT md5(i::text)
FROM generate_series(1, 100000) AS i;

SELECT relfilenode
FROM pg_class
WHERE oid = 'rewrite_probe'::regclass;

ALTER TABLE rewrite_probe
  ALTER COLUMN code TYPE varchar(255);

SELECT relfilenode
FROM pg_class
WHERE oid = 'rewrite_probe'::regclass;

If relfilenode stays the same, that tested change did not rewrite the table. Repeat the exercise against the exact PostgreSQL major version and the exact source and target types you use. Extensions and domains can add coercion behavior that a generic table cannot predict.

The test answers the rewrite question. It does not waive the ACCESS EXCLUSIVE lock, so run the real migration with lock_timeout anyway.

Indexes and statistics still matter

Skipping a table rewrite does not guarantee zero secondary work. PostgreSQL may rebuild indexes unless it can prove the old and new index definitions are logically equivalent. A collation change is the obvious case where the sort order changes and the index must be rebuilt.

PostgreSQL also removes the changed column’s statistics during SET DATA TYPE. Run ANALYZE afterwards so the planner does not operate with missing statistics:

ANALYZE customers (external_reference);

This is why a technically metadata-only change still deserves a production plan.

What pgfence can prove

Without the source column type, static SQL only shows the target:

ALTER TABLE customers
  ALTER COLUMN external_reference TYPE varchar(255);

That is not enough to know whether the change widens varchar(50), narrows varchar(500), or converts an unrelated type. pgfence therefore treats the source as unknown unless you provide a schema snapshot.

Generate and use a schema snapshot so pgfence can compare the actual source column with the target:

pgfence snapshot \
  --db-url postgres://readonly@replica:5432/app \
  --output pgfence-snapshot.json

pgfence analyze --snapshot pgfence-snapshot.json migrations/*.sql

With schema context, pgfence can prove text-family changes and varchar widening, and it can flag numeric changes for widening verification. It reports the same ACCESS EXCLUSIVE lock in every case, then lowers the rewrite risk only when the available source and target facts support it.

For any case that cannot be proven statically, trace mode can test the migration against disposable PostgreSQL and observe whether relfilenode changed.

Review checklist

• Identify the exact source type, target type, typmod, scale, and collation.
• Treat unknown source schema as a possible rewrite.
• Remember that metadata-only still takes ACCESS EXCLUSIVE.
• Put lock_timeout before the first DDL statement.
• Plan for index rebuilds and temporary disk if PostgreSQL cannot reuse them.
• Run ANALYZE after changing the type.
• Use expand and contract for physical representation changes.
• Verify unusual domains and extension types on the production PostgreSQL major version.

For the lock queue mechanics, see Postgres ALTER TABLE hangs. For staged column changes, see the expand and contract pattern.