How to Add a Column to a Billion‑Row Order Table Without Locking Production

Story Background

We needed to add a new business column to a core order table that stores tens of millions of rows. The requirement came from another team for statistical analysis, but directly executing ALTER TABLE on such a large table could lock it and disrupt online services.

1. DDL Operations Lock Tables

Running a plain ALTER TABLE order ADD COLUMN new_field VARCHAR(255); on MySQL (especially older versions) acquires a lock on the table, potentially causing request blockage and service avalanche.

2. Master‑Slave Switch Solution

One approach is to add the column on the replica, promote the replica to master, then apply the same change on the original master:

Keep the primary handling business traffic.

Execute ALTER TABLE on the replica.

Promote the replica to master.

Apply the change on the original master and restore the original master‑replica relationship.

While theoretically feasible and low‑impact, this method risks data lag or loss, requires the replica to be read‑only, and adds operational complexity.

3. Online DDL Solutions

Tools like pt-online-schema-change or MySQL 8’s INSTANT option perform the change by creating a shadow table, copying data, adding triggers, and swapping table names at an appropriate moment. This avoids direct locking but introduces trigger‑induced write latency and requires careful timing of the table‑swap.

4. Rethink the Requirement

After discussing with the product manager, we learned the new field is only needed for downstream data analysis. The team can simply write the data to logs, which they already consume, eliminating the need for a schema change.

5. Extension Table Pattern

If the field must be stored, an extension table can be created:

order_extend
- order_id
- extra_field_x
- extra_field_y
...

The original order table stays unchanged; new fields are inserted into order_extend and queried via JOIN. This keeps the core table stable and isolates schema changes.

6. JSON Extension Field

Another flexible approach is to add a single TEXT or JSON column (e.g., ext) and store all additional attributes as a JSON object, parsing them as needed.

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

7. Reusing a Redundant Column

We discovered an unused remark_ext column (VARCHAR(512)) in the order table. By repurposing it to hold the extension JSON, we avoid adding new columns or tables. The format is agreed upon and written programmatically.

No new column addition.

No extra join queries.

No new table deployment.

We increased its length to 2000 to accommodate future growth:

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

Testing on a 100‑million‑row table showed that increasing column length does not lock the table, while decreasing it does because MySQL must check for overflow.

Conclusion

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 hidden risks; evaluate trigger latency and swap timing.

Always validate changes in a production‑scale test environment before rollout.