How to Safely Add a Column to a Billion‑Row Order Table Without Downtime

Story Background

We encountered a seemingly simple but tricky requirement: add a business field to a table with tens of millions of orders. The field is needed for statistical analysis by another team.

DDL operations lock tables, online execution must be careful

Running a plain ALTER TABLE order ADD COLUMN new_field VARCHAR(255); on a large MySQL table can lock the table, causing request blockage and potential service avalanche.

Master‑slave switching solution: add field on replica then switch

The idea is to add the column on the replica, promote the replica to master, then repeat on the original master. Steps:

Keep the primary database serving traffic.

Execute 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 relationship.

This approach minimizes impact but carries risks such as data lag, consistency issues, and high operational cost.

Online DDL solution: behind the scenes

Tools like pt-online-schema-change or MySQL 8’s INSTANT option create a shadow table, copy data, use triggers, and swap table names at an appropriate moment. This avoids direct locking but adds complexity, trigger latency, and requires careful timing.

Rethink: do you really need the field in the database?

After discussing with the product manager, we learned the field is only needed for data analysis and could be written to logs instead. The other team can pull logs for their analysis, eliminating the need for a schema change.

Plan B: extension table, on‑demand join queries

Keep the main table stable and store additional attributes in a separate extension table. This preserves core table stability while allowing flexible queries.

Advanced: JSON extension field

Define a generic ext column of type TEXT or JSON and store all extra attributes as JSON. Example:

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

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

Final solution: reuse redundant field

We discovered an unused remark_ext column (length 512) in the order table. By agreeing on a format and encapsulating data into this field, we avoided adding a new column, extra joins, or new tables.

Testing results

Increasing the length of remark_ext to 2000 with ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000); on a test table with 100 million rows did not lock the table, whereas reducing the length would lock it.

Summary

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

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

Online DDL carries risk; evaluate impact carefully.

Testing in a realistic environment is essential before production rollout.