UNDER PRESSURE

Level 2 · The Database Is the Bottleneck

Session 21: Separating the Read Path and the Write Path: CQRS & Materialized Views

What if the data structure that's perfect for recording incoming money turns out to be the worst, slowest shape for showing transaction history in the user's app?

Session 21 / 344 min read

1. Imagine If

An accounting office stores millions of financial transactions as single-line receipts in a physical cash ledger: - Receipt #1: Budi deposits Rp 50,000 - Receipt #2: Ani withdraws Rp 20,000 - Receipt #3: Budi pays Rp 10,000 for coffee

Recording a new receipt takes just 1 second (very fast). But when the boss asks "What's Budi's total net worth, what were his last 5 purchases, and what's his monthly spending summary?", the accountant has to read 10 million receipts starting from page one, punch a calculator for 30 minutes, and the whole office grinds to a halt.

2. What Actually Happens

This is the fundamental dilemma of relational databases (OLTP vs OLAP / Read vs Write Optimization).

To solve this dilemma, architects apply the CQRS (Command Query Responsibility Segregation) pattern:

  1. Total Separation of Roles: - Command (Write Model): Dedicated to receiving business action commands (CreateOrder, TransferMoney). Stored in a strictly normalized schema (3NF / Third Normal Form) to guarantee referential integrity and prevent data anomalies. - Query (Read Model): Dedicated to serving the user interface (GetOrderHistory, SearchProductCatalog). Stored in a Denormalized Flat Document schema (for example: JSON in MongoDB, ElasticSearch for full-text search, or a Materialized Read Table in PostgreSQL) that's ready to serve without joining 10 tables!

  2. Asynchronous Projection (Event-Driven Sync): - Every time the Write Model successfully saves a transaction (OrderPlaced), an event is published to the message bus. - A background worker (Projector / Worker) picks up that event, builds a ready-to-serve aggregate document, and saves it to the Read Model. - The dashboard query now reads just 1 flat document in 1 millisecond without doing any JOIN at all!

  3. Materialized Views: - The simplest form of CQRS inside a single database engine: a virtual table that stores the pre-computed result of a heavy query and is refreshed periodically (REFRESH MATERIALIZED VIEW CONCURRENTLY).

3. The Steep Price You Have to Pay

Why shouldn't CQRS be used carelessly? - Eventual Consistency: There's a time gap (projection lag) between when a transaction is written to the Write Model and when it shows up in the Read Model. A user might only see their order invoice 500 ms after hitting the pay button. - Double the Operational Complexity: The team has to maintain two different databases, the event projector's sync logic, data replay scenarios when the projector crashes, and handling of duplicate-event bugs.

4. The Official Name

  • CQRS: Command Query Responsibility Segregation.
  • Command: A state mutation that doesn't return business data (only a success/failure status).
  • Query: Pure data retrieval with no side effects (side-effect free).
  • Projection: The process of transforming events into a ready-to-read Read Model representation.
  • Event Sourcing: Storing the history of changes as an immutable sequence of events (append-only event log).

5. In Our World

  • E-Commerce Product Search: Products are edited in MySQL (Write Model), but the search box, category filters, and prices on the front page are pulled from ElasticSearch / OpenSearch (Read Model).
  • Social Media Timeline: Posts are stored in a relational DB, but the home timeline feed is generated and stored in a ready-to-read Redis Sorted Set.

6. The Performance Tester's Lens

Crucial metrics and tests: 1. Projection Lag Under Load: How many milliseconds does the projector need to update the Read Model under a load of 3,000 Write QPS? 2. Query Latency Improvement: Compare the latency of a JOIN 8 TABLES query (without CQRS: 450 ms) vs DIRECT READ MODEL FETCH (with CQRS: 3 ms). 3. Projector Recovery Time: Shut down the projector worker for 5 minutes during peak hours, then bring it back up. How many minutes until the event queue backlog is fully processed?

7. Question for the Next Round

Level 2 (Database) is complete! We've conquered connection pools, read replicas, cache, sharding, and CQRS.

Now we step into LEVEL 3: SYSTEMS THAT DEPEND ON EACH OTHER. When an application is split into hundreds of independent microservices, what happens when a single user request quietly triggers 30 internal network calls that all wait on each other?

The answer is in Session 22: When Services Are Split Apart (Monolith to Microservices & API Gateway).