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

Background

We needed to add a new business field to a core order table that holds tens of millions of rows. A simple ALTER TABLE would lock the table in MySQL, potentially causing a service outage.

Attempted Direct DDL

ALTER TABLE order ADD COLUMN new_field VARCHAR(255);

Running this on the primary would lock the table because MySQL (especially older versions) locks the table during DDL.

Master‑Slave Switch Approach

Keep the master serving traffic.

Run ALTER TABLE on the replica.

Promote the replica to master.

Repeat the change on the original master and restore the original topology.

Works in theory but risky: possible data lag, consistency issues, high operational cost.

Online DDL Tools

Tools like pt-online-schema-change or MySQL 8’s INSTANT option create a shadow table, copy data, use triggers, and swap names.

Online DDL actually “creates a new table + copies data + writes triggers + switches table names”.

This reduces lock time but adds complexity: trigger latency, timing of the switch, and extra resources.

Rethinking the Requirement

Discussion with product revealed that the new field is only needed for analytics and could be written to logs instead of the database.

Alternative Low‑Cost Solutions

Extension Table

order_extend
- order_id
- extra_field_x
- extra_field_y
...

The original table stays unchanged; new fields are stored in the extension table and joined when needed.

JSON / Schema‑less Column

Define a single ext column of type TEXT or JSON and store arbitrary key‑value pairs.

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

This provides flexibility without altering the schema.

Reuse an Existing Redundant Column

We repurposed an unused remark_ext column (VARCHAR(512)) as a catch‑all extension field, later expanding it to VARCHAR(2000) after testing.

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

Increasing length does not lock the table.

Decreasing length does lock the table because MySQL must check overflow.

Key Takeaways

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

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

Online DDL carries risk and must be evaluated carefully.

Testing with realistic data volumes (e.g., 100 million rows) is essential before production rollout.