StackPractices
advanced By Mathias Paulenko

Scale Read and Write Workloads with CQRS

Separate read and write models with CQRS for optimized queries, event sourcing, and independent scaling of read and write paths.

Topics: design

Overview

Most CRUD apps run everything through one data model — reads and writes share the same tables. A relational table serves SELECT queries for dashboards and INSERT/UPDATE operations for form submissions. This simplicity works for small domains but breaks down at scale. Write-optimized schemas (normalized, transactional) are slow for complex reads. Read-optimized schemas (denormalized, indexed) are expensive to update. As traffic grows, the two workloads end up fighting over the same connection pool, indexes, and I/O.

Command Query Responsibility Segregation (CQRS) splits the data model in two: a write model optimized for commands (create, update, delete) and a read model optimized for queries. Commands mutate state in the write model and publish events. Event handlers update read models — denormalized projections tailored for specific query patterns. The two models can use different databases, different schemas, and scale independently. Below you’ll find the write side, the projection side, and three ways to wire them together — from a simple dual-database setup to full event sourcing. For composing reusable query logic on the read side, see the Specification Pattern.

When to use it

Use this recipe when:

  • The gap between read and write volume is large — a 100:1 read-heavy split is common. See Database Read Replicas for read scaling.
  • Complex queries require joins across several aggregates, degrading write performance. See SQL Joins for query optimization.
  • Building real-time dashboards, analytics, or search that needs data shaped differently than the transactional model
  • Working with event-sourced systems where state is derived from a sequence of events. See Event Sourcing for event store patterns.
  • Teams need to optimize read and write schemas independently without coordination

Skip this recipe when reads and writes are symmetric and plain CRUD covers the queries — a single model is far easier to run and debug.

Solution

Command Handler with Event Publishing (TypeScript)

interface Event {
  type: string;
  aggregateId: string;
  payload: unknown;
  occurredAt: Date;
}

interface EventStore {
  append(events: Event[]): Promise<void>;
  getEvents(aggregateId: string): Promise<Event[]>;
  getEventsAfter?(aggregateId: string, fromVersion: number): Promise<Event[]>;
}

class OrderWriteModel {
  constructor(
    public id: string,
    public customerId: string,
    public items: Array<{ productId: string; quantity: number; price: number }>,
    public status: 'pending' | 'paid' | 'shipped' = 'pending'
  ) {}

  pay(paymentMethod: string): Event[] {
    if (this.status !== 'pending') throw new Error('Order already paid');
    return [{
      type: 'OrderPaid',
      aggregateId: this.id,
      payload: { paymentMethod, total: this.total() },
      occurredAt: new Date(),
    }];
  }

  total(): number {
    return this.items.reduce((sum, item) => sum + item.price * item.quantity, 0);
  }
}

class PayOrderCommand {
  constructor(
    public orderId: string,
    public paymentMethod: string
  ) {}
}

class PayOrderHandler {
  constructor(private eventStore: EventStore) {}

  async handle(command: PayOrderCommand): Promise<void> {
    const events = await this.eventStore.getEvents(command.orderId);
    const order = this.rehydrate(events);

    const newEvents = order.pay(command.paymentMethod);
    await this.eventStore.append(newEvents);
  }

  private rehydrate(events: Event[]): OrderWriteModel {
    if (events.length === 0) throw new Error(`No events found for order`);
    const order = new OrderWriteModel(events[0].aggregateId, '', []);
    for (const event of events) {
      if (event.type === 'OrderCreated') {
        const p = event.payload as {
          customerId: string;
          items: OrderWriteModel['items'];
        };
        order.customerId = p.customerId;
        order.items = p.items;
      } else if (event.type === 'OrderPaid') {
        order.status = 'paid';
      }
    }
    return order;
  }
}

Read Model Projection (SQL)

-- Denormalized read model for order summaries
CREATE TABLE order_summaries (
  order_id UUID PRIMARY KEY,
  customer_name VARCHAR(255),
  total_amount DECIMAL(10,2),
  item_count INT,
  status VARCHAR(20),
  last_updated TIMESTAMP
);

-- Projection handler updates read model when events occur
CREATE OR REPLACE FUNCTION project_order_paid()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE order_summaries
  SET status = 'paid', last_updated = NEW.occurred_at
  WHERE order_id = NEW.aggregate_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Event Handler Updating Read Model (Node.js)

class OrderProjection {
  constructor(readDb, elasticsearch) {
    this.readDb = readDb;
    this.elasticsearch = elasticsearch;
  }

  async handleOrderPaid(event) {
    // Update relational read model
    await this.readDb.query(
      'UPDATE order_summaries SET status = $1, last_updated = $2 WHERE order_id = $3',
      ['paid', event.occurredAt, event.aggregateId]
    );

    // Update search index
    await this.elasticsearch.update({
      index: 'orders',
      id: event.aggregateId,
      doc: { status: 'paid', paidAt: event.occurredAt }
    });
  }

  async handleOrderCreated(event) {
    await this.readDb.query(
      `INSERT INTO order_summaries (order_id, customer_name, total_amount, item_count, status, last_updated)
       VALUES ($1, $2, $3, $4, $5, $6)`,
      [event.aggregateId, event.payload.customerName, event.payload.total,
       event.payload.items.length, 'pending', event.occurredAt]
    );
  }
}

Explanation

  • Command model: owns writes and state changes. Each command is validated against invariants, mutates the write model, and produces domain events. The write model is normalized and transactional — it enforces business rules at the cost of query complexity.
  • Event sourcing: instead of storing current state, store the sequence of events that led to it. The write model appends events to an event store. Replaying the events rebuilds the state. This gives you a complete audit history and temporal querying.
  • Read model (projection): a denormalized, query-optimized view built from events. A customer_orders read model might flatten order items, customer names, and shipping status into a single table with appropriate indexes. Projections are built and refreshed in the background.
  • Eventual consistency: a completed command doesn’t update the read model right away. There’s a brief window (milliseconds to seconds) where the write model reflects the change but the read model doesn’t. This is eventual consistency — acceptable for most read-heavy systems.
CQRS flow: a command updates the write model, publishes events that a projection consumes to refresh the read model the client queries

Variants

ApproachWrite modelRead modelConsistencyBest for
Single DB, separate viewsRelationalMaterialized viewsStrongSimple CQRS
Dual DBRelationalDocument/SearchEventualHigh read scale
Event sourcingEvent storeMultiple projectionsEventualAudit, temporal queries
Read replicas (not strict CQRS)Primary DBReplica DBNear-strongRead scaling without code changes

What Works

  • Keep read models simple and disposable: a read model is a cache, not a source of truth. If one corrupts, replay the event stream and rebuild it. Don’t put business logic or write operations in read models.
  • Version your events: as schemas evolve, older projections must still understand historical events. Include a version field in events and write handlers for each version. So migrations can roll out gradually with no downtime.
  • Make projections idempotent: brokers deliver events at least once, so a handler can see the same event twice. Design projections so that processing the same event twice produces the same result. Use UPSERT instead of INSERT.
  • Monitor projection lag: the delay between write and read model updates must be bounded. Alert if projection lag exceeds your SLA (e.g., 5 seconds). Slow projections indicate backpressure or inefficient event handlers.
  • Start simple, evolve to CQRS: don’t build CQRS from day one on a greenfield project. Run on a single model first. When read complexity or performance becomes a problem, extract a read model. Premature CQRS adds unnecessary complexity.

Common mistakes

  • CQRS for its own sake: with simple CRUD and balanced read/write ratios, the split adds complexity and buys nothing. Use it when read/write asymmetry or query complexity justifies the split.
  • Putting business rules in read models: read models are for querying. If you find yourself validating or mutating state in a projection, you’ve violated the separation. Business rules belong in command handlers.
  • Ignoring eventual consistency in UX: users may submit a form and immediately refresh, seeing stale data. Design the UI to handle this — show a success message, optimistically update, or redirect to a confirmation page rather than immediately querying the read model.
  • Replaying events from the beginning on every deploy: in development, it’s tempting to wipe the read model and rebuild from scratch. In production, where a busy event store can hold billions of events, a full replay takes days. Implement snapshotting — periodically save aggregate state so replays start from the snapshot, not event 1.

Advanced Examples

Python Implementation with Event Sourcing

from dataclasses import dataclass, field
from datetime import datetime, timezone
from typing import List, Any
from abc import ABC, abstractmethod

@dataclass
class Event:
    type: str
    aggregate_id: str
    payload: Any
    occurred_at: datetime = field(
        default_factory=lambda: datetime.now(timezone.utc)
    )

class EventStore(ABC):
    @abstractmethod
    async def append(self, events: List[Event]) -> None:
        ...

    @abstractmethod
    async def get_events(self, aggregate_id: str) -> List[Event]:
        ...

@dataclass
class OrderItem:
    product_id: str
    quantity: int
    price: float

class OrderWriteModel:
    def __init__(self, order_id: str, customer_id: str, items: List[OrderItem]):
        self.id = order_id
        self.customer_id = customer_id
        self.items = items
        self.status = 'pending'

    def pay(self, payment_method: str) -> List[Event]:
        if self.status != 'pending':
            raise ValueError('Order already paid')
        self.status = 'paid'
        return [Event(
            type='OrderPaid',
            aggregate_id=self.id,
            payload={'payment_method': payment_method, 'total': self.total()}
        )]

    def total(self) -> float:
        return sum(item.price * item.quantity for item in self.items)

class PayOrderHandler:
    def __init__(self, event_store: EventStore):
        self._event_store = event_store

    async def handle(self, order_id: str, payment_method: str) -> None:
        events = await self._event_store.get_events(order_id)
        order = self._rehydrate(events)
        new_events = order.pay(payment_method)
        await self._event_store.append(new_events)

    def _rehydrate(self, events: List[Event]) -> OrderWriteModel:
        if not events:
            raise ValueError('No events found for order')
        order = OrderWriteModel(events[0].aggregate_id, '', [])
        for event in events:
            if event.type == 'OrderCreated':
                order.customer_id = event.payload['customer_id']
                order.items = [OrderItem(**i) for i in event.payload['items']]
            elif event.type == 'OrderPaid':
                order.status = 'paid'
        return order

Snapshotting for Large Event Streams

interface Snapshot {
  aggregateId: string;
  version: number;
  state: unknown;
}

class SnapshotStore {
  async save(snapshot: Snapshot): Promise<void> {
    // Persist snapshot to database
  }

  async load(aggregateId: string): Promise<Snapshot | null> {
    // Load latest snapshot
    return null;
  }
}

class PayOrderHandlerWithSnapshots {
  constructor(
    private eventStore: EventStore,
    private snapshots: SnapshotStore
  ) {}

  async handle(command: PayOrderCommand): Promise<void> {
    const snapshot = await this.snapshots.load(command.orderId);
    let order: OrderWriteModel;
    let fromVersion = 0;

    if (snapshot) {
      order = snapshot.state as OrderWriteModel;
      fromVersion = snapshot.version;
    } else {
      const events = await this.eventStore.getEvents(command.orderId);
      order = this.rehydrate(events);
      fromVersion = events.length;
    }

    // Apply only events after snapshot
    const recentEvents = await this.eventStore.getEventsAfter(
      command.orderId, fromVersion
    );
    for (const event of recentEvents) {
      this.apply(order, event);
    }

    const newEvents = order.pay(command.paymentMethod);
    await this.eventStore.append(newEvents);

    // Save snapshot every 100 events
    if (fromVersion + recentEvents.length + newEvents.length >= 100) {
      await this.snapshots.save({
        aggregateId: command.orderId,
        version: fromVersion + recentEvents.length + newEvents.length,
        state: order,
      });
    }
  }

  private apply(order: OrderWriteModel, event: Event): void {
    // Apply event to order
  }

  private rehydrate(events: Event[]): OrderWriteModel {
    if (events.length === 0) throw new Error('No events found for order');
    return new OrderWriteModel(events[0].aggregateId, '', []);
  }
}

Separate Read Database with Redis

interface OrderSummary {
  orderId: string;
  customerName: string;
  totalAmount: number;
  itemCount: number;
  status: string;
}

interface OrderCreatedPayload {
  customerName: string;
  total: number;
  items: Array<{ productId: string; quantity: number; price: number }>;
}

interface RedisClient {
  hgetall(key: string): Promise<Record<string, string>>;
  hset(key: string, fields: Record<string, string>): Promise<number>;
}

class OrderReadModelRedis {
  constructor(private redis: RedisClient) {}

  async getOrderSummary(orderId: string): Promise<OrderSummary | null> {
    const data = await this.redis.hgetall(`order:${orderId}:summary`);
    if (!data || Object.keys(data).length === 0) return null;

    return {
      orderId,
      customerName: data.customerName,
      totalAmount: parseFloat(data.totalAmount),
      itemCount: parseInt(data.itemCount, 10),
      status: data.status,
    };
  }

  async handleOrderCreated(event: Event): Promise<void> {
    const payload = event.payload as OrderCreatedPayload;
    await this.redis.hset(`order:${event.aggregateId}:summary`, {
      customerName: payload.customerName,
      totalAmount: payload.total.toString(),
      itemCount: payload.items.length.toString(),
      status: 'pending',
    });
  }

  async handleOrderPaid(event: Event): Promise<void> {
    await this.redis.hset(`order:${event.aggregateId}:summary`, {
      status: 'paid',
    });
  }
}

Further Reading

Frequently Asked Questions

Is CQRS the same as event sourcing?

No. CQRS separates reads and writes. Event sourcing stores state as events. They're often used together because event sourcing naturally produces events that can populate read models. But you can use CQRS without event sourcing (e.g., dual databases) and event sourcing without CQRS (a single model rebuilt from events).

How do I handle queries that need real-time data?

For truly real-time requirements (sub-second), query the write model directly. Accept the performance cost for the small subset of queries that need perfect freshness. Most dashboards and lists can tolerate eventual consistency.

What happens if a projection fails?

Failed projections shouldn't block the write path. Use a dead-letter queue for failed events. Fix the projection handler and replay from the failure point. The write side keeps taking commands the whole time.

Can I use CQRS with a relational database?

Yes. The write model can be a normalized relational schema. The read model can be a separate schema with denormalized views, or a different technology entirely (Elasticsearch, Redis, ClickHouse). Match the storage to what each query needs.

What's the difference between CQRS and read replicas?

Read replicas copy the same data to another server — same schema, same queries. CQRS changes the data model itself: the read side stores denormalized projections shaped for each query, which replicas can't give you. Replicas fix read volume; CQRS also fixes read shape.

How do I keep read models in sync after a schema change?

Version the events and add an upcaster that migrates old payloads on read. For breaking changes, spin up a new projection version, replay events into it, then swap traffic once it catches up — never edit a live projection in place.