How to Add a Column to Billion‑Row Tables Without Downtime

In large‑scale development, tables with billions of rows (e.g., e‑commerce order or logistics tables) are common, and adding a new column directly can lock the table for hours, causing service outages.

To avoid downtime, a metadata table can be defined to describe extension fields, while the actual values are stored in a separate extension table, effectively turning rows into columns.

The metadata table records information such as field name (e.g., user_name ), type (String), length (64), whether it is required, and creation time, allowing easy management of extensible fields.

Suppose a requirement arises to add user name, product type (self‑operated or not), gift flag, and order remarks to an existing order table with billions of rows. These fields are stored in the extension table as shown below.

When an order is placed, the extension table holds the additional data for that order.

The relationship diagram illustrates how the order ID links to the extension table to retrieve the added fields.

Using this extension‑table method is essentially a row‑to‑column transformation, but it introduces two challenges: (1) the extension table itself can become extremely large (billions of rows), and (2) queries that filter on extension fields may suffer performance degradation.

To address the size issue, the extension table can be sharded by order ID using a hash function, storing data across multiple physical tables.

When extension fields need to be queryable, the data can be synchronized to Elasticsearch via a binlog listener, enabling fast search.

The overall workflow is: shard the extension table, sync changes to Elasticsearch through binlog listening, read from Elasticsearch for latency‑tolerant scenarios, and fall back to the primary database for latency‑critical queries.

Summary:

For billion‑row tables, use a metadata table plus an extension table (row‑to‑column) to add fields without affecting online services.

Shard the extension table to manage its massive size.

Synchronize extension data to Elasticsearch to ensure efficient query performance.