CQRS pattern from one database up, and what each step costs
The CQRS pattern can start in one database: a demo queue page went from 2,000 hydrated comment rows to 50 integers before any second store existed.
One page of a support queue shows 50 tickets, with a subject, an assignee and a comment count on each.
In a demo database seeded with 40 comments per ticket, loading that page through the Ticket aggregate
pulls 2,000 comment rows to produce 50 integers. The aggregate loads its comments because its rules need
them, and it is the only code path to a ticket. Over time the class also collects display-only properties
such as CommentCount and LastReplyPreview, and the rules become harder to find among them.
Martin Fowler's short definition of CQRS is a different model for updating information than the one used for reading it. Commands go through a model that protects invariants. Queries go through a model shaped for a screen. Nothing in that sentence mentions a second database.
It helps to separate this from its older cousin. Command-query separation,
from Bertrand Meyer, is a rule about methods: a query returns a result and leaves observable state
alone. A GetUnreadCount() that marks notifications as read breaks it, because calling it twice gives
two answers. CQRS applies the same split one level up, to whole models. You want both, since a read
model whose queries mutate state has lost the property that made it safe to call freely. One
design exercise drills this on a queue whose preview must not
consume work.
Much of the fear around this pattern comes from things that travel with it in conference talks. Event sourcing is a separate decision about storing state as a history of events. Fowler notes that the two fit well together, and Microsoft's CQRS guidance describes combining them as an optional variant that adds complexity. The same guidance states that messaging is not a requirement, and it presents one shared data store as the foundational form of the pattern. You also do not need a mediator library or separate teams. A query class that returns a record is enough to count.
That gives you a ladder, and each rung has its own cost.
Climb only when the rung below stops being enough
-
1. Separate code paths, same tables
Query handlers select straight into flat records and skip the aggregate.
No staleness. Reads still compete with writes on the same tables.
-
2. Read tables in the same database
A projection table or materialized view is shaped for one screen.
Same-transaction projection: no staleness, slower writes. Refreshed view: stale until the next refresh.
-
3. Separate read store
Events or change capture feed a replica, search index or document store.
Always some lag, plus an outbox, idempotent consumers and a rebuild procedure.
The write side keeps the aggregate and its rules. This version closes a ticket, and it refuses to do so for anyone but the assignee:
namespace Support.Tickets;
public sealed record CloseTicket(long TicketId, string ClosedBy, int ExpectedVersion);
public sealed class Ticket(long id, string status, string? assignee, int version)
{
public long Id { get; } = id;
public string Status { get; private set; } = status;
public string? Assignee { get; } = assignee;
public int Version { get; private set; } = version;
public void Close(string closedBy)
{
if (Status == "closed") throw new InvalidOperationException("Already closed.");
if (Assignee != closedBy) throw new InvalidOperationException("Only the assignee can close.");
Status = "closed";
Version++;
}
}The read side never touches that class. It selects what the screen needs into a flat record, and the comment count becomes an aggregate function in SQL instead of 40 hydrated objects per ticket:
namespace Support.Queue; // No using Support.Tickets.
public sealed record QueueItem(long Id, string Subject, string? Assignee, long CommentCount);
public sealed class QueueQuery(NpgsqlDataSource db)
{
private const string Sql = """
SELECT t.id, t.subject, t.assignee, count(c.id)
FROM tickets t
LEFT JOIN ticket_comments c ON c.ticket_id = t.id
WHERE t.status = 'open'
GROUP BY t.id
ORDER BY t.id
LIMIT $1
""";
public async Task<IReadOnlyList<QueueItem>> OpenAsync(int limit, CancellationToken ct)
{
await using var command = db.CreateCommand(Sql);
command.Parameters.Add(new() { Value = limit });
await using var reader = await command.ExecuteReaderAsync(ct);
var items = new List<QueueItem>();
while (await reader.ReadAsync(ct))
{
items.Add(new QueueItem(
reader.GetInt64(0),
reader.GetString(1),
reader.IsDBNull(2) ? null : reader.GetString(2),
reader.GetInt64(3)));
}
return items;
}
}There is no staleness here, because both sides read the same committed rows. The whole gain is that
the aggregate can shed display properties and the query can change freely. Guard the split, because
it erodes easily. Once QueueQuery takes a Ticket parameter or the query namespace imports the
aggregate, every refactor of the write model breaks a screen again. A
fitness function can fail the build on that import, and the same
rule decides Split the Read From the Write, where a read slice has to
stop naming a write-side aggregate.
Many systems should stop here. If the query is fast with a good index, moving it elsewhere only adds failure modes.
When the query itself becomes the problem, because it joins too much or competes with writes, store a shape prepared in advance. A materialized view is the smallest version, and it is the first rung where reads can be wrong. This ran against PostgreSQL 17 with the same demo data:
CREATE MATERIALIZED VIEW ticket_queue AS
SELECT t.id, t.subject, t.status, t.assignee, count(c.id) AS comment_count
FROM tickets t
LEFT JOIN ticket_comments c ON c.ticket_id = t.id
GROUP BY t.id;
CREATE UNIQUE INDEX ON ticket_queue (id);
UPDATE tickets SET status = 'closed', version = version + 1 WHERE id = 1;
SELECT id, status FROM tickets WHERE id = 1; -- closed
SELECT id, status FROM ticket_queue WHERE id = 1; -- open
REFRESH MATERIALIZED VIEW CONCURRENTLY ticket_queue;
SELECT id, status FROM ticket_queue WHERE id = 1; -- closedThe unique index is there because PostgreSQL requires one for a concurrent refresh, which keeps the view readable while it rebuilds. Refresh frequency is now a product decision: how old may this screen be?
The other option at this rung is a projection table updated by the command, in the command's transaction. Reads stay consistent, and in exchange every write path has to remember the projection and pays for updating it. Choose that when a screen cannot tolerate lag and the extra write cost is small.
A search index, a document store or read replicas earn their place when reads need a different engine or need to scale apart from writes. Now the two stores must be synchronized, and you usually cannot enlist a broker and a database in one transaction. Microsoft's guidance points to the transactional outbox for publishing the change and an idempotent consumer for applying it. Change data capture is the other common feed. Plain replicas have their own version of the same lag, covered in replication and failover.
You also take on a new operational job: being able to rebuild the read store from scratch, and watching how far behind it is. Measure the age of the oldest unapplied change. A projection stuck on one bad message can serve stale data while every health check stays green.
Staleness is abstract until you trace it through a screen.
Users do not see their own write. An agent closes a ticket, the queue reloads, and the ticket is still there. They click close again and get "Already closed." The simplest fix is to have the command return the new state or version and let the client update what it already holds. A sturdier fix passes that version to the query, which compares it with the version the projection has reached and either waits briefly or answers from the write side. Returning a version from a command does break a strict reading of command-query separation, and it is a good trade.
Decisions get made on old data. If a command trusts what the read model showed, it can assign a ticket closed a minute ago. Commands must validate against the write model, and they should carry the version the user saw, so a changed row fails instead of being overwritten:
await using var update = new NpgsqlCommand(
"UPDATE tickets SET status = $1, version = $2 WHERE id = $3 AND version = $4",
connection)
{
Parameters =
{
new() { Value = ticket.Status },
new() { Value = ticket.Version },
new() { Value = ticket.Id },
new() { Value = command.ExpectedVersion },
},
};
var changed = await update.ExecuteNonQueryAsync(ct);
if (changed == 0) throw new InvalidOperationException("Ticket changed since it was read.");Sending a stale ExpectedVersion of 9 against the demo data produced that exception. The
optimistic concurrency guide covers the same check through EF
Core.
Numbers disagree across screens. A dashboard fed by a projection and a detail page fed by the write side can show different totals at the same moment. Label the data's age where people compare figures, and decide which source wins for anything that gets exported or billed.
In the demo, rung one had no stale reads. The first appeared at rung two:
closedin the table andopenin the view until the refresh.
Fowler says that for most systems CQRS "adds risky complexity," and he recommends scoping it to specific bounded contexts. Microsoft lists simple domains and plain CRUD screens as poor fits. A settings page in the same application can stay ordinary CRUD while the ticket queue uses a projection.
Decide per screen. If the aggregate is bloating with display fields, take rung one, which costs almost nothing. If that query cannot be made fast enough, try rung two and write down the staleness the screen can accept. Move to rung three when you can name the engine or scaling requirement that the primary database cannot meet.
Practice what you just read
- Open in the editor: A Preview Must Not Consume Work
Design principles Easy Free, no account needed
A Preview Must Not Consume Work
Give queue observers a read-only capability and keep consumption behind an explicit command; repeated previews must leave the next item unchanged.
- Open the Pro kata: Split the Read From the Write
Architecture Medium Katabench Pro
Split the Read From the Write
The history query formats its lines straight off the write-side aggregate, welding the two halves of a CQRS split together. A published snapshot already exists and sits unused; moving the aggregate into the contracts leaf instead trips the role rule.
More like this: SOLID principles exercises →