What happens when you run ALTER TABLE ... ALTER COLUMN TYPE on a large table?
In most cases PostgreSQL rewrites the whole table while holding an ACCESS EXCLUSIVE lock, so reads and writes wait. Only some changes, such as widening varchar or dropping a length limit, skip the rewrite.
Explain it simply
Imagine changing every ticket in a train's reservation chart from pencil to ink. The chart must be frozen while you do it, or passengers would see half-pencil, half-ink. PostgreSQL does the same: when the new type needs different stored bytes, it builds a new copy of the table and swaps it in, blocking every other query on that table for the duration.
Some changes are only metadata and finish instantly, for example increasing the limit of varchar(n) or changing varchar(n) to text.
Example
-- Instant: metadata only
ALTER TABLE users ALTER COLUMN name TYPE text;
-- Full rewrite: bytes change, table locked
ALTER TABLE events ALTER COLUMN id TYPE bigint;
-- Safer pattern for a big live table
ALTER TABLE events ADD COLUMN id_new bigint;
-- backfill in small batches, add index, then swap names in a short transactionSetting lock_timeout before the ALTER stops it from queueing behind a long query and blocking everyone behind it.
What to say in the interview
"A type change usually rewrites the table under an ACCESS EXCLUSIVE lock. For a big live table I add a new column, backfill in batches, then switch over, and I set lock_timeout so the DDL fails fast instead of piling up a lock queue."
Common mistakes
- Running the ALTER during peak traffic without a lock_timeout.