Dynamic Extension of Fields in Billion‑Row Tables: Challenges and Practical Solutions

When a core table containing billions of rows needs a new column, the naive "ALTER TABLE" approach can cause table locks, page splits, and index performance decay, potentially leading to production incidents; the article seeks a low‑impact, configuration‑only method for dynamic field expansion.

The background explains that business changes often require adding fields, but on massive tables this introduces lock contention, page fragmentation, and index degradation, especially when using JSON‑based extension fields in older MySQL versions.

The first solution, an "extension field", stores extra data as a JSON column. The article provides a Java model illustrating how the JSON is serialized and deserialized:

public class Order {
    private Long orderId;
    private String extend;

    /** * Inner class for the extend field */
    @Data
    public static class ExtendObj implements Serializable {
        private Long uId;
        private String name;
        // ... other fields
    }

    /** * Set method for extend */
    public void setExtendObj(ExtendObj extendObj) {
        // Simplified for illustration
        this.extend = JSON.toJSONString(extendObj);
    }

    public ExtendObj getExtendObj() {
        // Simplified for illustration
        return JSON.parseObject(extend, ExtendObj.class);
    }
}

This approach suffers from three major drawbacks: the JSON fields cannot be indexed, concurrent updates may overwrite each other (even with CAS), and every new field requires code changes and redeployment, leading to repetitive work.

The second solution, an "extension table", normalizes each JSON key‑value pair into separate rows in a dedicated table, enabling indexing and reducing concurrency conflicts. Sample tables show how order_id, key, and value are stored, and how adding a new attribute (e.g., age) simply adds more rows.

While the extension‑table method solves indexing and concurrency issues, it multiplies row counts and requires sharding; the article therefore adopts a three‑component architecture: a data‑management service for field metadata, a storage layer using the extension table, and a retrieval layer powered by Elasticsearch (ECP) to merge main‑table and extension‑table data for queries.

With this configuration‑driven system, business users can add new fields via a management UI without code changes; the fields are automatically stored, passed through interfaces, and searchable, achieving dynamic extensibility while maintaining stability and performance.

The conclusion emphasizes that dynamic field extension in high‑volume tables is a trade‑off between flexibility and stability, and that separating core and extension data, coupled with decoupled management, storage, and retrieval, reduces design complexity and mitigates technical risk.