Architecture Patterns

CQRS (Command Query Responsibility Segregation)

Term 56 of 68 in the ERPStack technical glossary

What is CQRS (Command Query Responsibility Segregation)?

CQRS is an architectural pattern that separates database read (query) operations from write (command) operations into distinct models, allowing each side to be optimized, scaled, and secured independently.

CQRS (Command Query Responsibility Segregation) at a glance

Split: 2 models: 1 write model enforcing invariants, 1 or more read models shaped for queries
Consistency: Read models are eventually consistent — usually milliseconds behind, never guaranteed to be current
Typical stack: PostgreSQL 18 for writes, ClickHouse or a materialised view for analytical reads
When to skip: If 1 model serves both paths acceptably, CQRS adds 2 things to operate for no gain
Built with: PostgreSQL 18 with Drizzle ORM 0.45 as the write model, ClickHouse or a PostgreSQL 18 materialised view as the read model, synchronised over Apache Kafka, queried from Next.js 16 and React 19
Numbers that matter: 2 models, 1 source of truth; lag measured in milliseconds; 0 guarantee the read side is current; 1 projection to rebuild when it breaks
Read-side options: 1 shared model in PostgreSQL 18, ClickHouse, TimescaleDB, MongoDB Atlas or Redis serving both paths, queried over REST or a GraphQL API Schema from Next.js 16
Commonly paired with: Event-Driven Architecture over Apache Kafka, Microservices Architecture boundaries, Drizzle ORM 0.45 projections into PostgreSQL 18 or ClickHouse, Redis for hot reads, Next.js 16 dashboards, and OpenTelemetry Observability across the lag
Projection rebuild: 1 replay from Apache Kafka rebuilds the ClickHouse or PostgreSQL 18 read model; expect minutes, not the 0 downtime a live schema change implies
Read models in practice: A PostgreSQL 18 materialised view first, then the ClickHouse analytics database, TimescaleDB for series or Redis for hot counters — 4 options, chosen after the lag is measured.
Where it is worth it: finance, logistics and retail estates already running a Microservices Architecture on Apache Kafka. Below that, 1 PostgreSQL 18 read replica behind a Monolithic Architecture, read from Next.js 16 and React 19 over REST, costs less to operate.

How CQRS (Command Query Responsibility Segregation) works in production

In a standard CRUD application, read and write operations compete for the same database resources. CQRS splits the data model: commands mutate state through an OLTP write database, while queries read from an optimized read replica or materialized view. This is particularly valuable in ERP systems where real-time reporting queries (e.g., 'show all orders in the last 30 days') would otherwise lock the same tables processing new order writes.

The ERPStack approach to CQRS (Command Query Responsibility Segregation)

We implement CQRS using PostgreSQL read replicas for analytics dashboards, keeping writes on the primary instance. This allows real-time reporting without impacting transaction throughput on the write path.

Related Resources

Frequently asked questions about CQRS (Command Query Responsibility Segregation)

What problem does Command Query Responsibility Segregation solve?

Conflicting shapes. A write model wants normalised tables that make invariants enforceable; a reporting screen wants wide denormalised rows that read in 1 query. Responsibility Segregation stops those requirements fighting inside 1 schema by giving each its own model, kept in sync by events. It is most valuable when read and write load differ by an order of magnitude, or when reporting queries are slowing down transactions.

Does CQRS require event sourcing?

No, and conflating the 2 is the most common misunderstanding. Responsibility Segregation only says reads and writes use different models; it says nothing about how state is stored. You can implement it with a normalised PostgreSQL 18 write table and a materialised view refreshed by triggers. Event sourcing is a separate decision with its own costs, and adopting both at once multiplies the risk of the change.

What does eventual consistency mean for users?

That a user can save something and not see it in the list yet. With Responsibility Segregation the read model lags the write model, usually by milliseconds, and the interface has to account for it — by returning the created object directly, by reading the writer's own recent changes from the write side, or by showing an explicit pending state. Ignoring the lag produces bug reports that are impossible to reproduce.

When is CQRS overkill?

Whenever 1 model is doing fine. Responsibility Segregation adds a second store, a synchronisation path and a new failure mode where the projection falls behind or breaks. On a system whose reporting fits comfortably in the transactional database, that is pure cost. The honest trigger is measured: reporting queries are affecting transactional latency, and indexing has already been exhausted.