Synthetic Industry

Troubleshooting guide · updated 2026-10-11

Adding a column to a big table: which steps rewrite it and which locks they take

What PostgreSQL and MySQL do for ADD COLUMN, defaults, NOT NULL, foreign keys and indexes on a large table, and how to order the steps so writes keep flowing.

The question is the lock, not the column

Adding a column sounds trivial and often is. On a large, busy table it becomes risky for two separate reasons: the statement may rewrite every row, and it may need a lock that queues behind a long-running query while every later query queues behind it. The effect users see is the table freezing. So the first questions about any step are what lock it takes and whether it touches every row.

The answers depend on the engine and its version, which is why a rehearsal on a copy of realistic size matters more than any rule of thumb.

  • Name the engine and version before choosing steps.
  • Treat each step as its own change with its own lock.

PostgreSQL: defaults, constraints and indexes

The PostgreSQL manual says ADD COLUMN with a non-volatile default stores the default in metadata and does not rewrite the table, while a volatile default, a stored generated column or an identity column rewrites the table and its indexes. It also says most ALTER TABLE forms take an ACCESS EXCLUSIVE lock unless noted. Constraints have gentler routes: a constraint added NOT VALID is not checked against existing rows and so does not scan the table, new and updated rows are checked immediately, and VALIDATE CONSTRAINT later takes only SHARE UPDATE EXCLUSIVE. Adding a foreign key takes SHARE ROW EXCLUSIVE.

SET NOT NULL normally scans the whole table to prove no nulls exist, but the manual says the scan is skipped if a valid CHECK constraint already proves it. Indexes have their own option: CREATE INDEX CONCURRENTLY does not block writes, though it takes longer, cannot run inside a transaction block and may leave an invalid index if it fails, which must be dropped and retried.

  • Add the column with no default or a non-volatile one, backfill in batches, then add constraints in the NOT VALID then VALIDATE order.
  • Set a lock_timeout for the session so a blocked step fails instead of queueing writes behind it.

MySQL: what InnoDB can do online

The MySQL 8.4 manual documents ADD COLUMN with ALGORITHM=INSTANT as a metadata-only change, with limits: it cannot be combined with actions that do not support it, does not work for some table types such as compressed row format or tables with a full-text index, and each instant change adds a row version up to a stated cap, after which a rebuild is needed. Adding a secondary index runs in place with reads and writes continuing, although it finishes only after transactions that were accessing the table complete. Changing a column data type, by contrast, is documented as needing a table copy that allows no concurrent changes.

MySQL also lacks transactional schema changes in practice; Django's documentation notes that if a migration fails there, you must unpick the changes by hand. That makes a rehearsed rollback more important, not less.

  • Request the algorithm explicitly so the statement fails if it cannot do what you expect.
  • Check the table for the listed exclusions before relying on INSTANT.

What a safe plan contains, and what the paid outcome covers

A safe plan names the order of steps, the lock each takes and a limit on how long a step may wait, a batched backfill that can stop and resume, a check that every row has the intended value, and a rollback that has been run, not just written. It is rehearsed on a copy of realistic size under a write workload, and the log of that rehearsal is the evidence.

The add-column outcome delivers exactly that for one column on one table, rehearsed on a staging copy you prepare, including running the rollback. It does not promise zero impact in production, where load differs, and it does not run anything there: you apply the runbook after a restore point you have tested. Changing the type of an existing column in place is a different, heavier change and is not covered.

  • Make the restore point a precondition, and confirm it by restoring it.
  • Agree in advance how long a lock wait you will tolerate.

Sources and limits

  • PostgreSQL 18: ALTER TABLE Checked 2026-10-11.
    • ADD COLUMN with a non-volatile DEFAULT does not rewrite the table, while a volatile default, a stored generated column or an identity column does.
    • An ACCESS EXCLUSIVE lock is acquired unless explicitly noted; ADD FOREIGN KEY needs SHARE ROW EXCLUSIVE and VALIDATE CONSTRAINT needs SHARE UPDATE EXCLUSIVE.
    • A constraint added NOT VALID does not scan the table and is checked for new rows; SET NOT NULL scans unless a valid CHECK constraint already proves no nulls exist.
  • PostgreSQL 18: CREATE INDEX Checked 2026-10-11.
    • CREATE INDEX CONCURRENTLY does not block inserts, updates or deletes, cannot run in a transaction block, takes longer, and can leave an invalid index if it fails.
  • PostgreSQL 18: Client connection defaults, timeouts Checked 2026-10-11.
    • lock_timeout aborts a statement that waits too long for a lock and is disabled (0) by default; setting it in postgresql.conf is not recommended because it affects every session.
  • MySQL 8.4: InnoDB online DDL operations Checked 2026-10-11.
    • ADD COLUMN can use ALGORITHM=INSTANT as a metadata-only change, with listed limits; adding a secondary index runs in place while reads and writes continue; changing a column data type needs a table copy with no concurrent changes.
  • Django 5.2: Migrations Checked 2026-10-11.
    • On MySQL, which lacks transaction support around schema alterations, a failed migration must be unpicked by hand.