Cassandra Data Model: Partition Key and Clustering Columns

← Back to the section

With Cassandra people usually go wrong in modelling, not in operations: they design tables the way they would in a relational database and then hit a wall where the query they need is simply impossible. The Cassandra data model follows from its architecture: data is spread across nodes, there is no JOIN, and that changes the order in which you design.

Wide-column: rows with a flexible set of columns

From the outside a Cassandra table looks relational — rows and columns, and a query language, CQL, with SQL-like syntax. Underneath it is different: physically the data is stored as wide rows — under one partition key sits a group of records, grouped and sorted. Hence the name "wide-column": one logical partition can hold anything from a single row to millions of ordered ones.

This is not a document store like MongoDB, and not tables with relations like PostgreSQL. The whole design effort goes into one question: how to choose the partition key.

The primary key: two different jobs

In Cassandra the primary key consists of two parts with different jobs.

CREATE TABLE messages (
    chat_id    uuid,          -- partition key: where the data lives
    sent_at    timestamp,     -- clustering column: order inside the partition
    message_id uuid,
    author     text,
    body       text,
    PRIMARY KEY ((chat_id), sent_at, message_id)
);

The partition key is the first part (here chat_id, in double parentheses). It decides which node holds the data: its hash gives a token, and the token gives a node (see the architecture). Every row with the same chat_id physically sits together, in one partition, on the same replicas.

The clustering columns are the remaining parts (sent_at, message_id). They set the order of rows inside the partition. Here the messages of one chat are stored sorted by time, so a range is read without sorting on the fly.

The same layout shows up in a small program: the partition key groups the rows, the clustering key sorts them inside the group.

live example

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class PartitionLayout {
    record Row(String chatId, String sentAt, String author) {}

    public static void main(String[] args) {
        List<Row> incoming = List.of(
                new Row("A", "10:07", "ann"), new Row("B", "10:02", "bob"),
                new Row("A", "10:02", "bob"), new Row("A", "10:05", "ann"),
                new Row("B", "10:09", "ann"));

        Map<String, List<Row>> partitions = new TreeMap<>();
        for (Row row : incoming) {
            partitions.computeIfAbsent(row.chatId(), k -> new ArrayList<>()).add(row);
        }
        partitions.forEach((key, rows) -> {
            rows.sort((x, y) -> x.sentAt().compareTo(y.sentAt()));
            System.out.println("partition " + key + ": " + rows.stream().map(Row::sentAt).toList());
        });

        List<Row> chatA = partitions.get("A");
        long slice = chatA.stream().filter(r -> r.sentAt().compareTo("10:02") > 0).count();
        System.out.println("chat_id = A AND sent_at > 10:02 -> " + slice + " adjacent of " + chatA.size());

        int all = partitions.values().stream().mapToInt(List::size).sum();
        System.out.println("author = 'ann' without a partition key -> read all " + all + " rows");
    }
}

The last two lines of output are the whole difference: a range inside a partition comes out as a contiguous slice, while a search on an ordinary column reads every partition in full.

What a query can and cannot do

The shape of the key leads to strict query rules that surprise newcomers:

• a query must name the partition key. Without it Cassandra does not know which node to look at and would have to walk the whole cluster, so WHERE chat_id = ? is mandatory;
• clustering columns can be filtered and range-scanned, but only left to right, in the order they are declared (you cannot filter on the second one while skipping the first);
• ordinary columns cannot be filtered (WHERE body = ? will not work). You can force it with ALLOW FILTERING, but that is a cluster-wide scan — in production it is almost never done;
• there is no JOIN and there are no subqueries. CQL does have GROUP BY, but grouping works only on a prefix of the primary key — arbitrary analytics is out of reach for this model.

-- Fast: partition key plus a time range
SELECT * FROM messages
WHERE chat_id = ? AND sent_at > ?;

-- Bad: no partition key — a cluster-wide scan
SELECT * FROM messages WHERE author = ?;  -- needs ALLOW FILTERING

Designing from queries

Hence the principle that turns a relational habit upside down: in Cassandra you design from queries, not from data. The order is:

1. write down every query the service will run;
2. design a table per query so that the query hits by partition key;
3. if the same fact is needed by several queries from different angles — store it in several tables.

So the same data is duplicated on purpose — that is denormalisation, and in Cassandra it is the norm, not a sin. Disk is cheap; a query without a partition key is not. Keeping the copies in agreement is the application's job (often by writing to several tables in one batch).

The practical conclusion: if you do not know in advance how the data will be queried, or the queries keep changing, Cassandra is the wrong tool — it rewards predictable access by key and punishes ad-hoc analytics.

Partition size traps

Another common mistake is a bad partition key that lets a partition grow out of hand. Two extremes:

• a "hot" key — if one key value collects disproportionately much data or traffic (say the partition key is a country, and all of Russia lands in one partition), that partition sits entirely on the same replicas and they become the bottleneck;
• unbounded growth — if rows are appended to a partition forever (the partition key is sensor_id and readings keep coming for years), it swells to an unmanageable size.

The cure is a composite partition key with a bucket: ((sensor_id, day), reading_ts), for instance — then each day's data forms its own partition of a sane size.

In short

• Physically Cassandra stores wide rows: under one partition key sits a group of sorted records. These are neither documents nor relational tables with relations.
• The primary key = partition key (picks the node, spreads the data) + clustering columns (set the order inside the partition).
• A query must name the partition key; ordinary columns cannot be filtered (ALLOW FILTERING is a cluster-wide scan, not for production); there is no JOIN and no subqueries, and GROUP BY only works on a primary key prefix.
• Tables are designed from queries, with data deliberately denormalised across several tables; keeping the copies in agreement is on the application.
• The partition key is chosen so that partitions are neither "hot" nor unboundedly growing (the bucket trick in a composite key).

What to read next

• Consistency and Replication in Cassandra: Tunable per Request — the guarantees writes and reads give on top of this model.
• Cassandra Architecture: a Ring With No Leader Node — why a query must name the partition key and how data is spread around the ring.
• Cassandra, PostgreSQL or MongoDB: when to reach for a wide-column NoSQL — the data models of three databases side by side.