What a schema change really costs

Three kinds of change

On a small table every migration is fast, which is why problems show up only in production. On a big one, each ALTER TABLE does one of three things:

• Catalog only. PostgreSQL updates its own description of the table and touches no rows. Milliseconds, at any size.
• Scan. It reads every row to check something, such as that no value is NULL. Time grows with the table.
• Rewrite. It writes a new copy of the whole table and rebuilds every index. Time grows with the table and its indexes, and you need room for both copies on disk.

The catch is the lock. Unless the documentation says otherwise, ALTER TABLE takes ACCESS EXCLUSIVE: no reads, no writes, for as long as the scan or rewrite takes.

We measured each change on a 5-million-row table (287 MB, plus its primary key), PostgreSQL 18.3 on an Apple M1 Pro laptop. Medians of three runs:

On a 500 GB table, multiply the scans and rewrites by a few thousand.

Split the check from the lock

Constraints are where the biggest wins hide. Adding a CHECK normally scans the table while holding ACCESS EXCLUSIVE. Adding it NOT VALID skips the scan: new and updated rows are checked from that moment, existing rows are not yet. A separate VALIDATE CONSTRAINT then scans, holding only SHARE UPDATE EXCLUSIVE, which does not conflict with inserts, updates or deletes.

ALTER TABLE payments ADD CONSTRAINT amount_positive CHECK (amount >= 0) NOT VALID;  -- instant
ALTER TABLE payments VALIDATE CONSTRAINT amount_positive;                           -- scans, writes continue

The same works for foreign keys. PostgreSQL 18 extends it to NOT NULL, which previously needed a workaround:

ALTER TABLE payments ADD CONSTRAINT amount_not_null NOT NULL amount NOT VALID;      -- PostgreSQL 18+
ALTER TABLE payments VALIDATE CONSTRAINT amount_not_null;

On older versions: add CHECK (amount IS NOT NULL) NOT VALID, validate it, then SET NOT NULL. That last step skips its scan because a valid CHECK already proves there are no nulls.

Indexes have their own version: CREATE INDEX CONCURRENTLY builds without blocking writes. It takes longer and cannot run inside a transaction block, and if it fails it leaves an invalid index you must drop and retry. In our runs it took 2.1 s instead of 1.3 s, while an insert during the build finished in under a millisecond instead of waiting 1.2 s for the plain build.

Change in stages: expand, migrate, contract

Some changes cannot be made in place at all without breaking running code. Renaming a column is instant for the database, but every application server still running the old code starts failing the moment it happens. The pattern for these is expand, migrate, contract:

1. Expand. Add the new thing alongside the old: a new column, table or index. Old code ignores it.
2. Migrate. Deploy code that writes both and reads the new one. Backfill existing rows in batches.
3. Contract. Once nothing uses the old thing, remove it in a later deploy.

Each step is backwards-compatible with the code running at the time, so any of them can be deployed, and rolled back, on its own. It is slower: a rename becomes three deploys. That is the price of never having a moment where old code and new schema disagree.

Check yourself

1. Why is ALTER COLUMN id TYPE bigint a rewrite, while changing varchar(20) to text is not?

   Show answer

   int and bigint are stored differently on disk (4 bytes against 8), so every row must be rewritten in the new format. varchar(20) and text are stored the same way, so only the catalog changes.

   I could explain this without looking

2. What exactly does NOT VALID change about adding a CHECK constraint?

   Show answer

   It skips the scan of existing rows, so the ALTER TABLE holding ACCESS EXCLUSIVE is instant. New and updated rows are checked straight away. Existing rows are checked later by VALIDATE CONSTRAINT, which takes a lock that does not block writes.

   I could explain this without looking

3. Why does renaming a column need three deploys under expand, migrate and contract?

   Show answer

   Old application code still uses the old name. Adding the new column, switching code to it and backfilling, and only then dropping the old column keeps every running version of the code working at each step.

   I could explain this without looking