How to Choose Storage for Query Services: Relational DB, Search, OLAP, and Time‑Series Models

Four Query Scenarios and Their Preferred Models

Four typical business questions map to distinct data‑access patterns:

Order payment status – requires the latest transaction fact with strong consistency → Relational OLTP .

Nearby low‑temperature milk availability – needs keyword recall, filtering, sorting and faceting → Search engine .

Monthly store performance – scans massive historical facts and aggregates them → OLAP .

Order‑API error‑rate in the last five minutes – selects series by tags and computes a time‑window → Time‑Series system .

01 Order Details – Keep in Relational OLTP

OLTP handles short, independent transactions such as store validation, order creation, inventory lock, payment confirmation and order‑detail lookup. Row‑oriented storage keeps order number, member, store, status, amount, fulfillment mode and timestamp together, enabling primary‑key lookups and B+Tree secondary indexes. WAL, locks and MVCC guarantee atomicity, isolation and recovery.

How Data Enters the Transaction Store

A mini‑program calls an application service; the domain model validates business rules; the repository persists the aggregate inside a local transaction. The order aggregate writes the order and order items and records an Outbox event in the same transaction. The outbox guarantees reliable publication of the fact change without creating a second copy of the fact.

Choosing a Relational DB

MySQL / MariaDB – mature ecosystem, widely used for internet OLTP.

PostgreSQL – rich SQL, data types, indexes and extensions.

Oracle – enterprise‑grade transactions, HA and recovery.

SQL Server – deep integration with Windows/.NET stack.

Chinese Java teams often default to MySQL because of tooling and talent availability; PostgreSQL is chosen when richer SQL features are needed. When transaction volume exceeds a single‑node capacity, distributed relational stores such as TiDB, OceanBase or PolarDB‑X are evaluated, but the query model remains unchanged.

02 Product Search – Model Around Documents

Search engines care about which documents match the query and how relevant they are. Users provide keywords, not primary keys. The optimal store must combine store ID, keyword, category, status, fulfillment mode, member price and promotion tags, then sort by relevance, sales or price. A single B+Tree cannot efficiently cover this combination.

Why Inverted Indexes Fit Search

During ingestion, text is tokenized and normalized; an inverted index maps each term to the documents that contain it. Numeric and date fields use columnar structures for filtering, sorting and aggregation. Sharding lets multiple nodes answer queries in parallel.

text → tokenizer → term → inverted index (term → doc list)

keyword / numeric / date → Doc Values (supports exact filter, sort, aggregation)

index shard → local results → coordinator merges

When searching “low‑temperature milk”, the engine finds the term’s document set, then applies filter, must or should clauses for store, category, fulfillment and promotion tags.

Document Design

Instead of mirroring the relational schema, the document is built around the search API’s return shape. Example JSON:

{
  "product_id": 10001,
  "sku_id": 100011,
  "store_id": 2001,
  "title": "低温鲜牛奶 950ml",
  "category_id": 310,
  "base_price": 1590,
  "status": "ON_SALE",
  "availability": "IN_STOCK",
  "fulfillment_modes": ["PICKUP", "DELIVERY"],
  "promotion_tags": ["MEMBER_PRICE", "COUPON"],
  "sales_30d": 3821,
  "version": 1731000100,
  "updated_at": "2026-08-13T10:00:00Z"
}

The title field is indexed for full‑text search; keyword sub‑fields handle exact sorting; version and updated_at support out‑of‑order handling and reconciliation. Adding more fields increases index size and write cost, so only fields needed for queries are indexed.

Elasticsearch vs. OpenSearch

Project roadmap – Elasticsearch is led by Elastic and evolves with the Elastic Stack; OpenSearch forked from OSS 7.10.2 in 2021 and is now under the Linux Foundation.

Ecosystem – Elasticsearch ships with Kibana, Logstash, Beats, APM and Elastic Cloud; OpenSearch provides OpenSearch Dashboards, Data Prepper, security plugins and deep AWS integration.

Typical selection criteria – Choose Elasticsearch when an existing Elastic Stack is in place or multi‑cloud plugins are needed; choose OpenSearch for heavy AWS usage or when Apache 2.0/open governance is required.

03 Business Analysis – Use OLAP

Operations need daily, store‑level, fulfillment‑type and promotion‑type GMV reports, scanning billions of order rows. The query only touches time, store, amount, member and a few dimensions, so scanning a row‑store would read many irrelevant columns and contend for cache, CPU and I/O.

OLAP excels at SUM, COUNT, GROUP BY, distinct, multi‑dimensional analysis and large‑table joins, making it ideal for dashboards, real‑time warehouses, user‑behavior analysis and log analytics.

Why Columnar Storage Helps Aggregation

Columnar databases store each column contiguously, allowing scans to read only the columns needed for filters, groups and calculations. Same‑type data benefits from dictionary, run‑length and delta encoding, while vectorized execution processes batches of values for better CPU cache and SIMD utilization.

Column pruning – skips columns not used by the query.

Data skipping – Min/Max, zone maps and sorting keys prune data blocks.

Encoding compression – similar adjacent values compress better.

Vectorized execution – batch processing improves cache and SIMD usage.

MPP – distributes scan and aggregation across nodes.

Materialized view – pre‑computes high‑frequency metrics, avoiding full scans.

Row‑store is better for point lookups and tiny transactions; column‑store shines for large‑scale scans.

Doris, StarRocks, ClickHouse

Doris – real‑time warehouse, reports, detail + aggregation; Unique Key supports upsert and partial column updates.

StarRocks – real‑time warehouse, high‑concurrency BI, complex joins; Primary Key supports real‑time insert/delete/update.

ClickHouse – log/event stream, massive append‑only detail; Mutation rewrites data parts; ReplacingMergeTree provides async merge semantics.

Doris’s Unique Key is suited for upserting order status; StarRocks’s Primary Key handles real‑time changes; ClickHouse excels at immutable event streams where updates are rare.

04 Monitoring – Use a Time‑Series System

Time‑series stores organize data by time, tags and continuous sampling. Metrics such as error‑rate, lock‑duration or callback failures are recorded as series, e.g.:

http_requests_total{service="order-service",method="POST",status="500",instance="10.0.1.23:8080"} 98231

Queries first select series by tags, then read the desired time window and apply rate, increments, moving aggregates or quantiles.

Time Partitioning and Tag Indexing

Tag index – locates series matching tag filters.

Time partition – reads only the target time window.

Time‑value compression – reduces space using delta encoding.

Rate & window functions – provide built‑in time‑series semantics.

Recording rule / continuous aggregation – pre‑computes high‑frequency long‑term queries.

Retention & down‑sampling – deletes expired raw data while keeping trends.

Series cardinality is the main capacity risk; high‑cardinality tags (e.g., per‑order ID) can exhaust memory before disk.

Choosing a TSDB

Prometheus local TSDB – single‑cluster recent monitoring, alerting, troubleshooting.

VictoriaMetrics – long retention or clustered setups while keeping PromQL.

Mimir / Thanos – multi‑cluster Prometheus with object storage and unified query.

InfluxDB – device, sensor, Telegraf ecosystem.

TimescaleDB – frequent joins with PostgreSQL relational data.

ClickHouse (as TSDB) – wide‑log, event, arbitrary‑dimension SQL analysis.

TimescaleDB’s continuous aggregates materialize results into hypertables, allowing hybrid queries that combine recent raw data with pre‑aggregated buckets.

05 Synchronization Challenges for Derived Read Models

All derived models—search indexes, OLAP tables, materialized views and time‑series metrics—must handle duplication, out‑of‑order delivery, deletions, rebuilds and reconciliation.

Data Ingestion Strategies

Dual‑write for very short‑term prototypes (exposes consistency issues quickly).

Outbox + message queue for services that can emit clear domain events.

Binlog CDC for low‑intrusion capture of table changes; downstream projections still need to rebuild documents.

Idempotency, Ordering and Deletion

business‑key + comparable version/commit‑position + idempotent write + explicit delete semantics

Repeated writes with the same key and version must be no‑ops. A later version must not be overwritten by an earlier one. Deletions in the source must be reflected as deletions or invisible flags in the target model.

Full‑Load → Incremental Sync Workflow

Record a consistent CDC start position.

Build the full snapshot from the corresponding point‑in‑time snapshot.

Consume incremental changes from the recorded position.

Wait for the backlog to catch up.

Reconcile key fields, amounts and distributions.

Enable shadow queries and gradual traffic shift.

During the full load, any changes are temporarily invisible; after the snapshot finishes, incremental consumption resumes. Validation must cover normal updates, duplicate deliveries, out‑of‑order overwrites, deletions and CDC offset continuity.

Search Index Upgrade Pattern

When a major mapping change is required, create a new versioned index, build it from the full load, continue incremental updates, verify document count, fields, query results and latency, then switch an alias. Keep the old index for rollback.

Operational Diagnostics per Model

OLTP – use EXPLAIN, EXPLAIN ANALYZE and slow‑query logs to check index usage, row scans and sorting.

Search – use _search, Profile API, and index/alias health checks.

OLAP – examine execution plans, query profiles, partition pruning and compaction stats.

TSDB – run promtool, /targets and query API to verify configuration, scrape health and sample windows.

Decision Flow

Write down query conditions, return fields, time range, latency target and consistency requirement.

Map the query to a model: relational OLTP for latest facts or transactional decisions, search engine for keyword‑based relevance, OLAP for large‑scale historical aggregation, time‑series for tag‑based metric windows.

Confirm data entry points: OLTP for core transactions, projection services for search documents, CDC/event pipelines for OLAP, exporters/agents for metrics.

Compare concrete products:

Doris or StarRocks for update‑heavy analytical data; ClickHouse for append‑only event streams.

Elasticsearch for existing Elastic Stack; OpenSearch when Apache 2.0 or AWS alignment is required.

Prometheus for short‑term monitoring; VictoriaMetrics or remote storage for long‑term retention.

The goal is a clean division of labor—each query type hits the data structure that best serves it, transaction facts stay authoritative, and derived read models can be rebuilt and reconciled when problems arise.