Schemas change all the time. You add a column, drop an index, switch a data type. The old way of doing this locked the table while MySQL rebuilt it, which meant downtime, blocked writes, and an unhappy on-call rotation. MySQL Online DDL is the feature that lets you make many of these changes while the database keeps serving traffic. It's genuinely useful, but it isn't magic, and the differences between how it runs your statement matter a lot in production.
Three Algorithms: INSTANT, INPLACE, and COPY
When you run a DDL statement, MySQL picks one of three algorithms to carry it out. They differ in how much work they do, how much they let other queries run alongside them, and which operations they can actually handle.
INSTANT: The Fastest Option
INSTANT changes only the table's metadata. There is no data copying and no table rebuild, so it finishes in a moment regardless of table size and barely touches CPU or I/O. It also plays nicely with replication. The catch is that it only works for a narrow set of operations, such as adding a column with a default value or modifying an enum. When it applies, it's the one you want.
INPLACE: The Balanced Approach
INPLACE modifies the table in place without building a temporary copy. It covers more ground than INSTANT, including creating indexes and dropping columns, and it allows concurrent DML (INSERT, UPDATE, DELETE) while it runs. The cost is real work: the operation can consume meaningful CPU, memory, and I/O while it churns through the table.
COPY: The Traditional Method
COPY is the old behavior: MySQL builds a new table, copies every row across, then swaps the two. It's the slowest of the three, but it supports every schema change, including data type modifications that the other two can't touch. Reach for it when neither INSTANT nor INPLACE can do the job.
Operation Support Matrix
| Operation | INSTANT | INPLACE | COPY |
|---|---|---|---|
| Adding a Column | ✅ * | ✅ | ✅ |
| Dropping a Column | ✅ * | ✅ | ✅ |
| Renaming a Column | ✅ * | ✅ | ✅ |
| Changing Data Type | ❌ | ❌ | ✅ |
| Adding Secondary Index | ❌ | ✅ | ✅ |
| Dropping Index | ❌ | ✅ | ✅ |
| Adding Primary Key | ❌ | ✅ * | ✅ |
| Dropping Primary Key | ❌ | ❌ | ✅ |
| Adding Foreign Key | ❌ | ✅ | ✅ |
Conditions apply. See MySQL documentation for details.
Key Limitations
"Online" sets expectations that the feature doesn't always meet, so it's worth knowing where it bites.
Resource Impact: INPLACE operations are not free. On a busy server, the CPU, memory, and I/O they consume can be enough to drag down the queries you actually care about.
Replication Lag: This is the one that surprises people. The operation runs on the primary, then runs again on every replica. An INPLACE change that takes 3 hours on the primary will take roughly 3 hours on each replica too, and replication stalls behind it the whole time. On a large table, that's a lot of lag.
Locking Issues: Even an online operation needs a metadata lock at certain points, and that lock can block other DDL and some DML. The window is usually brief, but it exists.
Limited INSTANT Support: The fast path is also the most restricted one. The heavier operations, such as data type changes, index additions, and primary key modifications, fall outside what INSTANT can do, which means you're back on INPLACE or COPY for exactly the changes that hurt the most.
Best Practices
A few habits keep schema changes boring, which is the goal.
Choose Wisely: Prefer INSTANT, fall back to INPLACE when you have to, and treat COPY as the last resort.
Test First: Run the change in a staging environment that mirrors production before you run it for real. Table size and data distribution change how long these operations take.
Monitor Performance: Keep an eye on CPU, memory, and I/O while the operation runs so you can catch trouble early.
Consider Third-Party Tools: For complex migrations or strict availability requirements, tools like gh-ost or pt-online-schema-change give you more control and handle replication more gracefully than the native algorithms.
Plan for Replication: In a replicated setup, think through how the change propagates. Running it on each server individually, or using an external tool, can save you from a multi-hour lag spike.
Conclusion
MySQL Online DDL is a real improvement over locking the table and waiting, but the name oversells it. INSTANT is great when it applies, INPLACE works for most everyday changes if you plan for the resource and replication cost, and COPY is there for the rest. The teams that avoid surprises are the ones who know which algorithm their statement will use before they run it, and who reach for gh-ost or pt-online-schema-change when native isn't enough.