PostgreSQL Table Rewrite Scenarios & Best Practices

Key Scenarios Causing Table Rewrites

1. ​Column Type Changes​

Modifying a column’s data type usually triggers a full table rewrite unless:

• The new type is ​binary coercible​ with the original (e.g., text → varchar).
• The column uses a ​domain​ without additional constraints.

​Example:​

-- No rewrite (binary coercible)
ALTER TABLE t1 ALTER COLUMN relowner TYPE integer;

-- Rewrite required (non-coercible type)
ALTER TABLE t1 ALTER COLUMN reltablespace TYPE bigint;

‍

Note:​​ Binary coercibility does not imply symmetry. For instance, xml → text is free, but text → xml requires validation. Use \dC+ to check coercibility rules.

2. ​Adding Non-Nullable Columns​

Appending a NOT NULL column typically triggers a rewrite, but exceptions exist:

• ​Virtual columns​ (PG 18+) using GENERATED ALWAYS AS avoid rewrites.
• Columns with ​non-volatile defaults​ (e.g., DEFAULT now()) do not cause rewrites.

​Exceptions:​​

• Volatile functions like random(), clock_timestamp(), or nextval() force rewrites.

​Test Case:​

-- No rewrite (generated column with STORED)
ALTER TABLE t1 ADD COLUMN generated_col BIGINT GENERATED ALWAYS AS (relowner + 1) STORED;

-- Rewrite triggered (volatile function)
ALTER TABLE t1 ADD COLUMN col_clock_tz TIMESTAMPTZ DEFAULT clock_timestamp();

‍

3. ​Table Access Method Changes​

Switching the table’s storage method (e.g., from heap to BRIN) requires a rewrite due to format differences. Example:

ALTER TABLE t1 SET ACCESS METHOD brin; -- Forces rewrite

‍

4. ​Logging Mode Changes​

Altering a table’s logging status (SET LOGGED/SET UNLOGGED) triggers a rewrite to apply WAL settings.

​Example:​

ALTER TABLE t1 SET UNLOGGED; -- Triggers rewrite
ALTER TABLE t1 SET LOGGED;   -- Triggers another rewrite

‍

5. ​Index Rebuilds​

While some DDL operations avoid table rewrites, they often require ​index rebuilds​ if the logical structure changes (e.g., altering a column’s collation).

​Verification:​

-- Check relation filepath before/after ALTER
SELECT pg_relation_filepath('t1_relowner_idx');

‍

Why Avoid Table Rewrites?

Rewrites lock the table, consume extra disk space (~2x size), and rebuild indexes. Use tools like the table_rewrite event to monitor and optimize these operations.

‍

Let me know if you’d like expanded tests or visual diagrams! 🚀

‍