Simply Order (Part 9) — CQRS Pattern: Separating Reads from Writes for Better Performance

This is the ninth article in our series, where we design a simple order solution for a hypothetical company called Simply Order. The company expects high traffic and needs a resilient, scalable, and distributed order system.

In the previous articles:

• Simply Order (Part 1) Distributed Transactions in Microservices: Why 2PC Doesn't Fit and How Sagas Help
• Simply Order (Part 2) — Designing and Implementing the Saga Workflow with Temporal
• Simply Order (Part 3) — Linking It All Together: Connecting Services and Watching Temporal in Action
• Simply Order (Part 4) — Reliable Events with the Outbox Pattern (Concepts)
• Simply Order (Part 5) — Hands-On: Building the Outbox Pattern for Reliable Events
• Simply Order (Part 6) – Making APIs Idempotent: Because Users Double-Click and Networks Lie
• Simply Order (Part 7) – Querying Orders with Details: API Composition Pattern
• Simply Order (Part 8) — Building GraphQL API Service with spring-graphql

We built the core services with distributed transactions, reliable event handling, and idempotent operations. We also created a GraphQL API service to compose queries across multiple microservices.

However, we've encountered a hidden performance problem. Let's explore it.

The Problem

Imagine a typical order workflow in Simply Order:

• A customer places an order.
• The Order Service writes the order to its database.
• The Payment Service processes the payment.
• The Inventory Service reserves items.
• The GraphQL API queries multiple services to return order details to the client.

This works fine, but consider what happens during peak traffic:

• Hundreds of orders are being written simultaneously.
• Simultaneously, customers are querying their orders, shipments, and payment statuses.
• The database is now handling both heavy write operations (inserts, updates) and read operations that fetch complex joins across tables.

The result? Database contention. Reads are blocked by writes. Writes compete for locks. Queries become slow. Your system feels sluggish.

🤔 What if we could optimize writes and reads separately?

CQRS: Command Query Responsibility Segregation

CQRS is a pattern that separates the model for updating data (writes) from the model for reading data (reads). Instead of having one unified model that handles both operations, you maintain two separate models optimized for their specific purpose.

Core Idea

• Commands: Operations that modify data (create, update, delete). They write to an optimized write model.
• Queries: Operations that fetch data. They read from an optimized read model.

The two models are kept in sync through events — typically the same events we are already publishing via the Outbox Pattern.

Benefits

• Independent Scalability: Scale writes and reads independently. Add more read replicas without affecting write throughput.
• Performance Optimization: The write database can be optimized for transactions and consistency. The read database can be optimized for queries and reporting.
• Simplified Queries: Instead of complex joins and aggregations, read models store denormalized, query-optimized data.
• Event Sourcing Ready: CQRS pairs naturally with event sourcing for complete audit trails.

CQRS Approaches

There are different ways to implement CQRS, ranging from simple to complex:

1. Separate Repositories (Simple CQRS)

In this lightweight approach, you maintain two separate repositories within the same service:

• Write Repository: Optimized for transactions and consistency. Used during command execution.
• Read Repository: Optimized for queries. Used during read operations.

Both repositories can use the same database or different databases — the key is logical separation.

Pros:

• Simple to implement.
• No additional infrastructure required.
• Great starting point for CQRS.
• Perfect for services that don't have extreme read/write asymmetry.

Cons:

• Limited separation of concerns.
• Both models live in the same process.
• Scaling reads independently is harder without external infrastructure.

2. Different Data Sources (Complex CQRS)

In this approach, the write and read models use completely different databases or data sources:

• Write Side: Transactional database (e.g., PostgreSQL) optimized for ACID compliance.
• Read Side: Search or analytics database (e.g., Elasticsearch, DynamoDB, or a data warehouse).

The read model is populated asynchronously through events published by the write side.

Pros:

• Complete separation of concerns.
• True independent scalability.
• Write model can focus on transactions; read model can focus on performance.
• Read replicas can be geo-distributed.

Cons:

• Increased complexity.
• Eventual consistency — reads might be slightly stale.
• Requires event infrastructure to keep models in sync.
• Operational overhead for multiple databases.

Implementation: Our Approach

In Simply Order, we've implemented a simple CQRS approach using separate logical repositories with same model:

The code for this project can be found in this repository:

https://github.com/hassan314159/simply-order

Since this repository is continuously updated, the code specific to this lesson can be found in the implment_cqrs_for_order_and_inventory branch. Start with:

git checkout implment_cqrs_for_order_and_inventory

We divided the Order Service into two distinct repositories:

Write Repository (Command Side)

public interface OrderRepository extends JpaRepository<OrderEntity, UUID> {
}

This repository handles:

• Creating new orders
• Updating order status during Saga workflow
• Committing changes to the transactional database

Read Repository (Query Side)

public interface OrderSearchRepository extends JpaRepository<OrderEntity, UUID> {

    @Query("""
        select distinct o from OrderEntity o
        left join o.items i
        where (:customerId is null or o.customerId = :customerId)
          and (:status     is null or o.status = :status)
          and (:fromDate   is null or o.createdAt >= :fromDate)
          and (:toDate     is null or o.createdAt < :toDate)
          and (:sku        is null or i.sku = :sku)
        order by o.createdAt desc
        """)
    List<OrderEntity> search(
            @Param("customerId") String customerId,
            @Param("status") String status,
            @Param("fromDate") OffsetDateTime fromDate,
            @Param("toDate") OffsetDateTime toDate,
            @Param("sku") String sku
    );
}

This repository handles:

• Fetching orders for a customer
• Searching orders by status
• Filtering by date ranges
• All read operations

Service Layer

we have split the service layer logically into two

• OrderCommandService
• OrderQueryService

OrderCommandService.java

@Service
public class OrderCommandService {
    @Transactional
    public UUID createOrder(CreateOrderRequest request) throws JsonProcessingException {

        UUID orderId = UUID.randomUUID();
        CreateOrderCommand cmd = CreateOrderCommand.from(orderId, request);
        OrderEntity order = OrderEntity.create(cmd);
        orderRepo.save(order);

        String payload = objectMapper.writeValueAsString(order);
        outboxRepo.save(OutboxEntity.pending("OrderCreated", orderId, payload));

        return orderId;

    }
}

OrderQueryService.java

@Service
public class OrderQueryService {

    private final OrderSearchRepository orderSearchRepository;
    public final OrderMapper orderMapper;

    public OrderQueryService(OrderSearchRepository orderSearchRepository, OrderMapper orderMapper){
        this.orderSearchRepository = orderSearchRepository;
        this.orderMapper = orderMapper;
    }

    public Optional<OrderResponse> findByOrderId(UUID orderId) {
        return orderSearchRepository.findById(orderId).map(orderMapper::toDto);
    }

    public List<OrderResponse> search(String customerId, String status, OffsetDateTime fromDate, OffsetDateTime toDate, String sku) {
        return orderSearchRepository.search(customerId, status, fromDate, toDate, sku)
                .stream()
                .map(orderMapper::toDto)
                .toList();
    }

}

Same have been applied to inventory repository

Homework

Our current implementation uses separate repositories logically but still using same tables and database. The next evolution would be to:

Evolution Path

Starting Simple: Separate the Model

By separating the model — i.e., having separate tables and entities for Search, so we can gain multiple benefits:

• Easier to optimize each side independently.
• Better testing — you can test write and read logic separately.

Keeping Models in Sync: When an order status changes (via the Saga workflow), we can simply update both repositories within updateOrderStatus(). We can also isolate the updates of the two repositories by sending an event or using event outbox pattern and create a relay that updates OrderStatus in OrderSearchRepository

Evolving to Separate Data Sources

As your system grows, you might migrate to completely separate databases:

1. Extract the read model to a dedicated read database (e.g., PostgreSQL read replica or Elasticsearch).
2. Publish order events to Kafka when orders are created or updated.
3. Build a read service that consumes these events and populates the read database.

This would be a full-fledged CQRS implementation with separate data sources. Give it a try — or consider the trade-offs and decide if your system really needs that level of separation.

This gives you:

• True independent scaling.
• Ability to optimize read databases for your specific query patterns.
• Geo-distributed read replicas.

But it also introduces complexity — you'll need to handle eventual consistency and synchronization failures.

Wrap-up

In this lesson, we introduced the CQRS pattern and showed that it doesn't have to be complex. By simply separating our repositories into write and read models, we've already gained the benefits of CQRS: independent optimization, clearer code structure, and a path forward for scaling.

CQRS pairs beautifully with the Outbox Pattern
— a pattern you've already seen in previous articles. Together, they form the backbone of scalable, reliable distributed systems.

The beauty of CQRS is that you can start simple (separate repositories) and evolve to more complex implementations (separate databases) only when you need to. Don't over-engineer from day one — understand your read/write patterns first, then evolve.