In PostgreSQL, which ALTER TABLE forms rewrite the whole table and which only change the catalog, and how do you check before production?
answer
- same lock, different duration
- volatile versus non-volatile default
- atthasmissing and attmissingval
- binary-coercible type changes
- relfilenode changes on rewrite
basics
~20 sIn PostgreSQL, ADD COLUMN with no or a non-volatile default, DROP COLUMN, SET DEFAULT, renames and varchar widening only change catalogs; volatile defaults, identity or stored generated columns and most type changes rewrite the table under a new relfilenode.
solid answer
~40 sMost `ALTER TABLE` forms take `ACCESS EXCLUSIVE`; what differs is how long they hold it. Catalog-only forms finish in milliseconds: `ADD COLUMN` with no default or a non-volatile one (evaluated once and stored in `pg_attribute.attmissingval`, flagged by `atthasmissing`), `DROP COLUMN`, `SET`/`DROP DEFAULT`, `RENAME`, and in PostgreSQL 18 adding a virtual generated column. Rewriting forms copy every row and rebuild every index while holding the lock: `ADD COLUMN` with a volatile default such as `clock_timestamp()` or `gen_random_uuid()`, an identity or `STORED` generated column, and `ALTER COLUMN ... TYPE` unless the old type is binary-coercible (`varchar(30)` to `varchar(64)` or `text` is free; `integer` to `bigint` is not). `SET NOT NULL` and `ADD CHECK` sit between: a full scan, no rewrite. To be sure, run the migration on a restored copy and compare `pg_relation_filenode('accounts')` before and after.
code
sql · 7 linesSELECT pg_relation_filenode('accounts'); -- e.g. 16421
ALTER TABLE accounts ADD COLUMN locale text NOT NULL DEFAULT 'en';
SELECT pg_relation_filenode('accounts'); -- 16421: catalog-only
ALTER TABLE accounts ADD COLUMN public_id uuid DEFAULT gen_random_uuid();
SELECT pg_relation_filenode('accounts'); -- a new number: table rewrittengo deeper
Remember that some ALTER TABLE forms are instant and some copy the whole table, and that a constant default on a new column is the instant kind.
Explain the fast default: one evaluation stored in attmissingval. Know which defaults are volatile, which type changes are binary-coercible, and that SET NOT NULL scans but does not rewrite.
Predict the cost of a migration before running it, prove it with pg_relation_filenode on a restored copy, and redesign rewriting changes into catalog-only steps plus batched work.
Decide where a team draws the line between allowed instant DDL and changes that need a staged plan, and whether to enforce it with review rules or a table_rewrite event trigger.
## Same lock, three different costs Most `ALTER TABLE` forms in PostgreSQL take an `ACCESS EXCLUSIVE` lock, the mode that blocks even plain `SELECT`; the main exceptions are foreign keys, constraint validation and statistics settings. The lock is the same; the time it is held is not. Three cost classes exist: - **Catalog-only (metadata) change.** Only rows in system catalogs such as `pg_attribute` change. Once the lock is granted the statement finishes in milliseconds, whatever the table size. - **Full scan.** Every row is read to prove something, as with `SET NOT NULL` or adding a `CHECK` or foreign key, but no new copy of the table is written. - **Rewrite.** PostgreSQL writes a complete new copy of the heap into a new file (a new **relfilenode**), rebuilds every index, and swaps it in at commit. This needs up to double the disk space, normally writes WAL for the whole table, and holds `ACCESS EXCLUSIVE` from start to finish. The rewriting forms are also not MVCC-safe: a concurrent transaction with an older snapshot sees the table as empty afterwards. ## Forms that only touch the catalog - `ADD COLUMN` with no default: existing rows read the new column as NULL. - `ADD COLUMN` with a **non-volatile** default. The default is evaluated once, at `ALTER` time, and stored in `pg_attribute.attmissingval`, with `atthasmissing` set to true. Any row physically lacking the column returns that stored value; it is written into the row only if the row is rewritten later. This covers constants and **stable** functions: `DEFAULT now()` stores one timestamp, the transaction's, for every existing row. - `ADD COLUMN ... NOT NULL DEFAULT 'en'`: no scan either, because the stored value cannot be NULL. - In PostgreSQL 18, adding a **virtual** generated column, which is what a plain `GENERATED ALWAYS AS (...)` now creates. It is computed on read, so nothing is written. - `DROP COLUMN`: the column is only marked dropped (`attisdropped`); its space is reclaimed as rows are later rewritten or updated. - `SET DEFAULT` and `DROP DEFAULT`: they affect future inserts and updates only. - `RENAME COLUMN`, `RENAME TO` and constraint renames. - `ALTER COLUMN ... TYPE` when the old type is **binary-coercible** to the new one and `USING` does not change the contents: `varchar(30)` to `varchar(64)`, `varchar` to `text`. Indexes are rebuilt only when PostgreSQL cannot prove them equivalent; a collation change forces it. ## Forms that rewrite the table | Form | Why every row must be rewritten | |---|---| | `ADD COLUMN ... DEFAULT clock_timestamp()`, `gen_random_uuid()` or `uuidv7()` | The default is volatile, so each row needs its own value | | `ADD COLUMN ... GENERATED ALWAYS AS IDENTITY` | Each existing row needs its own sequence value | | `ADD COLUMN ... GENERATED ALWAYS AS (...) STORED` | The value must be computed and stored per row | | `ADD COLUMN` of a domain type that has constraints | Each row's value must be checked against the domain | | `ALTER COLUMN ... TYPE integer` to `bigint`, or `varchar(64)` to `varchar(30)` | The on-disk representation changes, or every value must be re-checked | | `ALTER COLUMN ... TYPE timestamp` to `timestamptz` | Rewritten unless the session `TimeZone` is UTC, where the stored values are already identical | | `SET TABLESPACE`, `SET ACCESS METHOD` | The data must physically move or change format | ## Scan without rewrite `ALTER COLUMN ... SET NOT NULL` and `ADD CONSTRAINT ... CHECK` read the whole table under `ACCESS EXCLUSIVE`; `ADD FOREIGN KEY` reads it under a weaker lock that still blocks writes. None of them write a new file, but on a large table the scan alone can take minutes. The `NOT VALID` option followed by `VALIDATE CONSTRAINT` moves that scan to a weaker lock. ## How to know before production 1. Read the Notes section of the `ALTER TABLE` reference page for the form you plan; it lists which forms rewrite. 2. On a restored copy of production, record `pg_relation_filenode('accounts')`, run the migration, and read it again. A changed value means the table was rewritten. 3. Time the migration on that copy at full size, because a scan is invisible to the filenode test but not to users. 4. Combine changes that must rewrite into a single `ALTER TABLE` with several subcommands: PostgreSQL then makes one pass over the table instead of one per change. Teams that want a hard guarantee can also install an event trigger on the `table_rewrite` event, which fires just before a rewrite and can log it or raise an error to stop it. ## What changed in PostgreSQL 18 Up to PostgreSQL 17 every generated column was stored, so adding one always rewrote the table. In 18 a plain `GENERATED ALWAYS AS (...)` column is virtual by default and is added without a rewrite; writing `STORED` brings back the rewrite.
- Why does ADD COLUMN created_at timestamptz DEFAULT now() not rewrite the table in PostgreSQL, while DEFAULT clock_timestamp() does?`now()` is STABLE: it returns the transaction start time, so PostgreSQL evaluates it once and stores that single value as the column's missing value. `clock_timestamp()` is VOLATILE, so each row would need its own value, and the only way to give it one is to rewrite every row. Every existing row therefore gets the same timestamp with `now()`.
- In PostgreSQL, how do you widen accounts.id from integer to bigint without a long ACCESS EXCLUSIVE rewrite?Not with `ALTER COLUMN ... TYPE bigint`, which rewrites the table and every index on the column under the lock. The online route is staged: add a `bigint` column, keep it filled for new rows, backfill in batches, make it NOT NULL without a locked scan, build its unique index concurrently, then switch the key with `ADD CONSTRAINT ... PRIMARY KEY USING INDEX` in a short transaction.
- Any ADD COLUMN with a DEFAULT rewrites every row in PostgreSQL.
- DEFAULT now() rewrites the table because each row needs a timestamp.
- Widening varchar(30) to varchar(64) rewrites the whole table.
- A catalog-only ALTER TABLE takes no lock, so it is always safe.
- DROP COLUMN frees the column's disk space immediately.
- SET NOT NULL rewrites the table to store the new constraint.
Assumes PostgreSQL 18, where a plain GENERATED ALWAYS AS column is virtual and adds without a rewrite; through 17 generated columns were stored only.