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

Story background

Recently we faced a seemingly simple but tricky requirement: add a business field to a table with tens of millions of orders. The request came from another project team that needed the field for statistical analysis.

From a development perspective, adding a column sounds easy—just run ALTER TABLE. However, the order table is a core online table; executing DDL on a table of this size can lock the table and severely impact business.

The challenge: how to add the field without affecting online services?

1. DDL operations lock tables; online execution must be cautious

Our initial idea was to execute directly on the primary database:

ALTER TABLE order ADD COLUMN new_field VARCHAR(255);

In MySQL (especially older versions), DDL locks the table, potentially causing request blockage and avalanche effects.

We consulted a friend who suggested a master‑slave switch approach:

2. Master‑slave switch: add the field on the replica, then switch roles

The proposed steps:

Keep the primary handling business.

Execute ALTER TABLE on the replica to add the column.

Promote the replica to primary.

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

This method is theoretically feasible and minimizes business impact, but it has drawbacks:

Switching masters requires caution; data delay or loss may occur.

The replica must be read‑only to avoid inconsistency.

Operational and risk costs are high, unsuitable for small teams.

3. Online DDL: a more complex solution

Tools like pt-online-schema-change or MySQL 8’s INSTANT option are often mentioned. Online DDL actually works by creating a shadow table, copying data, adding triggers, and swapping table names at the right moment.

Online DDL uses “create a new table + copy data + write triggers + table name switch” to avoid direct modifications on the original table.

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

4. Rethink the requirement: do you really need the field in the database?

We discussed with the product manager and discovered the field was only needed for log‑based analysis. The other team could extract the data from logs, eliminating the need for a new column.

5. Plan B: use an extension table with on‑demand joins

When a column must be stored, a common solution is an extension table:

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:

Main table structure stays stable.

Extension fields can be managed dynamically.

No impact on existing business logic.

6. Advanced technique: JSON extension field

Another team suggested defining a single ext column of type TEXT or JSON and packing all new fields inside it, parsing as needed. Example:

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

This “schema‑less” design is flexible and widely used in internet companies.

7. Final solution: reuse an existing redundant column

We noticed a historic column remark_ext (VARCHAR(512)) that was unused. We repurposed it to store our extension data, avoiding schema changes.

No new column needed.

No join queries required.

No new table to deploy.

The product raised a concern about length; we increased the column to VARCHAR(2000). In a test environment with 100 million rows, we ran:

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

Results:

Increasing length does not lock the table.

Decreasing length locks the table because MySQL must check for overflow.

Summary

Adding a column to a core large table is far from trivial. Key takeaways:

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

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

Online DDL carries risk; evaluate business impact carefully.

Testing with realistic data volumes is essential before production rollout.