It is using separate models for writing and reading. The write model protects consistency and business rules, so it is normalised and constrained. The read model is denormalised and pre-computed for what the screen needs. Separating them lets each scale without blocking the other. Neither a separate database nor event sourcing is required.

When should you use a materialized view?

When the same query runs frequently, is expensive, and its result being a few seconds old is acceptable. Portfolio summaries, end-of-day reports and per-symbol aggregates are typical examples. It is not used where instant accuracy is required, such as margin checks.

What if a user cannot see what they just wrote?

This is the read-your-writes problem and it comes from the read model lagging by a few hundred milliseconds. The real protection is an idempotency key on the write side; that is what stops the same order being accepted twice. To fix what the user sees, there are three practical fixes: show the result optimistically in the UI, read only the row the order touched from the write side, or have the read wait until the view catches up to the version number returned by the write.

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 · 1 August 2026, 22:00 · 9 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.
Without an ordering guarantee, a version filter loses events. In a delta-updated view, solve ordering first (the partition key), then duplicates.
Rebuildability is its most valuable property. You must be able to regenerate a broken view from events — which needs the full history, or a snapshot plus the events after it.

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
Reads only from the user's point of view; no waiting on locks

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. One note: I use “materialized view” here as a concept — a view whose result is computed in advance and stored. It is not PostgreSQL’s CREATE MATERIALIZED VIEW + REFRESH, which re-runs the whole query on every refresh. This is a plain table, updated incrementally by events:

A read-ready table

CREATE TABLE portfolio_view (
  account_id   bigint,
  symbol       text,
  net_lots     numeric,
  avg_entry    numeric,
  realised     numeric,      -- from closed positions; updated by events
  used_margin  numeric,
  last_trade   timestamptz,
  version      bigint,       -- seq of the last applied event (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.

-- Current price and unrealised P&L are NOT in this table.
-- They are computed at read time from the price cache:
--   unrealised = net_lots * (current_px - avg_entry)

Also look at what is not in the table: no current price, no unrealised P&L. The price changes on every tick. If you store it here, every price move updates the row of every account that holds that symbol. The event that feeds this view (FillExecuted) does not touch the price anyway. The rule: only things that change with an event go into the view; price-dependent values are computed when you read.

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

From event to view

-- Precondition: events for one account come from one partition, in order.

-- Inbox: the key is NOT just event_id, it is (consumer, event_id)
CREATE TABLE processed_events (
  consumer     text,       -- 'portfolio_view', 'risk_view', 'portfolio_view_v2'
  event_id     text,
  processed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer, event_id)
);

-- When a FillExecuted event arrives, ONE statement:
-- if the inbox row is not written, the delta is not applied either.
WITH fresh AS (
  INSERT INTO processed_events (consumer, event_id)
  VALUES ('portfolio_view', :event_id)
  ON CONFLICT DO NOTHING
  RETURNING 1                              -- a redelivered event returns no row
)
INSERT INTO portfolio_view (account_id, symbol, net_lots, avg_entry, last_trade, version)
SELECT :account, :symbol, :lots, :price, :ts, :event_seq
 WHERE EXISTS (SELECT 1 FROM fresh)        -- otherwise the INSERT never runs
ON CONFLICT (account_id, symbol) DO UPDATE
   SET net_lots   = portfolio_view.net_lots + EXCLUDED.net_lots,
       avg_entry  = (...),
       last_trade = EXCLUDED.last_trade,
       version    = EXCLUDED.version;      -- not a filter, just a record

Four details. All four have hurt me.

There is no version filter. The first draft had WHERE version < EXCLUDED.version and I wrote “a late old event cannot corrupt the view”. That was wrong. This table is updated by deltas (“300 lots added”), not by absolute state (“net lots is now 300”). If event 5 arrives before event 4, the filter drops event 4 completely and that fill’s lots are lost. The view is not corrupted, it is incomplete — which is worse, because it is harder to notice. If you work with deltas, ordering is not negotiable: events for the same account come from the same partition, in the same order. And if you do have ordering, the filter becomes useful again — see below.

The inbox key is (consumer, event_id). My first version used only event_id. The same fill event also feeds risk_view and eod_view. If they share one inbox, whichever consumer runs first marks the event as processed and the other two skip it as a duplicate. Two silently incomplete views. The same mistake also kills a rebuild: when you replay history into portfolio_view_v2, every event ID is already in the inbox, so v2 stays empty. Each view and each version of it keeps its own record under its own consumer name.

The check is in the statement, not in a comment. I once wrote this as “insert into the inbox first, skip if it affects 0 rows” — as a comment. A comment does not stop the code: the second INSERT ran unconditionally and the delta was applied twice, which is exactly what the inbox was there to prevent. Either reduce it to one statement as above, or write the branch explicitly in application code. That is also what the transaction is for: not to wrap a single statement, but to write the “processed” record and the update together.

event_seq is not the outbox bigserial. That ID is assigned when the row is inserted, not when it commits, so event 101 can commit before event 100 (the outbox post covers this). The sequence here is produced per account: the transaction that writes the fill also increments a counter on the account row and puts that value in the event. Transactions writing to the same account serialise on that row lock, so the counter increases monotonically. The partition key is the account too: the counter must increase per the same key that ordering is guaranteed for. If they are different keys, you have no guarantee at all.

Do you need the inbox? How big does it get?

The honest answer: if you have ordering, you do not need it. With ordering, an at-least-once duplicate is always a redelivery of an event you already applied, which means event_seq ≤ version. So WHERE portfolio_view.version < EXCLUDED.version drops those correctly — free, permanent, and it never grows. The filter itself was never wrong; my mistake was using it together with unordered delivery.

An inbox earns its place in two cases. First, when you cannot produce a monotonic sequence number to compare against, even if you do have ordering — if the filter has nothing to look at, the dedup has to happen somewhere else. Second, when processing an event does more than one row’s delta (send a notification, write to a second table); then “I processed this” has to live in one shared place, not inside the row. With unordered delivery, though, an inbox does not save you: it stops duplicates, not reordering — the deltas still add up wrong.

If you do choose the inbox, plan its cleanup: the table grows forever. A daily job deletes old rows, but the deletion window must be longer than the broker’s longest redelivery window — otherwise a very late duplicate looks “new” and the delta is applied a second time. Keeping 7 days against a 3-day retention works in practice. With a version filter the whole problem disappears: version already sits in the row and needs no cleanup.

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

A market order: there was a matching price, it filled immediately.

10:30:00.000  Customer clicked "BUY"
10:30:00.040  Order accepted and filled in one transaction
              (orders, fill_records, positions, outbox)
              response: "filled"
10:30:00.050  UI reloads the portfolio
10:30:00.055  portfolio_view NOT UPDATED YET
              → the customer cannot see their position
10:30:00.180  event processed, 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.

The word “market order” in that timeline matters. The event that feeds portfolio_view is FillExecuted, so the screen shows a position, not an order. A limit order that has not filled yet does not belong here at all — its place is a separate open_orders view fed by OrderAccepted. Mix the two on one screen and you will blame eventual consistency for “where is my order?”, when the real problem is a view fed by the wrong event.

The real protection is not in the UI, it is on the write side: every order carries an idempotency key (a client order ID) and a second order with the same key is rejected. The three fixes below correct what the user sees; the key is what stops the second order from being accepted. Put the key in first, then fix the perception.

Three practical fixes, simplest first:

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.
Read the touched row from the write side. For 3 seconds after placing an order, that customer's portfolio still comes from the view; only the row for the symbol the order touched is fetched from the write-side positions table and laid over it. One account, one symbol: one row, not 14 joins. Fetching the whole portfolio from the write side means 8 seconds — it brings back the problem from the opening and makes the double click worse. This only works if the order actually filled: what changes the positions table is the fill, not the order record. For a resting limit order this is not the right fix — the first one is.
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. Let v2 consume the live stream too; wait until it catches up with v1 (lag ≈ 0)
   (skip this and the events that arrived during the replay are missing from v2)
4. Compare: do the v1-v2 differences come ONLY from the known bug?
   (there will be differences, v1 is wrong; any other difference means v2 has a problem too)
5. Point reads at v2
6. 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 need the full event history. If you delete the outbox table after three days, you cannot rebuild the view from scratch: you have the last three days of deltas, but not the position from three days ago. There are two ways. Either keep the events from day one in a separate archive (or with a long retention in Kafka), or take regular snapshots — an end-of-day copy of the write-side positions table, plus the events after that moment. The second is cheaper, and because the snapshot comes from the write side it does not carry the bug in the view.

A snapshot has two conditions, and skipping either one gives you a quietly wrong result. First, you must know which event position the snapshot corresponds to. “An end-of-day copy, then the events after it” is not enough: the events on the boundary get applied either twice or not at all. Take the copy and the last sequence in the event history inside the same transaction (the same MVCC snapshot), and start the replay exactly from that sequence. Second, the snapshot does not cover every column: the positions table has no view-specific columns such as realised or used_margin. For the columns it does not cover you need either a separate source or the full history. Whichever you choose, write it down, or you will find out on the night of your first rebuild.

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

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?
If the same event arrives twice, is it applied twice? (Is the check in a comment, or in the statement?)
Is the inbox key (consumer, event_id)? Does each view and each version keep its own record?
When is the inbox cleaned up? Is the window longer than the broker's redelivery window?
Do events for the same key arrive in order? (Is the partition key the account?)
Where does the sequence number come from? Is it produced per the same key as the partition?
Which event feeds the view, and is that what the screen shows? (A position, or an order?)
Is there an idempotency key on the write side that blocks a double order?
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?
For a rebuild, do I have the full history or a snapshot plus later events? Which one, and is it written down?
Which event sequence does the snapshot correspond to? Was it taken in the same transaction?
Does the snapshot cover every column in the view?
During a rebuild, does the live stream also flow into the new table?

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.