CQRS: When Reads and Writes Stop Wanting the Same Database

One model, two workloads

A commerce backend serves roughly a million product searches and ten thousand order writes a day. Those numbers are two orders of magnitude apart, and so are their requirements.

Serving both from one normalised schema means the search query joins six tables, the reporting query locks rows the checkout path needs, and every index you add for reads slows writes down.

What CQRS actually says

Command Query Responsibility Segregation: separate the model that changes state from the model that reads it. Commands validate invariants and produce state changes; queries return data and never mutate.

Three levels of separation

1. Code only. Separate command and query handlers over the same tables. Almost free, and often enough — this is where you should start.
2. Same database, different schema. Materialised views or denormalised tables maintained by triggers or jobs. Strong consistency if you refresh in-transaction.
3. Different datastores. PostgreSQL for writes, Elasticsearch for search, ClickHouse for analytics, Redis for hot reads, fed asynchronously from events.

Keeping read models in sync

A projection is a consumer that turns events into rows shaped for one query. The mechanics matter more than the concept:

• Source events from an outbox or CDC, never from a second write in application code — that is the dual-write bug again.
• Make projections idempotent. Upsert by aggregate id; delivery is at-least-once.
• Keep them rebuildable. A projection you cannot drop and replay is a liability, because every bug in it becomes permanent corruption.
• Version them. Build v2 alongside v1, switch reads, then delete v1.

Living with eventual consistency

The user places an order and lands on a list rendered from a projection that is 400ms behind. Their order is missing. They place it again. Handle this deliberately:

Also monitor projection lag as a first-class SLO. “Search is 40 seconds stale” is an incident, and without that metric nobody will notice for a day.

Implementation sketch

// COMMAND — owns invariants, writes the authoritative model
async function placeOrder(cmd: PlaceOrder) {
  return db.transaction(async (tx) => {
    const order = await tx.orders.insert({ ...cmd, status: "PENDING" });
    await tx.order_items.insertMany(cmd.items.map(i => ({ ...i, orderId: order.id })));
    await tx.outbox.insert({ type: "OrderPlaced", aggregateId: order.id, payload: order });
    return order;
  });
}

// PROJECTOR — idempotent, rebuildable, shaped for the list screen
async function onOrderPlaced(e: OrderPlaced) {
  await readDb.order_summaries.upsert({
    id: e.orderId,
    customerName: e.customer.name,       // denormalised on purpose
    itemCount: e.items.length,
    total: e.total,
    status: e.status,
    updatedAtVersion: e.version,          // guards out-of-order delivery
  });
}

// QUERY — no joins, no domain logic, one index
const listOrders = (customerId: string) =>
  readDb.order_summaries.where({ customerId }).orderBy("createdAt", "desc").limit(50);

CQRS is not event sourcing

They are frequently taught together and are entirely separable. CQRS is about having different models for reads and writes. Event sourcing is about storing the sequence of state changes as the source of truth instead of current state.

• CQRS without event sourcing: extremely common, and the sane default.
• Event sourcing without CQRS: possible, awkward, since reading from a log is painful.
• Both: powerful, and roughly triples the amount of infrastructure you must operate.

When not to use it

Skip CQRS if reads and writes have similar volume, if your queries are satisfied by an index, if the team cannot yet explain eventual consistency to product, or if the system is a CRUD admin panel. The pattern's cost is permanent; its benefit only appears at asymmetric scale.

Interview framing

“Orders stay in PostgreSQL, normalised, since that is where the invariants live. Search reads from an Elasticsearch projection fed by the order outbox, keyed by order id and idempotent so it can be rebuilt from scratch. The projection is eventually consistent, so after checkout we read the confirmation from the write model and monitor projection lag as an SLO.” Naming the lag SLO and the rebuild path is what signals production experience.