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

Problem background : A requirement arose to add a new business field to a core order table containing tens of millions of rows. Directly executing ALTER TABLE would lock the table and risk a service outage.

1. Direct DDL risks

Running a simple statement like:

ALTER TABLE order ADD COLUMN new_field VARCHAR(255);

in MySQL (especially older versions) acquires an exclusive lock, potentially blocking all incoming requests.

2. Master‑slave switch approach

One possible workflow is to add the column on the replica, promote the replica to master, then repeat the change on the original master:

Keep the original master serving traffic.

Execute ALTER TABLE on the replica.

Promote the replica to become the new master.

Apply the same change to the old master and restore the original topology.

While theoretically feasible and low‑impact, this method introduces risks such as replication lag, data inconsistency, and high operational overhead.

3. 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, adding triggers, and swapping table names. This avoids a direct lock but adds complexity, especially around trigger‑induced write latency and precise timing of the table‑swap.

Online DDL essentially works by "create a new table + copy data + write triggers + switch table name".

4. Rethinking the requirement

Discussion with the product team revealed that the new field is only needed for analytical logs, which can be captured in existing log streams without persisting to the order table.

5. Extension‑table pattern

Create a separate table to store additional fields:

order_extend
- order_id
- extra_field_x
- extra_field_y
- ...

When querying, join the extension table:

SELECT ... FROM order o LEFT JOIN order_extend e ON o.id = e.order_id;

Advantages: the core table remains stable, extra fields are managed dynamically, and existing business logic is untouched.

6. JSON‑based schema‑less extension

Define a single ext column of type TEXT or JSON and store all future fields as a JSON object, parsing them as needed. This provides maximum flexibility without further schema changes.

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

7. Reusing an existing redundant column

Identify an unused column (e.g., remark_ext VARCHAR(512)) and repurpose it to hold the JSON payload, avoiding any DDL:

No new column addition.

No join queries required.

No new table deployment.

When the length needed grew, the column was altered to VARCHAR(2000) in a test environment with 100 million rows. The experiment showed that increasing column length does **not** lock the table, whereas decreasing it does because MySQL must verify existing data fits the new size.

ALTER TABLE order MODIFY COLUMN remark_ext VARCHAR(2000);

Summary of lessons

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

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

Online DDL carries hidden risks; always evaluate impact on latency and business continuity.

Testing with realistic data volumes is essential before rolling out schema changes.