Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table | BytebaseSkip to main content<br>13.4k<br>Contact us<br>Contact us
Try Bytebase<br>Try Bytebase
Contact us<br>Contact us
Try Bytebase<br>Try Bytebase
Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table<br>Tianzhou · Jun 26, 2026
*:first-child]:mt-0 [&>p:first-of-type>img:first-child]:mt-0 [&>p:first-of-type>span:first-child>img:first-child]:mt-0 blog-article w-full">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:
CREATE TABLE users (<br>id bigint,<br>name text,<br>created_at timestamptz<br>);<br>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:
-- MySQL: this just works<br>ALTER TABLE users ADD COLUMN email text AFTER name;<br>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:
SELECT attname, attnum, atttypid::regtype, attisdropped<br>FROM pg_attribute<br>WHERE attrelid = 'users'::regclass<br>AND attnum > 0<br>ORDER BY attnum;<br>attname | attnum | atttypid | attisdropped<br>-----------+--------+-------------+-------------<br>id | 1 | bigint | f<br>name | 2 | text | f<br>created_at | 3 | timestamptz | f<br>email | 4 | text | f<br>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)
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:
ALTER TABLE users DROP COLUMN created_at;
SELECT attname, attnum, attisdropped<br>FROM pg_attribute<br>WHERE attrelid = 'users'::regclass AND attnum > 0<br>ORDER BY attnum;<br>attname | attnum | attisdropped<br>-----------------------------+--------+-------------<br>id | 1 | f<br>name | 2 | f<br>........pg.dropped.3........ | 3 | t<br>email | 4 | f<br>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:
CREATE TABLE events (<br>is_ok boolean, -- 1 byte<br>user_id bigint, -- 8 bytes, must start on an 8-byte boundary<br>is_mobile boolean -- 1 byte<br>);<br>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:
CREATE TABLE events (<br>user_id bigint, -- 8 bytes<br>is_ok boolean, -- 1 byte<br>is_mobile boolean -- 1 byte<br>);<br>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<br>The internal gap is gone and...