Demystifying MySQL ALTER TABLE ALGORITHM: INPLACE vs INSTANT vs COPY

 

Schema evolution is one of the trickiest aspects of database management. For developers and DBAs, the dreaded ALTER TABLE command often conjures fears of downtime, locks, and performance bottlenecks. But MySQL offers a powerful word! The "ALGORITHM" modifier that determines how schema changes are executed.

In this article, we’ll break down the three key algorithms "INPLACE", "INSTANT", and "COPY" to help you understand their trade-offs, performance implications, and even some unconventional ways to use them. Whether you’re optimizing for zero downtime or experimenting with stress testing, this guide will give you practical insights and real-world strategies.

IMPORTANT: Check your MySQL version: In MySQL 8.0+, many table changes are instantaneous, and the command modifications presented here are only available in this and later versions.

Understanding the ALGORITHM Modifier

When you run an ALTER TABLE in MySQL, you can specify the algorithm, like presented bellow:

ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL 
ALGORITHM=INPLACE;

The choice of algorithm determines how MySQL applies the change. It could be one of the follows:

COPY: It will create a full temporary copy of the table at a new repository. So if your table have 1Gigabyte it will create another 1Gigabite table then swap it latter on. The bigger the table the bigger "the problem".

• What It Does: Applies changes to the copy, then swaps it back;
• Implication: High disk usage, long downtime, full table lock;
• Used Lock Method: Full table lock. Blocks reads/writes until completion;
• Expected Downtime: High;
• Disk Usage: High;
• Best Use Case: Legacy changes, complex schema modifications. When you want the schema to do a full fix on the table to be sure everything is in place;

INPLACE: It alters the table without creating a full copy of it.

• What It Does: May still require metadata locks but avoids full duplication. Will not work with a few simples simpler change like setting a default CURRENT_TIMESTAMP to a datetime field or applying a CHECK constraint on a field;
• Implication: Faster than COPY, but can still block writes depending on the operation;
• Used Lock Method: Metadata lock. Faster, but may block writes briefly
• Expected Downtime: Moderate;
• Disk Usage: Low;
• Best Use Case: Adding indexes, partition adjustments;

INSTANT: It will do a simple "metadata"-only change to the database structure. It is the fastest change there could be;

• What It Does: No table rebuild, no copy, no downtime. JSON, TEXT, and ENUM columns cannot be changed with INSTANT;
• Implication: Ideal for zero-downtime schema evolution;
• Used Lock Method: Minimal to None;
• Expected Downtime: None. Metadata-only, no downtime;
• Disk Usage: Negligible;
• Best Use Case: Adding simple columns, lightweight schema evolution;

Sample Scenarios

Zero-Downtime Schema Evolution with INSTANT

Imagine deploying a new feature that requires adding a column to a production table with millions of rows. Using ALGORITHM=INSTANT, you can evolve the schema without interrupting traffic.

ALTER TABLE `users` ADD COLUMN `preferences` JSON, ALGORITHM=INSTANT;

This unlocks continuous delivery pipelines where schema changes can be deployed alongside application updates seamlessly.

When COPY Still Matters

Despite its overhead, COPY can be necessary for structural changes that INPLACE or INSTANT cannot handle (e.g., changing column types). Like checking if a value of a new field is valid.

ALTER TABLE users
    ADD COLUMN `account_level` SMALLINT NOT NULL DEFAULT 1
    CHECK ((`2fa_active`=0 AND `account_level` BETWEEN 1 AND 2) OR (`2fa_active`=1 AND `account_level` BETWEEN 3 AND 10))
    AFTER `id`,
ALGORITHM=INPLACE;

This code above will raise an error:

SQL Error [1845] [0A000]: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

Practical Insights

I can say a few words with this modification:

DevOps Integration can use it as much as possible to automate schema changes in CI/CD pipelines with INSTANT, ensuring deployments don’t stall production traffic.

When you find yourself in a situation where you need to add a new field that do not comply with the INSTANT restrictions, like creating a default CURRENT_TIMESTAMP to a datetime field you can  run the following sequence: 1) Lock the table; 2) Create the field with a default fixed value; 3) Implement an "ALTER TABLE" to change the column and set the default CURRENT_TIMESTAMP; 4) Unlock the table; 5) Run limited updates multiple times till all fields are updated.

Experiment and Comment!

As always it is nice to explore and learn. You will have your moments and find specific scenarios where this and that would work .. or not work. But it is nice to share your experience. Let us know what happened and how have you come around the issues your found related to those commands.