Changing a 200 GB Table Without Taking the Site Down

A deploy needs one more column on orders. On a laptop copy the ALTER TABLE finishes in under a second. On production, orders is 180 GB, and the same statement locks up checkout for forty minutes. This is one of the most common ways an otherwise routine release turns into an incident on an established LAMP application, and it is entirely avoidable — the mechanics are well documented, they are just easy to skip.

There are three ways to change a big table. Pick deliberately.

1. Ask MySQL first: is this change instant?

Since MySQL 8.0 a useful set of changes are metadata-only. Adding a nullable or defaulted column, renaming a column, adding a virtual generated column, extending a VARCHAR within the same length-byte class, setting or dropping a default — these complete in milliseconds regardless of table size, because no rows are rewritten.

Make MySQL prove it rather than assuming:

ALTER TABLE orders ADD COLUMN source VARCHAR(32) NULL, ALGORITHM=INSTANT;

With ALGORITHM=INSTANT specified explicitly, MySQL either does it instantly or refuses with an error. That error is the cheapest possible failure: it happens before you have taken any locks, and it tells you to plan the change differently. Never let a large ALTER run without an explicit algorithm — the default silently falls back to the expensive path.

The next rung down is ALGORITHM=INPLACE, LOCK=NONE, which rebuilds or builds in the background while reads and writes continue. Adding a secondary index is the common case here: it is inplace and concurrent, but it still does real I/O for the length of the build, so it belongs in a quiet window even though it does not block.

Things that are still a full copy under ALGORITHM=COPY (blocking writes) include changing a column's data type, changing a column to NOT NULL, dropping or adding a primary key, and changing the character set. Those are the ones that need tooling.

Two details bite people on 8.0:

• Instant columns are append-only. Pre-8.0.29, an instant ADD COLUMN could only append to the end of the row; from 8.0.29 positional adds work, but each instant change consumes a row-version slot, and after 64 versions the table needs a rebuild before more instant changes are allowed. Check SELECT TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES if instant DDL starts refusing.
• Metadata locks still exist. Even an instant ALTER needs a brief exclusive metadata lock, and it will queue behind a long-running transaction or an idle transaction holding a read on the table — and then every subsequent query queues behind it. A sub-second change becomes a five-minute outage. Before any DDL: check SHOW PROCESSLIST and information_schema.innodb_trx for old transactions, and set lock_wait_timeout = 10 in the session so a blocked ALTER gives up instead of building a pile-up.

2. When it is a copy: pt-online-schema-change or gh-ost

Both tools do the same trick from the outside: create an empty copy of the table with the new definition, backfill it in small chunks, keep it current with ongoing writes, then swap the names. The difference is how they track changes.

pt-online-schema-change (Percona Toolkit) installs triggers on the original table to mirror writes into the copy. It is a single command and works anywhere, including managed MySQL where you have no binlog access:

pt-online-schema-change \
  --alter "MODIFY COLUMN status TINYINT NOT NULL" \
  D=shop,t=orders \
  --max-load Threads_running=40 \
  --critical-load Threads_running=100 \
  --chunk-time 0.5 \
  --alter-foreign-keys-method=auto \
  --execute

Cost: the triggers add write latency to every INSERT/UPDATE/DELETE on a busy table, and the tool cannot be used if the table already has triggers of its own. Foreign keys pointing at the table are the sharp edge — read what --alter-foreign-keys-method will actually do, on a rehearsal, before production.

gh-ost reads the binary log instead of using triggers, so the write path is untouched and the copy can run against a replica. It also pauses and throttles on demand, and can be told to abort on a flag file:

gh-ost --database=shop --table=orders \
  --alter="MODIFY COLUMN status TINYINT NOT NULL" \
  --max-load=Threads_running=40 \
  --throttle-control-replicas="replica1:3306" \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

That last flag is the reason to like gh-ost: the copy runs for as long as it needs, and the final rename — the only moment with a real lock — waits until you delete the file. You choose when the risky second happens, and you can choose 6 a.m. Sunday.

Rough guidance: pt-osc if you have no binlog access or want one dependency-free command; gh-ost if the table is write-heavy, very large, or the cut-over needs to be scheduled separately from the copy. Both need free disk for a second copy of the table — check df -h against the table's size in information_schema.TABLES before starting, because both fail ugly on a full volume.

3. The option that is sometimes right: replica promotion

For changes that are slow everywhere — a character-set conversion, a primary-key change on a huge table — the calmest path is to run the ALTER on a replica, let it take as long as it takes, then promote that replica during a short planned window. This trades a long online copy for a few minutes of scheduled read-only time, and for a business that can take a 5 a.m. maintenance window it is usually the lowest-risk plan available. It is also the same rehearsal you should already be doing for version upgrades, so the muscle exists.

Rehearse on a restored backup

Whichever path you pick, the rehearsal is not optional, and it is not expensive: restore last night's backup to a scratch machine, run the exact command, and record three numbers — wall-clock duration, peak disk used, and replica lag if any. Production will be slower than the scratch box, so treat the number as a floor, not an estimate. If the rehearsal took two hours, do not schedule ninety minutes.

Then make the change reversible. An additive change (new nullable column, new index) is reversible by ignoring it, which is why the schema changes that ship safely are the additive ones. Destructive changes — dropping a column, narrowing a type — should be split in two: deploy code that no longer reads the column, let it sit for a release, then drop. Expand, migrate, contract. The intermediate state feels untidy and it is the reason nobody gets paged.

The short version

• Always name the algorithm: ALGORITHM=INSTANT first, INPLACE, LOCK=NONE second, tooling if MySQL refuses both.
• Check for long-running transactions before any DDL, and set lock_wait_timeout so a blocked ALTER fails instead of queueing the application behind it.
• Time the change on a restored backup and write the number down.
• Split destructive changes across releases so every deploy is reversible.

If you are staring at an ALTER on a table nobody wants to touch, that is a normal problem with known answers — and it is about an hour of work to decide which of the three paths yours is. Tell us the table size, the exact change, and your MySQL version, and we will tell you which one it is.