Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 75 ยท Tables, Constraints and Schema Design

ALTER TABLE and Online DDL

Change MySQL tables safely: ADD, MODIFY, CHANGE and DROP, the INSTANT, INPLACE and COPY algorithms, the LOCK clause, metadata locks and gh-ost or pt-osc.

Schemas change. A new feature needs a column, a name turns out to be wrong, a type is too small. In MySQL, ALTER TABLE makes these changes, and on a small table it is instant and boring. On a table with a billion rows that serves live traffic, the same statement can lock writes for hours, fill the disk, or stall every query behind it.

This lesson shows the everyday forms of ALTER TABLE, then the part that matters in production: online DDL, which is MySQL's set of strategies for changing tables while the application keeps working. Almost everything here is read-only, because it depends on a real InnoDB server. The two live examples use the SQLite stand-in to show the shape of a column change and the table-rebuild pattern that the slow path uses.

Three algorithms

INSTANT Only metadata changes. Done in milliseconds.
INPLACE Rebuilds inside InnoDB, usually with DML allowed.
COPY Builds a full copy of the table. Blocks writes.

MySQL picks the fastest one the change allows.

1 / 2

The everyday forms

SQL
-- add a column (position optional)
ALTER TABLE members ADD COLUMN plan VARCHAR(20) NOT NULL DEFAULT 'free' AFTER email;

-- change a column's type, keeping its name: MODIFY restates the whole definition
ALTER TABLE members MODIFY COLUMN plan VARCHAR(40) NOT NULL DEFAULT 'free';

-- rename a column (8.0+)
ALTER TABLE members RENAME COLUMN fullname TO full_name;

-- rename and redefine in one go: CHANGE needs the old name, new name AND definition
ALTER TABLE members CHANGE COLUMN nick nickname VARCHAR(50) NULL;

-- remove a column
ALTER TABLE members DROP COLUMN legacy_code;

-- indexes, constraints, table options
ALTER TABLE members ADD INDEX idx_members_plan (plan);
ALTER TABLE members DROP INDEX idx_members_plan;
ALTER TABLE members ADD CONSTRAINT chk_plan CHECK (plan IN ('free','pro'));
ALTER TABLE members ENGINE=InnoDB;      -- rebuilds the table
RENAME TABLE members TO customers_2025; -- atomic, very fast

These statements are read-only because they use MySQL syntax such as AFTER, MODIFY and CHANGE.

Several can be combined into one ALTER TABLE separated by commas. One combined statement rebuilds the table once, while three separate statements might rebuild it three times, so combine whenever you can.

MODIFY and CHANGE replace the entire column definition. If you write ALTER TABLE members MODIFY plan VARCHAR(40); and the column was NOT NULL DEFAULT 'free', both the NOT NULL and the default disappear. Always copy the full current definition from SHOW CREATE TABLE and edit it.

The next example runs in the SQLite stand-in. SQLite has its own ADD COLUMN, RENAME COLUMN and DROP COLUMN, which look like MySQL's, and a pragma that lists a table's columns the way DESCRIBE does:

SQL Add, rename and drop columns

Press Run (or Ctrl + Enter) to see the result here.

Existing rows received plan = 'free' automatically, because the new column has a default. This is the same rule MySQL uses: a column added to a table that has rows gets a value for every existing row. Without an explicit default, MySQL fills in the type's implicit zero (0 for numbers, an empty string for text), which is rarely what you meant. Always provide a default when you add a NOT NULL column.

Online DDL: ALGORITHM and LOCK

Since MySQL 5.6, and much improved in 8.0, InnoDB can run many changes without blocking the table. You control this with two clauses at the end of the statement:

SQL
ALTER TABLE orders
  ADD COLUMN coupon_code VARCHAR(30) NULL,
  ALGORITHM=INSTANT;

ALTER TABLE orders
  ADD INDEX idx_orders_created (created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM chooses how InnoDB does the work:

  • INSTANT changes only the table's definition in the data dictionary. No rows are touched, so it takes milliseconds regardless of table size. Since MySQL 8.0.12 it covers adding a column (at the end, and since 8.0.29 at any position), and since 8.0.29 dropping a column. It also covers renaming a column, setting or dropping a default, and renaming a table. InnoDB keeps a limited number of instant changes (64 row versions) before it needs a rebuild.
  • INPLACE works inside InnoDB without creating a second copy of the whole table through the server layer. Adding or dropping a secondary index is INPLACE and does not rebuild the table. Other changes, such as making a column NOT NULL or changing the primary key, are INPLACE but rebuild the table in place, and can take a long time on large tables.
  • COPY is the old method. MySQL creates a new table with the new definition, copies every row into it, and swaps. Writes are blocked while it runs. Changing a column's data type (for example INT to BIGINT) always uses it.
  • DEFAULT (or no clause) lets MySQL use the fastest algorithm that works.

LOCK says how much concurrent access you require while it runs: NONE (reads and writes continue), SHARED (reads continue, writes wait), EXCLUSIVE (nothing continues) or DEFAULT (as little locking as possible).

The reason to spell both out is that if you request something the change cannot deliver, MySQL refuses instead of silently degrading:

TEXT
mysql> ALTER TABLE orders MODIFY total BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

So adding ALGORITHM=INSTANT (or INPLACE, LOCK=NONE) to a migration is a safety check. If MySQL cannot honor your promise, the statement fails in a fraction of a second and nothing has changed, instead of starting a six-hour table copy on a busy production table.

Change Typical algorithm Rebuilds table? Concurrent writes?
Add column INSTANT No Yes
Drop column INSTANT (8.0.29+), else INPLACE No / Yes Yes
Rename column INSTANT No Yes
Add or drop secondary index INPLACE No Yes
Extend VARCHAR within the same length-byte class (up to 255 bytes) INPLACE No Yes
Set column NOT NULL INPLACE Yes Yes
Change data type COPY Yes No
Change character set of a column COPY Yes No
Change the primary key INPLACE Yes Yes

Treat the table as a guide. The exact answer depends on the MySQL version, so check the documentation for your release, or try the statement on a copy of the table with the explicit ALGORITHM clause and let the server tell you.

Locks that bite: metadata locks

Even a fully online ALTER TABLE must take a brief metadata lock at the beginning and at the end. It cannot get the lock while another transaction that has read the table is still open, including an idle one that forgot to commit. And the nasty part is that while your ALTER waits for that lock, every new query on the table queues behind the ALTER. A forgotten transaction can therefore freeze the whole table for everyone.

SQL
-- Session 1 (a report that someone left open)
START TRANSACTION;
SELECT COUNT(*) FROM orders;      -- now holds a metadata lock; never commits

-- Session 2
ALTER TABLE orders ADD COLUMN x INT, ALGORITHM=INSTANT;   -- waits...

-- Session 3 (normal traffic)
SELECT * FROM orders WHERE id = 1;                        -- waits behind the ALTER

Protect yourself with a short SET SESSION lock_wait_timeout = 5; before the DDL, so it gives up quickly and you can retry, and check SHOW PROCESSLIST or performance_schema.metadata_locks for the culprit. The locking lesson covers lock diagnostics in more depth.

The rebuild pattern behind COPY

What ALGORITHM=COPY does, and what every external tool does in some way, is the classic table rebuild: create a new table with the right shape, copy the data across, then swap the names. You can run the same pattern live. Here the price column is stored as text and we convert it to integer cents:

SQL Changing a column type by rebuilding the table

Press Run (or Ctrl + Enter) to see the result here.

The result shows prices 250, 1200 and 4025. Copying every row is why this approach scales with table size, and why writes made to the old table while the copy runs would be lost unless something replays them. That is exactly the problem the tools below solve. In MySQL you can swap two tables atomically with RENAME TABLE products TO products_old, products_new TO products;.

Large tables: gh-ost and pt-online-schema-change

When a change needs COPY (or INPLACE is still too heavy), teams use an external online schema change tool. The two common ones follow the rebuild pattern, with a way to keep up with writes:

  • pt-online-schema-change (from Percona Toolkit) creates a shadow table, installs INSERT, UPDATE and DELETE triggers on the original so new writes are mirrored, copies the old rows in chunks, then renames the tables.
  • gh-ost (from GitHub) also builds a shadow table and copies in chunks, but instead of triggers it reads the binary log to replay changes. It adds no trigger load to the primary, can be paused, throttled and cancelled, and can test the migration on a replica first.
flowchart LR
  A["1. Create shadow table<br/>with new definition"] --> B["2. Copy rows in small chunks"]
  B --> C["3. Replay concurrent writes<br/>(triggers or binlog)"]
  C --> D["4. Atomic rename:<br/>shadow becomes live"]

Both tools follow the same four steps; they differ in how writes are replayed.

Both need a primary key (or a unique key) on the table, and both are tricky with foreign keys. You run them from the shell, not from SQL:

TEXT
$ gh-ost --host=db1 --database=shop --table=orders \
    --alter="MODIFY total BIGINT NOT NULL" --chunk-size=1000 --max-load=Threads_running=25 \
    --execute

Managed services (RDS, Aurora, Cloud SQL) support these tools with some limits. Many teams use them for every change on their biggest tables, and plain ALTER TABLE for the rest.

A safe migration routine

  1. Read the manual for your exact version, or test the statement on a restored copy, and note the time.
  2. Add an explicit ALGORITHM (and LOCK) so an unexpected rebuild fails fast.
  3. Check for long-running transactions before you start, and set a short lock_wait_timeout.
  4. Run during a quiet window, and watch replication lag if you have replicas, because a long ALTER also replays on each replica.
  5. Prefer small, reversible steps. Add the new column, backfill in batches, switch the application, and drop the old column in a later release. This expand and contract pattern turns one risky change into several boring ones.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why add ALGORITHM=INSTANT (or INPLACE, LOCK=NONE) explicitly to a migration?

    With an explicit algorithm, an unsupported request fails immediately with an error and changes nothing. Without it, MySQL would choose a slower method on its own.
  2. 2. A table has plan VARCHAR(20) NOT NULL DEFAULT 'free'. What does ALTER TABLE t MODIFY plan VARCHAR(40); do to NOT NULL and the default?

    MODIFY and CHANGE replace the entire column definition, so any attribute you leave out disappears.
  3. 3. Which of these usually rebuilds the whole table and blocks writes?

    Changing a data type uses the COPY algorithm. Adding or renaming a column is INSTANT, and adding an index is INPLACE without a rebuild.
  4. 4. How does gh-ost keep the shadow table up to date while it copies rows?

    gh-ost reads the binary log and replays changes on the shadow table. pt-online-schema-change is the one that uses triggers.

Summary

  • ADD, MODIFY, CHANGE, RENAME COLUMN and DROP cover most changes. MODIFY and CHANGE replace the whole definition, so restate every attribute.
  • ALGORITHM=INSTANT only edits metadata, INPLACE works inside InnoDB (indexes are cheap), and COPY rebuilds the whole table and blocks writes.
  • State the ALGORITHM and LOCK you expect: MySQL then fails fast instead of silently choosing a slow path.
  • A waiting ALTER TABLE needs a metadata lock and blocks queries queued behind it. A forgotten open transaction can freeze a table, so set a short lock_wait_timeout.
  • For huge tables use gh-ost (binary log) or pt-online-schema-change (triggers): shadow table, chunked copy, replay writes, atomic rename.
  • Prefer expand-and-contract migrations: add, backfill, switch, then drop.

Next: Schema Design Patterns for MySQL, where we apply all of this to real design decisions.