CQRS: Separating Reads from Writes Without Losing Your Mind

TL;DR: CQRS means using a different model to update data than the model you use to read it¹. In its mild form, you split command and query code paths against the same database. In its strong form, writes land in an OLTP store and one or more read stores (Elasticsearch, Redis, ClickHouse) are kept in sync asynchronously via CDC or events. GitHub serves 5.5 million queries per second across 1,200+ MySQL hosts by partitioning reads from writes at the schema-domain level². The pattern earns its complexity only when read and write shapes genuinely diverge. Greg Young is explicit: “CQRS is not an architecture” but “an architectural pattern” that “describe[s] something inside a single system or component” - apply it inside a bounded context, not across a whole system³.

Learning Objectives

After this module, you will be able to:

• Explain CQRS beyond the buzzword and distinguish mild from strong forms
• Decide when the cost of two models is worth the benefit
• Design projection pipelines from writes to read stores
• Handle eventual consistency in the UI (read-your-writes, stale indicators)
• Avoid common CQRS anti-patterns

Intuition

You run a restaurant. The kitchen has a ticket rail where cooks read orders. The dining room has a menu board where guests browse what is available tonight.

The ticket rail is optimized for speed and correctness: each ticket names one table, lists dishes in prep order, and tracks modifications (“no onions, extra sauce”). It is ugly, abbreviated, and only cooks can read it.

The menu board is optimized for browsing: dishes grouped by course, photos, allergen icons, wine pairings. It is beautiful, denormalized, and updated once per evening. Nobody would hand a guest a kitchen ticket. Nobody would ask a cook to plate food from the menu board.

That is CQRS. The write model (kitchen tickets) is shaped for enforcing business rules: “this table ordered the prix fixe, so they cannot add a la carte mains.” The read model (menu board) is shaped for the consumer’s query: “show me all gluten-free entrees under $30.” Forcing both jobs into one schema is like printing the menu on kitchen tickets. It works when the restaurant is small. It collapses when you have 200 covers and a 15-page wine list.

Event-Driven Architecture introduced the transport primitives that connect producers to consumers asynchronously. This chapter builds on that foundation to show how you split the models those events connect.

Theory

The core idea: commands, queries, two models

A command expresses intent to change state: PlaceOrder, CancelReservation, RateProduct. It is handled by a write model that validates business rules against a rich domain aggregate⁴. A query returns a DTO shaped for the caller and never mutates state: GetOrderSummary, SearchProducts, ListRecentActivity⁴.

The insight is that these two jobs want different data shapes. The write model needs normalization, foreign keys, and constraints to enforce invariants. The read model needs denormalization, pre-joined documents, and indexes tuned for the access pattern. Forcing both through one schema creates lock contention, complex joins on OLTP, and security concerns when one entity handles both sides⁴.

CQRS pushes Meyer’s method-level Command Query Separation (CQS) outward to whole models and sometimes whole stores¹. The two sides can share a database, use different ORMs against the same database, or live in completely separate stores synchronized by events.

Mild CQRS: one store, two models

In mild CQRS, commands and queries share the same database but use different code paths, DTOs, and repositories. No asynchronous pipeline is required.

The write side loads an aggregate, applies business logic, and persists through a repository. The read side uses separate query objects mapped to SQL views or denormalized projections in the same database, bypassing the domain model entirely⁴.

flowchart LR
    subgraph Application
        CMD[Command Handler] --> WM[Write Model<br/>aggregates + rules]
        QH[Query Handler] --> RM[Read Model<br/>views + DTOs]
    end
    WM --> DB[(Single Database)]
    RM --> DB

When mild CQRS is enough:

• Complex domain logic where query shapes differ from write shapes
• Task-based UIs with intent-named commands (BookRoom, not SetStatus)
• Teams that want cleaner separation of concerns without infrastructure overhead

The trade-off: you still share database I/O, buffer pool, and connection budget across reads and writes. If read load is crushing the primary, mild CQRS alone will not save you.

Strong CQRS: separate stores, asynchronous projection

In strong CQRS, the write model persists to one store (Postgres, DynamoDB) and one or more read stores (Elasticsearch, Redis, ClickHouse) are kept up to date asynchronously through events or change data capture.

flowchart LR
    Client -->|command| API
    API --> WriteDB[(Postgres<br/>write model)]
    WriteDB -->|WAL via Debezium| Kafka[(Kafka)]
    Kafka --> Proj1[Projector 1<br/>Flink join]
    Kafka --> Proj2[Projector 2<br/>aggregate]
    Proj1 --> ES[(Elasticsearch<br/>search)]
    Proj2 --> CH[(ClickHouse<br/>analytics)]
    Client -->|query search| ES
    Client -->|query analytics| CH

The write side publishes events after commit. A projector (Flink, Kafka Streams, a custom consumer) materializes each read store. Change Data Capture covers the mechanics of Debezium, WAL streaming, and the transactional outbox pattern that avoids dual-write hazards.

Key operational concerns:

• You cannot enlist a message broker and a database into a single distributed transaction. Use the transactional outbox or CDC to avoid dual writes⁴.
• Each projector must persist its offset atomically with the read-model update, or use idempotent writes keyed on event ID.
• Full rebuilds require the event log to be retained long enough (or snapshotted) that starting from offset zero is feasible.

CQRS and event sourcing: orthogonal patterns

CQRS separates read and write models. Event sourcing stores state as an immutable append-only log of events. They are orthogonal patterns that are frequently combined but neither requires the other¹³.

CQRS without event sourcing looks like: write to Postgres, CDC-stream changes via Debezium to Kafka, project to Elasticsearch for search. No event store, no aggregates reconstructed from event history. This is widely considered the most common production deployment of CQRS.

Event sourcing without CQRS looks like: append events to an event store, reconstruct aggregates by folding events at read time, serve reads from the same aggregates. This works until query-by-non-key becomes expensive, which is why the combination is so common.

CQRS + event sourcing gives you full history, replayable projections, and the ability to build new read models retroactively by replaying the log. But it is maximum complexity. Event Sourcing covers this combination in depth.

Eventual consistency as a UX problem

With separate stores, reads can lag writes by milliseconds to seconds. The system must either mask the lag or surface it honestly.

The classic failure: user submits a form, the command succeeds, the read model has not caught up, the user’s next page load shows old data, and the user re-submits. This is not a theoretical concern. It is a frequent source of CQRS bug reports in production.

stateDiagram-v2
    [*] --> Editing
    Editing --> Submitting: user clicks Save
    Submitting --> OptimisticView: command returns 200 OK
    OptimisticView --> Confirmed: read model version matches
    OptimisticView --> Reconciling: read model version < expected
    Reconciling --> Confirmed: poll succeeds
    Reconciling --> ConflictBanner: timeout exceeded

Practical mitigations:

• Read-your-writes for the acting user. Route the writer’s immediate next read back to the write side, or cache the command response client-side.
• Optimistic UI. Render the expected state immediately on the client. Reconcile when the read model catches up.
• Version numbers. Return a version with the write response. The client polls the read model until it sees that version (or times out).
• Lag-aware routing. Vitess routes replica reads only to replicas below a configured lag threshold so applications do not see wildly stale data⁵.
• Honest indicators. For dashboards that refresh every 10 seconds, users do not notice sub-second lag. Show “last updated 3s ago” and move on.

Real-World Example

GitHub: 5.5 million QPS across 1,200+ MySQL hosts

GitHub’s architecture is a textbook example of mild-to-moderate CQRS applied pragmatically. The platform grew from a single mysql1 cluster handling 950,000 QPS in 2019 to 1,200+ MySQL hosts across 50+ clusters serving 5.5 million QPS by 2023².

The CQRS-like separation works at two levels:

Read/write split via ProxySQL and freno. ProxySQL routes reads to replicas and writes to primaries. freno, a cooperative throttling service (HA via Raft), polls replica lag continuously and returns 200/throttle decisions to bulk write jobs so replicas never fall behind user-visible thresholds. Roughly 30% of read traffic is rerouted from the primary to replicas².

Schema domains as bounded contexts. Instead of hash sharding, GitHub groups tables by logical domain (repositories, issues, users) and moves each domain to its own cluster once SQL linters prove no cross-domain joins or transactions remain². This is mild CQRS at the infrastructure level: each domain’s write path is isolated, and read-heavy domains get dedicated replica capacity without pressuring the write primary.

Key engineering decisions:

• Invested in tooling (gh-ost for triggerless migrations, orchestrator for 10-13 second Raft-backed failover) for five years before partitioning, so migrations were low-risk.
• Partitioned by logical domain rather than by load, preserving transactional consistency within domains.
• Chose not to migrate off MySQL. The economics of rewriting Rails query patterns were worse than investing in MySQL tooling.

The lesson: you do not need a broker, projectors, and Elasticsearch to get CQRS benefits. Separating read and write paths at the database routing layer, with domain-aligned clusters, delivers most of the scaling wins with a fraction of the operational complexity.

Trade-offs

Common Pitfalls

Exercise

Design the read side of an e-commerce admin dashboard that queries 50M orders with 20 filter combinations, while the OLTP side handles checkout at 5,000 writes/sec. Decide between read replicas, a projected Elasticsearch index, and a ClickHouse warehouse. Specify lag tolerance and consistency UX.

flowchart LR
    Checkout[Checkout Service] --> PG[(Postgres<br/>orders, line_items)]
    PG -->|Debezium CDC| K[Kafka]
    K --> Proj[Projector<br/>joins + denormalizes]
    Proj --> ES[(Elasticsearch<br/>order documents)]
    Proj --> CH[(ClickHouse<br/>aggregates)]
    Admin[Admin Dashboard] -->|filter queries| ES
    Admin -->|revenue charts| CH

Key Takeaways

• CQRS means different models for reads and writes. It is a spectrum from mild (same DB, split code) to strong (separate stores, async projection).
• Most teams should start with mild CQRS or stay with CRUD. Strong CQRS earns its complexity only when read/write shapes genuinely diverge and read replicas cannot keep up.
• Eventual consistency is a UX problem, not just a theoretical one. Solve it with optimistic UI, version polling, and read-your-writes for the acting user.
• CQRS does not require event sourcing. The most common production deployment is CQRS with a relational write store and CDC-fed projections.
• Projections are cheap to add and hard to remove. Start with one. Budget ownership and retirement.
• Apply CQRS per bounded context, not globally. If you cannot articulate why your read and write patterns differ for a specific subsystem, you do not need it there.
• The dual-write bug is the silent killer. Always use CDC or the transactional outbox to feed read stores.

Further Reading

• CQRS by Martin Fowler - The definitional essay; short, opinionated, and honest about when CQRS makes things worse. Read this first.
• Greg Young’s CQRS Documents (2010 PDF) - The originating technical write-up that formalized the pattern and distinguished it from CQS.
• Clarified CQRS by Udi Dahan - The 2009 essay that separates CQRS from event sourcing and from SOA; essential for avoiding conflation.
• CQRS Pattern - Azure Architecture Center - Microsoft’s reference guide with example code, trade-off tables, and implementation guidance for both mild and strong forms.
• CQRS and Stream Processing by Streamkap - Concrete pipeline patterns with CDC and Flink; shows how to build projections with streaming SQL.
• GitHub: Scaling MySQL from One Database to 1,200+ Hosts - Deep dive into GitHub’s schema-domain partitioning, freno throttling, and read/write separation at scale.
• Scaling Event Sourcing for Netflix Downloads, Episode 2 - Netflix’s production CQRS + ES system for downloads licensing; shows the pattern in a regulated domain.
• An introduction to Monzo’s data stack - Monzo’s event-firehose to BigQuery projection pipeline; strong CQRS at organizational scale with centralized data management via Kafka, NSQ, and dbt.

Flashcards

References