CQRS (Command Query Responsibility Segregation) means using one model to handle writes (commands: "place order", "cancel order") and a different model to serve reads (queries: "show my orders page"). Interviewers probe it because it is the standard answer to "our read shape and our write shape have diverged", and because it is easy to name-drop and hard to justify. For a Backend or AI engineer it shows up whenever a search index, a feed, a dashboard, or an embeddings store is kept in sync with a system of record: that is a read model, whether or not anyone calls it CQRS.
COMMAND SIDE (writes) QUERY SIDE (reads)
client --> validate + enforce invariants client --> "SELECT ... one row"
| ^
v |
+----------------+ change notification +-----------------------------+
| normalized | ----------------------> | read model (denormalized, |
| write tables | outbox / event / CDC | pre-joined, maybe a different|
| (3NF, FKs, | (sync or async) | store: Elasticsearch, Redis, |
| constraints) | | wide table, cache) |
+----------------+ +-----------------------------+
optimized for: correctness, optimized for: the exact screen /
small transactions, no duplication API response; joins done in advanceThe core idea is that "the shape that is easiest to keep correct" and "the shape that is fastest to display" are usually different. A normalized schema stores each fact once, so an update touches one row and the database can enforce foreign keys and uniqueness. A screen like "my orders" wants customer name, item count, and total in one row, and computing that with a 4-way join on every request is wasted work if it is read 1000 times for every write. CQRS lets you pay that join cost once per write (in the projection) instead of once per read. Data flows in one direction only: commands change the write model, and the read model is derived from it. You never write to the read model from a user request.
SYNCHRONOUS projection (same transaction) ASYNCHRONOUS projection (after commit)
BEGIN; BEGIN;
INSERT order ...; INSERT order ...;
INSERT items ...; INSERT items ...;
UPSERT order_summary_read ...; <-- same tx INSERT outbox(event) ...; <-- same tx
COMMIT; COMMIT;
=> read model always consistent with write ... later, a worker/CDC tails the outbox ...
(but same DB, same lock contention, and => UPSERT order_summary_read (idempotent)
the read model cannot live in another store) => read model lags by ms to seconds, can be
in ES/Redis/another DB, can be rebuiltA synchronous projection (same database, same transaction, or a trigger) gives you read-your-writes for free, but you have then given up the two things that usually motivate CQRS: independent scaling and a different storage engine for reads. An asynchronous projection is what people usually mean. The price is eventual consistency: for a short window the read side shows the old state. The write to the outbox table must be in the same transaction as the business change, otherwise a crash between "commit order" and "publish event" loses the update silently (the dual-write problem). Because delivery is generally at-least-once, the projection must be idempotent: applying the same event twice must give the same read row, which is why the demo below uses an upsert keyed by order id rather than "add 1 to a counter".
User clicks "Place order" (command)
-> server returns 202/201 with the new order id AND the data it just wrote
-> UI inserts that order into the local list optimistically
-> screen never has to wait for the read model
Other techniques (pick per use case):
* read-your-writes routing: for N seconds after a write, serve THIS user's reads
from the write model (or a version-checked cache)
* version tokens: command returns version=42; query says "give me data with
version >= 42", read side waits/retries briefly or falls back
* "processing..." state for the item, then push/poll until the projection landsThe user-visible bug in an asynchronous CQRS system is "I just placed an order and my list does not show it". The fix is almost never to make the projection synchronous; it is to make the client not depend on the read model for data it already has. Returning the created resource from the command, or carrying a version/position token that the read side can compare against, turns a confusing race into a defined behaviour. Note the honest limit: other users looking at the same list may see stale data for the projection lag, and for many screens that is completely acceptable, but for "available balance" or "seats left" it may not be, which is a reason to keep those reads on the write model.
CQRS : separate read model from write model (a structure)
Event sourcing : store state as an append-only log of events (a persistence style)
CDC : stream row changes out of a DB's log (a delivery mechanism)
Combinations that all make sense:
CQRS alone : write tables (normal rows) + projection built by app code
CQRS + CDC : write tables + Debezium-style CDC feeding the projection
CQRS + event sourcing : event log is the write model; projections are the read models
event sourcing without CQRS: rebuild state by replaying events on every read (rare, slow)These three are independent ideas that are often taught as one bundle, which is the source of most interview confusion. You can do CQRS with plain relational tables on the write side; event sourcing is not required. Event sourcing naturally produces a stream that projections consume, so the two fit well, but event sourcing has its own costs (event schema evolution, replay time, harder ad-hoc queries on the write side). CDC is just one way to get changes to the projection without modifying application code paths; it reads the database's replication log, so it carries physical-change semantics rather than business events unless you design an outbox.
-- WRITE SIDE: normalized, enforces invariants (FKs, PK, NOT NULL)
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT NOT NULL, price_cents INTEGER NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id),
product_id INTEGER NOT NULL REFERENCES products(id),
qty INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
-- READ SIDE: one denormalized row per order, shaped for the "My orders" screen
CREATE TABLE order_summary_read (order_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL,
status TEXT NOT NULL, item_count INTEGER NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Asha'),(2,'Ravi');
INSERT INTO products VALUES (10,'Keyboard',5000),(11,'Mouse',2000),(12,'Monitor',15000);
-- Command 1: place order 100 (write model only)
INSERT INTO orders VALUES (100,1,'PLACED');
INSERT INTO order_items VALUES (100,10,1),(100,11,2);
-- PROJECTION for one order: recompute its read row from the write model.
-- In production this runs AFTER commit (outbox / event / CDC), not in the same request.
INSERT OR REPLACE INTO order_summary_read
SELECT o.id, c.name, o.status, SUM(oi.qty), SUM(oi.qty * p.price_cents)
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 100 GROUP BY o.id;
-- Command 2: place order 101 -- the projection has NOT run yet
INSERT INTO orders VALUES (101,2,'PLACED');
INSERT INTO order_items VALUES (101,12,1);
.print write side, orders:
SELECT COUNT(*) FROM orders;
.print read side, orders (stale by one):
SELECT COUNT(*) FROM order_summary_read;
-- Projection catches up
INSERT OR REPLACE INTO order_summary_read
SELECT o.id, c.name, o.status, SUM(oi.qty), SUM(oi.qty * p.price_cents)
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 101 GROUP BY o.id;
-- Command 3: ship order 100, then project the status change
UPDATE orders SET status = 'SHIPPED' WHERE id = 100;
UPDATE order_summary_read SET status = (SELECT status FROM orders WHERE id = 100) WHERE order_id = 100;
.headers on
.mode column
.print read side after catch-up:
SELECT * FROM order_summary_read ORDER BY order_id;
-- Real output (sqlite3 3.51, run locally):
-- write side, orders:
-- 2
-- read side, orders (stale by one):
-- 1
-- read side after catch-up:
-- order_id customer_name status item_count total_cents
-- -------- ------------- ------- ---------- -----------
-- 100 Asha SHIPPED 3 9000
-- 101 Ravi PLACED 1 15000 The write side is normalized with foreign keys; the read side is one wide row per order. The projection statement recomputes a single order's row from the write model and uses INSERT OR REPLACE (an upsert) so running it twice is harmless. Between the second command and the projection catching up, the two counts disagree: that is eventual consistency made visible in six lines. Two honest simplifications: in a real system the projection runs in a separate process triggered by an outbox row or a CDC event, and the "ship" status change would be part of a full re-projection or a targeted update handler rather than a hand-written UPDATE.
-- Run in any SQLite shell: sqlite3 :memory: then paste the SQL from "A Projection in SQL" above.
-- Exercise ideas:
-- 1. Run the projection statement for order 101 twice and confirm the read table still has 2 rows (idempotent).
-- 2. Add a third order item to order 100, DO NOT re-project, and observe the read row's total_cents is stale.
-- 3. DROP the read table, recreate it, and rebuild it for ALL orders with one INSERT ... SELECT (no WHERE):
INSERT OR REPLACE INTO order_summary_read
SELECT o.id, c.name, o.status, SUM(oi.qty), SUM(oi.qty * p.price_cents)
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id;
-- Expected: the same rows as before. That "rebuild from the write side" ability is what makes a read model disposable.Q: What is CQRS, in one sentence, and what problem does it solve?
A: It splits the model that handles writes from the model that answers reads, so each can have the shape, storage, and scale that suits it. It solves the mismatch where a normalized schema is right for correctness but slow or awkward for a specific read, at the cost of a second model and a sync path to maintain.
Q: Does CQRS require event sourcing or a message broker?
A: No. The minimum is a write table and a read table (or a view or cache) with something keeping them aligned. Event sourcing and brokers are common companions but independent choices, and adding them multiplies the operational surface, so add them only for their own reasons.
Q: What is the main consistency problem and how do you handle it?
A: With an asynchronous projection the read side lags, so a user can write and then not see their change. You handle it in the client and API: return the created or updated resource from the command, use a version/position token that queries can wait on, or route that user's next reads to the write side for a short time. The alternative, a synchronous projection in the same transaction, removes the lag but also removes most of the independence you wanted.
Q: How do you keep the read model in sync reliably?
A: Write the business change and an outbox row in one transaction, then have a worker or CDC pipeline deliver it to the projector. Delivery is generally at-least-once, so the projector must be idempotent, for example an upsert keyed by entity id guarded by a version number to ignore out-of-order older events.
Q: When is CQRS worth it, and when is it a mistake?
A: It is worth it when read and write workloads differ sharply: many-join or search-style reads, very different scaling needs, multiple different read views of the same data, or a read store (search engine, cache) that must be fed anyway. It is a mistake for ordinary CRUD, small teams, or systems that need strict read-your-writes everywhere; you take on lag, duplication, and a rebuild story for no measurable gain.
Q: How is a read replica different from a CQRS read model?
A: A replica is a copy of the same schema, so it scales read volume but does nothing for read shape: you still run the same joins. A CQRS read model is a different, pre-shaped representation. They combine well: the read model can live on its own replicas.
Follow-up: The projection consumer crashes halfway through a batch and restarts. What goes wrong and how do you design against it?
A: On restart it will re-read events it may already have applied, so a non-idempotent projection (for example incrementing a counter) double counts. Make each handler an upsert or a "set to computed value" operation, and store the last processed position (offset or event id) atomically with the read-model change so you resume exactly, or accept safe re-application.
Follow-up: Events arrive out of order for the same order id. How do you avoid the read model going backwards?
A: Give each entity a monotonically increasing version, and apply an event only if its version is greater than the stored one (discard or park older ones). In practice, partitioning the stream by entity id, as Kafka-style logs do, preserves per-entity order and makes this much rarer, but you should still guard because retries and replays can reorder.
Follow-up: How would you change the shape of a read model that has 200 million rows without downtime?
A: Build the new version alongside the old: create a new table or index, backfill it from the write model while also consuming live changes, verify counts and samples, then switch readers via a flag or alias and drop the old one. This is a real advantage of derived data, and a real cost you must budget for: backfill tooling and enough capacity to run both at once.
Most "design X" answers quietly contain CQRS: a product catalog that lives in PostgreSQL but is searched through Elasticsearch, a news feed that is precomputed per user, a timeline of orders shown from a denormalized table, or embeddings for RAG that are derived from source documents and must be re-indexed when a document changes. In each, the relational store is the system of record and enforces correctness, and the derived store is rebuildable and tuned for reads. When you present this in an interview, name the pipeline (outbox plus CDC or a queue), state the acceptable staleness per screen, say how you make the projector idempotent, and describe how you backfill. That is far more convincing than saying "we use CQRS".
You are trading simplicity and immediate consistency for read performance, independent scaling, and the freedom to shape reads any way you like. The costs are concrete: two models that must be kept aligned, an asynchronous pipeline to monitor (lag, poison messages, dead-letter handling), rebuild and backfill tooling, harder debugging ("which side has the bug?"), and a UI that has to tolerate staleness. It is the wrong choice when the app is basically CRUD, when a well-indexed query or a plain materialized view would meet the latency target, when you cannot tolerate stale reads on the main path, or when the team is not ready to operate the sync pipeline. Try indexes, caching, and materialized views first; reach for CQRS when a measured read/write mismatch remains.