Skip to content

Path 1 · lesson 7 of 8

Intermediate30 minPostgreSQL 18.7 · Apache Cassandra 5.0.9, both in Docker

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 fromEntities and their relationshipsThe list of queries the application runs
Each fact is storedOnceOnce per table that needs it
Combining dataJoins at read timeNo joins; data is combined at write time
A new query next monthUsually just a new query, maybe an indexUsually a new table, backfilled
Changing a factUpdate one rowUpdate every copy
Where each engine puts the work: Postgres at read time, Cassandra at write time.

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=3
163 pages in total for 30 rows from three tables of millions of rows. Each step is an index lookup: the customer's 10 orders, then 3 items for each order (loops=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)
Not fast, since it reads every order, but available on day one. In a query-first model, this question would need a new table and a backfill.

Database outputRenaming a product

Real database output
Time: 3.116 ms
 order_lines_showing_new_name
------------------------------
                          600
One row updated in 3 ms; all 600 order lines that reference the product show the new name, because the name is stored once.

Cassandra: 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 output
SyntaxException: line 1:47 mismatched input 'o' expecting EOF (...o.order_id FROM shop.orders_by_customer [o]...)
CQL has no JOIN; the parser does not even accept a table alias.

Database outputLooking up an order by id in the table keyed by customer

Real database output
InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "order_id" cannot be restricted as preceding column "created_at" is not restricted"
A table can only be queried along its primary key, in order. The id lookup needs its own table.

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 output
InvalidRequest: Error from server: code=2200 [Invalid query] message="Materialized views are disabled. Enable in cassandra.yaml to use."
Keeping query tables in step is the application's job in a default Cassandra 5.0 cluster.

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 output
By customer:
order_id | status | total
----------+--------+-------
7 |   paid | 99.50
(1 rows)
By order id:
customer_id | status | total
-------------+--------+-------
4242 |   paid | 99.50
Each query reads one partition of its own table. Neither needs the other.

Read traceWhat a logged batch does, traced

Real database output
Determining 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
The batch is first written to the batchlog (the 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 output
Warnings :
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.
Grouping unrelated writes makes one coordinator do all the work and wait for every partition's replicas. Separate asynchronous writes spread the load.

Database outputLarge batches across partitions

Real database output
40 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"
A warning above 5 KiB and a hard failure above 50 KiB, the 5.0 defaults (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
Where the work happens in each model.
  • 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
A logged batch, as in the lab's trace.
  1. 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.
  2. Writes to different partitions are not isolated from each other; only writes within one partition are.
  3. 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

OrderRepository.javaJava
// 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

schema/orders.cqlCQL
-- 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 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 loops count 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