TopolearnThe primary key is the most important design decision in Cassandra: it fixes which queries are possible and how data spreads.
CQL tables look like SQL tables, but the primary key has two parts with very different jobs:
- the partition key (first element) decides which partition, and therefore which nodes, a row lives on. Rows with the same partition key are stored together,
- the clustering columns (the rest) order rows within the partition, on disk.
PRIMARY KEY (user_id) is a single-row partition per user. PRIMARY KEY ((sensor_id, day), ts) has a composite partition key (the inner parentheses), so each sensor-day is one partition, and rows within it are sorted by ts. PRIMARY KEY (channel_id, sent_at, msg_id) stores a channel's messages in time order, with msg_id as a tie-breaker.
The full primary key uniquely identifies a row. Inserting another row with the same key overwrites it; there is no duplicate-key error.
A good partition key spreads data evenly (high cardinality, no celebrity values) and keeps partitions bounded in size, while matching the query: you'll almost always need to supply the whole partition key to read.
Columns marked STATIC are stored once per partition and shared by all its rows, useful for per-partition attributes such as a channel name. Table options like default_time_to_live, compaction and CLUSTERING ORDER BY go in the WITH clause.
Example
CREATE TABLE shop.messages (
channel_id uuid,
sent_at timestamp,
msg_id timeuuid,
channel_name text STATIC, -- one value per partition
author text,
body text,
PRIMARY KEY ((channel_id), sent_at, msg_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, msg_id DESC);
CREATE TABLE shop.readings (
sensor_id text,
day date,
ts timestamp,
value double,
PRIMARY KEY ((sensor_id, day), ts) -- composite partition key
);Remember
- PRIMARY KEY ((partition key), clustering columns...)
- Partition key = placement; clustering columns = sort order inside a partition
- Same primary key means the same row: writes overwrite
- STATIC columns are stored once per partition
Watch out for
- Using a low-cardinality partition key like country, creating a few giant hot partitions.