4. The data model

This is the most important chapter in the guide. A Cassandra cluster with a good data model is fast, stable, and boring. A Cassandra cluster with a bad data model cannot be rescued by tuning, better hardware, or more nodes — and the failure usually arrives six months in, when the data has grown enough for the mistake to matter.

The primary key does two jobs

CQL looks like SQL, which makes this the easiest thing in Cassandra to get wrong. In SQL, a primary key identifies a row. In Cassandra it does that and decides where the data physically lives and in what order it is stored.

CREATE TABLE messages (
  room_id    uuid,
  sent_at    timestamp,
  message_id timeuuid,
  sender     text,
  body       text,
  PRIMARY KEY ( (room_id), sent_at, message_id )
);
--              └────────┘  └──────────────────┘
--            partition key   clustering columns

The partition key is hashed to a token, which picks the node (chapter 2). All rows sharing a partition key live together, on the same replicas, in the same SSTables.

The clustering columns determine the sort order of rows inside that partition, on disk.

Those two sentences generate every rule that follows.

   partition "room_42"  ── lives entirely on nodes B, C, D ──
   ┌──────────────────────────────────────────────────────────┐
   │ sent_at              message_id   sender   body          │
   │ 2026-08-01 09:00:00  aaa...       alice    "morning"     │  sorted
   │ 2026-08-01 09:04:12  bbb...       bob      "hi"          │  by
   │ 2026-08-01 09:04:12  ccc...       carol    "hello"       │  clustering
   │ 2026-08-01 11:30:00  ddd...       alice    "lunch?"      │  columns
   └──────────────────────────────────────────────────────────┘

   partition "room_99"  ── lives on nodes A, B, E ── entirely elsewhere

Notice the two rows with identical sent_at — that is why message_id is in the key. Without it the second write would silently overwrite the first, because in Cassandra the primary key is the identity, and writing the same key again is an update, not a duplicate. There is no such thing as an insert that fails because the row exists (except with IF NOT EXISTS, which is expensive — chapter 11).

The rules that follow

1. You must give the full partition key

SELECT * FROM messages WHERE room_id = ?;                    -- ✓
SELECT * FROM messages WHERE sent_at > ?;                    -- ✗ rejected

Without the partition key, Cassandra does not know which node to ask, so the query would have to visit every node in the cluster. It refuses.

2. Clustering columns must be used left to right

PRIMARY KEY ((room_id), sent_at, message_id)

WHERE room_id=? AND sent_at=?                       -- ✓
WHERE room_id=? AND sent_at>? AND sent_at<?         -- ✓ range on the last
                                                    --   one you restrict
WHERE room_id=? AND message_id=?                    -- ✗ skipped sent_at

The data is sorted by sent_at then message_id, like a phone book by surname then first name. You cannot efficiently find everyone named “James” without a surname. This is the same prefix rule as a multicolumn B-tree index in PostgreSQL, applied to physical row order.

You may range-scan on the last clustering column you restrict, and only that one.

3. `ORDER BY` can only follow the disk order

CREATE TABLE messages (...)
  WITH CLUSTERING ORDER BY (sent_at DESC, message_id DESC);

SELECT * FROM messages WHERE room_id=? ORDER BY sent_at DESC;  -- ✓ free
SELECT * FROM messages WHERE room_id=? ORDER BY sender ASC;    -- ✗ rejected

There is no sort step. Rows come back in the order they are stored, or reversed. If you always read newest-first, declare CLUSTERING ORDER BY (... DESC) — then “the latest 50 messages” is a read of the first 50 rows in the partition, which is the cheapest possible read.

4. No joins, no aggregates worth using, no ad-hoc filtering

There is no JOIN. count(*), sum() and friends exist but only make sense within a single partition — across a table they are a distributed full scan and will time out.

5. `ALLOW FILTERING` is a trap

SELECT * FROM messages WHERE sender = 'alice' ALLOW FILTERING;

This works. It reads every row on every node, then discards the ones that do not match. On a test dataset it is instant. In production it takes down the cluster.

Treat ALLOW FILTERING in application code as a bug. The two legitimate uses are interactive debugging in cqlsh, and a query already constrained to a single partition where you are filtering a handful of rows.

Query-first design

Because a table can only answer questions its primary key anticipated, you do not design a schema and then write queries. You write down the queries and build a table for each one.

Worked example. A chat application needs:

   Q1: the most recent messages in a room
   Q2: all messages a user has sent
   Q3: look up a message by its id
   Q4: which rooms a user belongs to

The relational instinct is one messages table with indexes. In Cassandra it is four tables:

-- Q1
CREATE TABLE messages_by_room (
  room_id uuid, bucket text, sent_at timestamp, message_id timeuuid,
  sender text, body text,
  PRIMARY KEY ((room_id, bucket), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id DESC);

-- Q2
CREATE TABLE messages_by_sender (
  sender text, bucket text, sent_at timestamp, message_id timeuuid,
  room_id uuid, body text,
  PRIMARY KEY ((sender, bucket), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id DESC);

-- Q3
CREATE TABLE messages_by_id (
  message_id timeuuid PRIMARY KEY,
  room_id uuid, sender text, sent_at timestamp, body text
);

-- Q4
CREATE TABLE rooms_by_user (
  user_id uuid, room_id uuid, joined_at timestamp, room_name text,
  PRIMARY KEY ((user_id), room_id)
);

Every message is written to three tables. The body is stored three times.

This is correct. You are trading disk — cheap, and getting cheaper — for the ability to answer each query with a single-partition read on a known node. There is no join to coordinate, no index to consult, no scatter-gather. Each query is one round trip to one set of replicas.

The cost you do pay is real, and you should name it: the application is responsible for keeping the copies in sync. There is no mechanism that guarantees all three writes happen. Chapter 11 covers logged batches, which help, and their limits.

Partition sizing: the thing that actually kills clusters

If you remember one number from this guide, make it this one.

Keep partitions under about 100 MB and 100,000 rows.

Partitions are the unit of everything: reads, repair, compaction, caching. A partition is never split across nodes — one partition lives entirely on RF nodes and is read as a unit.

Consequences of an oversized partition:

Reading it means reading the whole thing into heap. A 2 GB partition causes long garbage-collection pauses, then OutOfMemoryError.
Compaction must process it as one unit, so it needs the memory and the time.
Repair streams it as one unit.
The three nodes holding it get all the traffic for it while everyone else idles — a hotspot.

The three ways partitions get too big

Unbounded growth over time. PRIMARY KEY ((sensor_id), reading_time) looks fine. A sensor reporting once a second produces 31 million rows a year, in one partition. This is the most common Cassandra mistake there is.

Low-cardinality partition keys. PRIMARY KEY ((country), user_id) gives you about 200 partitions for a billion users. Cassandra distributes by partition key; 200 keys cannot be spread across 50 nodes.

Skew. PRIMARY KEY ((customer_id), event_time) is fine until one customer is a thousand times bigger than the rest.

The fix: bucketing

Add something to the partition key that bounds its size.

Time bucketing, for time-series:

CREATE TABLE readings (
  sensor_id uuid,
  day       date,          -- ← the bucket
  reading_time timestamp,
  value     double,
  PRIMARY KEY ((sensor_id, day), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);

One partition per sensor per day. 86,400 rows — comfortably bounded, forever.

The price is that a query spanning a week is seven partition reads instead of one. Do them in parallel, asynchronously; seven parallel single-partition reads is still fast, and it is vastly better than one 31-million-row partition.

Choose the bucket size so a typical partition lands in the tens of megabytes. Hourly for high-frequency sensors, daily for moderate, monthly for slow. Getting this wrong in the other direction — buckets so small that a common query needs 500 of them — is also a real problem.

Hash bucketing, when there is no natural time dimension:

PRIMARY KEY ((customer_id, bucket), event_id)
-- bucket = hash(event_id) % 10

Ten partitions per customer instead of one. Reading everything means ten parallel queries.

Measure it

nodetool tablehistograms my_keyspace.readings
# Percentile   Partition Size   Cell Count
# 50%                    1109           50
# 95%                  126934         2299
# 99%                 3379391        73457    ← 3 MB, fine
# Max               186563160      1629722    ← 186 MB, a problem

nodetool tablestats my_keyspace.readings | grep -i "partition"
#   Compacted partition maximum bytes: 186563160

Watch the max, not the mean. The average is always fine. It is the one partition at the 99.9th percentile that causes the incident.

Cassandra will also warn you in the logs, and you can make it refuse outright:

# cassandra.yaml — guardrails (4.1+)
partition_size_warn_threshold: 100MiB
partition_size_fail_threshold: 1GiB
partition_tombstones_warn_threshold: 1000

Types worth knowing

CREATE TABLE example (
  id          uuid,
  created     timeuuid,      -- v1 UUID: sortable by time, unique
  name        text,
  amount      decimal,       -- exact. NOT double, for money.
  flags       set<text>,     -- unordered, unique
  history     list<text>,    -- ordered — see the warning
  attributes  map<text,text>,
  address     frozen<addr>,  -- a UDT, written and read as one blob
  embedding   vector<float,384>,   -- 5.0+, for similarity search
  PRIMARY KEY (id)
);

Collections are cells, not tables. Each element of a set or map is stored as its own cell with its own timestamp, which is what lets you add one element without rewriting the whole thing. Keep them small — a few hundred elements. collection_size_warn_threshold exists for a reason.

Avoid list. Appending is fine, but a positional update or delete requires a read-before-write internally, which is slow and not idempotent under retries. Use set if order does not matter, or clustering columns if it does.

frozen<> means “treat this as one opaque value.” A frozen collection or UDT is rewritten in full on every update, but it can be part of a primary key.

Counters live in their own world — a table may contain counter columns or normal columns, never both — and they are not idempotent. Chapter 11.

TTLs are per cell and are the idiomatic way to expire data:

INSERT INTO sessions (id, data) VALUES (?, ?) USING TTL 86400;
ALTER TABLE sessions WITH default_time_to_live = 86400;

An expired cell becomes a tombstone. That is not free, and chapter 7 explains why it matters.

Secondary indexes

You will want to query by a non-key column. There are three mechanisms and one right answer.

Legacy secondary indexes (CREATE INDEX) build a hidden index table local to each node. A query using one must ask every node in the cluster, wait for all of them, and merge. That is a scatter-gather, and its cost grows with cluster size regardless of how many rows match.

They are acceptable in exactly one situation: when the query also specifies a partition key, so only one set of replicas is involved.

CREATE INDEX ON messages (sender);
SELECT * FROM messages WHERE room_id=? AND sender='alice';   -- ok: one partition
SELECT * FROM messages WHERE sender='alice';                 -- scatter-gather

They are also actively bad on high-cardinality columns (an index entry per row) and on very low-cardinality ones (enormous index partitions).

SAI — Storage-Attached Indexes (Cassandra 5.0) are the modern replacement and a genuine improvement. They are built into the SSTable lifecycle rather than being a separate table, so they use far less space, index multiple columns efficiently, support range queries and AND across several indexed columns, and cost much less to write.

CREATE INDEX ON messages (sender) USING 'sai';
CREATE INDEX ON products (price) USING 'sai';
SELECT * FROM products WHERE price > 100 AND category = 'books';

SAI is a real feature and worth using. But it is still a scatter-gather when the partition key is absent — better constants, same asymptotics. It does not turn Cassandra into a relational database, and a denormalised table is still faster for a query you run constantly.

Materialized views ask Cassandra to maintain a denormalised copy for you:

CREATE MATERIALIZED VIEW messages_by_sender AS
  SELECT * FROM messages WHERE sender IS NOT NULL AND room_id IS NOT NULL
  PRIMARY KEY ((sender), room_id, sent_at);

They have been marked experimental since 3.0 and remain so. There are known, unfixed correctness issues where a view can drift permanently out of sync with its base table, and no supported way to detect or repair the drift.

Do not use them in production. Write to a second table from your application instead. The extra code is worth the absence of silent data corruption.

Anti-patterns

The queue. Write jobs, read the oldest, delete it. This produces a partition that is almost entirely tombstones, and every read scans through all of them to find the few live rows. It gets slower every hour until it stops working. Chapter 7 explains the mechanism; chapter 14 has the war story. Use a queue.

Read-modify-write. SELECT, change it in the app, UPDATE. There is no locking, so concurrent writers silently clobber each other. Write immutable rows, or use collections and counters, or accept the cost of a lightweight transaction.

Unbounded partitions. Covered above. It is the number one cause of Cassandra failure.

Using it as a relational database. Normalised tables plus indexes plus ALLOW FILTERING gives you a very expensive, very slow relational database with no joins and no transactions.

SELECT * on a wide table. Fetches every column of every row, including big text blobs. Name the columns you need.

Unlogged batches to spread writes around. A batch spanning many partitions makes one coordinator responsible for all of them, which is slower than issuing the writes in parallel. Chapter 11.

Checklist for a new table

□ Which exact query does this table serve? Write it down.
□ Is the full partition key in that query's WHERE clause?
□ What is the maximum size this partition can ever reach?
   → over ~100 MB or ~100k rows? Add a bucket to the partition key.
□ Do the clustering columns match the order the query wants?
   → set CLUSTERING ORDER BY so no reversal is needed
□ Is any partition much hotter than the others?
□ Is there a natural TTL? Set default_time_to_live.
□ Does this table involve deletes?
   → if so, will the pattern create tombstone-heavy partitions? (ch. 7)
□ Am I about to write ALLOW FILTERING? Redesign instead.

What to take from this chapter

The primary key decides placement (partition key) and on-disk order (clustering columns).
You must supply the full partition key; clustering columns are used left to right; ORDER BY can only follow or reverse disk order.
Design from queries, not from entities. One table per access pattern, denormalised, written from the application.
Keep partitions under ~100 MB and ~100k rows. Bucket by time or by hash. Watch the max in nodetool tablehistograms, not the mean.
Prefer set over list; use decimal not double for money; use TTLs for expiry.
Separate tables beat indexes; SAI beats legacy 2i; materialized views are experimental and should be avoided.
ALLOW FILTERING in application code is a bug.

Next: what actually happens when you write a row.

Written by Sepuri Sai Krishna