Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table
PostgreSQL does not allow adding a column in the middle of a table due to its fixed physical row layout and MVCC architecture. Instead, new columns are appended at the end, as altering the position would require rewriting the entire table, which is costly and complex. The article explains the internal reasons behind this limitation and discusses workarounds like custom views or table rebuilds.
Background
- PostgreSQL stores table rows as fixed‑width "tuples" on disk, where each column's position matters. Adding a column in the middle requires rewriting every existing row — a heavy operation that locks the table.
- MySQL and MariaDB *can* insert columns anywhere without rewriting rows because their storage engines (InnoDB, Aria) keep column metadata separate and reorder columns internally.
- Why this matters: Schema changes are routine in live services. PostgreSQL forces a full table rewrite for mid‑table column additions, so most teams just append columns at the end — which makes schemas messy over time.
- The limitation stems from PostgreSQL's "heap‑based" design, which favors concurrency and crash safety over schema flexibility. Fixing it would require a major storage engine overhaul that the community hasn't taken on.