Postgres is a powerful and popular open-source object-relational database system. However, like any complex system, it has its intricacies. One of the most important concepts to understand for maintaining a healthy and performant Postgres database is the idea of a table rewrite.
When Postgres "rewrites" a table, it creates a brand-new copy of the relation (a new relfilenode) and swaps it in. This process often requires approximately 2Γ temporary disk space and usually holds strong locks that can block traffic. A handy mental model is: if the on-disk row layout must change or the table has to be physically moved/reordered, expect a rewrite.
This article will explore which Postgres operations cause a table rewrite, which ones don't, and the locking implications of these operations.
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. |
The default lock level for most ALTER TABLE forms is ACCESS EXCLUSIVE:
- ACCESS EXCLUSIVE blocks everything
- SHARE blocks writes but not reads;
- SHARE UPDATE EXCLUSIVE allows normal reads & writes.
How to Test for a Rewrite
You can verify whether an operation causes a table rewrite by checking 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 occurred. This is a reliable way to test risky DDL before running it in production.
Playbook
To avoid production issues with re-write:
Prefer non-rewriting forms: Use binary-compatible type changes (e.g., varchar(50) to varchar(100)) when possible.
Schedule rewrites: Plan operations like VACUUM FULL, CLUSTER, and SET TABLESPACE during maintenance windows.
Conclusion
Understanding which Postgres operations cause table rewrites and their associated locking behavior is crucial for maintaining a healthy and performant database. Modern Postgres databases know which Postgres ops rewrite tables and what they lock. Newer versions skip many rewrites (e.g., constant DEFAULT). Follow the playbook: prefer non-rewriting changes and schedule rewrites in off-peak hours.
