Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 65 ยท Databases at Scale

CQRS and Database per Service

Learn CQRS (command query responsibility segregation) and the database-per-service pattern: separate read and write models, sync strategies and trade-offs, with Python.

Two patterns shape how large systems organize their data. CQRS splits the model you use to change data from the model you use to read it. Database per service gives each service its own private store instead of one shared database. Both reduce coupling and let each part scale on its own terms, and both introduce the same new problem: copies of data that are not instantly in sync.

The problem: one model for everything

A single table design has to serve both writes (validate, enforce rules, keep data normalized) and reads (join five tables, sort, paginate, show a dashboard). These goals pull in opposite directions. Reads usually outnumber writes by a hundred to one, and they want denormalized, pre-joined, cached data. Writes want normalized, constrained, transactional data. Tuning for one slows the other.

CQRS in one picture

Command Query Responsibility Segregation separates the two sides:

  • Commands change state (PlaceOrder, CancelOrder). They are validated against business rules and go to the write model.
  • Queries read state (GetOrderSummary, SearchProducts). They go to a read model, which is shaped for exactly what the screen needs and can live in a different database.
flowchart LR
  UI[Client] -->|command| W[Write model<br/>normalized DB]
  W -->|events or change stream| P[Projector]
  P --> R[(Read model<br/>denormalized view)]
  UI -->|query| R

Commands hit the write store. A projector keeps a read-optimized copy up to date.

You do not have to use two databases. The simplest CQRS is two sets of classes over the same database. Full CQRS gives the read side its own store, such as a search index, a document store or a cache, and keeps it updated asynchronously. That means the read model is eventually consistent: a user who just placed an order might briefly not see it in the list.

Python A write model and a projected read model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The read side answers the dashboard with one key lookup instead of a join and a sum. The gap between the two printed lines is the staleness window, which in a real system is usually milliseconds to seconds.

When CQRS helps, when it hurts

Use it when:

  • reads and writes have very different load or shape (a catalog read a million times, edited rarely),
  • you need several read views of the same data (list, search, analytics),
  • the domain is complex and the write side deserves rich validation.

Avoid it for plain CRUD screens. It adds moving parts (projectors, lag, rebuilds), and many teams adopt it too early. CQRS pairs naturally with event sourcing, where the write side stores events and read models are built from them, but neither requires the other.

Database per service

In a microservice architecture, the rule is that each service owns its data and no other service touches its database directly. Others must ask through its API or consume its events.

flowchart LR
  O[Order service] --> OD[(Orders DB)]
  U[User service] --> UD[(Users DB)]
  I[Inventory service] --> ID[(Inventory DB)]
  O -->|API or events| U
  O -->|API or events| I

No shared tables: services interact only through APIs and events.

Benefits:

  • Loose coupling: a service can change its schema without breaking others.
  • Independent scaling and technology choice: search in a search engine, payments in SQL, sessions in Redis.
  • Fault isolation: one slow database does not drag down every service.

Costs:

  • No cross-service joins. "Orders with the customer's name" needs an API call, a replicated copy of the data or an API-composition layer.
  • No ACID transaction across services. Multi-service business transactions need a saga.
  • Duplicated data that must be kept in step through events.

Querying across services

Three common techniques:

  1. API composition: an aggregator calls each service and merges results in memory. Simple, but slow and fragile for big result sets.
  2. Local replica via events: the order service keeps a small copy of the user's name, updated when the user service publishes UserRenamed.
  3. CQRS read model: one dedicated read store consumes events from many services and serves joined views.
Python Keeping a local copy updated through events

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A "shared database" between services is the classic distributed monolith: you pay microservice operational cost and keep the coupling. If two services must read the same tables, they are probably one service.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In CQRS, which side is optimized for denormalized, pre-joined data?

    The read model is shaped for the screens that consume it, often denormalized and stored in a different database from the write model.
  2. 2. What is a main consequence of a separate read store?

    Projections run after the write, so there is a short delay before the read model reflects a change.
  3. 3. In database per service, how should the Order service get a customer's name?

    Services must not touch each other's databases. They use APIs or events, possibly keeping a local replica.
  4. 4. Which is a downside of database per service?

    Different databases cannot share one ACID transaction, so cross-service workflows need sagas and eventual consistency.

Summary

  • CQRS separates commands (writes, rules) from queries (reads, views), often with different stores.
  • The read model is built by a projector from events or change streams and is eventually consistent.
  • Use CQRS where read and write needs differ greatly; avoid it for simple CRUD.
  • Database per service gives each service private data and loose coupling.
  • The price is no cross-service joins or ACID transactions: use API composition, local replicas, read models and sagas.

Next: Message Queues