How to Add a Column to a Tens‑Million‑Row Order Table Without Locking It

Background

We needed to add a new business column to an order table that already holds tens of millions of rows. The request came from another project for statistical analysis. Directly executing ALTER TABLE order ADD COLUMN new_field VARCHAR(255); would lock the table in MySQL (especially older versions), potentially causing a service outage.

Why DDL Locks Are Dangerous

DDL on a core online table can block reads/writes, leading to request blockage and avalanche effect.

Master‑Slave Switch Approach

One suggested solution was to add the column on the replica, promote the replica to master, then repeat the change on the original master. This minimizes impact but introduces risks: data lag or loss during promotion, need for the replica to be read‑only, high operational cost, and it is unsuitable for small teams.

Online DDL Tools

Tools such as pt-online-schema-change or MySQL 8’s INSTANT option perform the change by creating a shadow table, copying data, using triggers, and swapping table names at an appropriate moment. This method adds write latency from triggers and requires careful timing of the swap.

Rethinking the Requirement

Discussion with the product manager revealed that the new field is only needed for analytics and could be logged instead of stored in the table. This simple solution avoids any schema change.

Low‑Cost Alternatives When a Column Is Required

1. Extension table : keep the main table stable and store extra fields in a separate table; queries become slightly more complex but the core schema remains untouched.

2. JSON or TEXT “ext” column : define a single ext column of type JSON or TEXT and pack all additional attributes inside it, a common “schema‑less” pattern in many internet companies.

Final Solution Using a Redundant Column

We discovered an unused remark_ext column (VARCHAR(512)). By expanding it to VARCHAR(2000) we could store our JSON payload without adding a new column or changing queries. In a test with 100 million rows, increasing the length did not lock the table, while decreasing it did lock because MySQL must check for overflow.

Takeaways

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

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

Online DDL carries risk; evaluate impact carefully.

Large‑scale testing (e.g., 1 E rows) is essential before production rollout.