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

Story Background

We recently faced a seemingly simple but tricky requirement: add a business field to a tens‑of‑millions‑row order table for statistical analysis.

From a development perspective, adding a column with ALTER TABLE seems trivial, but the table is a core online table; executing DDL could lock the table and severely impact business.

How can we add a column to a massive order table without affecting online services?

1. DDL locks the table, so online execution must be careful

Initial idea was to run directly on the primary database:

ALTER TABLE order ADD COLUMN new_field VARCHAR(255);

In MySQL (especially older versions) DDL locks the table, even briefly, which can cause request blockage.

2. Master‑slave switch: add column on replica then promote

Primary continues serving business.

Execute ALTER TABLE on the replica.

Promote replica to primary.

Repeat on the original primary to restore the original topology.

This is theoretically feasible with minimal business impact, but it carries risks such as data delay, consistency, and high operational cost.

3. Online DDL: more complex under the hood

Tools like pt-online-schema-change or MySQL 8’s INSTANT option perform online DDL by creating a shadow table, syncing data, using triggers, and swapping table names at the right moment.

Online DDL actually creates a new table, copies data, writes triggers, and switches names, rather than modifying the original table directly.

This approach requires careful evaluation of trigger latency and timing of the table‑name switch.

4. Rethink the requirement: does the field really need to be stored?

Discussion with the product manager revealed that the field is only needed for data analysis and could be logged instead of stored in the order table.

5. Plan B: Extension table with on‑demand joins

order_extend
- order_id
- extra_field_x
- extra_field_y
...

The main table remains unchanged; new fields are written to the extension table and queried via JOIN. Advantages: stable main schema, dynamic management of extra fields, no impact on existing business logic.

6. Advanced: JSON extension field

Define a single ext column of type TEXT or JSON and pack all extra fields inside it.

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

This “schema‑less” design allows flexible addition of new fields without altering the table structure.

7. Final solution: reuse a redundant column

We discovered an unused remark_ext column (VARCHAR(512)) in the order table. By expanding its length to 2000 and storing JSON data there, we avoided schema changes, joins, and new tables.

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

Testing on a table with 100 million rows showed that increasing column length does not lock the table, while decreasing it does.

Summary

Technical solutions are not unique; sometimes changing requirements is simpler.

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

Online DDL carries risk; evaluate business impact carefully.

Testing at scale is essential before production deployment.