Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
Schema changes on a live MySQL database range from trivial (adding a nullable column to a small table) to dangerous (modifying a column on a 500GB table that handles 50K writes per second). The difference is whether and how long MySQL locks the table.

​
How ALTER TABLE Works

MySQL’s ALTER TABLE has two modes depending on the operation and version: Rebuilding DDL (the old behavior): MySQL creates a new table with the new schema, copies all rows, swaps the tables, and drops the original. The table is locked for writes for the entire duration — minutes to hours on large tables. Online DDL (MySQL 5.6+): Many operations can run without blocking reads or writes. MySQL performs the change in-place or rebuilds the table in the background while allowing concurrent DML. To probe what a specific operation will do, add ALGORITHM and LOCK clauses explicitly — MySQL will reject the statement if it can’t satisfy the requested constraints: 
-- Fail fast if the operation would require any locking
ALTER TABLE orders ADD COLUMN notes TEXT, LOCK=NONE;

​
Online DDL Lock Modes

The ALGORITHM and LOCK clauses control how MySQL executes the change:
ALGORITHMBehavior
INSTANTChanges only metadata — no table rebuild, no row reads. Available in MySQL 8.0 for some operations.
INPLACEModifies the table in-place with minimal locking. Rows may not be rebuilt.
COPYCreates a new table, copies all rows. Slowest; required for some operations.
LOCKBehavior
NONENo lock — reads and writes proceed normally during the change
SHAREDRead lock — reads proceed, writes block
EXCLUSIVEFull lock — reads and writes block
-- Request a specific algorithm; fails if MySQL can't do it that way
ALTER TABLE orders
    ADD COLUMN notes TEXT,
    ALGORITHM=INSTANT;

-- Fail if the operation would require a write lock
ALTER TABLE orders
    ADD COLUMN notes TEXT,
    LOCK=NONE;

​
What’s Fast vs. What’s Slow

Operations that use ALGORITHM=INSTANT (MySQL 8.0) are nearly instantaneous regardless of table size:
  • Adding a nullable column at the end of the table
  • Adding/dropping a virtual generated column
  • Changing a column’s default value
Operations that require ALGORITHM=COPY block writes for the full duration:
  • Changing a column’s data type
  • Converting character sets
  • Adding a full-text index on some versions
Most index additions use ALGORITHM=INPLACE, LOCK=NONE — the table rebuild happens in the background and DML proceeds. Adding an index on a 100GB table takes time but doesn’t block your application.

​
Large Table Migrations in Production

For operations that require ALGORITHM=COPY on tables too large to lock, use an external tool:

​
pt-online-schema-change (Percona Toolkit)

pt-online-schema-change \
  --alter "ADD COLUMN notes TEXT" \
  --execute \
  D=mydb,t=orders
pt-osc creates a shadow table, copies rows in batches, uses triggers to replicate ongoing writes, then swaps the tables. No application downtime.

​
gh-ost (GitHub)

gh-ost \
  --user=root --host=localhost \
  --database=mydb --table=orders \
  --alter="ADD COLUMN notes TEXT" \
  --execute
gh-ost uses the binary log instead of triggers, which is more reliable under high write load. It’s the tool of choice for high-traffic tables. Both tools require the table to have a primary key.

​
Migration Tools and Version Control

Track schema changes in version-controlled migration files. Common tools:
ToolLanguageStyle
FlywayJava/anySQL-based; versioned files
LiquibaseJava/anyXML/YAML/SQL; change sets
golang-migrateGoSQL-based; numbered files
AlembicPythonPython or SQL; auto-generates from models
Each tool maintains a migrations table in the database to track which migrations have run, so reruns are safe.

​
Frequently Asked Questions

​
Can I add a column without locking the table?

In MySQL 8.0, adding a nullable column at the end uses ALGORITHM=INSTANT and completes in milliseconds regardless of table size. In older versions or for other column positions, use ALGORITHM=INPLACE, LOCK=NONE — available for most column additions since MySQL 5.6. If neither works, use pt-osc or gh-ost.

​
How do I check if an ALTER TABLE will lock my table?

Add ALGORITHM=INPLACE, LOCK=NONE to the statement. MySQL will execute it without locking if it can, or return an error if it can’t — either way you get a definitive answer without guessing: 
-- If this succeeds, the index add won't lock the table
ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
Test on a copy of the table first when you’re unsure about large tables in production.

​
Should I run migrations inside a transaction?

DDL statements (ALTER TABLE, CREATE INDEX, DROP TABLE) cause an implicit commit in MySQL — they can’t be rolled back. Running them inside a BEGIN/COMMIT block doesn’t protect you. Plan your rollback strategy at the application level (dual-write, feature flags) rather than relying on transaction rollback.

​
Troubleshooting

ProblemSolution
ALTER TABLE blocking production trafficUse ALGORITHM=INSTANT if available, or pt-osc/gh-ost for large tables
ERROR 1846: ALGORITHM=INSTANT is not supportedOperation requires a table rebuild — use ALGORITHM=INPLACE or an online tool
pt-osc failing with trigger errorsTable already has triggers — use gh-ost (binary log based, no triggers)
Migration ran twiceMigration tool’s tracking table out of sync — check the schema version table
Long-running ALTER holding lockKill it with KILL <query_id> and use an online tool instead