Levels of CQRS and When to Stop

A spectrum, not a switch

CQRS comes in levels. Level 1: separate command and query classes or handlers over the same database and tables, a code-organisation choice with almost no cost. Level 2: the same database but separate read structures, such as views, materialised views or denormalised tables updated in the same transaction or by triggers; reads are fast and still strongly consistent. Level 3: separate read stores (a search index, a document database, a cache) updated asynchronously from events or CDC; maximum flexibility and scale, with eventual consistency and more moving parts. Level 4: CQRS plus event sourcing, where the event store is the write model. Move up a level only when a concrete need appears: read load the write database cannot handle, query shapes the write model cannot serve, or independent scaling. Many successful systems stop at level 1 or 2.

Level 2: a materialised view in the same database

Fast reads without a second datastore; refresh strategy decides freshness.

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', placed_at) AS day,
       currency,
       count(*)          AS orders,
       sum(total_minor)  AS revenue_minor
FROM   orders
WHERE  status <> 'CANCELLED'
GROUP  BY 1, 2;

CREATE UNIQUE INDEX ON daily_sales (day, currency);

-- refresh periodically without blocking readers (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

Count the moving parts

Each level adds infrastructure to deploy, monitor and back up, and new failure modes such as a stuck projection. Write down which problem the next level solves before adopting it.

Quick check: Which CQRS level introduces eventual consistency between writes and reads?

• Separate handlers on the same tables
• Database views refreshed in the same transaction
• Separate read stores updated asynchronously from events
• None of them