← Back to the section

When you look at a project with CQRS for the first time, it's easy to get confused: some commands, some queries, separate handlers, and sometimes even different databases. What's all this for?

The answer depends on the scale of the problem. CQRS is a spectrum of solutions, not a single specific one. On one end, it's simply different types for reads and writes. On the other, it's physically separate databases synchronized through events. There's an intermediate option between them. You need to pick the point where the benefit outweighs the complexity — and not jump straight to the far end.

What CQRS is in plain terms

Usually a service works with one data model: the same classes are used both when writing an order and when showing a customer their list of orders. This is convenient as long as the load is small.

The trouble starts when you read far more often than you write, or when the data structure for reading looks nothing like the structure for writing. Saving an order means writing an aggregate with business rules. Showing a customer their list of orders means gathering data from six tables, computing totals, and returning flat JSON.

CQRS (Command Query Responsibility Segregation) is the separation of responsibility between commands and queries. Commands change data, queries only read. They can have different classes, different handlers, different transaction settings, and even different storage.

Three levels: start simple

CQRS doesn't necessarily mean two databases. There are three levels of complexity, and for most projects the first or second is enough.

Level 1 — markers without storage separation

The simplest form: no additional infrastructure, just different types for commands and queries.

public record ConfirmOrderCommand(Long orderId, String idempotencyKey)
    implements UseCaseCommand<Order> {}

public record GetOrderSummaryQuery(Long orderId)
    implements UseCaseQuery<OrderSummary> {}

Commands and queries go to the same database through the same repositories. The only difference is in the types and transaction settings: query handlers are marked @Transactional(readOnly = true), command handlers are not.

This gives three real advantages:

  • The compiler distinguishes reads from writes. You can't accidentally pass a command where a query is expected.
  • Separate transaction settings. readOnly = true enables optimizations in PostgreSQL and protects against accidentally modifying data during a read.
  • Metrics are separated by meaning. Command and query execution time is measured separately — it's easy to see exactly what is slow.

This level costs nothing extra, and it's worth applying almost always.

Level 2 — a separate read table in the same database

When queries become heavy but it's still too early to move to another database.

A typical situation: a customer's order history page gathers data from six tables — order, line items, products, buyer, payment, delivery. Each query loads the CPU and takes hundreds of milliseconds.

The solution is a denormalized read-only table:

CREATE TABLE order_summary (
    order_id      BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    customer_name TEXT NOT NULL,
    status        TEXT NOT NULL,
    item_count    INTEGER NOT NULL,
    total_amount  NUMERIC(19,4) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL,
    updated_at    TIMESTAMPTZ NOT NULL
);
CREATE INDEX ix_order_summary_customer ON order_summary(customer_id, created_at DESC);

Now the order history query is a single SELECT on an index, with no table joins. The order_summary table is updated on every order change through events within the same service.

When this is justified:

  • queries join five or more tables;
  • you need heavy groupings and aggregations over millions of rows;
  • the data structure for display differs greatly from the structure for writing.

The database still stays single — that's important. Two separate stores mean synchronization, possible divergence, double administration. You should only reach this point when there's a real need.

Level 3 — separate storage for reads and writes

The most complex option. Justified only when the situation has been measured and one of the problems is actually present:

Reads outnumber writes by an order of magnitude. A typical example: an order is created once, but is then read dozens of times in different scenarios — history, search, analytics, notifications. At a ratio of 10:1 and above, the read load starts to dictate the architecture.

You need a fundamentally different structure for search. Full-text search over descriptions, filters across twenty parameters, ranking by relevance — this is not what PostgreSQL is built for. ElasticSearch with an inverted index will handle it an order of magnitude better.

The read load interferes with writes. PostgreSQL writes excellently, but if thousands of read queries compete with write operations, both suffer. A separate read store removes this contention.

How it looks in practice:

  • Writes go to PostgreSQL with the full order aggregate.
  • When an order changes, an event is published through the outbox.
  • A Kafka consumer updates the denormalized document in ElasticSearch.
  • POST /orders is handled by the write handler through PostgreSQL.
  • GET /orders?q=... is handled by the query handler through ElasticSearch.

This is expensive infrastructure: two stores, two monitoring setups, possible temporary data divergence, recovery procedures for failures. It pays off only when a PostgreSQL replica and a cache can no longer cope.

Common mistakes

Rolling out full CQRS with two databases at the start of a new project. This is above all uncertainty: you don't yet know what the real load patterns will be. Teams spend months maintaining ElasticSearch and investigating divergences — while the real load turns out to be 500 queries per day. Start with markers, measure, evolve.

Separating storage "because there will be a lot of reads." An assumption without measurements is not a reason. The real reason is a specific measured problem: the 95th-percentile response time has broken through the acceptable limit, PostgreSQL CPU is constantly at 80% specifically from reads, the business has added tasks that a relational database fundamentally can't handle.

Below these thresholds, PostgreSQL replication and a cache cover most cases. Storage separation is the last step, not the first.

In short

  • CQRS is a spectrum, not a single specific solution. Pick the level to match the real problem.
  • Level 1 (markers) — different types for commands and queries, readOnly = true on queries, one database. Costs nothing extra, apply it always.
  • Level 2 (read table) — a denormalized table in the same database. Justified for heavy queries with five or more joins or complex groupings.
  • Level 3 (separate storage) — PostgreSQL for writes plus ElasticSearch or Redis for reads. Only for measured problems: a read-to-write ratio of 10:1 and above, a fundamentally different structure, writes degrading because of readers.
  • Evolve from the bottom up: start with markers, add complexity when metrics show real pain.
  • Starting straight with two databases means adding synchronization, divergence, and double administration without a measured reason.
  • Command side in CQRS — how the write handler is built.
  • Query side in CQRS — the read handler and a separate repository for projections.
  • Read-model in CQRS — where to store and how to update the denormalized projection.