How to Add a Column to a Ten‑Million‑Row MySQL Table Without Locking It

Story Background

We needed to add a business field to an online core order table that already holds tens of millions of rows. A simple ALTER TABLE order ADD COLUMN new_field VARCHAR(255); would lock the table in MySQL (especially older versions) and could cause a service avalanche.

DDL Locks the Table

Executing DDL on the primary database is risky because MySQL acquires an exclusive lock on the table, blocking all reads and writes for the duration of the operation.

Master‑Slave Switch Solution

One friend suggested adding the column on the replica, promoting the replica to master, then applying the same change to the original master. The steps are:

Keep the primary handling traffic.

Run ALTER TABLE order ADD COLUMN new_field VARCHAR(255); on the replica.

Promote the replica to become the new primary.

Apply the same change to the old primary and restore the original master‑slave topology.

This approach minimizes impact but introduces high operational risk: possible replication lag, data loss, the need for the replica to be read‑only, and considerable maintenance overhead.

Online DDL Options

Tools such as pt-online-schema-change or MySQL 8’s INSTANT column addition claim to avoid locking. In reality, online DDL works by creating a shadow table, copying data, adding triggers, and swapping table names at a suitable moment, which adds complexity and may introduce write latency due to triggers.

Rethinking the Requirement

After discussing with the product manager, we discovered the new field was only needed for downstream analytics; the other team could read the same data from logs. Storing the field in logs solved the problem without any schema change.

Plan B: Extension Table

If the field must be persisted, the simplest low‑cost method is an extension table that references the primary order table. Benefits include a stable main schema, dynamic management of extension fields, and no impact on existing business logic.

Advanced Play: JSON Extension Field

Another proposal was to create a generic ext column of type TEXT or JSON and pack all future custom fields into it, e.g.:

{
  "source": "marketing",
  "utm_campaign": "202406-promo",
  "coupon": "ABCD1234"
}

This schema‑less design is common in many internet companies.

Final Solution: Reuse a Redundant Column

We found an unused remark_ext column (VARCHAR(512)) in the order table. By expanding it to VARCHAR(2000) we could store our JSON payload without adding any new column. The test on a 100 million‑row table showed:

Increasing column length does not lock the table.

Decreasing column length does lock the table because MySQL must check for overflow.

We executed:

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

and confirmed the operation completed without blocking.

Takeaways

Technical solutions are not unique; sometimes changing the requirement is the safest path.

Avoid modifying core table structures when possible—use extension tables, JSON fields, or redundant columns.

Online DDL carries hidden complexity and should be evaluated carefully.

Always test schema changes on a realistic data volume; a 1 E‑row test environment is invaluable.