Cassandra Wide Rows

A wide row is a partition that contains many rows, all grouped under the same partition key. Wide rows are one of the most powerful features of Cassandra — they let you store time-ordered sequences, event histories, or ranked lists as a single, contiguous unit on disk. Reading a large set of related rows becomes one sequential disk scan instead of many scattered lookups.

The Accordion Folder Analogy

An accordion folder has one label on the front (the partition key) and many sections inside (the rows, sorted by clustering columns). You open one folder and flip through the sections in order. Wide rows work the same way — one partition key, many sorted rows inside, all on the same node and readable in a single sequential sweep.

Narrow table (many partitions, few rows each):

Partition 'order-001': [1 row]
Partition 'order-002': [1 row]
Partition 'order-003': [1 row]
...
→ 1,000 partitions for 1,000 orders

Wide row table (few partitions, many rows each):

Partition 'customer-alice':
  [order-001, 2024-01-01]
  [order-002, 2024-02-15]
  [order-003, 2024-03-20]
  ...
→ 1 partition, 1,000 rows, one sequential read

Creating a Wide Row Table

-- Time-series sensor data: one partition per sensor per day
CREATE TABLE sensor_readings (
  sensor_id  TEXT,
  day        DATE,
  read_time  TIMESTAMP,
  value      DOUBLE,
  unit       TEXT,
  PRIMARY KEY ((sensor_id, day), read_time)
) WITH CLUSTERING ORDER BY (read_time ASC);

Wide row for sensor 'S1' on 2024-06-01:

Partition: sensor_id='S1' + day=2024-06-01
  read_time            | value | unit
  ─────────────────────────────────────
  2024-06-01 00:00:00  | 21.5  | °C
  2024-06-01 00:01:00  | 21.6  | °C
  2024-06-01 00:02:00  | 21.7  | °C
  ...
  2024-06-01 23:59:00  | 20.8  | °C
  (1440 rows — one per minute)

Reading a Wide Row Efficiently

-- Read all readings for sensor S1 on June 1:
SELECT * FROM sensor_readings
WHERE sensor_id = 'S1' AND day = '2024-06-01';

-- Read a specific time range:
SELECT * FROM sensor_readings
WHERE sensor_id = 'S1' AND day = '2024-06-01'
  AND read_time >= '2024-06-01 08:00:00'
  AND read_time <  '2024-06-01 12:00:00';

-- Latest 10 readings:
SELECT * FROM sensor_readings
WHERE sensor_id = 'S1' AND day = '2024-06-01'
ORDER BY read_time DESC LIMIT 10;

Common Wide Row Patterns

Pattern 1: Time Series (by day bucket)

CREATE TABLE stock_prices (
  symbol  TEXT,
  day     DATE,
  trade_time TIMESTAMP,
  price   DECIMAL,
  volume  BIGINT,
  PRIMARY KEY ((symbol, day), trade_time)
) WITH CLUSTERING ORDER BY (trade_time DESC);

Using a day bucket in the partition key prevents unbounded partition growth. Without the day bucket, one partition would hold every trade ever made for a given stock symbol.

Pattern 2: Chat Message History

CREATE TABLE messages (
  chat_id  TEXT,
  msg_time TIMEUUID,
  sender   TEXT,
  body     TEXT,
  PRIMARY KEY (chat_id, msg_time)
) WITH CLUSTERING ORDER BY (msg_time DESC);

Pattern 3: User Activity Feed

CREATE TABLE activity_by_user (
  user_id     UUID,
  bucket      TEXT,   -- e.g. '2024-06'
  activity_id TIMEUUID,
  action      TEXT,
  target_id   UUID,
  PRIMARY KEY ((user_id, bucket), activity_id)
) WITH CLUSTERING ORDER BY (activity_id DESC);

Partition Size Guidelines for Wide Rows

Cassandra performs best when partitions stay under 100 MB and under 100,000 rows. Use a time-based bucket in the compound partition key to cap partition size.

Problem: Unbounded growth
  PRIMARY KEY (sensor_id, read_time)
  → Partition 'S1' grows forever (millions of rows over years)

Solution: Day bucket limits growth
  PRIMARY KEY ((sensor_id, day), read_time)
  → Partition 'S1 / 2024-06-01' holds only 1,440 rows
  → Partition 'S1 / 2024-06-02' holds only 1,440 rows
  → Each partition stays small and compacts quickly

Choosing the Right Bucket Size

Write Frequency          Recommended Bucket
──────────────────────────────────────────────────────────────
1 write per second       Day bucket (86,400 rows/day)
10 writes per second     Hour bucket (36,000 rows/hour)
100 writes per second    Hour bucket (360,000 rows/hour → consider minute)
1,000+ writes per second Minute or 10-minute bucket

Deleting Expired Wide Row Partitions

Old partitions of time-bucketed wide rows become cheap to delete because you can drop an entire partition with a single DELETE statement. This is far more efficient than deleting rows one by one.

-- Delete all sensor readings for S1 on a specific day:
DELETE FROM sensor_readings
WHERE sensor_id = 'S1' AND day = '2024-01-01';

This is even better paired with TTL so old buckets expire automatically.

Wide Rows and Compaction

TimeWindowCompactionStrategy (TWCS) pairs naturally with time-bucketed wide rows. TWCS groups SSTables by their write time window, which aligns perfectly with time-bucketed partitions. When a window closes, its SSTables compact and then never merge with newer data — making cleanup very efficient.

Summary

Wide rows store many related rows under one partition key, enabling fast sequential reads of grouped data. Use compound partition keys with a time bucket to cap partition size and prevent unbounded growth. Choose the bucket granularity based on your write rate — aiming for partitions under 100,000 rows and 100 MB. Wide rows are the natural model for time-series data, message histories, activity feeds, and any append-heavy sequential data pattern.

Previous lesson

Back to course

Next lesson