PostgreSQL ALTER COLUMN TYPE: How to Change a Column Type Without Rewriting the Whole Table

We needed to widen an integer id to bigint before it overflowed. The migration was one line. It also took an ACCESS EXCLUSIVE lock on a 150-million-row table and rewrote every row for forty minutes, during which the application was completely down. We found that out in staging, thankfully, not production.

The lesson: ALTER TABLE ... ALTER COLUMN ... TYPE is sometimes instant and sometimes an outage, and the difference is whether PostgreSQL has to rewrite the table. Knowing which is which — and how to avoid the rewrite — is the whole game.

Why some type changes rewrite the table

Changing a column type can change the on-disk representation of every value in that column. When it does, PostgreSQL must rewrite the entire table to the new format, and it holds an ACCESS EXCLUSIVE lock the whole time — blocking all reads and writes. On a large table that is a long, total outage.

Worse, a rewrite caused by a type change also forces every index on the table to be rebuilt, adding to the time the table is locked.

The changes that are free

Some type changes are binary-coercible: the stored bytes are already valid for the new type, so PostgreSQL can change the column's type as a metadata-only operation with no rewrite. The classic example is varchar(n) to text, or widening a varchar(n) to a larger length. Increasing the precision of a numeric without changing scale is another.

Notably, integer to bigint is NOT free — the representations differ — which is exactly why our migration rewrote the table. Test the specific change; do not assume.

-- Free: binary-coercible, metadata-only, instant
ALTER TABLE users ALTER COLUMN email TYPE text;        -- from varchar(255)

-- Not free: rewrites every row + rebuilds indexes under ACCESS EXCLUSIVE
ALTER TABLE events ALTER COLUMN id TYPE bigint;        -- from integer

The safe pattern for expensive changes

When a change does require a rewrite, avoid doing it in place on a live table. Instead, add a new column of the target type, backfill it in batches while the application keeps running, keep it in sync with a trigger, and then swap the columns in a quick final step.

Each piece is cheap and online: adding a nullable column is instant, backfilling in batches never holds a long lock, and the final rename is fast. You trade one giant locked operation for several small unlocked ones.

-- 1. Add the new column (instant, nullable)
ALTER TABLE events ADD COLUMN id_big bigint;

-- 2. Keep it in sync for new writes
CREATE FUNCTION sync_id_big() RETURNS trigger AS $$
BEGIN NEW.id_big := NEW.id; RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER events_sync_id_big BEFORE INSERT OR UPDATE ON events
  FOR EACH ROW EXECUTE FUNCTION sync_id_big();

-- 3. Backfill existing rows in batches (no long lock)
UPDATE events SET id_big = id WHERE id_big IS NULL AND id BETWEEN 1 AND 100000;
-- ... repeat over ranges ...

-- 4. Swap in a short transaction once backfilled + validated

Protect yourself regardless

Whatever change you run, set a lock_timeout first so a type change that does need a strong lock fails fast instead of queueing behind a long query and freezing the application — the same lock-queue trap that catches plain DDL.

And always rehearse the exact ALTER on a production-sized copy. The only reliable way to know whether a given change rewrites the table is to run it and watch.

• Type changes rewrite the table when the on-disk format changes — full lock, index rebuilds.
• Binary-coercible changes (varchar→text, widen varchar, raise numeric precision) are instant.
• integer→bigint does rewrite; never assume a change is free.
• For real rewrites: add column, backfill in batches, sync with a trigger, swap.
• Set lock_timeout and rehearse on a production-sized table.

The practical standard

The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.