Why It's So Hard to Add a Column in the Middle of a PostgreSQL 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:

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:

-- 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:

SELECT attname, attnum, atttypid::regtype, attisdropped
FROM   pg_attribute
WHERE  attrelid = 'users'::regclass
AND    attnum > 0
ORDER  BY attnum;

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)

• 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
FROM   pg_attribute
WHERE  attrelid = 'users'::regclass AND attnum > 0
ORDER  BY attnum;

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:

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:

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

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, 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.

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: stop making one number do three jobs, and split attnum into three.

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 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 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.

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 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 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
• pgsql-hackers discussion on logical column ordering: the 2014 thread and the 2022 phased rollout
• Stack Overflow: column tetris and table size
• PayPal Engineering: PostgreSQL at scale, saving space for free
• MySQL: InnoDB instant ADD and DROP columns