DEV Community

Bibek
Bibek

Posted on Originally published at bibekkakati.com

CQRS: Read-Write Separation Design Pattern

#distributedsystems #cqrs #systemdesign #software

In traditional software architectures, we almost instinctively reach for the CRUD (Create, Read, Update, Delete) paradigm. We design an entity model, map it to a relational schema using an ORM, and use that identical abstraction to both alter state and display data on user dashboards.

For simple applications, this works flawlessly. But as systems scale—both in business complexity and throughput, this dual-purpose model starts showing fractures:

  • Write logic demands tight validation, transactional boundaries, normalization, and domain invariants.
  • Read logic demands flat, pre-aggregated, denormalized representations across dozens of tables to serve responsive UIs.

Trying to satisfy both masters with a single schema leads to unwieldy SQL joins, lock contention, compromised domain boundaries, and performance gridlock.

This is where Command Query Responsibility Segregation (CQRS) enters the picture.

1. What is CQRS?

Coined by Greg Young and based on Bertrand Meyer’s Command-Query Separation (CQS) principle, CQRS states that an application should use separate models to update and read data.

At its philosophical core:

  • Command (Write): Represents an intent to alter domain state (e.g., SubmitOrder, DeactivateUser, ChangeBillingAddress). A command should focus entirely on domain logic, data integrity, and business rules. In strict CQRS, commands do not return domain data — only an acknowledgment, validation failure, or generated entity ID.
  • Query (Read): Retrieves data without mutating application state (e.g., GetOrderSummaryById, ListCustomerInvoices). Queries should execute side-effect-free operations that return lightweight Data Transfer Objects (DTOs). 
         ┌────────────────────────────────────────────────────────┐
         │                       Client                           │
         └─────────────┬────────────────────────────▲─────────────┘
                       │                            │
             Execute Command                    Run Query
                       │                            │
                       ▼                            │
         ┌───────────────────────────┐  ┌───────────┴─────────────┐
         │       Command Model       │  │       Query Model       │
         │ (Validation & Invariants) │  │   (Optimized for DTOs)  │
         └─────────────┬─────────────┘  └───────────▲─────────────┘
                       │                            │
                 Mutates State                Direct Read
                       │                            │
                       ▼                            │
         ┌───────────────────────────┐  ┌───────────┴─────────────┐
         │       Write Storage       │──|    Synchronization      │
         └───────────────────────────┘  │   (Sync / Async CDC)    │
                                        └─────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

2. How CQRS Works in Practice

Let’s trace an end-to-end user interaction in a CQRS system using an e-commerce order:

The Write Path (Command Flow)

  1. Client Action: The user clicks "Place Order", dispatching a PlaceOrderCommand.
  2. Command Handler: The handler receives the command, loads the aggregate (e.g., Order), validates business rules (inventory checks, credit limits), and produces state changes.
  3. Write Persistence: The entity state (or an event stream) is committed to the Write Data Store within an atomic transaction.
  4. Notification / Projection: An event (e.g., OrderPlaced) or a database Change Data Capture (CDC) stream is emitted.

The Read Path (Query Flow)

  1. Read Model Projection: A background projector (or synchronous handler) catches the update and reshapes the data into a denormalized table or document store (order_views).
  2. Client Action: The user visits their dashboard, issuing GetCustomerOrdersQuery.

  3. Query Handler: The query bypasses business validation engines, complex ORM logic, and multi-table SQL joins, executing a direct indexed lookup:

    SELECT * FROM order_views WHERE customer_id = :customerId;

  4. DTO Return: The UI receives ready-to-render JSON data immediately.

3. Types of CQRS Implementations

CQRS is not a binary choice. It exists on an implementation spectrum, ranging from simple code separation to distributed event-driven systems.

Single-Database CQRS (Code-Level Separation)

Both read and write models target the same relational database, but your application code completely decouples the command handling logic from the query handling logic.

  • How it works: The write path uses domain entities, while the read path uses raw SQL, lightweight micro-ORMs, or database views to fetch flat DTOs.
  • Consistency: Strong / Immediate. Everything happens within a single ACID transaction.
  • When to use: When your codebase is becoming bloated with domain logic inside query paths, but you do not have massive scale or high read/write asymmetry.

Dual-Model, Single Database (Synchronous Projection)

The write model updates normalized tables (e.g., orders, order_items, customers). Inside the same database transaction, the application updates a denormalized summary table (order_view_flat).

  • How it works: The read queries hit the denormalized table, eliminating runtime joins without introducing message queues.
  • Consistency: Strong / Immediate.
  • When to use: When you need sub-millisecond query lookups for complex pages, but cannot tolerate the eventual consistency or infrastructure overhead of separate databases.

Split Databases with Asynchronous Sync

The write model targets a dedicated Write DB (e.g., PostgreSQL primary), while the read model targets an independent Read DB (e.g., a denormalized PostgreSQL instance, Elasticsearch, or MongoDB).

  • How it works: Commits on the write DB publish events or WAL updates. CDC pipelines (Debezium/Kafka) or event subscribers consume these updates, transform the data, and upsert them into the read database.
  • Consistency: Eventual Consistency. Read views lag slightly behind the write path (typically 25ms–300ms).
  • When to use: High read-to-write ratios (100:1+), disparate indexing requirements, or where full-text search / flexible document storage is required for queries.

CQRS with Event Sourcing (ES)

Instead of storing the current state of an entity, you store an append-only log of immutable domain events (OrderCreated, ItemAdded, ShippingAddressUpdated).

  • How it works: The write store is an Event Store. The query models (projections) are derived read representations built by replaying and subscribing to this event stream.
  • Consistency: Eventual Consistency.
  • When to use: Systems requiring complete auditability, temporal querying ("what was the state at 2:00 PM yesterday?"), financial ledgers, or complex distributed domains.

4. Alternative Approaches (and Why They Might Fall Short)

Before adopting full CQRS, engineers often evaluate simpler architectural alternatives. Each comes with clear trade-offs:

Traditional Read Replicas

  • Concept: Direct read traffic to replica instances using standard database replication (e.g., Postgres WAL streaming).
  • Drawbacks:
    • Schema Coupling: Read replicas contain the exact same normalized schema as the primary.
    • Query Overhead: Complex joins, subqueries, and aggregations still occur at query time.
    • Index Contention: Adding excessive indexes to optimize read queries on replicas creates overhead and replication lag.

Database Materialized Views

  • Concept: Define views that pre-compute joins and aggregations directly in the database.
  • Drawbacks:
    • Refresh Costs: Standard materialized views require manual or periodic refreshes (REFRESH MATERIALIZED VIEW), locking rows or draining database CPU.
    • Incremental Refresh Complexity: Incremental view maintenance (IVM) is either unsupported natively or strictly limited to simple single-table operations without subqueries.

Application-Layer Cache (Redis / Memcached)

  • Concept: Wrap queries in a caching layer (Cache-Aside pattern).
  • Drawbacks:
    • Cache Invalidation Nightmares: Knowing exactly which cached objects to invalidate when a nested entity updates is notoriously difficult.
    • Cold Start Latency: Cache misses force heavy fallbacks onto the relational database.
    • Limited Query Flexibility: Key-value caches do not excel at multi-attribute filtering, sorting, or pagination across variable criteria.

5. Addressing the Obvious Doubts & FAQ

"Isn't a denormalized read DB in Postgres just a read replica?"

No. A read replica is an identical physical clone of your write database schema.

In a CQRS split-database setup, the read database contains an entirely different, query-tailored schema. It holds pre-joined JSON documents, flattened tabular projections, and specialized indexes (such as GIN or full-text) that would be too heavy to maintain on your transactional write primary.

"Can I write denormalized tables in the same DB and just replicate them?"

Yes, but watch your write latency. Writing to both normalized tables and denormalized summary tables inside the same transaction increases transaction duration, amplifies WAL generation, and can introduce severe row-lock contention on aggregate records (e.g., multiple orders updating the same merchant total row).

"Do I have to use Event Sourcing to use CQRS?"

No. This is the single most common misconception in system design. CQRS can be implemented with standard state-based ORMs and relational databases. While Event Sourcing almost always requires CQRS (because event streams are difficult to query directly without projections), CQRS does not require Event Sourcing.

"How much latency does eventual consistency introduce?"

In a well-architected CDC and message-broker pipeline, the typical end-to-end lag ranges between 25 ms and 300 ms under normal load.

Spikes can occur during large batch updates, rebalances, or heavy read-side lock contention.

"How do I deal with users seeing stale data immediately after submitting a form?"

When using asynchronous CQRS, navigating immediately to a list page might show outdated data if the projection is lagging by 100ms. Common mitigation patterns include:

  1. Optimistic UI: The frontend client updates its local state immediately upon a successful command dispatch without refetching from the read model.
  2. Command Response Payloads: Return the newly updated projection directly in the command response body for immediate display.
  3. Read-Your-Own-Writes Tokens: The command returns a version identifier (e.g., version=42). The subsequent read query passes this token; if the read store has only caught up to version=41, the query either waits for catch-up or momentarily queries the write primary.

6. Summary: When to Use (and When to Avoid) CQRS

Indicator Avoid CQRS Consider CQRS
Domain Complexity Simple CRUD, small team, standard forms Intricate domain logic, complex invariants, distinct team ownership
Workload Profile Balanced read/write, low traffic Heavy read-to-write asymmetry (e.g., 50:1 to 1000:1)
Data Relationships Single-table queries, basic joins High-dimensional views requiring expensive 10-table joins
Consistency Needs Strict global ACID consistency mandatory Read views can tolerate 100ms–500ms eventual consistency

CQRS is an architectural investment. While it eliminates performance bottlenecks and disentangles write-side business rules from read-side presentations, it introduces operational overhead, deployment complexity, and eventual consistency challenges. Start with simple CQS at the code level, and introduce split databases only when access patterns and scale justify the trade-off.

Top comments (0)