Read models without a second database

Every introduction to CQRS arrives with the same diagram. Commands go left into a relational database. Events flow through a broker. A projector writes documents into MongoDB on the right, and queries read from there.

It is a good diagram. It is also the reason a lot of teams try CQRS once and conclude it is not worth it — because the diagram quietly doubles the number of datastores you operate, back up, monitor, secure, and reason about during an incident, and it does that on day one, before the read side has any measured problem to solve.

You can have the separation without the second database.

What the separation is actually for

Strip CQRS back and it makes one claim: the shape of data that is correct for writing is rarely the shape that is fast for reading.

A write model is normalised because normalisation is how you make invariants enforceable. An entry belongs to a competitor, a competitor belongs to a club, a club belongs to an organisation — four tables, foreign keys, and a constraint that makes an invalid state unrepresentable.

A read model is denormalised because a screen wants one row. The list view needs the competitor’s name, their club’s name, their category, their payment state and their status, and it needs them for two hundred rows without four joins each.

Nothing in that argument mentions MongoDB. The claim is about shape, and a second product is one way to get a second shape, not the only one.

The cost nobody prices in

The second datastore is not free, and the invoice arrives in operations rather than engineering.

You now have two backup regimes with two restore procedures, and two chances that one of them has never been tested. You have a consistency window that is now visible to users, and a support conversation about why a page showed stale data for four seconds. You have a projector that can fall behind, and you need to know when it has. You have a rebuild path for when projection logic changes, and it needs to work under load rather than at three in the morning on a good day.

None of that is unmanageable. All of it is work, and it is work you are taking on in exchange for a read-shape benefit you can usually get another way.

Projections as JSONB

PostgreSQL has had a real document type since 9.4. jsonb is parsed, binary, and indexable — it is not a text column with JSON inside it.

So a projection table is a normal table with a document in it:

CREATE TABLE entry_projection (
    entry_id     UUID PRIMARY KEY,
    event_id     UUID NOT NULL,
    document     JSONB NOT NULL,
    projected_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Index the paths the list view filters on, not the whole document.
CREATE INDEX entry_projection_status
    ON entry_projection ((document ->> 'status'));

CREATE INDEX entry_projection_event
    ON entry_projection (event_id);

The read side selects one row and gets the whole screen. No joins, no assembly in PHP, one shape.

And because it is the same database, the projector can write inside the same transaction as the thing that triggered it when you want that, or asynchronously when you do not. That choice is yours per projection rather than forced on you by the topology.

The projector

In Vortos an event subscriber is declared by attribute and discovered at compile time, so there is no registry file and no switch statement over topic names:

use Vortos\Messaging\Attribute\AsEventHandler;

final readonly class EntryProjector
{
    public function __construct(
        private EntryProjectionRepository $projections,
    ) {}

    #[AsEventHandler]
    public function onEntryApproved(EntryApproved $event): void
    {
        $this->projections->upsert(
            entryId: $event->entryId,
            eventId: $event->eventId,
            document: $this->render($event),
        );
    }
}

upsert is the important word. Message delivery is at-least-once, so a projector will see the same event twice sooner or later — on a redeploy, on a consumer rebalance, on a retry after a timeout that actually succeeded. The write has to be idempotent, which means INSERT ... ON CONFLICT DO UPDATE keyed on the entity, not an append that silently doubles a row.

Rebuilding

The reason to keep a projection disposable is that projection logic changes more often than domain logic does. A screen gains a column; the document needs a field it never carried.

Because the source of truth is still the normalised tables in the same database, a rebuild is a query and a loop, not a cross-datastore migration:

foreach ($this->entries->iterateAll() as $entry) {
    $this->projections->upsert(
        entryId: $entry->id(),
        eventId: $entry->eventId(),
        document: $this->render($entry),
    );
}

Run it into a new table, swap the name, drop the old one. The read side is never down, because the old table serves until the moment the new one is complete.

When you do want the second datastore

This is not an argument that document stores are pointless. It is an argument about ordering.

Reach for a separate read store when you have a measured reason: read volume that genuinely needs to scale independently of writes, a query pattern relational indexes are bad at — full-text relevance, geospatial, graph traversal — or a read side that must survive the write side being down.

Those are real. They are also things you will know you have, rather than things you should assume at the start. Until then the separation you want is logical, and one database is a simpler thing to run.

What this buys

One datastore to back up, restore, monitor, patch and secure. One consistency model to explain. Projections you can rebuild with a loop. And every actual benefit of CQRS — write-side invariants that hold, read-side shapes that are fast, and a boundary that stops one from deforming the other.

The diagram gets less impressive. The system gets easier to operate at three in the morning, which is the only time the diagram matters.