# Designing The Read Path In CQRS 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: ```sql 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: ```bash 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: ```sql SELECT COUNT(*) FROM events WHERE user_id = ? ``` Example of fan-out: ```sql 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) | DB Type | Strengths | Weaknesses | | --- | --- | --- | | Columnar Stores (ClickHouse, BigQuery) | Super-fast aggregations, compression, distributed reads | Slow inserts, merge delays, poor transactional consistency | | Search Engines (Elasticsearch) | Full-text search, scoring, fuzzy queries, flexible indexing | Index bloat, no joins, hard to manage consistency under sync pressure | | Graph DBs (Neo4j, JanusGraph) | Relationship-centric queries, path traversal, recommendations | Not ideal for high-throughput reads, costly joins on deep traversals | | OLAP Cubes / Materialized Views | Precomputed views, excellent for dashboards | Stale data unless sync is done right, can't support ad-hoc exploration | | In-Memory Caches (Redis, Memcached) | Extremely low latency for key-based queries | Volatile storage, no secondary indexing or range queries | --- ## 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.