How to Safely Add a Column to a Billion‑Row MySQL Order Table

Background and Problem

When a neighboring project team requested a new business field for statistical analysis, the straightforward idea of running ALTER TABLE on a core order table with tens of millions of rows was quickly dismissed because MySQL (especially older versions) locks the table during DDL, potentially causing a service outage.

1. DDL Locks the Table – Execute with Extreme Caution

ALTER TABLE order ADD COLUMN new_field VARCHAR(255);

Even a short‑lived lock can trigger a cascade of blocked requests and an avalanche of failures.

2. Master‑Slave Switch Strategy

Keep the primary database serving traffic.

Run ALTER TABLE on the replica to add the column.

Promote the replica to primary.

Repeat the operation on the original primary and restore the original master‑slave relationship.

This approach minimizes impact but introduces risks such as data lag, potential data loss, read‑only replica misconfiguration, and high operational overhead.

3. Online DDL Tools

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.

Online DDL essentially builds a new table, synchronizes data, and then switches the name, which can add write latency due to triggers and requires careful timing.

While technically feasible, the added complexity and performance impact must be evaluated.

4. Rethink the Requirement

Discussion with the product manager revealed that the field is only needed for analytics; logging the information and letting the downstream team parse the logs would satisfy the need without any schema change.

5. Plan B: Extension Table

If persisting the data is still required, an “extension table” can be used:

order_extend
- order_id
- extra_field_x
- extra_field_y
- ...

The main order table remains untouched, and queries join the extension table when needed. Benefits include a stable core schema, dynamic management of extra fields, and no impact on existing business logic.

6. Advanced Technique: JSON Extension Field

Define a generic ext column of type TEXT or JSON and store all additional attributes as a JSON object.

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

This “schema‑less” approach is common in large‑scale internet companies and offers great flexibility.

7. Final Solution: Reuse an Existing Redundant Column

The order table already contained an unused remark_ext column (VARCHAR(512)). By repurposing it for the new data, the team avoided any DDL on the core schema.

No new column addition.

No join queries required.

No new table deployment.

After increasing its length to 2000 characters with:

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

tests on a 100 million‑row table showed that expanding the column size does not lock the table, whereas shrinking it would cause a lock because MySQL must verify data length.

8. Key Takeaways

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

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

Online DDL carries hidden risks—always evaluate business impact.

Testing against production‑scale data is essential; a realistic test environment is the best safety net.