# Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table > PostgreSQL has no ADD COLUMN ... AFTER. The reason one little number in a system catalog turned a "simple" feature into a decade-long engineering saga, and what you can do about it today. Tianzhou | 2026-06-26 | Source: https://www.bytebase.com/blog/why-its-hard-to-add-a-column-in-the-middle-of-postgres-table/ --- Bytebase helps teams manage schema migrations for MySQL and PostgreSQL, and one of the first things that trips people up when moving from MySQL is this: they try to add a column in the middle of a table, and Postgres flatly refuses. There is no `ADD COLUMN ... AFTER`. New columns go to the end, full stop. It looks like a five-minute feature nobody got around to. It is not. The reason cuts straight to how Postgres physically stores your data. ## The innocent request You have a `users` table: ```sql CREATE TABLE users ( id bigint, name text, created_at timestamptz ); ``` Product asks for an `email` column, and because you like a tidy schema, you want it right after `name`. Years of MySQL muscle memory type the answer: ```sql -- MySQL: this just works ALTER TABLE users ADD COLUMN email text AFTER name; ``` In PostgreSQL, you can't. `ADD COLUMN` always appends to the end, and there is no syntax to put it anywhere else. To see why, you have to meet one little number. ## Meet `attnum`, the number wearing three hats Every column in Postgres is a row in the `pg_attribute` system catalog. The field that defines column order is `attnum`: ```sql SELECT attname, attnum, atttypid::regtype, attisdropped FROM pg_attribute WHERE attrelid = 'users'::regclass AND attnum > 0 ORDER BY attnum; ``` ```text attname | attnum | atttypid | attisdropped -----------+--------+-------------+------------- id | 1 | bigint | f name | 2 | text | f created_at | 3 | timestamptz | f email | 4 | text | f ``` `attnum` is assigned when the column is created, and it never changes. `ADD COLUMN` simply grabs `max(attnum) + 1`. Here is the catch: that single integer is wearing three hats at once. ![attnum serves three jobs at once: identity (indexes, constraints, and rules reference it), physical position (its byte offset in the on-disk tuple), and logical position (its column order in SELECT * and psql \d)](/content/blog/why-its-hard-to-add-a-column-in-the-middle-of-postgres-table/attnum-three-jobs.svg) - **Identity**: every index, constraint, stored rule, and statistic points at the column by this number, not by name. - **Physical position**: where the column's bytes sit in each on-disk tuple. - **Logical position**: the order you see in `SELECT *`, `psql`'s `\d`, and `COPY`. One value, three jobs. Now the problem writes itself. To move `email` after `name`, it has to become `attnum` 3. But that is `created_at`'s identity, and every index, constraint, and rule already refers to columns by number. You cannot renumber one without rewriting all of them. Move a column and you move all three hats at once. ## The holes that never fill It gets worse. Drop a column and Postgres does not physically remove it or reclaim its `attnum`. It just marks it dead: ```sql ALTER TABLE users DROP COLUMN created_at; SELECT attname, attnum, attisdropped FROM pg_attribute WHERE attrelid = 'users'::regclass AND attnum > 0 ORDER BY attnum; ``` ```text attname | attnum | attisdropped -----------------------------+--------+------------- id | 1 | f name | 2 | f ........pg.dropped.3........ | 3 | t email | 4 | f ``` The column is renamed to a tombstone, `attisdropped` flips to `true`, and `attnum` 3 is burned forever. The slot is never reused, and the dead column keeps taking space in every row until the table is rewritten. `attnum` is append-only by design, so Postgres cannot even compact it to make room. Ever seen a table report more columns in `pg_attribute` than in `\d`? This is why. ## Plot twist: column order is not just cosmetics Tempting to shrug here. Who cares what order the columns print in? Here is the twist: column order changes how much disk your table uses. Postgres stores each row as a packed tuple, and most data types carry an alignment requirement. An 8-byte `bigint` must begin at an offset divisible by 8, a 4-byte `int` at a multiple of 4, and so on. When the next column does not naturally land on its boundary, Postgres inserts padding bytes to push it there. Padding is pure waste, paid on every single row. Look at this table: ```sql CREATE TABLE events ( is_ok boolean, -- 1 byte user_id bigint, -- 8 bytes, must start on an 8-byte boundary is_mobile boolean -- 1 byte ); ``` A 1-byte `boolean` in front of an 8-byte `bigint` forces 7 bytes of padding to reach the boundary, and another stretch of padding at the end to align the whole row. Now reorder largest-alignment-first: ```sql CREATE TABLE events ( user_id bigint, -- 8 bytes is_ok boolean, -- 1 byte is_mobile boolean -- 1 byte ); ``` Here is the same row on disk, before and after. Same three columns, same data, laid out byte for byte: ![Postgres row byte layout: naive column order wastes 14 bytes of padding for a 24-byte row, while the packed order needs only 6, shrinking the row to 16 bytes](/content/blog/why-its-hard-to-add-a-column-in-the-middle-of-postgres-table/column-padding.svg) The internal gap is gone and the row drops from 24 bytes to 16, same data. Packing columns widest-alignment-first (8-byte, then 4-byte, 2-byte, 1-byte, with variable-length `text` and `bytea` last) is affectionately called [column tetris](https://stackoverflow.com/a/7431468), and the savings are real: on real schemas it routinely reclaims up to ~20% of table size. Eight bytes per row on a 100M-row table is about 800 MB off disk, plus fewer pages to scan and better cache behavior, basically for free. PayPal wrote up [exactly this win](https://medium.com/paypal-tech/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9a). So the ability to place a column where you want it is not a vanity feature. It is the difference between a compact table and one quietly bleeding storage. Which is exactly why people keep asking for it. ## The decade-long attempt to fix it To be fair, the Postgres developers are not against it. There is even a known, elegant design that just never got finished. The idea, [on pgsql-hackers since at least 2014](https://www.postgresql.org/message-id/20141209174146.GP1768@alvh.no-ip.org): stop making one number do three jobs, and split `attnum` into three. | Field | Meaning | Changes? | | ------------ | ------------------------------------------------ | -------- | | `attnum` | Permanent **identity** of the column | Never | | `attphysnum` | **Physical** position in the on-disk tuple | Yes | | `attlognum` | **Logical** position (`SELECT *`, `\d`, COPY, …) | Yes | Give each number its own job: reorder columns logically without touching their identity, and let Postgres auto-tune the physical layout for alignment on its own. Álvaro Herrera even [sketched a phased rollout](https://www.postgresql.org/message-id/20220628085314.kgqrcniznaqc3nvs@alvherre.pgsql) to get there. So why, a decade on, is it still not in? Because `attnum`-as-position is assumed in an astonishing number of places. The moment the three numbers can disagree, every one has to be found and fixed: - tuple descriptors and `heap_form_tuple` / `heapam.c`, where the physical row is built; - `SELECT *` and JOIN star-expansion in the planner; - `COPY` import and export ordering; - composite-type and set-returning-function result expansion; - index column references; - `psql`'s `\d`, `pg_dump`, and every other introspection path. A textbook invasive change: simple in concept, enormous in surface area. As the [Postgres wiki](https://wiki.postgresql.org/wiki/Alter_column_position) puts it bluntly, the developers aren't opposed, but "no one has stepped forward to do the work." ## What to do today Most of the time, reordering columns just to keep them tidy is not worth the trouble. Append order is ugly, not harmful, so the honest first answer is usually to leave it alone. When you genuinely need the physical layout changed, to win at column tetris on a hot table, there is really one correct way: rebuild the table. ```sql BEGIN; CREATE TABLE users_new ( id bigint, name text, email text, created_at timestamptz ); INSERT INTO users_new SELECT id, name, email, created_at FROM users; -- recreate indexes, constraints, FKs, triggers, grants, sequences/defaults... DROP TABLE users; ALTER TABLE users_new RENAME TO users; COMMIT; ``` It takes an `ACCESS EXCLUSIVE` lock, rewrites the whole table (roughly 2x disk during the copy), and you must reattach everything that pointed at the old one: indexes, foreign keys in both directions, check constraints, triggers, views, default and sequence ownership, privileges. The `INSERT` is the easy line. That one-line comment is where reorders go wrong. BTW, this is exactly the kind of change worth putting through Bytebase. Automatic [SQL review](https://docs.bytebase.com/sql-review/review-policy) plus a human reviewer catches the pieces that are easy to drop on the floor. ## How MySQL implements instant `ADD COLUMN AFTER` So why does MySQL shrug this off? Because it never welded a column's position to one immutable identity the way Postgres welds everything to `attnum`. For years MySQL still paid for `ADD COLUMN ... AFTER` with a full table rebuild under the hood, but it owned that rebuild: one statement, and the engine copied the table and reattached every index and constraint for you. You never met the `attnum` problem because MySQL never created it. Then InnoDB went further. Since MySQL 8.0.29, an [instant ADD COLUMN](https://dev.mysql.com/blog-archive/mysql-8-0-instant-add-and-drop-columns/) can slot a column into any position without rewriting a single row, because InnoDB keeps the logical column order (in its data dictionary) separate from the physical order on disk. New columns land at the end of storage but appear wherever you asked. Sound familiar? That is exactly the `attlognum` / `attphysnum` split Postgres has been debating for years. The split Postgres keeps on its wishlist has been shipping in MySQL for years, and it is a clean showcase of what decoupling logical from physical buys you: ask for a column in the middle and it just goes there, no rewrite. One of those everyday places where MySQL's usability quietly beats Postgres. ## Further reading - [PostgreSQL Wiki: Alter column position](https://wiki.postgresql.org/wiki/Alter_column_position) - pgsql-hackers discussion on logical column ordering: the [2014 thread](https://www.postgresql.org/message-id/20141209174146.GP1768@alvh.no-ip.org) and the [2022 phased rollout](https://www.postgresql.org/message-id/20220628085314.kgqrcniznaqc3nvs@alvherre.pgsql) - [Stack Overflow: column tetris and table size](https://stackoverflow.com/a/7431468) - [PayPal Engineering: PostgreSQL at scale, saving space for free](https://medium.com/paypal-tech/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9a) - [MySQL: InnoDB instant ADD and DROP columns](https://dev.mysql.com/blog-archive/mysql-8-0-instant-add-and-drop-columns/)