ALTER

ALTER

ALTER TABLE edits a table's definition in the data dictionary (the catalog that describes every column, type, and constraint), and depending on what you change, the engine either flips a few bytes of metadata in microseconds or copies every existing row into a brand-new physical table while holding a lock — the entire art of safe schema change is knowing which of those two paths a given statement takes.

The whole surface (not just ADD)

One verb, many sub-commands. Syntax differs slightly between engines — MySQL/MariaDB use MODIFY/CHANGE, PostgreSQL uses ALTER COLUMN ... TYPE. The operations:

The grader's note was right that the old page promised MODIFY/DROP/RENAME and showed none — they are all above. The interesting part is not the syntax, though. It is what each one costs.

The mechanism: metadata-only vs. table rewrite

Every ALTER lands on one of three execution strategies. In MySQL 8 you can name the strategy with ALGORITHM= and force it to refuse anything slower with LOCK=; in Postgres the planner picks automatically but the same three buckets exist.

• INSTANT — touch only the data dictionary; rows on disk are untouched. The new column's value is materialised lazily when a row is next read. O(1), no matter how big the table. MySQL 8.0+ does this for adding/dropping a column (added at the end), changing a default, or widening an ENUM.
• INPLACE — rebuild in the background (e.g. an index build) while concurrent reads and writes continue; a brief lock only at start and finish.
• COPY — build a full shadow copy of the table row-by-row, then swap. The original is locked for writes (often reads too) for the entire copy. This is the dangerous one: O(rows), and it blocks.

Which bucket you get is decided by the operation, not by you. MODIFY c INT → BIGINT changes the on-disk byte width of every row, so it cannot be INSTANT — it falls to COPY. That single fact is why an innocent-looking ALTER can take a 50M-row table offline for 20 minutes.

Worked example: one ADD that is free, one MODIFY that is not

Start with a real Employees table (the same one the old page used) and trace what the engine physically does, step by step. Assume MySQL 8, InnoDB, 50 million rows.

CREATE TABLE Employees (
  id        INT PRIMARY KEY,
  firstName VARCHAR(50),
  lastName  VARCHAR(50)
);

Step 1 — Add a column (INSTANT path).

ALTER TABLE Employees
  ADD COLUMN salary INT,
  ALGORITHM=INSTANT;        -- explicitly demand the fast path

Trace of what InnoDB does:

1. Bump the table's row version in the data dictionary from v0 to v1, recording "as of v1 there is a 4th column salary, default NULL."
2. Touch zero of the 50M rows on disk. The statement returns in milliseconds.
3. A later SELECT salary FROM Employees reads an old (v0) row, sees it predates v1, and synthesises salary = NULL on the fly. New inserts store the column physically.

Step 2 — Widen id from INT to BIGINT (COPY path, the trap). Your sequence is about to overflow 2.1 billion, so:

ALTER TABLE Employees
  MODIFY id BIGINT,
  ALGORITHM=INSTANT;        -- you ASK for instant...

Trace:

1. The engine evaluates the request: changing INT(4 bytes) to BIGINT(8 bytes) rewrites the byte layout of every row. INSTANT is impossible.
2. Because you pinned ALGORITHM=INSTANT, MySQL aborts with ER_ALTER_OPERATION_NOT_SUPPORTED rather than silently doing something slow — exactly the safety net you want.
3. Drop the hint and the engine falls back to ALGORITHM=COPY: it builds a 50M-row shadow table, copies row by row (minutes), holds a write lock the whole time, then atomically swaps. Every INSERT/UPDATE from your app blocks until it finishes.

Same verb, same table, two outcomes separated by 6+ orders of magnitude. The lesson: never run an ALTER in production without first knowing which path it takes — and let ALGORITHM=INSTANT / LOCK=NONE fail loudly instead of finding out at 3 a.m.

Doing it online on a large table

When the operation genuinely requires a rewrite (type change, adding a column in the middle, certain constraints), the production answer is not "run ALTER and pray." It is an external online-schema-change tool that turns one blocking ALTER into a slow, throttle-able, abortable background copy:

• pt-online-schema-change (Percona) — creates an empty _new table with the desired schema, installs triggers on the original so live writes are mirrored into the copy, backfills existing rows in small chunks, then renames the new table into place. The original is never long-locked.
• gh-ost (GitHub) — same goal, but trigger-less: it reads the binlog (the replication stream) to capture live changes, which avoids the write amplification of triggers and lets you pause/throttle by lag.
• Postgres — no rewrite for adding a column with a constant default (v11+ stores it in the catalog as attmissingval); a volatile default like clock_timestamp() still rewrites the whole table. For type changes use a new-column-plus-backfill pattern or a tool like pg-osc.

Pitfalls

• The hidden full rewrite. MODIFY col BIGINT, ADD COLUMN ... AFTER x (inserting mid-table), or adding a FULLTEXT index all force COPY even in MySQL 8. On a large table that is a multi-minute write outage. Always test on a row-count-realistic staging table and watch the algorithm chosen.
• The metadata-lock (MDL) queue. Even an INSTANT alter must briefly grab an exclusive metadata lock. If a long-running transaction or an idle-in-transaction connection holds the table, your ALTER blocks waiting for it — and every new query queues behind your ALTER. One forgotten open transaction can stall the whole table. Set lock_wait_timeout low so a stuck ALTER fails fast instead of forming a pile-up.
• Postgres volatile defaults. ADD COLUMN ts timestamptz DEFAULT now() is fine (now() is evaluated once, stored as a constant). ADD COLUMN ts timestamptz DEFAULT clock_timestamp() is volatile and rewrites every row under ACCESS EXCLUSIVE. The two look almost identical; only one is safe.
• Adding NOT NULL with no default. If the column has existing rows and no default, the statement fails (nothing to put in old rows) — or worse, with a default it scans the whole table to validate. Add the column nullable, backfill in batches, then add the constraint NOT VALID and VALIDATE separately in Postgres.
• The 64-version ceiling (MySQL INSTANT). Each INSTANT ADD/DROP bumps the row version; after 64 of them on one table InnoDB refuses further instant changes and demands a full rebuild (OPTIMIZE TABLE) to reset. Churning migrations can quietly burn through this.
• ALTER is mostly non-transactional in MySQL. Unlike Postgres (where DDL is transactional and rollback-able), a MySQL ALTER that fails partway cannot always be cleanly rolled back, and on older versions it implicitly commits the surrounding transaction.

Takeaways

• ALTER TABLE covers ADD / MODIFY-or-CHANGE-type / RENAME / DROP of columns and constraints — but the syntax is the easy half; the cost is the half that pages you.
• The single question that matters: does this change rewrite every row? Metadata-only (INSTANT) is O(1) and safe; a COPY/rewrite is O(rows) and write-blocking.
• In MySQL, pin ALGORITHM=INSTANT, LOCK=NONE so an unsafe alter fails loudly instead of silently locking production; in Postgres, mind volatile defaults and use NOT VALID + VALIDATE for constraints.
• For genuine rewrites on big tables, reach for gh-ost or pt-online-schema-change — they convert one blocking statement into a throttled, abortable background copy.

Sources: MySQL 8.4 Reference Manual, “ALTER TABLE Statement” and “Online DDL Operations” (ALGORITHM/LOCK, INSTANT supported operations, 64-version limit); Oracle MySQL Server Blog, “MySQL 8.0 INSTANT ADD and DROP Column(s)”; PostgreSQL 18 Documentation, “ALTER TABLE” and “Modifying Tables” (constant vs. volatile defaults, attmissingval fast path); Percona pt-online-schema-change and GitHub gh-ost documentation. Re-authored and deepened for this guide: the original covered only ADD COLUMN despite promising MODIFY/DROP/RENAME, and omitted the locking / table-rewrite mechanism that makes ALTER systems-relevant.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — ALTER TABLE

Why this concept exists (judgment chain)

ALTER is catalog mutation with three cost classes: INSTANT (metadata), INPLACE (online rebuild), COPY (full rewrite, write-blocking). Production safety is knowing the path and failing closed with ALGORITHM=INSTANT/LOCK=NONE, or using gh-ost/pt-osc for true rewrites. MDL queues turn a "fast" alter into a global stall.

Worked example with numbers or traced steps

50M-row Employees: ADD COLUMN salary INT ALGORITHM=INSTANT → row version bump, 0 row rewrites, ms.
MODIFY id INT→BIGINT with ALGORITHM=INSTANT → ER_ALTER_OPERATION_NOT_SUPPORTED (good fail).
Without pin: COPY shadow table, minutes of write lock.
Postgres: ADD col DEFAULT constant (v11+) catalog-only; DEFAULT clock_timestamp() rewrites.
pt-osc/gh-ost: shadow table + backfill + cutover; gh-ost uses binlog not triggers.
MySQL INSTANT version ceiling 64 before rebuild required.

When NOT to use / named alternative

Run raw COPY ALTER only on small/dev tables. Prefer expand-contract (add nullable col, backfill, switch reads, drop old) over in-place type shrink. Do not ADD NOT NULL without default on populated tables in one step — multi-phase instead.

Failure / ops fingerprint

Outage: ALTER waits on long transaction MDL; new queries queue behind ALTER. Disk fill during COPY. Failed mid-ALTER on MySQL (non-transactional DDL). Ops: lock_wait_timeout low; monitor Threads_running; rehearsal on prod-sized staging; ALGORITHM=INSTANT in migration PR checklist.

Hostile-panel Q&As (model answers)

Q1. Why pin ALGORITHM=INSTANT?
Model answer: Forces loud failure if the engine would rewrite; prevents silent multi-minute locks.

Q2. INSTANT vs gh-ost?
Model answer: INSTANT only for metadata-safe ops; type changes and many constraints need online tools that copy without long exclusive locks.

Q3. MDL pile-up mechanism?
Model answer: ALTER needs exclusive metadata lock; idle-in-transaction holders block it; subsequent DML queues behind the waiting ALTER.