CQRS Pattern — Projection Lag and Stale Read Pitfalls

The Core Pattern

CQRS splits your system into two logical halves: the command side (write) and the query side (read). The command side receives commands—imperative instructions like 'PlaceOrder' or 'UpdateProfile'—validates business rules, writes to a normalised store, and publishes an event. The query side subscribes to those events and builds denormalised read models that serve UI or API responses without joins. This separation lets you scale read and write independently: you might have 10 read replicas and 1 write master, or use different database technologies entirely (e.g., PostgreSQL for writes, Elasticsearch for reads).

# Package: io.thecodeforge.python.system_design

# COMMAND: changes state — returns nothing or just an ID
class CreateOrderCommand:
    def __init__(self, user_id: int, items: list, total: float):
        self.user_id = user_id
        self.items = items
        self.total = total

class OrderCommandHandler:
    def handle(self, cmd: CreateOrderCommand) -> int:
        # Business logic: validate, apply rules
        if cmd.total <= 0:
            raise ValueError('Order total must be positive')

        # Write to normalised store
        order_id = orders_db.insert({
            'user_id': cmd.user_id,
            'total': cmd.total,
            'status': 'pending'
        })
        for item in cmd.items:
            order_items_db.insert({'order_id': order_id, **item})

        # Publish event for read model update
        event_bus.publish('OrderCreated', {'order_id': order_id, **vars(cmd)})
        return order_id

# QUERY: reads state — returns data, changes nothing
class GetUserOrdersQuery:
    def __init__(self, user_id: int, page: int = 1):
        self.user_id = user_id
        self.page = page

class OrderQueryHandler:
    def handle(self, query: GetUserOrdersQuery):
        # Read from DENORMALISED read model — no joins needed
        return read_db.query(
            'SELECT * FROM user_orders_view WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET ?',
            [query.user_id, (query.page - 1) * 20]
        )

Maintaining the Read Model

Read models are not kept in sync by the write side. Instead, they are built and updated by projections—event handlers that listen to events and denormalise data into query-optimised tables. In the example below, an OrderProjection class listens for OrderCreated events and upserts a row in user_orders_view that includes denormalised user info and pre-joined item names. This removes all joins from the read path, making queries extremely fast. The trade-off is eventual consistency: there is a window between the write commit and the projection update where the read model is stale.

# Read model is updated asynchronously by consuming events
class OrderProjection:
    """Keeps user_orders_view up to date by handling OrderCreated events."""

    def on_order_created(self, event):
        # Denormalise: join order + user + items into one read-optimised row
        user = users_db.get(event['user_id'])
        items = event['items']

        read_db.upsert('user_orders_view', {
            'order_id':   event['order_id'],
            'user_id':    event['user_id'],
            'user_name':  user['name'],         # denormalised
            'user_email': user['email'],         # denormalised
            'total':      event['total'],
            'item_count': len(items),            # pre-computed
            'item_names': ', '.join(i['name'] for i in items),  # pre-joined
            'status':     'pending',
            'created_at': event['timestamp']
        })

# Trade-off: read model is eventually consistent with the write model
# Between OrderCreated event and projection update, a brief window of inconsistency

When to Apply CQRS (and When to Avoid It)

CQRS adds significant complexity: you now maintain two models, an event pipeline, and deal with eventual consistency. Apply it only when the benefits clearly outweigh the cost. The sweet spot is when read and write workloads have fundamentally different performance characteristics—for example, writes are transactional with frequent updates to many related tables, while reads need to aggregate data from multiple sources and serve high traffic with low latency. Avoid CQRS for simple CRUD apps where a single normalised model can handle both tasks efficiently. Start with a monolithic model, measure, and extract read models only when you hit a measurable performance bottleneck.

CQRS and Event Sourcing: Separate Patterns That Work Well Together

CQRS and Event Sourcing are often mentioned together but are independent. CQRS separates read and write models. Event Sourcing stores all state changes as an ordered sequence of immutable events, instead of current state. They combine naturally: the event store becomes the write model, and the projections build read models from those events. However, you can absolutely use CQRS without Event Sourcing — you can implement a simple write model that updates a normalised table and emits events to update the read model. Conversely, you can use Event Sourcing without CQRS by building a single model from events for both reads and writes (though that's unusual). The key insight: CQRS is about separation of concerns, not about storage strategy.

Production Trade-offs: Consistency, Complexity, and Cost

Deploying CQRS in production introduces three core trade-offs you must design for. First, eventual consistency: your read model lags behind the write model. You must decide acceptable staleness per use case — 1 second for dashboards, near-zero for payment confirmations. Second, operational complexity: you now have two databases, an event bus, projections, and monitoring. Each component becomes a failure domain. Third, cost: storing two copies of data (write model + read model) doubles storage. Denormalised read models can be larger due to redundant data. You also pay for the event bus infrastructure. However, read query performance improvements can offset these costs by reducing need for read replicas and expensive joins.