How to Add a Column to a Billion‑Row Order Table Without Breaking Production

Background

A product team requested a new column on a core order table that already holds tens of millions of rows. Directly executing ALTER TABLE on such a large table can lock the table and cause severe service disruption.

Initial Direct DDL Attempt

Running a simple ALTER TABLE order ADD COLUMN new_field VARCHAR(255); would lock the table in MySQL, especially older versions, leading to request queuing and possible outages.

Master‑Slave Switching Approach

Keep the master serving traffic.

Apply the ALTER TABLE on the replica.

Promote the replica to master after the change.

Switch the original master back as a replica.

While theoretically sound and low‑impact, this method introduces risks: data lag or loss during promotion, read‑only replica requirements, and high operational overhead, making it unsuitable for small teams.

Online DDL Solutions

Tools like pt-online-schema-change or MySQL 8’s INSTANT column addition perform the change by creating a shadow table, copying data, using triggers, and swapping table names. This avoids locking the original table but adds complexity: trigger‑induced write latency and precise timing for the table‑swap.

Rethinking the Requirement

Discussion with the product manager revealed that the new field is only needed for analytics and could be logged instead of stored in the DB. Logging the data satisfied the requirement without any schema change.

Extension Table Pattern

Create a separate order_extend table:

order_extend
- order_id
- extra_field_x
- extra_field_y
- ...

Write new fields to this table and join it when needed. Benefits include a stable main table, dynamic management of extra fields, and no impact on existing business logic.

JSON‑Based Schema‑Less Extension

Define a single ext column of type TEXT or JSON in the main table and store all additional attributes as a JSON object. Example payload:

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

This approach offers flexibility and avoids further table alterations.

Leveraging an Existing Redundant Column

The order table already contained an unused remark_ext VARCHAR(512) column. The team repurposed it to hold the JSON payload, avoiding any new column addition.

Adjusting Column Length Safely

To accommodate future growth, the column length was increased to VARCHAR(2000) with:

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

Testing on a 100 million‑row table showed that increasing length does not lock the table, whereas decreasing length does because MySQL must check for overflow.

Key Takeaways

Technical solutions are not unique; revisiting the business need can eliminate complex changes.

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

Online DDL carries hidden costs such as trigger latency and swap timing.

Always validate schema changes in a realistic test environment before production rollout.