Cassandra Clustering Columns

Clustering columns are the second component of a composite primary key. After the partition key determines which node stores a group of rows, clustering columns determine the sort order of those rows within the partition. They are what make Cassandra powerful for time-series, event logs, and any data that benefits from reading in a specific sequence.

The Filing Cabinet Analogy

Imagine a filing cabinet with many drawers. The drawer label is the partition key — it tells you which drawer to open. Inside each drawer, folders are sorted alphabetically or by date. The sort rule for those folders is the clustering column. Finding a specific folder is instant once you open the right drawer because the sort order is predictable.

Partition Key  ──▶  Selects the Drawer
Clustering Col ──▶  Determines Sort Order Inside the Drawer

Drawer: user_id = 'alice'
  ├── Folder: 2024-01-01 (oldest)
  ├── Folder: 2024-01-02
  ├── Folder: 2024-01-03
  └── Folder: 2024-06-15 (newest)

Defining Clustering Columns

CREATE TABLE user_activity (
  user_id     UUID,
  event_time  TIMESTAMP,
  event_type  TEXT,
  details     TEXT,
  PRIMARY KEY (user_id, event_time)
);

user_id is the partition key. event_time is the clustering column. All events for a given user live in one partition, sorted chronologically by event_time.

Partition: user_id = 'alice-uuid'

event_time               | event_type | details
─────────────────────────────────────────────────────
2024-05-01 08:00:00      | login      | desktop
2024-05-01 08:05:00      | view_page  | /dashboard
2024-05-01 08:10:00      | purchase   | order-099
2024-05-01 08:30:00      | logout     |

Multiple Clustering Columns

You can have more than one clustering column. The first clustering column sorts the rows at the top level. When the first column's values are equal, the second clustering column provides the sub-sort, and so on.

CREATE TABLE game_scores (
  game_id     TEXT,
  level       INT,
  player_id   UUID,
  score       INT,
  PRIMARY KEY (game_id, level, player_id)
);

Partition: game_id = 'chess-pro'

level | player_id | score
──────────────────────────────────
1     | uuid-A    | 980
1     | uuid-B    | 870
1     | uuid-C    | 760
2     | uuid-A    | 1200
2     | uuid-B    | 1100

Rows sort first by level, then by player_id within each level.

Controlling Sort Order with CLUSTERING ORDER BY

By default, clustering columns sort in ascending (ASC) order. Use the CLUSTERING ORDER BY clause to reverse the order to descending (DESC), which is useful when you always want the newest events first.

CREATE TABLE messages (
  room_id    TEXT,
  sent_at    TIMESTAMP,
  sender     TEXT,
  body       TEXT,
  PRIMARY KEY (room_id, sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);

Partition: room_id = 'team-chat'

sent_at                  | sender | body
──────────────────────────────────────────────────
2024-06-15 16:55:00      | carol  | See you tomorrow
2024-06-15 16:50:00      | bob    | Meeting is done
2024-06-15 16:45:00      | alice  | Starting now
2024-06-15 16:40:00      | bob    | Be there soon

The most recent message always appears first, making inbox-style queries extremely fast.

Querying with Clustering Columns

Clustering columns support range queries within a partition. You can filter by equality or by range using >, <, >=, and <=.

-- All events for alice on 2024-05-01:
SELECT * FROM user_activity
WHERE user_id = [alice-uuid]
  AND event_time >= '2024-05-01 00:00:00'
  AND event_time <  '2024-05-02 00:00:00';

-- Last 10 messages in a chat room:
SELECT * FROM messages
WHERE room_id = 'team-chat'
LIMIT 10;

The Column Order Rule

You must provide clustering column filters in the order they appear in the primary key definition. You cannot skip a clustering column and filter on a later one without using ALLOW FILTERING.

-- Table: game_scores PRIMARY KEY (game_id, level, player_id)

-- Valid: filter by first clustering column
SELECT * FROM game_scores WHERE game_id='chess-pro' AND level = 1;

-- Valid: filter by both clustering columns
SELECT * FROM game_scores WHERE game_id='chess-pro'
  AND level = 1 AND player_id = [uuid-A];

-- Invalid without ALLOW FILTERING: skips 'level'
SELECT * FROM game_scores WHERE game_id='chess-pro'
  AND player_id = [uuid-A];

Clustering Columns and Storage

On disk, Cassandra stores each partition as a sorted sequence of cells. Clustering column values are physically embedded in the storage key of each cell. This means range reads on clustering columns are sequential disk reads — extremely fast on modern hardware.

Physical Storage (simplified):

Partition key: 'chess-pro'
  Cell: [chess-pro | level=1 | player_id=uuid-A] = score:980
  Cell: [chess-pro | level=1 | player_id=uuid-B] = score:870
  Cell: [chess-pro | level=2 | player_id=uuid-A] = score:1200
  Cell: [chess-pro | level=2 | player_id=uuid-B] = score:1100

Clustering Columns vs Secondary Indexes

Clustering columns provide sorted, efficient range access within a known partition. Secondary indexes allow filtering by columns that are not part of the primary key, but they scatter lookups across many nodes and perform worse at scale. Prefer clustering columns for any sort or range requirement that you know at table design time.

Summary

Clustering columns sort rows within a partition and allow fast range queries. You define them in the PRIMARY KEY clause after the partition key. Use CLUSTERING ORDER BY to control ascending or descending sort direction. Always provide clustering column filters in the order they are defined, and design your clustering columns around the range queries your application makes most frequently.

Previous lesson

Back to course

Next lesson