Phase 2: CQL Cellular Data Modeling & Physical Storage Mapping¶

Audience: Principal Distributed Systems Architect & Big Data Engineer
Source References: src/java/org/apache/cassandra/cql3/, src/java/org/apache/cassandra/db/rows/

1. Anatomy of the Primary Key¶

In Cassandra, how you query data dictates how you must store data. There are no relational joins, and foreign keys do not exist.

PRIMARY KEY Hierarchy Definition:
PRIMARY KEY ((partition_key_1, partition_key_2), clustering_key_1, clustering_key_2)
             ▲                                   ▲
             │                                   └────── Controls Physical Row Ordering
             └────────────────────────────────────────── Controls Cluster Node Placement

CREATE TABLE sensor_readings (
    tenant_id    uuid,
    sensor_id    text,
    reading_date date,
    event_time   timestamp,
    metric_type  text,
    value        double,
    sensor_model text STATIC, -- Shared across all clustering rows in partition!
    PRIMARY KEY ((tenant_id, reading_date), sensor_id, event_time)
) WITH CLUSTERING ORDER BY (sensor_id ASC, event_time DESC);

2. Physical Storage Mapping: The Wide-Row Architecture¶

How does Cassandra map the tabular CQL schema above into its internal cellular storage engine?

Physical Storage Organization on Disk:
Partition Key: Murmur3(tenant_id, reading_date) ===> Token -849182049182 (Routes to Node)

Inside the Partition (Contiguous block on disk):
┌────────────────────────────────────────────────────────────────────────┐
│ PARTITION HEADER                                                       │
│ - Partition Key: [tenant_id, reading_date]                             │
│ - Static Columns: sensor_model = "ProSensor-v2" (Stored exactly once!) │
├────────────────────────────────────────────────────────────────────────┤
│ CLUSTERING ROW 1: [sensor_id: "S-101", event_time: 1716382000]         │
│   - metric_type: "TEMPERATURE", value: 24.5                            │
├────────────────────────────────────────────────────────────────────────┤
│ CLUSTERING ROW 2: [sensor_id: "S-101", event_time: 1716381900]         │
│   - metric_type: "TEMPERATURE", value: 24.2                            │
├────────────────────────────────────────────────────────────────────────┤
│ CLUSTERING ROW 3: [sensor_id: "S-102", event_time: 1716382000]         │
│   - metric_type: "PRESSURE", value: 101.3                              │
└────────────────────────────────────────────────────────────────────────┘

2.1 The Sequential Disk Read Superpower¶

Because rows are physically sorted on disk by CLUSTERING ORDER BY (sensor_id ASC, event_time DESC):

SELECT * FROM sensor_readings 
WHERE tenant_id = ? AND reading_date = ? AND sensor_id = 'S-101'
LIMIT 50;

This query translates into a single sequential disk read. The disk head seeks once to sensor_id = 'S-101' and reads contiguous bytes sequentially off NVMe storage at gigabytes per second with zero random seeks!

3. Static Columns & Non-Frozen Collections¶

3.1 Static Columns (`STATIC`)¶

A static column belongs to the entire partition, not to an individual clustering row. - If a partition contains 100,000 clustering rows, a normal column is written 100,000 times. - A STATIC column is written once in the partition header. - Modifying a static column updates that single cell for all clustering rows in \(O(1)\) time without rewriting historical rows.

3.2 Non-Frozen vs. Frozen Collections¶

-- Non-Frozen Collection:
tags set<text>

-- Frozen Collection:
metadata frozen<map<text, text>>

- Non-Frozen Collections: Each element in the set or map is serialized as an independent internal cell with its own timestamp and tombstone. Elements can be appended (tags = tags + {'iot'}) or removed without reading the full collection. - Frozen Collections: The entire map/set/list is serialized as a single opaque binary byte array. Any update overwrites the entire collection, resetting the root timestamp.

4. Secondary Indexing vs. Storage-Attached Indexing (SAI)¶

Legacy 2i Architecture (The Scatter-Gather Trap):
[Client Query: WHERE metric_type = 'TEMP']
                     │
                     ▼
             [Coordinator Node]
                     │
       ┌─────────────┼─────────────┐
       ▼             ▼             ▼
   [Node 1]      [Node 2]      [Node 3]   <--- Scans local 2i on EVERY node!

4.1 The Legacy 2i Failure Mode¶

Legacy 2i indexes indexed non-partition keys by building a hidden table on every node. Because the index was partitioned locally by the base table's token, querying by a secondary index required querying every single node in the entire cluster (Scatter-Gather). If a single node experienced a 500ms GC pause, the entire query stalled.

4.2 Storage-Attached Indexing (SAI) in Cassandra 5.0+¶

SAI deeply binds the secondary index directly to the physical lifecycle of each SSTable: - Indexes are constructed asynchronously during Memtable flush and compaction. - Uses Trie structures for string prefix lookups and KD-Trees for numeric and geo-spatial range filters. - Eliminates cluster-wide scatter-gather penalties by executing optimized post-filtering directly during data reads.