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. The reason comes down to a single number in a system catalog.

Meet attnum#

Every column in Postgres is a row in the pg_attribute system catalog, and 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 never changes; ADD COLUMN simply grabs max(attnum) + 1. The problem is that this one number does three separate jobs:

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.

That coupling is what makes reordering hard. To move email after name it would have to become attnum 3 — but that number is already created_at's identity, referenced by every index, constraint, and rule. You can't renumber one column without rewriting all of them.

The holes that never fill#

Dropping a column has a related consequence. 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. That is why a table can report more columns in pg_attribute than in \d.

Column order is not just cosmetics#

It's tempting to shrug at this — who cares what order the columns print in? But 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 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.

A decade on, it is still not in, because attnum-as-position is assumed in a large 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.

This is the kind of change worth putting through Bytebase. Automatic SQL review plus a human reviewer catches the pieces that are easy to miss.

How MySQL implements instant ADD COLUMN AFTER#

MySQL handles this because it never tied a column's position to one immutable identity the way Postgres ties everything to attnum. For years MySQL still paid for ADD COLUMN ... AFTER with a full table rebuild under the hood, but it handled that rebuild itself: one statement, and the engine copied the table and reattached every index and constraint for you. The attnum problem never surfaced because MySQL never created it.

InnoDB later 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 — exactly the attlognum / attphysnum split Postgres has been debating for years.

The split Postgres is still debating has been shipping in MySQL for years. That is what decoupling logical from physical order buys you: ask for a column in the middle and it goes there, with no rewrite. It is one of the everyday places where MySQL is more usable than Postgres.

References#

• 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