Query Separation: Optimizing Large Table Queries with Separate Read Stores

In previous articles the author introduced cold‑hot separation for slow primary table reads and writes, but noted its limitations such as slow cold‑data queries, inability to modify cold data, and scalability issues. To address these, the author presents query separation , which involves writing data to a primary store while simultaneously maintaining a backup copy in a separate read‑optimized storage system.

Query separation is suitable when data volume is large, write performance is acceptable, read performance is poor, data may be modified at any time, and the business seeks faster query responses. A real‑world example from a SaaS customer‑service system shows that moving millions of ticket records to a separate query store reduced query latency from tens of seconds to under 500 ms.

The article outlines three ways to trigger and build query separation:

Synchronous creation : After each write, the application immediately creates the corresponding query record. This ensures consistency and real‑time availability but adds code intrusion and may slow writes.

Asynchronous creation : Writes are followed by background processes that generate query records. This avoids impacting the main flow but can lead to temporary consistency gaps.

Binlog‑based creation : Database binary logs are captured and processed to build query records without modifying application code. It offers zero code intrusion but introduces architectural complexity and potential consistency issues.

Each method’s advantages and disadvantages are discussed, helping readers choose the most appropriate approach for their environment.

Implementation details include using a message queue (MQ) for asynchronous pipelines, with recommendations to reuse existing MQ solutions or select a new one based on prior articles. The author also warns about MQ failures, idempotent consumption, and message ordering, providing mitigation strategies such as flagging unprocessed records and handling out‑of‑order updates.

For the storage of query data, relational databases are deemed unsuitable for massive read workloads. The author suggests three alternatives: MongoDB, HBase, and Elasticsearch, emphasizing that the final choice should align with existing infrastructure and team expertise.

When accessing query data, the article notes two consistency strategies: blocking reads until the latest data is available, or informing users that the data may be slightly stale (e.g., up to one second) and encouraging refreshes.

In summary, query separation offers a practical solution to large‑scale query latency problems, though it introduces new considerations such as write‑side performance, data migration, and consistency handling, which will be explored in future articles.