You’ve split the write and read paths.

Your source-of-truth database is lean, consistent, and focused only on capturing the ground truth.
But users don’t want ground truth — they want answers. Fast.

• “Show me my leaderboard rank.”

• “Find all invoices tagged 'pending' over ₹10K from last quarter.”

• “Auto-complete as I type a product name.”

These queries are expensive, frequent, and often shaped very differently from how data is written.

This is where read-optimized databases step in — not to store truth, but to shape truth into answers.

But the real challenge is: which read DB do you pick?

• Do you go with Elasticsearch for text-heavy queries?

• Or a columnar DB like ClickHouse for slicing and aggregating?

• Or a materialized streaming DB that gives low-latency snapshots?

And what if you need two?

This post is all about making those choices — understanding what makes read workloads fundamentally different, how read-optimized DBs think, and what trade-offs you invite by choosing one over the other.

Let’s begin.

---

What Makes Read Demands Unique

The read side of a CQRS system isn’t just a mirror of the write side — it behaves fundamentally differently under load, schema expectations, and query semantics. Here's why:

---

1. Multi-Dimensional Aggregations Break OLTP Models

Read queries often span multiple dimensions:

SELECT city, product, hour, COUNT(*) 
FROM orders 
GROUP BY city, product, hour;

But OLTP databases are row-oriented and optimized for fast inserts, not full-table scans.
They struggle with:

• Inefficient use of indexes (multi-column GROUP BY)

• Poor cache locality due to scattered reads

• CPU/memory pressure from large aggregations without vectorized execution

Columnar DBs (e.g., ClickHouse, Apache Druid) outperform here by design.

---

2. Complex Filters and Full-Text Search

Users demand flexible queries:

Find all products where title contains 'ultra', category = 'laptops', price < 70K

OLTP indexes aren't built for fuzzy matching or partial text filters.

Key challenges:

• Lack of inverted indexes or tokenized search trees

• JOINs needed to resolve denormalized fields

• Query planners not optimized for filter-first execution

Search-optimized engines like Elasticsearch or Typesense handle this better with Lucene-backed structures.

---

3. High-Concurrency, Low-Latency Pressure

In real-world production:

• OLTP systems can handle a few hundred QPS (queries/sec) before degradation.

• Read-heavy dashboards, user profiles, and reports easily hit 10K+ QPS.

Read DBs mitigate this by:

• Pre-aggregating views

• Using cache-aware indexes

• Supporting horizontal read replicas

Response targets often fall under P95 < 100ms, something OLTP write DBs can't promise without caching or denormalization.

---

4. Fan-out / Fan-in Query Patterns

Example of fan-in:

SELECT COUNT(*) FROM events WHERE user_id = ?

Example of fan-out:

SELECT * FROM user_orders u JOIN refunds r ON u.order_id = r.order_id WHERE u.user_id = ?

These patterns stress relational joins and create I/O amplification.
Read DBs overcome this by:

• Using wide tables or nested JSON columns

• Performing pre-joins at ingestion time

• Leveraging document stores or vectorized scans

---

5. Time-Series, Snapshots, and Retention-Aware Reads

Time-based queries — think metrics dashboards or user activity charts — are extremely common.

Characteristics:

• Large range scans with fine-grained timestamps

• Need for downsampling, rollups, or windowed aggregation

• Data pruning or TTL for storage hygiene

OLTP stores aren't optimized for this access pattern. Specialized TSDBs like Prometheus or TimescaleDB are.

---

Designing Queries and Read Models

1. Queries Are Information Requests — Never Decision Triggers

Queries must be purely declarative, side-effect free, and detached from business rules.
Their output is data shaped for consumption, not input for decisions.

❌ Bad: SELECT * FROM orders WHERE status = 'pending' → cancel order
✅ Good: SELECT order_id, expected_ship_time → display on dashboard

Reads must never influence domain transitions. That’s the job of the write model.

---

2. Projections Are Purpose-Built — Not Just Denormalized Mirrors

A read model is not a 1:1 copy of the write schema.
It is customized for specific access patterns — built for rendering, filtering, and aggregation.

One command model → multiple read projections:

• User profile view

• Admin analytics

• Mobile summary tiles

Expect divergence. Structure for the consumers, not the source of truth.

---

3. Read Models Must Be Disposable and Horizontally Scalable

Projections should be rebuildable from event logs or sync layers.
No coupling to domain invariants. No assumptions of global consistency.

Design for:

• Partitioned access (e.g., by region, tenant, shard)

• Lag tolerance and compensatory UIs

• Write-optimized appenders + read-optimized aggregators

They must scale out, degrade gracefully, and tolerate replay or drift.

---

Choosing the Right Read Database — What to Consider

Just like writes, reads have their own workload shape. But unlike writes, reads are shaped by access patterns, not data correctness. Your system may survive a slow write — but a slow read kills UX.

Here’s what architects must evaluate when selecting a read-optimized database:

---

1. Query Complexity & Shape

• Does your system need aggregations, groupings, percentile calcs, or cross-dimensional filters?

• Will it serve ad-hoc queries from dashboards or fixed projections?

• Choose columnar or pre-joined DBs (e.g., ClickHouse, Apache Druid) for high-dimensional queries.

• Avoid key-value stores unless access is predictable and flat.

---

2. Concurrency & Latency Profile

• What's your expected QPS (queries per second) and P99 latency target?

• If your reads are bursty (e.g., dashboards refreshing every 5s for 10K users), you need a DB with:

  • Efficient caching (e.g., Redis, Rockset)

  • Low index lookup latency

  • Read replicas to distribute load

---

3. Indexing & Search Requirements

• Do users need full-text search, fuzzy match, or wildcard queries?

  • If yes: Elasticsearch, Typesense, or Meilisearch

• Do they sort, paginate, or do complex filtering?

  • Go beyond B-tree indexes: look at inverted indexes or bitmap indexes

---

4. Freshness vs Staleness

• Is eventual consistency acceptable?

  • E.g., dashboards with 30s delayed data = OK

  • Fraud detection requiring up-to-the-second reads = NOT OK

• If freshness matters:

  • Choose DBs with real-time ingest (Materialize, Apache Pinot)

  • Consider stream-to-query systems, not batch ETL

---

5. Cost of Joins and Denormalization

• Read paths usually prefer denormalized shapes

• But denormalization increases storage + update complexity

• Choose DBs that support:

  • Materialized views for precomputed joins

  • Or query-time joins with fast lookups (e.g., Rockset or StarTree)

---

6. Data Volume and Retention Windows

• Are you querying across hours or months?

• Time-series DBs (e.g., TimescaleDB, InfluxDB) handle large timestamped datasets well

• Analytics stores (e.g., BigQuery, Snowflake) handle petabyte scans — but with high latency and cost

---

7. Tolerance to Staleness, Lag, and Replay

• If the sync pipeline fails, can your read DB tolerate partial sync or out-of-order events?

• Choose append-only models where possible

• Use idempotent updates and compaction strategies to avoid state drift

---

8. Operational Considerations

• Does your team have ops experience with this DB?

• Is observability built-in? Does it scale read replicas cleanly?

• Some read DBs (like Elasticsearch) are high-maintenance under load

---

Read-Optimized DB Categories (and Their Strengths)

---

Thinking Like an Architect (for Reads)

Instead of asking "which DB gives the fastest SELECT?", ask:

• Can the DB scale with read concurrency without blowing up CPU or cache pressure?

• Does it support multi-dimensional access patterns (e.g., group-by + filter + sort)?

• Can it serve sub-second latency under high dashboard or mobile-app traffic?

• How expensive is it to materialize or refresh derived views?

• Can it handle partial availability without exploding with errors?

---

What to Avoid (for Read Side)

1. Assuming one read pattern = one DB

   • Most read models evolve. Don’t lock yourself into Elasticsearch just because “we search stuff”.

2. Thinking analytics = logs

   • True analytical queries require joins, filters, group-bys — logs alone won't help.

3. Ignoring cache invalidation

   • Reads often use Redis layers. Forgetting cache update strategy = stale data everywhere.

4. Using the sync DB as the read DB

   • Just because your materializer wrote to Mongo doesn’t mean Mongo is the best read engine for the end-user app.

---

How to Choose a Read DB — 6 Real Systems, 6 Tradeoffs

🧾Note:
These aren’t “always use this DB” rules.
They’re just examples of how you might think through the read-side choice — based on your app, your traffic, and what really matters for your reads.
Your mileage will vary. The goal is to understand the reasoning, not blindly copy the tool.

---

Example 1: E-Commerce Order History

The Read Shape:

• Read-heavy page with filters (date, product, price), paginated lists, and occasional search.

• Most customers check their orders via web or mobile app.

• Query volume is high but predictable.

What Matters:

• Fast pagination over large datasets (per user).

• Ability to serve sorted, filtered results quickly.

• Indexing on multiple fields (e.g., status, date).

• Low latency — it’s a user-facing view.

• Read scaling under sales spikes (e.g., festive seasons).

DB Candidates:

• Elasticsearch: Great for filtered search + sorting across millions of documents.

• Postgres with materialized views: Viable if data is denormalized and views are refreshed smartly.

• ClickHouse (if queries are analytical in nature, e.g., spend trends, not just order list).

Why These Work:

• Search indices like Elasticsearch shine when you want pre-tokenized filtering + sorting.

• Postgres can work, but needs tuning (GIN indexes, partial indexes, smart refresh policies).

• ClickHouse is fast but better when querying aggregates than fetching single user order lists.

Avoid:

• Mongo here if sorting across multiple large fields — unless you model carefully.

• Dynamo if you want flexible querying — key-value access alone won’t help with filters.

---

Example 2: Ride-Sharing Platform – Matching, Pricing, and Surge Heatmaps

The Read Shape

This is a highly real-time, spatial, and user-contextual read workload. Your app may request:

• Nearby drivers for a rider within 1–3 seconds.

• Surge pricing details for a given geohash tile.

• Heatmaps for operational dashboards every few seconds.

• ETA predictions based on live traffic and driver density.

Reads must be fast, dynamic, and localized — with minimal lag, as stale data directly affects user trust and matching logic.

---

What Matters

• Low-latency geospatial lookups (bounding box, radius, polygon).

• Read freshness — writes and reads may be decoupled, but riders must see a consistent view of supply/demand.

• Concurrent query handling, especially in high-traffic cities.

• In-memory or cache-accelerated indexes for real-time experience.

---

DB Candidates

• Redis + Geo API (for nearest drivers)

• Elasticsearch (for filtered queries on indexed driver metadata)

• Apache Druid or Pinot (for aggregated metrics & surge calculation)

• PostGIS (for durable geospatial queries — mostly internal tools)

---

Why These Work

• Redis Geo delivers sub-50ms radius queries from memory — ideal for driver lookup, if consistency lag is tolerable.

• Elasticsearch supports secondary filtering like driver ratings, trip count, vehicle type.

• Druid/Pinot offer lightning-fast aggregations over millions of driver pings, ideal for surge computation or dashboard heatmaps.

• PostGIS can offer powerful geo logic, but it’s heavier and better suited for offline map data processing than runtime lookups.

Each serves a narrow slice — CQRS works because no single DB can do all this equally well in production at scale.

---

Avoid

• Using the write DB (e.g., Mongo or Postgres) for live geo reads — geospatial indexes often choke on frequent writes and bounding-box scans.

• Relying only on cache without invalidation control — causes ghost drivers or surge zones to linger.

• Pushing read logic to mobile clients — leads to duplicate logic, inconsistent user experience, and worse ops visibility.

---

Example 3: Real-Time Game Leaderboards

The Read Shape:

• High-concurrency reads (thousands of players polling every few seconds).

• Sorted ranking by score or time.

• Often filtered by region, mode, or timeframe (e.g., “Top 100 this week, in Asia, for Solo Mode”).

What Matters:

• Millisecond reads under load.

• Sorted, bounded reads (e.g., Top-N queries).

• High update rate — scores change constantly.

• Multi-tenant isolation (sharding by game/mode/region).

DB Candidates:

• Redis Sorted Sets: Lightning-fast top-N queries, atomic updates, and TTL support.

• ClickHouse (for periodic materialization): if full history and aggregations are also needed.

• DynamoDB with Global Secondary Indexes (GSIs): if strong multi-region support is critical.

Why These Work:

• Redis ZSETs are a classic fit — write score updates as atomic operations, read top ranks in O(logN).

• If you want durability + long-term analysis, ClickHouse pairs well as a secondary store.

• DynamoDB gives horizontal scale and global distribution, but needs careful modeling for sort + filter.

Avoid:

• Traditional RDBMS unless you’ve precomputed ranks — SQL row-level locking and sort queries won't scale.

• Document stores — not optimal for live, sorted global views.

---

Example 4: Real-Time Financial Platform – Portfolio Views and Market Feeds

The Read Shape

Users expect live dashboards showing:

• Portfolio performance across stocks, crypto, and mutual funds.

• Ticker-level market feeds updating every second.

• Aggregated risk metrics, asset allocations, and gain/loss views.

• Read-heavy operations like filtering by asset class or sorting by gain %.

The data is event-driven, often time-series in nature, and aggregated on-the-fly. Users want precision, but also speed.

---

What Matters

• Sub-second query latency, even with thousands of concurrent users.

• Efficient time-window aggregations (e.g., last 1 hour, 1 day).

• Fast recalculation of derived fields (e.g., daily % change, volatility).

• High read concurrency with read-isolation from volatile write streams.

---

DB Candidates

• Apache Druid / TimescaleDB (for portfolio aggregates + charts)

• ClickHouse (for OLAP-style performance with fresh inserts)

• Materialized views in PostgreSQL (if data freshness is relaxed)

• Redis Sorted Sets (for leaderboards, top gainers/losers, etc.)

---

Why These Work

• Druid supports low-latency slice-and-dice queries, perfect for dashboards with real-time stock movement.

• ClickHouse offers high throughput and excellent compression for time-series financial events.

• Redis enables real-time ranking and percentile calculations for top assets.

• Materialized views work well when the market data is delayed (e.g., 15 min) and not truly real-time.

You’ll often split data by use case — Redis for top movers, Druid for portfolio charts, ClickHouse for analytics — each read path tuned for speed and query shape.

---

Avoid

• Querying raw transaction logs for read models — transforms are too expensive and introduce delay.

• Mixing trading engine writes with read dashboards — you risk locking the write DB and introducing read spikes that impact critical trade flow.

• Assuming BI tools alone are “read side” — real-time users need APIs and near-instant responses, not Tableau refreshes.

---

Example 5: Health Monitoring Platform – Patient Vitals and Alerting

The Read Shape

Medical staff dashboards need real-time views of:

• Patient vitals (heart rate, BP, oxygen saturation)

• Alerts when metrics cross thresholds

• Time-series plots of vitals over the last 30 mins / 6 hours / 1 day

• Audit logs or historical comparisons

This is a low-latency, high-integrity read flow — human lives depend on it.

---

What Matters

• Streaming freshness — stale vitals = wrong clinical decisions

• Efficient range queries on time-indexed vitals

• Redundancy and failover — reads should never go down

• Concurrency — multiple users (nurses, doctors, dashboards) querying same patient

---

DB Candidates

• Apache Kafka + Materializer (e.g., Materialize or Flink SQL)

• InfluxDB or TimescaleDB for time-series access

• Redis Streams + TTL for short-term, in-memory critical data

• Postgres with indexed JSONB columns for structured clinical events

---

Why These Work

• Kafka + Materialize supports reactive alerting and real-time materialized tables from streams.

• InfluxDB excels at time-windowed queries over high-frequency sensor data.

• Redis is ideal for a short working set of vitals under 5–10 minutes old.

• Postgres handles the slow-path — audit logs, clinical tags, historical info.

A multi-tiered read strategy is essential — Redis or Influx for hot reads, and a slower store for compliance/retention.

---

Avoid

• Polling the write DB for every metric update — it kills write throughput and lags behind actual vitals.

• Using dashboards that query across partitions — slow and error-prone.

• Ignoring temporal resolution — 1-second precision vs 1-minute aggregation matters a lot here.

---

Example 6: Enterprise SaaS Dashboard – Reports and Executive Views

The Read Shape

C-level and operations teams want:

• High-level summary dashboards with KPIs

• Customizable filters (region, product, owner)

• Historical trends across weeks/months

• Scheduled reports + real-time exploration

These are OLAP-heavy, slice-and-dice reads, often coming from billions of rows.

---

What Matters

• Fast aggregations with GROUP BYs across multiple dimensions

• Support for derived metrics — conversion %, drop-offs, churn

• Schema flexibility — users may change filters, drilldowns on the fly

• Concurrency scaling — many users hitting similar dashboards

---

DB Candidates

• ClickHouse – high-performance column store

• Apache Druid / Pinot – built for dashboards and time-based aggregations

• BigQuery (batch reads) – great for scale, bad for interactivity

• Elasticsearch – for search-heavy filtering and keyword-based facets

---

Why These Work

• ClickHouse and Druid are proven at dashboard workloads — pre-aggregated rollups, fast group-bys, smart caching.

• Elasticsearch supports text search and faceted navigation well.

• BigQuery works when you have patience — good for precomputed reports or async analytics, not for snappy reads.

A CQRS read DB here needs to scale to massive volume, offer sub-second response times, and integrate well with BI tools.

---

Avoid

• Overloading transactional DBs (like Postgres) for reports — you’ll drown in index scans.

• Trying to "join everything live" — precompute as much as possible.

• Letting filters bypass pre-aggregations — one ad-hoc query can wreck performance.

---

Conclusion: Read Isn’t Just a Mirror — It’s a Product

Choosing the right database for your read path isn't about replicating write data blindly — it's about reshaping it into something useful, fast, and predictable under load.

In every example we saw:

• The read access pattern was very different from the write structure.

• Latency, freshness, and fan-out mattered more than transactional guarantees.

• Each use case needed a purpose-fit engine, not just a replica of the OLTP system.

So whether you’re building a real-time leaderboard, a ride-tracking system, or a CEO dashboard — your read DB is not just a cache. It’s a production surface, and it deserves design respect.

And sometimes… more than one read DB is the right answer.

---

🔜 Next up in this series:

We are going to design an app from scratch using CQRS - models, dbs, sync, et al.