Sertaç Yıldırımfield notes

Home → Engineering

CQRS and Materialized Views

The portfolio screen takes 8 seconds to load: 14 tables joined and P&L recalculated on every open. The same database must, at the same time, write an order in 3 milliseconds. One model cannot do both jobs.

Sertaç Yıldırım · · 10 min read

Summary
  • Writing and reading want opposite things. One normalised, the other denormalised.
  • CQRS does not mean two databases. Its simplest form is separate read tables in the same database.
  • The read model will be stale. The point is to design for that, not to hide it.
  • Rebuildability is its most valuable property. You must be able to regenerate a broken view from events.

Why one model cannot serve both

What the write side wants
  • Normalised tables — data in one place
  • Constraints, foreign keys, locks
  • Small, fast transactions
  • Few indexes (every index slows writes)
What the read side wants
  • Denormalised — everything in one row
  • No joins, pre-computed aggregates
  • Plenty of indexes, one per filter
  • No locks, reads only

Try to reconcile these in one family of tables and you get a mediocre result on both sides: add indexes for reads and writes slow down; remove them for writes and screens slow down. That is not a balance, it is a tug of war.

What CQRS is not

This topic has an unnecessarily intimidating reputation. Let us clear it up:

Three things that are not required
  • A separate database is not required. Keeping separate read tables inside the same PostgreSQL is also CQRS.
  • Event sourcing is not required. They are often mentioned together but they are independent decisions. Plain tables work fine.
  • It is not applied everywhere. Only where read load and write load conflict.

At its core CQRS is one sentence: the structure you read from does not have to be the structure you write to. Everything else is implementation detail.

A concrete example: the portfolio view

The write side stays exactly as it is: orders, fill_records, positions, margin_movements. Constrained, normalised, correct.

On the read side you put the table the screen actually needs:

A read-ready table 
CREATE TABLE portfolio_view (
  account_id   bigint,
  symbol       text,
  net_lots     numeric,
  avg_entry    numeric,
  current_px   numeric,
  unrealised   numeric,      -- ← pre-computed
  realised     numeric,
  used_margin  numeric,
  last_trade   timestamptz,
  version      bigint,        -- ← for "read your writes"
  PRIMARY KEY (account_id, symbol)
);

-- The screen's query is now this:
SELECT * FROM portfolio_view WHERE account_id = 42;
-- No 14 joins. One index scan. ~2 ms.

What updates this table is the events coming out of the outbox:

From event to view 
// When a FillExecuted event arrives:
BEGIN;
  INSERT INTO portfolio_view (account_id, symbol, net_lots, avg_entry, version)
  VALUES (:account, :symbol, :lots, :price, :event_seq)
  ON CONFLICT (account_id, symbol) DO UPDATE
     SET net_lots  = portfolio_view.net_lots + EXCLUDED.net_lots,
         avg_entry = (...),
         version   = EXCLUDED.version
   WHERE portfolio_view.version < EXCLUDED.version;   -- ← old events cannot write
COMMIT;

That last line protects against reordering: a late old event cannot corrupt the view.

The same event can feed several views, each optimised for its own screen: risk_view for the risk engine, symbol_view for the trading desk, eod_view for reports. Each updates independently, and if one breaks the others keep working.

The real cost: the read model is stale

The view updates after the event arrives, so there is a lag — usually 50–300 ms. Unnoticeable for most screens. But noticeable here:

Not seeing what you just wrote 
10:30:00.000  Customer clicked "BUY"
10:30:00.040  Order written, response: "success"
10:30:00.050  UI reloads the portfolio
10:30:00.055  portfolio_view NOT UPDATED YET
              → the customer cannot see their order
10:30:00.180  view updated

What does the customer think during those 130 ms? "Did it not go through?"
And they click again.

Result: two orders. A technical delay turned into a commercial error.

Three practical fixes, simplest first:

  1. Optimistic UI. The moment the order response arrives, the UI adds it to the list itself rather than waiting for the view. Cheapest and usually enough.
  2. Let the acting user read from the write side. For 3 seconds after placing an order, that customer's portfolio query goes to the real tables instead of the read model. Slower but correct — and only for one user.
  3. Version waiting. The write returns a version number; the read requests "at least this version" and waits (with a short cap) for the view to catch up. The most correct and the most complex.
Where not to use it

Do not make decisions from views. Margin checks, risk limit checks, order acceptance — these must always read the real data on the write side. Accepting an order based on a 200 ms old margin figure means letting through an order that should have been rejected.

The rule: a view is for showing, not for deciding.

The most valuable property: rebuildability

This is the least discussed side of CQRS and the most useful in operations. The read model is derived data — which means if it is lost it can be regenerated.

When a view is broken 
# A bug was found in the calculation logic, the view is wrong.
# Without touching the write side at all:

1. Create a new table:  portfolio_view_v2
2. Replay the event history  → fill v2
3. Compare: report the differences between v1 and v2
4. If it looks right, point reads at v2
5. Drop v1

The user sees no downtime and the real data is never touched. In a classic architecture the same job means a migration on a live table and a night with no way back.

The condition: you must have kept the events. If you delete the outbox table after three days, your rebuild window is three days. For critical views, keeping events in a separate archive for longer (or a long retention in Kafka) is what keeps this property alive.

When not to do CQRS

Not needed
  • Read and write load are already balanced
  • Screen queries are still fast (check the indexes first!)
  • The data must be instantly accurate
  • The team is small and lacks the discipline to keep two models in sync
Needed
  • Reads vastly outnumber writes
  • The screen query is expensive and frequent
  • Different screens want the same data in very different shapes
  • Heavy reports slow down live writes

A mistake I see often: reaching for CQRS instead of fixing a slow query. Most "8-second screens" are really a missing index or an N+1 query. Measure first, change the architecture second — the second step is expensive to undo.

Checklist AI agent task list

When building a read model
  • Have I measured the cause of the slowness? (It may be a missing index.)
  • Which events feed the view? Is the list written down?
  • Can a late old event corrupt the view? (Is there a version check?)
  • How far behind does the view run? Is it measured?
  • Can a user see what they just wrote?
  • Are decisions being made from the view? (They should not be.)
  • Can I rebuild the view from scratch?
  • How far back does the event history go for a rebuild?

Conclusion

CQRS is a far simpler idea than it sounds: the data you show does not have to have the same shape as the data you store. The moment you make that split, both sides relax.

The cost is clear too: there are now two copies of the data and one lags behind the other. Systems that try to hide this produce strange bugs; systems that accept it openly and design around it end up both fast and predictable.