CQRS Explained: Separate Reads and Writes Only When It Pays

Heavy searches and the few bookings that earn money compete for the same database. When searches spike, bookings slow down or time out, even though they are far fewer.

Picture a common setup. A doctor appointment app lets patients search by area, speciality, fee and language, then book a 15-minute slot. The setup is made up, but the problem is real.

The app has one PostgreSQL database. A doctor search joins five tables: doctors, clinics, specialities, ratings and slots. It then works out each doctor's next free slot, and every search repeats that work from scratch.

On weekday evenings, between 6 and 9, search gets about 1,500 requests a second. Bookings are about 25 a second (all numbers in this setup are made up). One Monday evening, the product manager brings three complaints.

• Search takes about 2.4 seconds at peak, and many patients leave before the list appears.
• Around 60 bookings timed out, because they waited for a database connection behind slow searches.
• The team wants a "sort by distance" filter, but the search query is already 80 lines long and nobody wants to touch it.

The fight is over shared resources, not row locks. In PostgreSQL, plain reads never block writes, so searches do not lock booking rows. They use up CPU, disk and the pool of database connections that bookings also need.

A bigger database server buys a few months. It does not fix the real problem: one set of tables is trying to serve two very different jobs.

CQRS, short for Command Query Responsibility Segregation, is a pattern where one model handles changes and a separate model handles reads. Each side is shaped for its own job.

Three words carry the idea. Each one gets a plain meaning and an example from the booking app.

• Model: the way your code and tables describe the data. The booking side sees a slot as a row with a status, a doctor and a version number.
• Write model: where every change happens and every rule is checked. "One slot, one patient" is enforced here, inside a database transaction (a group of changes that all succeed or all fail).
• Read model: a copy of the data shaped for one screen. Search keeps one ready-made document per doctor, with name, area, fee, rating and next free slot, so no joins run at search time.

Think of a local cable TV operator. At the office he keeps a register of connections and complaints. Every new connection and pack change goes in there, carefully checked.

Homes never read that register. Each home gets a printed channel guide, arranged by time and language, the way a viewer wants it. The register is the write model, and the channel guide is the read model.

Where the picture breaks: a printed guide is reprinted once a month, while a read model is usually updated within seconds of each change. The idea of a late copy carries over; the size of the delay does not.

Martin Fowler, who first heard the pattern described by Greg Young, puts it the same way. You split one conceptual model into separate models for update and display. He also warns that most systems should not do this.

A command changes data, and a query returns data without changing anything. CQRS takes that small rule and applies it to whole parts of a system.

The rule is older than CQRS. Bertrand Meyer named it Command Query Separation, or CQS, in his book Object-Oriented Software Construction. It was a rule about methods inside one object.

• Query: returns a result and has no side effects you can observe. Example: find_doctors(speciality="skin", max_fee=500).
• Command: changes state and, in strict CQS, returns nothing. Example: book_slot(patient_id, slot_id), which marks a slot as booked.
• Side effect: any change a call leaves behind, such as a new row, a sent SMS or a counter that moved.

The rule bends sometimes. Fowler points out that popping an item off a stack both returns a value and changes the stack, and he accepts that. This guide's booking command also bends it: it returns a short confirmation.

CQS keeps two kinds of method apart inside one model. CQRS goes one step further and gives each side its own model, its own tables and sometimes its own store.

That helps because the two sides want different things. Commands care about rules: is the slot still open, and does the clinic allow online booking? Queries care about speed and shape: 20 doctors, sorted, in 100 milliseconds.

Commands write to the normal tables and record each change as a small message. A background worker uses those messages to update the read model, and queries read only the read model.

The team applies CQRS only to doctor search. Login, payments and the doctors' admin screens stay as they are. In the diagram, follow the booking path first, then notice that the search API touches only the search index.

An event is a short record of something that already happened, such as SlotBooked. The outbox is a table of events written in the same commit as the change. A relay, here a tool called Debezium, copies outbox rows to a topic, a named stream of messages. A projector is the worker that reads events and updates the read model.

The naive booking check and why it fails

The first version most people write reads the slot, checks that it is open, then updates it. It looks correct, and it is wrong.

Pause and predict: two patients run that check at the same moment. What happens? Both read "open" before either writes, so both bookings succeed.

We ran exactly this on PostgreSQL 16.15, with a half-second gap between the check and the write. Both patients got the slot, and the outbox held two SlotBooked events for one slot.

The command side in Python

Here is the booking command that fixes it. It uses psycopg 3, the PostgreSQL driver for Python, and SlotTaken is the error raised when someone got there first.

import json


class SlotTaken(Exception):
    pass


def book_slot(conn, patient_id, slot_id):
    with conn.transaction():
        doctor_id, status, version = conn.execute(
            "SELECT doctor_id, status, version FROM slots"
            " WHERE id = %s FOR UPDATE", (slot_id,)).fetchone()
        if status != "open":
            raise SlotTaken(slot_id)
        conn.execute(
            "UPDATE slots SET status = 'booked', patient_id = %s,"
            " version = version + 1 WHERE id = %s", (patient_id, slot_id))
        event = {"type": "SlotBooked", "slot_id": slot_id,
                 "doctor_id": doctor_id, "version": version + 1}
        conn.execute(
            "INSERT INTO outbox (id, aggregatetype, aggregateid, type, payload)"
            " VALUES (gen_random_uuid(), 'doctor', %s, 'SlotBooked', %s)",
            (doctor_id, json.dumps(event)))
    return {"slot_id": slot_id, "status": "booked", "version": version + 1}

The with conn.transaction() block commits when it ends, and rolls everything back if an error escapes it. FOR UPDATE locks the slot row until the transaction ends. A second booking for the same slot waits at that line, then reads the row again after the first one commits. It sees "booked" and raises SlotTaken.

That re-read is the behaviour of Read Committed, PostgreSQL's default isolation level (the setting for how much a transaction sees of other running transactions). Repeatable Read is a stricter level where each transaction works from one snapshot of the data. There, the waiting booking fails with "could not serialize access due to concurrent update" instead, and your code must retry it.

The outbox insert is the line that matters most for CQRS. The event is saved in the same commit as the booking. Either both are saved or neither is, so no booking is ever saved without its event.

The outbox columns are not random. They are the default names that Debezium's outbox router expects: id, aggregatetype, aggregateid, type and payload. The command sets the aggregate, the main record an event belongs to, to the doctor, so all of one doctor's events share a key.

You can check this yourself. Save the command as book_slot.py, install the driver with pip install "psycopg[binary]", and run the script below against a scratch database, because it drops and recreates two tables. Ten patients try to book one slot at the same moment.

import threading
import psycopg
from book_slot import book_slot, SlotTaken

DSN = "dbname=cqrs_test"  # point this at a scratch database
SLOT = "2026-11-03 18:30 D417"
with psycopg.connect(DSN, autocommit=True) as conn:
    conn.execute("DROP TABLE IF EXISTS slots, outbox")
    conn.execute("CREATE TABLE slots (id text PRIMARY KEY, doctor_id text,"
                 " status text DEFAULT 'open', patient_id text, version int DEFAULT 1)")
    conn.execute("CREATE TABLE outbox (id uuid PRIMARY KEY, aggregatetype text,"
                 " aggregateid text, type text, payload jsonb)")
    conn.execute("INSERT INTO slots (id, doctor_id) VALUES (%s, 'D417')", (SLOT,))

results, start = [], threading.Barrier(10)


def patient(n):
    with psycopg.connect(DSN, autocommit=True) as conn:
        start.wait()
        try:
            book_slot(conn, f"P{n}", SLOT)
            results.append("booked")
        except SlotTaken:
            results.append("taken")


threads = [threading.Thread(target=patient, args=(n,)) for n in range(10)]
for t in threads:
    t.start()
for t in threads:
    t.join()
with psycopg.connect(DSN) as conn:
    rows = conn.execute("SELECT count(*) FROM outbox").fetchone()[0]
    version = conn.execute("SELECT version FROM slots").fetchone()[0]
print("booked:", results.count("booked"), "taken:", results.count("taken"),
      "outbox rows:", rows, "version:", version)

Expect one line: booked: 1 taken: 9 outbox rows: 1 version: 2. We got exactly that in 10 of 10 runs, with psycopg 3.3.6 on PostgreSQL 16.15.

How does the event leave the database?

An outbox relay copies new outbox rows to Kafka, a system that stores ordered streams of messages. The team uses change data capture (CDC), which reads committed changes from the database's log instead of polling the table.

Debezium is an open source CDC tool, and its PostgreSQL connector uses logical decoding, a PostgreSQL feature that turns the log into a stream of committed row changes. As of 19 October 2026, its latest stable docs are for version 3.7, and they describe a ready-made outbox event router.

• Message key: by default, the router uses the aggregateid column as the Kafka message key. The command fills it with the doctor id.
• Topic: by default, the topic name is outbox.event. followed by the aggregatetype value, so these events go to outbox.event.doctor.
• Deletes: the router expects only inserts and filters out deletes on the outbox table, so a cleanup job can remove old rows.

Keying by doctor id gives you order. Kafka writes events with the same key to the same partition, a single ordered log inside a topic. Every consumer, a program reading the topic, gets a partition's events in the order they were written.

That order has one catch. Kafka's operations docs warn that adding partitions to a topic can send the same key to a different partition, which can break ordering for existing keys. Decide the partition count up front.

The read side in Python

The projector applies each event to one doctor's search document. Here the index is a plain dictionary standing in for the search store, so you can run the file as it is.

Duplicates are normal, not rare. Debezium's FAQ says consumers can see an event more than once after a crash. The outbox pattern's own description says the relay might publish a message more than once.

def apply_event(index, event):
    doc = index.get(event["doctor_id"])
    if doc is None:
        return "skipped: doctor not in the read model"
    seen = doc["slot_versions"].get(event["slot_id"], 0)
    if event["version"] <= seen:
        return "ignored: duplicate or older event"
    if event["type"] == "SlotBooked":
        doc["open_slots"].discard(event["slot_id"])
    elif event["type"] == "SlotReleased":
        doc["open_slots"].add(event["slot_id"])
    doc["slot_versions"][event["slot_id"]] = event["version"]
    doc["next_free"] = min(doc["open_slots"], default=None)
    return "applied"


index = {"D417": {"open_slots": {"2026-11-03 18:30 D417", "2026-11-03 18:45 D417"},
                  "slot_versions": {}, "next_free": "2026-11-03 18:30 D417"}}
booked = {"type": "SlotBooked", "doctor_id": "D417",
          "slot_id": "2026-11-03 18:30 D417", "version": 2}
released = dict(booked, type="SlotReleased", version=3)
for event in [booked, booked, released, booked]:
    print(event["type"], event["version"], apply_event(index, event),
          "| next free:", index["D417"]["next_free"])

Run it and you get four lines. The first SlotBooked is applied and the next free slot moves to 18:45. The repeat is ignored, the release is applied, and the late repeat is ignored again.

A doctor who is missing from the index is skipped here. A real projector would create the document from a DoctorAdded event, or log the doctor for the next rebuild. Newly published slots need an event too, such as SlotOpened, handled like SlotReleased.

What just happened? Each slot carries a version number that the write model raises on every change. The projector remembers the last version it applied, so a repeated or older event can never undo a newer one.

Slot ids start with the date and time, so min picks the earliest free slot. The search page now fetches ready documents and filters them. The expensive work happens once per change, about 25 times a second, not on each of 1,500 searches.

CQRS accepts that the read model may be a little behind, and makes the write side re-check the truth before every change. Eventual consistency means copies agree once changes stop arriving and the delay passes.

Back to the cable operator. He moves a news channel from number 45 to 62 on Tuesday, and the printed guides still say 45. A viewer who tries 45 finds out at the TV, not from the guide.

The booking app has the same gap. Lag is the time from a booking's commit to the index update. In this setup it is usually under 2 seconds, and about 20 seconds during a Kafka slowdown (made up).

Here is what happens when two patients chase the same evening slot.

1. 1

   Both see the slot

   At 7:01 pm, two patients search. The index shows the 7:30 pm slot as free for both, which is true at that moment.

2. 2

   The first patient books

   Patient A taps Book. The booking service locks the row, sees it open, marks it booked and writes the outbox row in one commit.

3. 3

   The second patient books a second later

   Patient B taps Book before the index catches up. The command reads the write model, sees booked, and rejects the request.

4. 4

   The app recovers politely

   Patient B sees a message: this slot was just taken, here are the next three free slots. Nobody is double booked.

5. 5

   The index catches up

   About a second later, the projector applies SlotBooked. New searches stop showing the 7:30 pm slot.

Lag is safe here because the rule lives in the command. The read model may be wrong for a moment, but the write model never is.

One more case matters. After Patient A books, the confirmation page must not show the slot as free. The app builds that page from the command's reply, not from the search index.

This guarantee is called read-your-writes: after you write something, your own next read sees it. Other users may still see the old copy for a moment.

• Re-check every rule on the write side, so a stale read can never cause a wrong change.
• Show users their own change from the command's reply, not from the read model.
• Turn a rejected command into a helpful next step, such as the next free slots.
• Measure lag in seconds and alert when it passes a limit agreed with product, such as 30 seconds.

No, CQRS does not need event sourcing. This app keeps normal tables with the current state, and uses events only to tell the read side what changed.

Event sourcing is a different idea. You store every change as an event and never overwrite anything, then rebuild the current state by replaying those events.

The two often appear together. Fowler notes that CQRS fits well with event-based designs, and Chris Richardson's pattern catalogue lists CQRS as necessary in an event-sourced architecture. The reverse is not true.

The site's guide on event sourcing and change data capture covers both ideas in depth. Here you need only one point: an outbox plus CDC builds read models without changing how you store data.

Use CQRS when reads far outnumber writes, need a very different shape, and users can live with a short lag. For most screens it is overkill.

Fowler is blunt here. He writes that for most systems, CQRS "adds risky complexity". He also says to use it only on specific portions of a system, not the whole thing.

Richardson's pattern page lists the same costs: increased complexity, possible duplicated code, and read views that lag behind. You pay them on every feature that touches the split screen.

So the team does not jump straight to CQRS. It climbs a ladder and stops at the first step that fixes the problem. Each step costs more than the one before.

The middle steps deserve a closer look. A read replica gives you more machines for reads, but it copies the same five tables. Every search still has to join them.

A materialized view is a saved query result that PostgreSQL stores like a table, and you can index it. You cannot update it directly, and it does not follow the tables by itself.

REFRESH MATERIALIZED VIEW discards the old contents and runs the query again. A plain refresh can block readers. With CONCURRENTLY, readers are not locked out, but the view needs a unique index first.

We checked all three on PostgreSQL 16.15. A reader with a 1-second lock timeout failed during a plain refresh and succeeded during a concurrent one. Without a unique index, CONCURRENTLY was refused with an error.

A materialized view is CQRS in its smallest form: a second model shaped for reads, in the same database. For this app, refreshing every few seconds re-runs the whole five-table join, and the team wants a search store built for text and distance queries. So the team climbs to the last step, for search only.

The diagram below is the check the team now uses for any slow screen. Start at the top and follow the answers. Only one path ends in full CQRS.

CRUD in that diagram stands for create, read, update and delete. It means the plain style where one set of tables serves every screen, and it is the right default.

The "read table in same commit" box is a normal table that the command updates in the same transaction as the booking. It has no lag, but every booking pays for the extra write. Bookings for one doctor also queue on that doctor's row until each commit.

Note the projector's current Kafka position first, build the new index from the write tables, then replay events from the noted position before switching. The version check makes the overlap harmless.

You will need a rebuild sooner or later. A projector bug can write wrong fees into thousands of documents, and you may add a field that old documents lack.

The naive rebuild copies the write tables, switches search to the new index, then reads only new events. Any booking or cancellation that lands while the copy runs is lost from the new index.

1. 1

   Note the position

   Record the projector's current offsets, its position in each Kafka partition, before you read anything.

2. 2

   Copy the write model

   Read every doctor's slots in one Repeatable Read transaction, so the copy is one consistent snapshot.

3. 3

   Build the new index

   Write one document per doctor, including each slot's version, into a fresh index while search keeps using the old one.

4. 4

   Replay from the noted position

   Feed every event since that offset through apply_event. Events the copy already includes are ignored by the version check. Kafka discards events older than the topic's retention setting, so replay must start before the noted events expire.

5. 5

   Switch and keep going

   Point search at the new index once replay has caught up, and delete the old index later.

We simulated both orders with the same projector code. With the naive order, the new index showed a booked slot as free and missed a cancellation. Replaying from the noted offset ignored one duplicate, applied two events and matched the write model.

Most CQRS failures come from the sync path between the two models: lost events, repeated events, events out of order, and a read model that quietly drifts from the truth.

• Lost events. The app writes the booking, then crashes before publishing. Guard: the outbox row in the same transaction.
• Repeated events. The relay or a consumer restart delivers the same event twice. Guard: the version check in the projector.
• Events out of order. A release overtakes the booking it undoes. Guard: key messages by doctor id, keep the partition count fixed, and compare versions.
• Silent lag. The projector stops, and search shows old slots all evening. Guard: stamp each event with its commit time, and alert on lag in seconds.
• Double the work. Every new field must be added to both models. Guard: keep the split to one screen.

Six weeks later the product manager checks the numbers. Search takes about 80 milliseconds at peak, timed-out bookings dropped to almost zero, and the distance filter shipped in four days (all made up).

Explain CQRS as a fix for one screen, justified with read and write numbers. Then spend most of your time on how the read model stays in sync and what users see during lag.

1. 1

   Show the pressure

   Give rough numbers: 1,500 searches a second against 25 bookings. Say that search needs joins and sorting, while booking needs strict rules.

2. 2

   Try the cheap fixes first

   Mention indexes, a cache, a read replica and a materialized view, and say why each falls short here.

3. 3

   Draw both sides

   The write model with the slot rule, and the read model as one document per doctor. Name which API reads which store.

4. 4

   Explain the sync path

   Outbox row in the same commit, a CDC relay, a topic keyed by doctor id, and a projector that ignores old versions.

5. 5

   Handle lag out loud

   Say the booking re-checks the write model under a row lock, the confirmation uses the command's reply, and lag is measured.

6. 6

   Plan for repair

   Describe the rebuild: note the offset, copy the write tables, replay from the offset, then switch.