Path 1 · lesson 7 of 8
Data modelling for access patterns
Postgres models the data and joins at read time; Cassandra models the queries and writes data once per query. How to design each and keep copies in step.
What you will learn
- Design a normalised Postgres schema and the indexes that serve its main access pattern
- Design Cassandra tables from a list of queries, one table per query
- Keep denormalised copies in step with logged batches, and know their cost and limits
- Decide, for a new feature, whether the model absorbs it or needs a new table
Before you start
The concept
The two engines in this academy start data modelling from opposite ends. In Postgres you model the data: each fact is stored once (normalisation), in tables linked by keys, and queries combine them with joins when they run. In Cassandra you model the queries: the documentation puts it plainly, "In Cassandra, data modeling is query-driven". Each query gets a table shaped exactly for it, and the same fact is written to every table that needs it (denormalisation).
| Postgres (data-first) | Cassandra (query-first) | |
|---|---|---|
| Start from | Entities and their relationships | The list of queries the application runs |
| Each fact is stored | Once | Once per table that needs it |
| Combining data | Joins at read time | No joins; data is combined at write time |
| A new query next month | Usually just a new query, maybe an index | Usually a new table, backfilled |
| Changing a fact | Update one row | Update every copy |
Why it exists
A schema outlives the code that first used it. In Postgres, a normalised model keeps working as new questions arrive, because joins and indexes can answer them. In Cassandra, the tables encode today's queries, so a model designed without the full list of queries forces migrations later. Both models are good; the expensive mistake is using one engine with the other's habits: joins expected from Cassandra, or Cassandra-style duplication in Postgres without a reason.
How it works inside
Postgres: normalised, joined at read time
The lab's shop has customers, products, orders and order items, each stored once: 100,000 customers, 5,000 products, 1,000,000 orders and 3,000,000 order lines. The main access pattern is a customer's 10 latest orders with their items and product names, served by an index on orders (customer_id, created_at DESC) and the primary keys.
Query planThree tables joined for one customer's latest orders
Real database output Nested Loop (actual rows=30.00 loops=1)
Buffers: shared hit=122 read=41
-> Nested Loop (actual rows=30.00 loops=1)
Buffers: shared hit=32 read=41
-> Limit (actual rows=10.00 loops=1)
Buffers: shared hit=10 read=3loops=10), then the product for each item (loops=30). Joins are cheap when every step is indexed.A month later, finance asks for revenue per product for shipped orders. The normalised model answers it with no schema change: the planner picks a different strategy, hash joins over the whole tables, because this question reads most of the data.
Query planA new question, the same schema
Real database output -> Parallel Hash Join
Hash Cond: (i.order_id = o.id)
-> Parallel Seq Scan on order_items i
-> Parallel Hash
-> Parallel Seq Scan on orders o
Filter: (status = 'shipped'::text)Database outputRenaming a product
Real database outputTime: 3.116 ms
order_lines_showing_new_name
------------------------------
600Cassandra: one table per query
The same shop in Cassandra starts from the queries: "orders of a customer, newest first" and "an order by its id". Each becomes a table whose primary key answers it: orders_by_customer keyed by ((customer_id), created_at, order_id) and orders_by_id keyed by order_id. Trying to use them the relational way fails:
Database outputAsking Cassandra for a join
Real database outputSyntaxException: line 1:47 mismatched input 'o' expecting EOF (...o.order_id FROM shop.orders_by_customer [o]...)
Database outputLooking up an order by id in the table keyed by customer
Real database outputInvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "order_id" cannot be restricted as preceding column "created_at" is not restricted"
Cassandra has materialized views, which would maintain a second table automatically. They are off by default in 5.0; the shipped cassandra.yaml says they "are considered experimental and are not recommended for production use".
Database outputCreating a materialized view
Real database outputInvalidRequest: Error from server: code=2200 [Invalid query] message="Materialized views are disabled. Enable in cassandra.yaml to use."
Keeping the copies in step: logged batches
The application writes each order to both tables. A logged batch makes the pair atomic in the sense the documentation gives: all of its writes eventually succeed, or none do.
Database outputOne order, written to both tables in one logged batch
Real database outputBy customer: order_id | status | total ----------+--------+------- 7 | paid | 99.50 (1 rows) By order id: customer_id | status | total -------------+--------+------- 4242 | paid | 99.50
Read traceWhat a logged batch does, traced
Real database outputDetermining replicas for atomic batch Appending to commitlog Adding to batches memtable Appending to commitlog Adding to orders_by_customer memtable Appending to commitlog Adding to orders_by_id memtable Appending to commitlog Adding to batches memtable
batches table), then the two real writes are applied, then the batchlog entry is removed: four commit-log appends for two writes. If the coordinator dies halfway, another node replays the batchlog. Atomicity costs a second write.Batches are for keeping related writes together, not for speed. Cassandra warns when they are misused:
Database outputAn unlogged batch across 20 partitions
Real database outputWarnings : Unlogged batch covering 20 partitions detected against table [shop.orders_by_id]. You should use a logged batch for atomicity, or asynchronous writes for performance.
Database outputLarge batches across partitions
Real database output40 rows of about 200 bytes: Warnings : Batch for [shop.orders_by_id] is of size 11760, exceeding specified threshold of 5120 by 6640. 400 rows of about 200 bytes: /tmp/big.cql:403:InvalidRequest: Error from server: code=2200 [Invalid query] message="Batch too large"
batch_size_warn_threshold, batch_size_fail_threshold). A batch should hold the few writes that must stay together, such as one order's copies.Architecture
Drawing diagram…
Two diagrams. Postgres: customers, orders, order items and products are linked tables; both a customer's latest orders and revenue per product are answered by joining them at read time. Cassandra: placing an order writes to orders_by_customer and orders_by_id in a logged batch; each query reads exactly one of those tables.
flowchart LR
subgraph PG["Postgres: store once, join when reading"]
CU["customers"] --- OR["orders"]
OR --- OI["order_items"]
OI --- PR["products"]
Q1["Customer's latest orders"] -.->|join at read time| OR
Q2["Revenue per product"] -.->|join at read time| OI
end
subgraph CA["Cassandra: one table per query, write to all"]
W["Place order"] -->|logged batch| T1["orders_by_customer"]
W -->|logged batch| T2["orders_by_id"]
Q3["Customer's latest orders"] --> T1
Q4["Order by id"] --> T2
end- In Postgres, adding a question rarely changes the tables; it may add an index.
- In Cassandra, adding a question usually adds a table, a write to the batch, and a backfill of existing data.
- Updating a fact is one row in Postgres and one row per copy in Cassandra, and every copy must be found.
Step by step
Drawing diagram…
Sequence diagram. The application sends a batch of two inserts. The coordinator stores the whole batch in the batchlog, applies the insert to orders_by_customer and to orders_by_id, removes the batch from the batchlog and returns success. A note says that if the coordinator fails before removal, another node replays the batch.
sequenceDiagram participant App as Application participant C as Coordinator participant BL as Batchlog participant T1 as orders_by_customer participant T2 as orders_by_id App->>C: BEGIN BATCH (two inserts) APPLY BATCH C->>BL: store the whole batch C->>T1: apply insert C->>T2: apply insert C->>BL: remove the batch C-->>App: success Note over BL: if the coordinator fails before removal, another node replays the batch
- The batchlog guarantees the writes all happen eventually, not that they appear at the same instant: a reader can see one table updated before the other.
- Writes to different partitions are not isolated from each other; only writes within one partition are.
- The batchlog write is the extra cost, which is why logged batches are for writes that must stay together, not for throughput.
In a real system
- In Postgres, normalise first, index for the main access patterns, and denormalise only a measured hot path (a cached count, a summary table) with a clear owner for keeping it correct.
- In Cassandra, write down every query before designing tables: its filters, sort order and expected volume. Each one maps to a table whose partition key is the equality filter and whose clustering columns give the order.
- Write the copies of one entity in a small logged batch, and keep batches to that entity: a few writes, well under 5 KiB.
- Plan for backfills. A new Cassandra table for a new query must be filled from existing data, usually with a Spark job or a script reading the source table.
- Store a fact once where it changes often. A product name copied into millions of Cassandra rows is costly to change; store the product id in the copies and look up the name, or accept old names on old orders.
Wrong, then right
A team moves an order service from Postgres to Cassandra and keeps the relational model: one table per entity, joined in application code.
Incorrect implementation
// Tables: customers(id), orders(id), order_items((order_id), product_id), products(id)List<Order> latest = cql.execute( "SELECT * FROM orders WHERE customer_id = ? ALLOW FILTERING", customerId) // scans every partition .all().stream().map(this::toOrder) .sorted(comparing(Order::createdAt).reversed()).limit(10).toList();for (Order o : latest) { o.items = cql.execute("SELECT * FROM order_items WHERE order_id = ?", o.id()).all(); // 10 queries for (Item i : o.items) { i.product = cql.execute("SELECT * FROM products WHERE id = ?", i.productId()).one(); // 30 more }}Why it is written this way
- Orders are partitioned by order id, so finding a customer's orders needs ALLOW FILTERING: a scan of every partition on every node.
- The join is done by hand: 41 round trips for one page, each a separate request to the cluster.
Why it fails
- The customer lookup scans the whole table, and gets slower as the table grows; the lesson on indexes showed the scan reading far more rows than it returns.
- Page latency is the sum of 41 sequential round trips, and any one slow replica slows the page.
Corrected implementation
-- Query: a customer's latest orders with their lines, newest first. One partition read.CREATE TABLE shop.orders_by_customer ( customer_id int, created_at timestamp, order_id bigint, line_no int, product_id int, product_name text, -- copied at order time: the name the customer saw qty int, PRIMARY KEY ((customer_id), created_at, order_id, line_no)) WITH CLUSTERING ORDER BY (created_at DESC, order_id ASC, line_no ASC);-- Query: an order by its id.CREATE TABLE shop.orders_by_id ( order_id bigint, line_no int, customer_id int, created_at timestamp, product_id int, product_name text, qty int, PRIMARY KEY ((order_id), line_no));-- Each order is written to both tables in one small logged batch.Why it is written this way
- The page is one query reading one partition, already in the order the page shows.
- Copying the product name is deliberate: an order shows what the customer bought, even if the product is renamed later.
Trade-offs
- Every order is written twice, through the batchlog, so writes cost more than before.
- A customer with years of orders makes a large partition; a busy business would add a time bucket to the partition key (lesson 6).
- Questions nobody listed, like revenue per product, need another table and a backfill, or an analytics system fed from the same writes.
Configuration
Limits and switches that shape modelling in Cassandra 5.0, from the shipped cassandra.yaml.
batch_size_warn_thresholdDefault 5KiB
- What it does
- Batches across partitions larger than this log and return a warning.
- When to change it
- Keep batches to the copies of one entity, well under it.
batch_size_fail_thresholdDefault 50KiB
- What it does
- Batches across partitions larger than this are rejected with "Batch too large".
- When to change it
- Do not raise it to make a bulk load work; send separate asynchronous writes instead.
unlogged_batch_across_partitions_warn_thresholdDefault 10
- What it does
- An unlogged batch touching more partitions than this returns a warning.
- When to change it
- Unlogged batches are only useful for several writes to the same partition.
materialized_views_enabledDefault false
- What it does
- Whether materialized views can be created. The file marks them experimental and not recommended for production.
- When to change it
- Maintain query tables from the application instead.
Hands-on exercise
Model the same shop both ways
Run the lab and see, on real Postgres 18 and Cassandra 5.0, a three-table join served by indexes, a new question answered without a schema change, a one-row rename, and in Cassandra the absence of joins, the primary-key rule, materialized views disabled, a logged batch's batchlog writes, and the batch warnings and limits.
Steps · 0 of 5 done
What you should see
Postgres joins three tables in about 160 pages using nested loops over index scans, plans the revenue question with hash joins and sequential scans, and renames a product in one row. Cassandra rejects the join as a syntax error, rejects the id lookup on the customer table because created_at is not restricted, refuses to create a materialized view, writes the logged batch to the batchlog, both tables and the batchlog again, warns about an unlogged batch over 20 partitions and a batch over 5 KiB, and rejects one over 50 KiB.
What can go wrong
Relational habits in Cassandra
- Symptom
- Pages make dozens of sequential queries, or use ALLOW FILTERING to find related rows.
- Cause
- One table per entity, joined in application code.
- Fix
- Design one table per query, with the data the query needs copied into it.
Batches used for bulk loading
- Symptom
- "Batch too large" errors, or coordinators with high latency during imports.
- Cause
- Thousands of unrelated rows grouped into batches for speed.
- Fix
- Send individual asynchronous writes with bounded concurrency; batch only the copies of one entity.
Copies that drift apart
- Symptom
- An order shows one status in the customer's list and another on its detail page.
- Cause
- The two tables were written separately, and one write failed or was never made by a newer code path.
- Fix
- Write all copies in one logged batch through one function, and run a periodic job that compares them.
Denormalising in Postgres too early
- Symptom
- Counters and copied names in several Postgres tables disagree after a bug or a partial update.
- Cause
- Copies were added for speed before measuring whether a join was actually slow.
- Fix
- Keep one source of truth; add an index or a materialized view first, and denormalise only a measured hot path.
How to troubleshoot
A Postgres join is slow
Check
EXPLAIN (ANALYZE, BUFFERS) <query>
- How to read it
- Look at each join node's inner side: an index scan with a high
loopscount is fine when each lookup is cheap; a sequential scan inside a nested loop means a missing index on the join column.
Cassandra rejects a query with "cannot be restricted as preceding column ... is not restricted"
Check
DESCRIBE TABLE <ks>.<table>
- How to read it
- The query does not follow the primary key: partition key by equality, then clustering columns in order. It needs a different table, or SAI for rare queries.
Batch warnings in Cassandra logs
Check
grep -E 'Batch for|Unlogged batch' /var/log/cassandra/system.log
- How to read it
- The warning names the tables; find the code that builds those batches and check it only groups the copies of one entity.
Key takeaways
- Postgres models the data: store each fact once and join at read time; indexed joins are cheap and new questions need no schema change.
- Cassandra models the queries: one table per query, keyed by its filters, with data copied into each table.
- CQL has no joins, and a table can only be queried along its primary key.
- Logged batches keep copies in step through the batchlog, at the cost of extra writes; keep them small and to one entity.
- Materialized views are experimental and disabled by default in Cassandra 5.0; maintain query tables yourself.
Practical check
Small scenarios, one decision each. Pick what you would do, then read why.
Scenario 1 of 3
A Cassandra app has videos_by_user ((user_id), uploaded_at). Product adds a page listing a video's details by video_id, used on every video view.
What should the team do?
Scenario 2 of 3
A Postgres app is slow on its order page, which joins orders, items and products. EXPLAIN shows a nested loop with a sequential scan of order_items inside it, repeated for each order.
What is the first fix to try?
Scenario 3 of 3
An import job loads 2 million rows into a Cassandra table by grouping them into logged batches of 1,000 rows across many partitions. It fails with "Batch too large" and the coordinators' latency spikes.
How should the import write the rows?
References
References: Apache Cassandra 5.0 documentation: Introduction to data modelling (query-driven) ↗ · Apache Cassandra 5.0 documentation: Defining application queries ↗ · Apache Cassandra 5.0 documentation: Data manipulation (BATCH) ↗ · Apache Cassandra 5.0 documentation: Materialized views ↗ · Apache Cassandra 5.0 default cassandra.yaml (batch thresholds, materialized_views_enabled) ↗ · PostgreSQL 18 documentation: Constraints (primary and foreign keys) ↗ · PostgreSQL 18 documentation: Using EXPLAIN ↗ · Report a problem