A table rewrite in Postgres is exactly what it sounds like: instead of editing the existing table in place, Postgres builds a brand-new copy of the relation (a new relfilenode) and swaps it in. Two things make this worth caring about. First, it needs roughly 2x the table's size in temporary disk space while both copies exist. Second, it usually takes a strong lock that blocks traffic for the duration.
The mental model that gets you most of the way there: if the on-disk row layout has to change, or the table has to be physically moved or reordered, expect a rewrite. Everything else is usually metadata-only.
Below is which operations trigger a rewrite, which ones don't, and what each one locks while it runs.
Which Operations Cause a Table Rewrite?
Here is a summary of common Postgres operations and whether they cause a table rewrite:
| Operation | Rewrite? | Lock Level | Why? |
|---|---|---|---|
TRUNCATE | ✅ Yes | ACCESS EXCLUSIVE | Deletes table content by replacing its file. |
ALTER TABLE ... SET TABLESPACE | ✅ Yes | ACCESS EXCLUSIVE | Moves data to new storage layout or tablespace. |
ALTER TABLE ... SET LOGGED/UNLOGGED | ✅ Yes | ACCESS EXCLUSIVE | Changing persistence forces a rewrite. |
ALTER TABLE ... ALTER COLUMN TYPE (incompatible) | ✅ Often | ACCESS EXCLUSIVE | Rewrite if type change isn't binary-compatible. |
ALTER TABLE ... ALTER COLUMN TYPE (compatible) | ❌ No | ACCESS EXCLUSIVE | Binary-compatible changes (e.g., varchar(50)→varchar(100)) avoid rewrite. |
ALTER TABLE ... ADD COLUMN DEFAULT (constant) | ❌ No (Postgres 11+) | ACCESS EXCLUSIVE | Stored as metadata only. Old rows remain untouched. |
ALTER TABLE ... ADD COLUMN DEFAULT (volatile) | ✅ Yes | ACCESS EXCLUSIVE | Each existing row needs its own computed value. |
ALTER TABLE ... DROP COLUMN | ❌ No (mostly) | ACCESS EXCLUSIVE | Column is marked dropped in metadata; no immediate rewrite. |
ALTER TABLE ... ALTER COLUMN SET NOT NULL | ❌ No | ACCESS EXCLUSIVE | Metadata-only change (with validation scan). |
ALTER TABLE ... ALTER COLUMN SET STORAGE | ❌ No | ACCESS EXCLUSIVE | Only changes future TOAST strategy; existing rows unchanged. |
CREATE INDEX | ❌ No | SHARE | Creates a separate index file, blocks writes but not reads. |
CREATE INDEX CONCURRENTLY | ❌ No | SHARE UPDATE EXCLUSIVE | Allows reads and writes; takes longer. |
DROP INDEX | ❌ No | ACCESS EXCLUSIVE | Deletes index files, leaves table file alone. |
VACUUM FULL | ✅ Yes | ACCESS EXCLUSIVE | Compacts table into a new file, frees all space. |
CLUSTER | ✅ Yes | ACCESS EXCLUSIVE | Reorders the table based on an index into a new file. |
ANALYZE | ❌ No | SHARE UPDATE EXCLUSIVE | Collects statistics, no file change. |
VACUUM (regular) | ❌ No | SHARE UPDATE EXCLUSIVE | Cleans dead tuples in-place, no rewrite. |
A couple of things are worth pulling out of the table.
A rewrite and a heavy lock are not the same thing. Plenty of ALTER TABLE forms take an ACCESS EXCLUSIVE lock without rewriting anything: DROP COLUMN, SET NOT NULL, and SET STORAGE all just touch metadata, but they still block the table while they do it. So even a "free" change can stall traffic if the table is busy.
The type change is the one most people get wrong. Going from varchar(50) to varchar(100) is binary-compatible and skips the rewrite; changing int to text is not, and Postgres has to rebuild every row. Same statement, very different cost depending on the types involved.
And the constant-DEFAULT case is a genuine improvement: before Postgres 11, ADD COLUMN ... DEFAULT <constant> rewrote the whole table. Since 11 it's stored as metadata and old rows are left alone. A volatile default (something like now() or random()) still forces a per-row rewrite, because each row needs its own computed value.
Here's how the three lock levels in the table differ:
- ACCESS EXCLUSIVE blocks everything
- SHARE blocks writes but not reads;
- SHARE UPDATE EXCLUSIVE allows normal reads & writes.
How to Test for a Rewrite
You don't have to take the table's word for it. Check the relfilenode before and after the operation:
-- 1) Check the current relfilenode
SELECT pg_relation_filenode('public.mytable');
-- 2) Run your DDL operation
-- 3) Check again
SELECT pg_relation_filenode('public.mytable');If the value changed, a rewrite happened. This is the most reliable way to find out what a piece of DDL will actually do, and it's cheap to run against a copy of the table before you ship it to production.
Playbook
Two habits keep rewrites from turning into incidents.
Prefer non-rewriting forms. Where you have a choice, reach for the binary-compatible change. Widening varchar(50) to varchar(100) skips the rewrite; picking a type that doesn't is what bites you.
Schedule the rewrites you can't avoid. VACUUM FULL, CLUSTER, and SET TABLESPACE are going to rewrite the table and hold ACCESS EXCLUSIVE no matter what, so run them in a maintenance window rather than at peak.
Conclusion
Most of the surprises here come from assuming a quick-looking ALTER TABLE is also a cheap one. It often isn't: the question is whether the row layout has to change. Newer Postgres versions have quietly removed a lot of the old rewrites (the constant DEFAULT case being the big one), but when a rewrite is unavoidable, check it with relfilenode first and schedule it for off-peak hours.