CQRS (Command Query Responsibility Segregation) is one of those patterns that gets recommended far more often than it’s warranted. It shows up in every system design interview, every architecture whiteboard — and most of the time, it’s the wrong answer. But when it’s the right answer, the improvement is dramatic.
This post is about when it was the right answer.
The Contention Problem CQRS Actually Solves
CQRS solves a specific problem: high-write systems where reads compete with writes for the same data model.
In a traditional architecture, your write path and your read path share the same schema, the same indexes, and the same connection pool. At low scale, this is fine. At high scale, writes hold locks that block reads, reads create query plans that conflict with write-optimised indexes, and your connection pool becomes a battleground.
The key insight is not “separate your reads and writes” — it’s “your read model and your write model have different requirements, and forcing them into the same schema creates contention that grows superlinearly with load.”
flowchart LR
subgraph Before["Shared Model - Contention"]
W1[Write Path] --> DB1[(Single Schema)]
R1[Read Path] --> DB1
end
subgraph After["CQRS - Separated Concerns"]
W2[Write Path] --> WDB[(Write Model)]
WDB -->|async refresh| RDB[(Read Model MV)]
R2[Read Path] --> RDB
end
style DB1 fill:#3d1515,color:#ff8080,stroke:#ff6464
style WDB fill:#0d3026,color:#64ffda,stroke:#64ffda
style RDB fill:#0d3026,color:#64ffda,stroke:#64ffda
What CQRS Is NOT
Before going further, let’s clear up common misconceptions:
CQRS is not Event Sourcing. Event sourcing stores state as a sequence of events. CQRS separates read and write models. You can use either without the other. Conflating them is the #1 source of over-engineering.
CQRS is not a microservices requirement. You can implement CQRS within a single service, a single database, even a single repository. It’s a data model pattern, not a deployment pattern.
CQRS is not a database pattern. It doesn’t require separate databases for reads and writes (though it can benefit from them). The separation can be as simple as a materialized view.
Where We Applied It: The Segmentation Engine
Our multi-tenant CRM platform ingests 1M+ events per 5 minutes from service operations across hundreds of locations. Marketing teams build customer segments using complex filters — “active subscribers at Location A with 3+ transactions in the last 30 days and wallet credit > $50.”
The Write Path (Commands)
Events arrive via Azure Event Hub across 32 partitions. The ingestion pipeline processes them into normalised tables:
Event Hub → Consumer Pods → subscription_account
→ transaction_log
→ wallet_account
→ package_accountThese tables are write-optimised: normalised schema, minimal indexes, append-heavy. The write path doesn’t care about query patterns.
The Read Path (Queries)
The segmentation API needs to answer questions like “how many customers match these 5 filters?” in under 200ms. Running this query against the write-optimised tables required 7-table joins with subqueries. Response time: 30+ seconds.
The Separation
Instead of fighting the write schema, we built a separate read model:
-- Read model: pre-computed, denormalised, query-optimised
CREATE MATERIALIZED VIEW customer_attributes_mv AS
SELECT
c.id,
COALESCE(sub.is_active_subscriber, false) as is_active_subscriber,
sub.location_codes, -- ARRAY type for GIN index
sub.package_ids, -- ARRAY type for GIN index
COALESCE(wal.total_balance, 0) as wallet_balance
FROM customer c
LEFT JOIN sub_stats sub ON c.customer_uuid = sub.customer_id
LEFT JOIN wal_stats wal ON c.customer_uuid = wal.customer_id;The read model is a PostgreSQL Materialized View with GIN-indexed arrays. It’s refreshed every 45 seconds using REFRESH MATERIALIZED VIEW CONCURRENTLY — zero query downtime during refresh.
The Full Data Flow
flowchart TB
EH["Event Hub 32 partitions"] --> Pods["Consumer Pods"]
Pods --> T1[("subscription_account")]
Pods --> T2[("transaction_log")]
Pods --> T3[("wallet_account")]
Pods --> T4[("package_account")]
T1 --> |"REFRESH every 45s"| MV1[("customer_attributes_mv")]
T3 --> |"REFRESH every 45s"| MV1
T4 --> |"REFRESH every 45s"| MV1
T2 --> |"REFRESH every 5min"| MV2[("customer_transactions_mv")]
MV1 --> API["Segmentation API"]
MV2 --> API
API --> |"under 200ms"| Result["Segment Count"]
style MV1 fill:#0d3026,color:#64ffda,stroke:#64ffda
style MV2 fill:#0d3026,color:#64ffda,stroke:#64ffda
style Result fill:#0d3026,color:#64ffda,stroke:#64ffda
The Result
| Query | Before (shared model) | After (CQRS) | Improvement |
|---|---|---|---|
| Is Active Subscriber | 30s | 50ms | 600x |
| Subscriber + Balance > $50 | 35s | 80ms | 437x |
| Complex 5-filter segment | 60s | 200ms | 300x |
The 30s → 100ms improvement came directly from this separation. Not from better indexes on the write tables. Not from query optimisation. From acknowledging that the read model and write model had fundamentally different requirements and should not share a schema.
The Real Cost
CQRS is not free. Here’s what you pay:
1. Eventual Consistency Windows
The read model is 45–90 seconds behind the write model. Every product decision must account for this.
sequenceDiagram
participant W as Write Path
participant DB as Source Tables
participant MV as Materialized View
participant API as Segmentation API
Note over W,DB: T=0 — new customer created
W->>DB: INSERT into subscription_account
Note over DB,MV: T=0 -> T=45s — MV is stale
API->>MV: Query segment count
Note over MV: Returns count WITHOUT new customer
Note over DB,MV: T=45s — REFRESH CONCURRENTLY runs
DB->>MV: Atomic swap with fresh data
API->>MV: Query segment count
Note over MV: Returns count WITH new customer
This is acceptable for marketing segmentation (not time-critical). It would be unacceptable for an order status page.
2. Debugging Difficulty
When a customer “doesn’t appear” in a segment, is it because:
- The event hasn’t been ingested yet?
- The MV hasn’t refreshed yet?
- The filter logic is wrong?
- The MV definition doesn’t include the right join?
Debugging CQRS requires tracing a data point across the write path, through the refresh cycle, and into the read model. This is substantially harder than debugging a single query against a shared database.
3. Operational Overhead
Materialized Views need monitoring:
- Refresh time — grows as data grows. A 45s refresh cycle that takes 5s today may take 50s next quarter.
- Staleness tracking — you need a
mv_refresh_statustable to know when data was last synced. - Index count — 30+ GIN indexes slow down refresh significantly. Every read optimisation has a write cost.
- Per-tenant management — with 100+ tenant databases, each MV refresh is a separate operation.
4. Schema Drift Risk
The write schema evolves independently. If you add a column to subscription_account, you must remember to update the MV definition. There’s no compiler error, no type check — just a missing column that won’t surface until someone tries to filter by it.
Decision Framework: 3 Questions Before Using CQRS
Before reaching for CQRS, ask:
1. “Do my reads and writes have fundamentally different data shapes?”
If your reads can be answered by the same schema your writes use, with appropriate indexes, CQRS adds complexity without benefit. Most CRUD applications fall into this category.
Our case: Writes are normalised inserts across 7 tables. Reads need a single denormalised row with pre-computed aggregates and array columns. These shapes are incompatible.
2. “Is the consistency window acceptable for every consumer of the read model?”
List every system that reads from your read model. For each one: can it tolerate N seconds of staleness? If any critical consumer needs real-time consistency, CQRS creates more problems than it solves.
Our case: Marketing segmentation, not real-time operations. 90 seconds of staleness is invisible to users.
3. “Have I exhausted simpler alternatives?”
Before CQRS:
- Can you add composite indexes?
- Can you use a query cache?
- Can you denormalise a single column?
- Can you pre-compute at write time?
Our case: We tried composite indexes first. The fundamental problem was 7-table joins with subqueries across millions of rows — no index strategy solves that at scale.
The Pattern, Applied Honestly
CQRS gave us a 300x performance improvement on the most critical user-facing feature of the platform. It turned an unusable 30-second wait into a responsive 100ms experience.
But it also gave us an eventual consistency model we must design around, a debugging surface area we must train for, and operational complexity we must monitor. These are not abstractions — they are real engineering costs that show up at 3 AM when an MV refresh starts timing out.
The pattern earns its complexity when the alternative — trying to serve both paths from a single model — is provably worse. In our case, it was. In most cases I’ve evaluated since, it isn’t.
Use CQRS when no simpler alternative works. Not because it’s architecturally elegant, but because it’s architecturally necessary.
This is part of a series on building high-performance systems. See also: PostgreSQL Materialized Views for Segmentation (the read model deep dive), LRU Connection Pooling (the infrastructure beneath), and Azure Event Hub at Scale (the write pipeline).