Skip to content
Katabench
Try free
8 min read The Katabench team

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.

What CQRS does not require

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. 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. 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. 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.

All three are CQRS. Event sourcing appears on none of the rungs because the pattern does not need it.

Rung one: separate code paths over the same tables

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:

C#
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:

C#
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.

Rung two: read tables in the same database

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:

SQL
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; -- closed

The 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.

Rung three: a separate read store

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.

What a stale read costs you

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:

C#
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: closed in the table and open in the view until the refresh.

How far up the ladder this screen should go

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

More like this: SOLID principles exercises →

Get new katas and .NET tips in your inbox

A short note when fresh kata land, plus the C# and performance tricks behind the grading. No spam, unsubscribe anytime.