Adding and Dropping Columns Without an Outage

A deploy runs a migration, the migration runs an ALTER TABLE, and every request that touches that table waits until it finishes. Nothing is broken. The surprise is that "add a column" and "change a column" are not the same kind of operation, and the cheap one is not free either.

The numbers below were measured on MySQL 9.6.0 against tables of 1,000 to 64,000 rows, with a fresh copy of the table for every run and the median of five to ten runs reported. Read them as ratios; the shape is what transfers to your hardware, and the shape is the point.

Three algorithms, and only one leaves the rows alone

MySQL implements ALTER TABLE in three ways, and you can ask for one with ALGORITHM=. INSTANT changes only the data dictionary. INPLACE rebuilds the table's contents in place, so its indexes are rewritten but no second copy of the table exists. COPY builds a new table, copies every row and swaps it in, and it is the only algorithm available when a row's layout on the page has to change.

operation                1k rows   4k rows  16k rows  64k rows
ADD COLUMN INSTANT        4.4 ms    2.5 ms    2.5 ms    2.9 ms
ADD COLUMN COPY           7.6 ms   12.9 ms   36.2 ms  104.0 ms
ADD INDEX INPLACE        14.8 ms   20.2 ms   33.9 ms   86.6 ms

Read the first row and the second row against each other. The instant add is the same cost at 64,000 rows as at 1,000, because it did not touch a row. The copy grows with the data, which is why an ALTER TABLE that takes a second on a staging database takes twenty minutes on the table that matters. An index build sits between them: it reads every row but does not write a new copy of the table.

You can make the server tell you which algorithm it chose, which is useful when a statement has changed behaviour between versions. Ask for the cheaper algorithm and read the error:

ALTER TABLE bench MODIFY email VARCHAR(64) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
  Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

That message is the server saying it cannot do this without rewriting every row. Run the same statement without the ALGORITHM clause and it succeeds, having silently done the copy.

Adding a column with a default is cheap, and it was not always

One consequence follows directly: the default value is not stored in each row, which is why the measurement above is flat, and why dropping the same column again is instant. A table that has taken several instant changes carries several row versions, and InnoDB's manual documents a ceiling of 64 row versions per row before the table has to be rebuilt. On the server used here, information_schema.INNODB_TABLES reported TOTAL_ROW_VERSIONS climbing by one per instant add, reaching 70 across 70 changes without an error, and returning to 0 after ALTER TABLE ... FORCE. The counter is worth watching, but the documented limit is not a wall you hit without warning:

SELECT INSTANT_COLS, TOTAL_ROW_VERSIONS
  FROM information_schema.INNODB_TABLES
 WHERE NAME = 'mydb/orders';

The "cheap" was also not always true. Before MySQL 8.0 an ADD COLUMN in any position other than the end of the table rebuilt it, which is what the ALGORITHM=COPY row above reproduces. If your experience of adding a column is that it locks a table for minutes, that experience is from an engine that behaved differently.

PostgreSQL reaches the same place by a different route. Its manual states that an ALTER TABLE subform acquires an ACCESS EXCLUSIVE lock unless the documentation says otherwise, and that a column added with a non-volatile DEFAULT has the value "stored in the table's metadata, where it will be returned when any existing rows are accessed". The lock is stronger than MySQL's metadata lock, so the statement is fast but the window in which it blocks readers is real. No PostgreSQL server was available on the machine this lesson was written on, so that claim is taken from the manual rather than measured here.

Adding a NOT NULL column to a populated table

This is the case where the obvious expectation is wrong in the helpful direction, and the real problem is quieter. MySQL does not refuse to add a NOT NULL column with no default to a populated table. It picks the implicit default for the type and writes that everywhere:

ALTER TABLE p ADD COLUMN b INT NOT NULL;
SELECT * FROM p;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 0 |
|  2 | 2 | 0 |
+----+---+---+

No warning, no error, and the column is instant. The problem is the value. Every row that existed before the change now claims b = 0, which is indistinguishable from a row where somebody deliberately set zero, and a VARCHAR column gets the empty string for the same reason. If the column means "how many times this was retried", zero is a fine guess. If it means "which plan the customer is on", the schema has invented an answer, and every report that groups by it will be wrong in a way that looks like data.

So the decision is about the placeholder rather than the constraint. Add the column nullable, backfill it with values you can defend, and tighten it later, which is the shape the next lesson builds.

Dropping a column an older deploy still selects

Dropping a column is instant, and it is the operation that causes outages, because the schema is the one interface every deploy shares. While the new version is rolling out, the previous version is still serving traffic and still contains the old column name in its queries. On a table with an is_active column and a new one called status:

old deploy: SELECT id FROM accounts WHERE is_active = 1        ok: 1
contract: DROP COLUMN is_active                                ok:
old deploy after the drop  FAILED: ERROR 1054 (42S22): Unknown column
                           'is_active' in 'where clause'

Nothing in the database is broken. Requests fail because a query names a column that no longer exists. The rule that follows is not "never drop a column" but "drop it only after the last build that reads it is gone", which means the drop belongs in a release after the one that stopped reading it. Many teams keep a dated comment beside the migration recording which release dropped the code, so the second release is a scheduled task rather than an act of memory.

A rename is two changes, not one

RENAME COLUMN old TO new is instant since MySQL 8.0, and it is still the most dangerous statement in this lesson, because it changes the interface atomically. The instant the rename lands, every build that still says email fails:

rename: RENAME COLUMN email TO email_address                 ok
old deploy: SELECT id FROM accounts WHERE email = '...'      FAILED:
  ERROR 1054 (42S22): Unknown column 'email' in 'where clause'

Expand and contract instead. Add the new column, write to both for one release, move reads to the new one, then drop the old one in a later release. With a trigger keeping the two in step, an old build that writes only the old column still populates the new one:

ALTER TABLE accounts ADD COLUMN email_address VARCHAR(255) NULL, ALGORITHM=INSTANT;

CREATE TRIGGER accounts_sync_email
BEFORE INSERT ON accounts
FOR EACH ROW SET NEW.email_address = COALESCE(NEW.email_address, NEW.email);

The trigger needs a BEFORE UPDATE twin, or updates through the old column will leave the new one stale. That is the cost of the pattern: for one release you maintain two representations and a mechanism to keep them equal, and you pay it in exchange for never requiring both builds to agree on a name.

Changing a type may rewrite the table, and the direction decides

Width changes are asymmetric, and the asymmetry is visible in the algorithm the server insists on. On 20,000 rows, widening VARCHAR(255) to VARCHAR(300) measured 2.2 ms, and the same statement with FORCE added to demand a rebuild measured 45.3 ms. The first did not read the rows; the second did. Narrowing VARCHAR(255) to VARCHAR(64) measured 40.7 ms and refused ALGORITHM=INPLACE outright, and changing CHAR(2) to VARCHAR(2) refused it too. Widening past 255 characters, where the length prefix itself has to grow, measured 1.9 ms on this data, which is the version-specific part of this section rather than something to rely on.

Do not turn that into a rule about character counts. The reliable question is whether the server will accept ALGORITHM=INPLACE for the change, and the way to find out is to ask it, on a copy of the table, with the same MySQL version you run in production. A change that refuses in-place is a change that needs a rebuild, and a rebuild needs a plan for the window it takes.

Narrowing also risks truncation: the 40.7 ms measurement above is what happens when the existing values fit. When they do not, MySQL fails the statement and leaves the table alone, which is the outcome you want:

ALTER TABLE nar MODIFY v VARCHAR(16) NOT NULL;
ERROR 1406 (22001): Data too long for column 'v' at row 1   -- column is still varchar(255)

Even an instant change waits for a metadata lock

Before any ALTER TABLE touches the data dictionary it takes an exclusive metadata lock, and it waits for every transaction that has touched the table to finish. The wait is the part that takes a table out of service, and it happens to instant changes too. With one session holding an open transaction that has read a single row:

metadata_locks while the reader's transaction is open:
| LOCK_TYPE   | LOCK_STATUS | n |
| SHARED_READ | GRANTED     | 1 |

ALTER with lock_wait_timeout=2s returned after 2.06s
  server said: ERROR 1205 (HY000): Lock wait timeout exceeded
after the reader commits, the same ALTER: ok

The ALTER was an instant column add. It did not need to read or write one row of the table, and it still could not proceed while a transaction held a shared metadata lock, because granting it would change the table's definition underneath a statement that had already resolved its columns. The isolation lesson covered the mechanism; what is new here is that the DDL side applies to the cheap statements too, so "this migration is instant" is not the same claim as "this migration will not block".

Set lock_wait_timeout on the migration connection so a stuck deploy fails loudly instead of queueing behind a long report. The wait above was charged against that variable, not innodb_lock_wait_timeout, which is the one people reach for first. And keep the statements that read schema_migrations in their own short transaction, so the runner's bookkeeping is not what a later ALTER waits for.

SET @t0 = NOW(6);
ALTER TABLE bench ADD COLUMN note VARCHAR(40) NULL, ALGORITHM=INSTANT;
SELECT TIMESTAMPDIFF(MICROSECOND, @t0, NOW(6)) / 1000 AS ms;