Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table
a day ago
- PostgreSQL's ADD COLUMN only appends new columns to the end, with no 'AFTER' clause, unlike MySQL. This is due to the attnum system catalog field, which assigns a unique identifier to each column upon creation and never changes. attnum serves three roles: as a column's identity, its physical position in disk tuples, and its logical order in queries, making reordering complex. Dropped columns leave tombstones with their attnum slots unreclaimed, leading to unused space until table rewrite. Column order impacts storage efficiency due to data type alignment padding; 'column tetris' optimization can save up to ~20% disk space by placing wider columns first. PostgreSQL developers have proposed splitting attnum into separate fields for identity, physical, and logical positions since 2014, but implementation is stalled due to widespread code dependencies. Currently, rebuilding the table with an ACCESS EXCLUSIVE lock is the only way to reorder columns for storage gains. MySQL handles column reordering easily because it separates logical and physical column ordering, allowing instant ADD COLUMN without table rewrites in recent versions.