Which ALTER TABLE Statements Rewrite the Table and Which Only Touch the Catalogue

ALTER TABLE looks like one operation and is at least four. Changing a column’s default value is a write to a single catalogue row. Adding a nullable column is a write to a catalogue row plus a change in how the row decoder interprets stored tuples. Changing a column’s type from integer to text is a full copy of every row and every index on the table. Dropping a column can be either, depending on whether the engine bothers to reclaim the space.

The distinction is not cosmetic. A catalogue edit holds an exclusive lock for milliseconds; a rewrite holds it for as long as it takes to read and write the entire table, and it doubles the table’s disk footprint while it runs. Knowing which category a statement falls into is the difference between a deploy that nobody notices and an outage, so it is worth understanding how the engine classifies the statement rather than memorising a list.

The physical model the decision rests on

A heap table is an array of fixed-size pages, each holding tuples written with the column layout that was current when they were stored. Nothing in a tuple names its columns. The decoder uses the catalogue’s attribute list for the table, in order, plus the tuple’s null bitmap, to interpret bytes.

That design has a consequence: as long as the byte layout of existing tuples remains a valid encoding of the new schema, no tuple needs to change. Adding a column at the end, where its absence can be represented by a null bitmap bit or by a stored default, is such a case. Inserting a column in the middle, or changing a type to something with a different width or representation, is not, because the bytes for the following columns would have to shift.

Engines therefore classify each ALTER by whether the change is representable in the existing encoding. The three outcomes are: catalogue only, catalogue plus a per-tuple fix-up performed lazily, and full rewrite.

Rewrites: allocate, copy, rebuild, swap

When a rewrite is required, the engine creates a new physical file for the table under a new relfilenode identifier, scans the old one in physical order, converts each tuple, writes it to the new file, rebuilds every index on the table against the new tuples, and finally swaps the catalogue’s pointer to the new file and unlinks the old one.

Because the copy runs inside the same transaction as the catalogue change, readers on the old snapshot continue to see the old file until commit. The swap is atomic; the cost is not. Three resources are consumed in proportion to table size:

• Disk. The old and new copies coexist until commit, so the operation needs free space at least equal to the table plus its indexes.
• I/O. Every page is read and written, and every index is rebuilt from scratch rather than updated.
• Lock time. The strongest table lock is held for the whole copy, which is why a rewrite of a large table is usually planned as a separate operation with its own maintenance window or replicated table.

Write-ahead logging amplifies the I/O further, because each new page written during the copy is logged. A rewrite therefore generates roughly its own size in WAL in addition to its own size in data pages, and on a replicated primary that volume is shipped to every standby.

Indexes are the part people underestimate. Rebuilding a B-tree is not a page-for-page copy: the tree is constructed bottom-up by sorting the new tuples’ keys, which is faster per row than inserting them one at a time but still touches every leaf. A table with four indexes rewrites five structures.

Catalogue-only changes

Renaming a table or column, changing a column’s default for future rows, attaching or detaching a partition where the engine supports declarative partitioning, changing a column’s storage compression setting, and adding or dropping most constraints are catalogue edits. They take an exclusive lock briefly, write a row to the system catalogue, and invalidate cached plans so that subsequent statements re-read the new definition.

Two of these deserve a warning. Renaming a column is metadata-only in the database and a breaking change everywhere else: any query, view, function, or application statement that names the old column fails immediately. The database has no way to keep both names live, which is why renaming is treated as a two-phase operation in the expand and contract pattern rather than as a cheap edit.

Changing a default is catalogue-only for existing rows too, and this is the subtle one. If the old default was non-null and rows were inserted without an explicit value, the stored tuples already contain a value; changing the default does not rewrite them. Only new inserts see the new default. A backfill that assumes otherwise produces a table where historical rows hold the old value and recent rows hold the new one, with no marker distinguishing them.

The fast-default trick

Adding a column with a non-null default was historically a rewrite, because the engine had to write the default into every existing tuple. PostgreSQL 11 removed that requirement by storing the default once, in the catalogue, and marking the attribute as having a missing value. A tuple written before the column existed has a null bitmap bit set for it; the decoder, seeing the bit and the stored default, substitutes the constant instead of reporting null.

The cost is paid at read time: every scan of the table performs the substitution per tuple, which is a few instructions and no I/O. The benefit is that adding a column with a default to a large table becomes a catalogue operation. The mechanism has visible edges. A tuple that is later updated writes the materialised value, so after a wave of updates the table holds a mix of substituted and stored values, and the substitution stops applying. Dropping the default does not remove the stored missing value, and a column added this way cannot have its default changed without a rewrite in some engine versions.

-- catalogue only: the missing-value mechanism supplies 0 to old rows
ALTER TABLE events ADD COLUMN retry_count integer NOT NULL DEFAULT 0;

-- rewrite: integer to bigint widens every stored value
ALTER TABLE events ALTER COLUMN payload_bytes TYPE bigint;

-- catalogue only: the stored representation is variable-length either way
ALTER TABLE events ALTER COLUMN label TYPE varchar(120);

Reading the three statements against the physical model gives the classification without consulting a compatibility table. The first does not change any tuple’s bytes, because a tuple without the column can be decoded using the stored default. The second changes the width of a value that follows in every tuple, so every tuple’s layout shifts. The third changes only the declared maximum length, and the stored value already carries its own length, so nothing moves.

The first statement’s classification depends on the default being a constant. A volatile default such as random() or clock_timestamp() cannot be stored once, because each row would need a different value, so the engine falls back to writing it into every tuple and the statement becomes a rewrite.

MySQL’s instant column operations are the same idea reached from a different direction: the table’s row format keeps a record of which columns existed when each row was written, so a column added without a rewrite can be absent from older rows and materialised on read. In earlier versions of that mechanism the new column had to be the last one, which is the same positional constraint the missing-value approach imposes. Later versions relax the position by storing a per-row column count and defaulting the columns that a row does not contain, at the cost of more bookkeeping in the row header.

The PostgreSQL mechanism has no such relaxation. A tuple written before the column existed has a null bitmap bit for it, and the decoder substitutes the stored default; inserting a column in the middle of the attribute list would shift every subsequent column’s position, which the stored bitmap cannot express. Any change that reorders attributes therefore forces a rewrite.

Type changes and the coercion boundary

Altering a column’s type is a rewrite unless the new type has the same binary representation and width, in which case the engine can relabel the attribute. Widening varchar(50) to varchar(100) is a relabel in PostgreSQL because the stored representation is variable-length either way; integer to bigint is a rewrite because four-byte values must become eight-byte values. MySQL distinguishes its algorithms explicitly, with an in-place algorithm for changes that fit the existing row format and a copying algorithm otherwise, and it refuses an instant or in-place request when the change is not eligible rather than silently copying.

Narrowing a type is a rewrite with an additional hazard: existing values may not fit the new type. The engine either fails the statement when it encounters such a value or, with an explicit conversion expression, applies that expression to every row and fails partway through if one row raises an error. The rewrite is transactional where the engine supports transactional DDL, so a failure rolls back the copy and leaves the old table intact — but the time spent is not refunded, and on a large table a failed narrowing can hold its lock for as long as a successful one.

Dropping columns, and why the space does not come back

Dropping a column is a catalogue edit that marks the attribute as dropped. The bytes stay in every existing tuple, because removing them would mean shifting the remaining columns and rewriting the table. The dropped attribute remains in the tuple descriptor, occupying a slot that readers skip.

The space is recovered only when the tuple is rewritten for another reason — an update, or a later ALTER that forces a rewrite — or by a physical rebuild such as VACUUM FULL or an equivalent table-copy operation. This is why dropping a dozen wide columns from a table does not reduce its size at all, and why a table that has been through many schema generations can carry substantial dead space in its tuples. An engine that supports rewriting on drop (MySQL’s copying algorithm does) will do it when asked, at the usual cost.

The operational rule that follows from all of this: classify the statement before running it, and treat the rewrite category as a data-movement job rather than a schema edit. The lock behaviour of that job is the next problem, covered in metadata locks and the lock queue.