Postgres ALTER COLUMN TYPE: When the Rewrite Costs and How to Avoid It

The ALTER TABLE statement to change a column's type is one of those Postgres operations where the documentation tells you the syntax but not what is going to happen to your production system when you run it. The naive version — ALTER TABLE events ALTER COLUMN payload TYPE jsonb — looks like a simple metadata change. In practice it can rewrite the entire table while holding an ACCESS EXCLUSIVE lock that blocks every read and write. On a hundred-million-row table that is hours of downtime.

The actual cost depends on the specific type change. Some changes are metadata-only and complete in milliseconds regardless of table size. Some require a full table scan but no rewrite. Some require a full table rewrite which produces both the lock duration and a temporary doubling of disk space. The difference between the three categories is not always obvious from the type names involved.

The three categories

The first category is binary-compatible changes. If the new type has the same on-disk representation as the old type, Postgres can change the type by updating the system catalogs without touching the heap. The classic examples are varchar(50) to varchar(100), varchar to text, and length-decreasing changes when the data already fits in the new length. These changes acquire ACCESS EXCLUSIVE briefly to update catalogs and release it; the table itself is untouched.

The second category is verify-only changes. The new type is not binary-compatible with the old, but the conversion is straightforward enough that Postgres can verify every row against the new type without rewriting. The classic examples are varchar to types with a CHECK constraint that the existing data satisfies, and some integer-width changes when the values fit in the narrower type. These changes acquire ACCESS EXCLUSIVE and scan the table to verify, then release. The duration scales with table size but is faster than a rewrite because no data is written.

The third category is full rewrites. The on-disk representation changes, so Postgres has to write every row in the new format. The rewrite happens under ACCESS EXCLUSIVE, so the table is unavailable for reads and writes for the duration. Disk usage temporarily doubles because the old and new versions of the table coexist until the rewrite completes and the old version is truncated. The classic examples are most integer-to-bigint conversions, text-to-jsonb, and any USING clause that transforms values.

How to predict which category

The Postgres documentation has tables of which type pairs are binary-compatible, but the easier diagnostic is to test on a copy. CREATE TABLE test_table (LIKE production_table INCLUDING ALL); followed by an INSERT INTO test_table SELECT * FROM production_table LIMIT 1000; and an ALTER TABLE test_table ALTER COLUMN ... gives you both the timing characteristics and any conversion errors before you run the same statement against production.

The EXPLAIN (FORMAT JSON, ANALYZE) SELECT ... output on the test does not directly tell you the type change category, but you can infer it from the timing. A binary-compatible change completes in a few milliseconds regardless of row count. A verify-only change scales linearly with row count but at a few microseconds per row. A full rewrite scales linearly at a much higher constant — typically tens of microseconds per row, depending on tuple size.

The other diagnostic is the disk usage. During a full rewrite, SELECT pg_total_relation_size('table_name') can be observed to grow temporarily before snapping back at the end. During a verify-only change, the size stays constant. During a binary-compatible change, the operation completes before you can observe the size.

The patterns that avoid the rewrite

The standard pattern for unavoidable type changes is the expand-contract pattern across multiple migrations. The shape is:

Migration 1: Add the new column with the desired type. ALTER TABLE ADD COLUMN is metadata-only when the column allows NULL or has a constant default (Postgres 11+), so this completes quickly.

Migration 2: Backfill the new column from the old column in chunks. The backfill is a series of UPDATE statements that touch a small fraction of rows per statement, releasing locks between statements and letting normal traffic flow.

Migration 3: Add code that writes to both columns and reads from the new column. Deploy and verify.

Migration 4: Remove the old column. DROP COLUMN is metadata-only — Postgres marks the column dropped in the catalog and reclaims space lazily during the next VACUUM.

The pattern trades multiple migrations and a brief period of dual-write code for the ability to keep production traffic flowing throughout. The total work done is more than a single ALTER COLUMN TYPE, but the work is spread over time and does not require a lock window.

Integer-to-bigint as canonical case

The most common type change that bites teams is integer to bigint. The standard reason is an id column that started as integer and is approaching the 2.1 billion limit. The ALTER COLUMN TYPE BIGINT against a hundred-million-row table will rewrite the entire table, lock everything for hours, and double the disk usage during the rewrite.

The expand-contract pattern for this case adds a new id_new bigint column, backfills in chunks via UPDATE table SET id_new = id WHERE id_new IS NULL AND id BETWEEN x AND y, switches code to use id_new, and eventually drops the old id. The complication is that id is typically a primary key and has foreign keys pointing at it, which means the migration has to coordinate the type change across all referencing tables. The full migration for a primary key type change is multi-week effort across multiple deploys; the brute-force ALTER COLUMN TYPE is one statement of code and several hours of downtime.

The pattern that avoids both extremes is changing the type at table creation, using bigint from day one for any column that might plausibly reach 2 billion rows. The retroactive type change is expensive enough that conservative-default-at-creation pays off most of the time.

What the USING clause does

The USING clause in ALTER COLUMN TYPE specifies an expression to convert old values to new values. The clause is required for any type change that is not implicitly castable. Some teams discover that adding USING converts a verify-only change into a full rewrite.

The mechanism is that USING means every row needs to be evaluated against the expression to produce the new value, and the new value is written to the heap. The rewrite is unavoidable in this case because the on-disk representation has to change to the new value. The lock is still ACCESS EXCLUSIVE and the rewrite still scales with table size.

The pattern for USING-required changes is the same as for any other rewrite-required change: do it in a maintenance window if the table is small enough, or use the expand-contract pattern if it is not.

The constraint trap

One subtlety is that constraints on the column may force a rewrite even when the type change would otherwise be metadata-only. Adding a NOT NULL constraint to a column that did not have one previously requires verifying every row, which is a verify-only operation. Adding a NOT NULL via ALTER COLUMN SET NOT NULL takes ACCESS EXCLUSIVE for the duration of the verification.

The Postgres 12+ pattern that avoids this is adding a CHECK constraint with NOT VALID first, validating it as a separate step (which takes SHARE UPDATE EXCLUSIVE, not ACCESS EXCLUSIVE), and converting the CHECK to NOT NULL when validation completes. The total elapsed time is similar but the lock duration is much shorter and concurrent writes are not blocked.

What we use across the four products

Our four products run on SQLite, which has different mechanics. SQLite's ALTER TABLE is restricted enough that the standard pattern for type changes is creating a new table with the desired schema, copying data via INSERT INTO new SELECT FROM old, dropping the old table, and renaming the new table. The whole operation is atomic if wrapped in a transaction, and the lock duration is essentially the time to copy the data.

The Postgres migration plan for the products specifies expanding the schema decisions early: bigint for any ID column, jsonb for any column likely to have nested structure, timestamptz for any timestamp, text for any string. The conservative-default-at-creation pattern is cheap during the initial design and expensive to fix later. The few cases that have come up where SQLite's type system loses information that Postgres would preserve get flagged at migration time.

What this tells us about Postgres

ALTER COLUMN TYPE is one of those operations where the syntax is simple but the operational cost varies by orders of magnitude depending on which specific type change. The right discipline is to test on a copy, predict which category the change falls into, and pick a migration strategy that matches the table size and traffic pattern. The brute-force ALTER COLUMN TYPE is correct for small tables and during maintenance windows; the expand-contract pattern is correct for large tables under continuous load.

The deeper observation is that schema changes in mature production databases are operational events rather than syntactic events, and the cost of the operation depends on what the database has to do under the covers rather than what the SQL statement says. The discipline of reading what the actual work is — rewrites versus verifies versus metadata-only changes — is what separates teams that do schema migrations smoothly from teams that schedule emergency maintenance windows.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.