Pattern — CQRS

The problem it solves

Reads and writes have opposite shapes, but they run against one model, one primary, one pool — so a heavy query starves the writes it shares a resource with. In Drill 79 a single unindexed /reports/statement-activity scan holds a database connection for 30–40 seconds; five overlapping reloads check out the entire 20-connection pool, and every 20-millisecond payment post then blocks in the checkout queue and times out — while the database sits at 15% CPU. The reporting query and the transactional commands were never separated, so the slow read took the writes down with it.

The pattern

CQRS — Command Query Responsibility Segregation — splits the write path (commands that change state) from the read path (queries that return it) into separate models, served by separate infrastructure. Commands hit the write model on the primary; queries hit a read model — a streaming replica or a purpose-built, denormalized projection — that is kept up to date asynchronously. A slow report now runs on the read side, against its own store and its own pool, where it can contend only with other reads. The cost is that the read model is eventually consistent: it trails the write model by a bounded replication lag.

How to apply it

1. Classify every endpoint as a command or a query. Commands (payment post, balance mutation) go to the primary; queries (/reports/statement-activity, transaction history, dashboards) go to the read side. In practice this is a routing rule: @Transactional(readOnly = true) or /reports/* selects the replica DataSource; everything else selects the primary.
2. Give the read side its own store and pool. Keep the write primary's transactional pool at cores × 2 ≈ 20 with statement_timeout = 2s. Point queries at a streaming read replica with its own pool and a permissive statement_timeout = 30s. The 40-second scan now cannot touch the 20 connections payments need — they're on different servers.
3. Build a real read model for the expensive query, not just a replica. For statement-activity, project a denormalized, indexed statement_activity_read table off the write stream (logical replication / CDC via Debezium, or a materialized view refreshed on a cadence). The scan that took 30–40s becomes an indexed lookup in tens of milliseconds — you fixed the shape, not just the isolation.
4. Budget the staleness and alarm on it. Streaming replication lag is typically sub-second; set an SLO of p99 replica lag < 2s and page at > 10s. For a projected read model, track projection lag (events pending) and alarm when it exceeds one refresh interval.
5. Handle read-your-writes explicitly. A command returns the primary's write position (Postgres LSN / a version token); route the immediate follow-up read either to the primary or to a replica that has caught up to that LSN (wait, bounded to ~250ms, else fall back to primary). Never assume a read right after a write sees it on the replica.

flowchart LR
    C["Command<br/>(payment post)"] --> W["Write model<br/>primary · pool 20 · timeout 2s"]
    W -->|"logical replication / CDC<br/>(lag p99 < 2s)"| R["Read model<br/>replica + statement_activity_read"]
    Q["Query<br/>(statement-activity report)"] --> R
    W --> WOK["Payments stay fast<br/>pool never drains"]
    R --> ROK["40s scan → indexed<br/>read, isolated"]

When to use it — and when not

Reach for CQRS when reads and writes have genuinely different shapes or scale — analytical/reporting queries beside transactional writes, a read rate that dwarfs the write rate, or a search/aggregate view that a normalized write schema serves badly — and you can tolerate the read side being seconds stale. Do not reach for it when a read must reflect a write immediately on the same path (show the new balance the instant a transfer posts) — route those reads to the primary or you'll show stale money. And don't pay CQRS's operational tax — two models, a projection to build and monitor, replica ops, dual-write and lag bugs — for simple CRUD at modest scale, where the read and write can happily share the primary. There, the cheaper fix is Pattern 04 (bulkhead): separate pools on the same database. CQRS is the next step up when isolation alone isn't enough and the query itself needs a different model.

Signals it's working

• The primary stops serving reporting. pg_stat_activity on the write primary shows no statement-activity scans; the transactional pool's utilization stays low through a report burst instead of pinning at 20-of-20.
• A game-day proves isolation. Fire five concurrent statement-activity reports at peak transactional load: transactional p99 on the primary stays < 50ms, the primary pool never exceeds ~50% utilization, and only the replica works hard.
• Lag stays bounded. Replica-lag p99 holds < 2s and projection lag drains within one interval — so "eventually consistent" stays measured in seconds, not minutes.
• The read got faster, not just isolated. The projected query returns in tens of milliseconds instead of 30–40 seconds.

Seen in the wild

• AWS Aurora / RDS read replicas with a separate reader endpoint; apps route readOnly traffic there while writes go to the writer endpoint — physical CQRS at the connection level.
• Debezium + Kafka streaming database changes into denormalized read models (Elasticsearch for search, a warehouse for reporting) — the projection half of CQRS.
• Greg Young's CQRS and event-sourced systems (EventStoreDB, Axon Framework) formalize separate command and query models.
• Postgres materialized views and logical replication used to carve reporting off the transactional primary.

See it

The CompareScene carries it: one shared 20-slot pool drained to 0 free by a 40-second report (payments time out) beside a split write-primary (pool 20, payments fast) and a read replica absorbing the scan; the CardsScene shows the load-bearing config — primary pool 20 / timeout 2s, replica timeout 30s, lag p99 < 2s / page > 10s, read-your-writes fallback 250ms.

Named concepts: CQRS, read/write path separation, read replicas, read-model projection.