Cassandra Deep Dive: Wide-Column Model, Tunable Consistency and the Write Path

Why Cassandra Exists

Cassandra was created at Facebook in 2008 to power the inbox search feature — a problem that required handling billions of writes per day across multiple datacenters with no single point of failure. It was open-sourced in 2009 and became an Apache project.

The design goals shaped every architectural decision:

• Write throughput above everything else — millions of writes per second

• No single point of failure — any node can go down without data loss

• Linear scalability — double the nodes, double the throughput

• Multi-datacenter replication — data in multiple geographies simultaneously

These goals come with trade-offs. Cassandra has no joins. It has limited secondary index support. Transactions span only a single partition. Ad-hoc queries are expensive. Understanding these constraints before you model your data is the difference between a system that works and one that degrades under load.

The Data Model: Tables, Partition Keys, and Clustering Keys

Cassandra looks like SQL on the surface — it uses CQL (Cassandra Query Language), which resembles SQL. But the underlying model is fundamentally different.

Every Cassandra table has two components to its primary key:

Partition key: Determines which node stores the data. All rows with the same partition key are stored together on the same node. This is the most important design decision in Cassandra.

Clustering key: Determines the order of rows within a partition. Rows within a partition are sorted by the clustering key on disk.

CREATE TABLE user_activity (
    user_id    UUID,
    timestamp  TIMESTAMP,
    event_type TEXT,
    metadata   TEXT,
    PRIMARY KEY (user_id, timestamp)
--              ↑ partition key  ↑ clustering key
);

This table stores all activity for a user together (same partition = same node), sorted by timestamp within each user's partition.

Node A (responsible for user_id=UUID-42):

Partition: user_id=UUID-42
│
├── Row: ts=2025-03-15 10:00:00, event=login
├── Row: ts=2025-03-15 10:01:23, event=page_view
├── Row: ts=2025-03-15 10:05:44, event=purchase
└── Row: ts=2025-03-15 10:23:11, event=logout

Node B (responsible for user_id=UUID-17):

Partition: user_id=UUID-17
│
├── Row: ts=2025-03-14 08:00:00, event=login
└── Row: ts=2025-03-14 08:45:00, event=purchase

The Golden Rule: Design Tables for Queries

In relational databases, you design tables to model your data, then write queries to answer business questions. Cassandra inverts this: you design tables to answer specific queries.

Each query pattern typically needs its own table. This is not a flaw — it is the intentional trade-off that enables Cassandra's write performance.

Example: designing a messaging system

Requirements:

1. Get all messages in a conversation, newest first

2. Get all conversations for a user, newest first

3. Get unread message count per conversation per user

-- Query 1: messages in a conversation
CREATE TABLE messages_by_conversation (
    conversation_id UUID,
    sent_at         TIMESTAMP,
    sender_id       UUID,
    body            TEXT,
    PRIMARY KEY (conversation_id, sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);

-- Query 2: conversations for a user (newest first)
CREATE TABLE conversations_by_user (
    user_id         UUID,
    last_message_at TIMESTAMP,
    conversation_id UUID,
    preview         TEXT,
    PRIMARY KEY (user_id, last_message_at, conversation_id)
) WITH CLUSTERING ORDER BY (last_message_at DESC, conversation_id ASC);

-- Query 3: unread count per conversation per user
CREATE TABLE unread_counts (
    user_id         UUID,
    conversation_id UUID,
    unread_count    COUNTER,
    PRIMARY KEY (user_id, conversation_id)
);

Three queries → three tables. When a message is sent:

• Insert into messages_by_conversation

• Update conversations_by_user for each participant

• Increment unread_counts for each recipient

This is intentional denormalization. The same data exists in multiple forms, each optimized for one access pattern.

Partition Key Design: Avoiding Hot Partitions

A hot partition occurs when too much data or too many requests concentrate on a single partition — one node receives disproportionate load while others are idle.

Bad partition key — time-based:

-- BAD: all activity in a day goes to one partition
CREATE TABLE events (
    date       DATE,
    event_id   UUID,
    payload    TEXT,
    PRIMARY KEY (date, event_id)
);
-- On a busy day: millions of rows in one partition on one node
-- That node is a hot spot

Better: composite partition key to distribute load:

-- BETTER: bucket by date + shard to distribute across nodes
CREATE TABLE events (
    date        DATE,
    shard       INT,    -- e.g. event_id % 10
    event_id    UUID,
    payload     TEXT,
    PRIMARY KEY ((date, shard), event_id)
--              ↑ composite partition key
);
-- 10 shards per day = 10 partitions per day = distributed across 10 nodes
-- Trade-off: must query all 10 shards to get all events for a day

Partition size guidelines:

• Maximum recommended partition size: ~100 MB (hard limit is much higher, but performance degrades)

• Maximum recommended rows per partition: ~100,000

• If a partition exceeds these: redesign with a composite key to split it

The Write Path

Cassandra's write performance comes from its LSM-Tree-based storage engine (covered in Post 10). Every write goes through four steps:

Step 1: Write to Commit Log (WAL equivalent)
  ─ Sequential append to disk
  ─ Durable: survives crash
  ─ One commit log per node (shared across all tables)

Step 2: Write to Memtable (in-memory sorted buffer)
  ─ Sorted by partition key + clustering key
  ─ Write returns success to client after Steps 1 and 2

Step 3 (async): Flush Memtable to SSTable
  ─ When memtable fills (~64-256 MB), flush to disk as immutable SSTable
  ─ Sequential write — very fast

Step 4 (background): Compaction
  ─ Merge SSTables, remove deleted data (tombstones), sort
  ─ Reduces read amplification
  ─ Leveled or STCS (Size-Tiered Compaction Strategy)

Write latency breakdown:
  Commit log write: ~1ms (sequential I/O)
  Memtable write:   ~0.1ms (in-memory)
  Total:            ~1-2ms local write latency
  With replication (QUORUM, 3 nodes): ~3-5ms

This is why Cassandra achieves millions of writes per second — each write is a sequential disk append plus an in-memory operation. No B-Tree page splits, no random I/O.

The Read Path

Reads are more expensive than writes in Cassandra — the inverse of most relational databases.

Read path for a partition:

Step 1: Check Bloom filters for each SSTable
  ─ O(1) probabilistic check: "is this partition in this SSTable?"
  ─ False positives possible (check SSTable even if key not there)
  ─ No false negatives (if Bloom filter says no, key definitely not there)

Step 2: Check key cache
  ─ In-memory cache of partition key → SSTable offset mappings
  ─ Cache hit: jump directly to SSTable offset

Step 3: Check row cache (if enabled)
  ─ Caches entire partitions in memory
  ─ Useful for small, hot partitions
  ─ Dangerous for large partitions (consumes too much heap)

Step 4: Read from SSTables + Memtable
  ─ For each SSTable not excluded by Bloom filter: binary search for partition
  ─ Merge results from all SSTables (most recent version wins)
  ─ Merge with current Memtable data

Step 5: Apply tombstone filtering
  ─ Remove deleted rows (marked with tombstones)
  ─ High tombstone count = slow reads (must scan and discard)

The tombstone problem: Deletes in Cassandra write a tombstone marker — they do not remove data immediately. Until the tombstone is compacted away (which requires all replicas to have the tombstone), reads must scan and discard tombstones. Tables with heavy deletes accumulate tombstones and slow down. This is a known Cassandra anti-pattern.

Tunable Consistency

Cassandra's consistency is tunable per operation — the same table can serve some queries with strong consistency and others with eventual consistency, depending on the read/write consistency levels specified.

Recall the quorum rule: with n replicas, consistency requires w + r > n.

-- Write with ONE: fastest, weakest consistency
-- Returns success when 1 replica confirms
INSERT INTO user_activity (user_id, timestamp, event_type)
VALUES (uuid(), toTimestamp(now()), 'page_view')
USING CONSISTENCY ONE;

-- Write with QUORUM: majority of replicas confirm
-- For n=3: 2 replicas must confirm
INSERT INTO orders (order_id, customer_id, amount)
VALUES (uuid(), ?, ?)
USING CONSISTENCY QUORUM;

-- Read with ONE: fastest, may return stale data
SELECT * FROM user_activity
WHERE user_id = ?
USING CONSISTENCY ONE;

-- Read with QUORUM: reads from majority, returns latest version
-- Combined with QUORUM write: strong consistency (w+r=4 > n=3)
SELECT * FROM orders
WHERE order_id = ?
USING CONSISTENCY QUORUM;

-- Read with ALL: all replicas must respond — slowest, unavailable if any replica down
SELECT * FROM critical_data
WHERE id = ?
USING CONSISTENCY ALL;

Consistency level trade-offs:

LOCAL_QUORUM is the most practical for multi-datacenter deployments — requires a quorum only within the local datacenter, avoiding cross-datacenter latency for most operations while maintaining consistency locally.

Anti-Patterns to Avoid

Anti-pattern 1 — Queries without partition key. Every Cassandra query must include the partition key in the WHERE clause. Without it, Cassandra must scan all partitions on all nodes — equivalent to a full table scan across the entire cluster.

-- BAD: no partition key — full cluster scan (ALLOW FILTERING required)
SELECT * FROM user_activity WHERE event_type = 'purchase' ALLOW FILTERING;

-- GOOD: always include partition key
SELECT * FROM user_activity WHERE user_id = ? AND event_type = 'purchase';
-- Requires a separate table modeled for this query

Anti-pattern 2 — Large partitions. A partition larger than 100 MB causes GC pressure on the JVM, increases read latency, and can cause node failures during compaction. Split with composite partition keys.

Anti-pattern 3 — Unbounded partition growth. Time-series tables keyed only by entity ID grow forever — one partition per user, growing daily. Add a time bucket to the partition key to cap partition size.

-- BAD: user:42 partition grows forever
PRIMARY KEY (user_id, timestamp)

-- GOOD: bucket by year-week, partition stays bounded
PRIMARY KEY ((user_id, week_bucket), timestamp)
-- week_bucket = YYYY-WW (e.g. "2025-11")
-- Each partition: one user's activity for one week

Anti-pattern 4 — Heavy use of secondary indexes. Cassandra's secondary indexes are stored locally on each node and require a full cluster scan to satisfy most queries. Use materialized views or separate tables instead.

Anti-pattern 5 — Excessive tombstones. Avoid delete-heavy workflows. If you must delete frequently, use TTL instead — data auto-expires without creating tombstones that linger until compaction.

-- Instead of DELETE, use TTL:
INSERT INTO sessions (session_id, user_id, data)
VALUES (?, ?, ?)
USING TTL 86400;   -- auto-expire after 24 hours, no manual deletes

When Cassandra Is the Right Choice

✅ Write throughput > 50,000 writes/second per table
✅ Time-series data: IoT, events, metrics, user activity
✅ Multi-datacenter active-active replication required
✅ Linear scalability: adding nodes linearly increases throughput
✅ Access patterns are known and stable (not ad-hoc)
✅ Eventual consistency is acceptable (or QUORUM is used for critical data)

❌ Complex queries with multiple JOINs
❌ Multi-row transactions (Cassandra's LWT/lightweight transactions are limited)
❌ Ad-hoc analytical queries (use a data warehouse instead)
❌ Strong global consistency required across all operations
❌ Small dataset (PostgreSQL is simpler and sufficient)

Chapter 6 Complete

You have finished the NoSQL Databases chapter:

• ✅ Post 19: NoSQL Explained — Document, Key-Value, Wide-Column & When NOT to Use SQL

• ✅ Post 20: MongoDB Deep Dive — Document Model, Aggregation Pipeline & Indexes

• ✅ Post 21: Redis Deep Dive — Data Structures, Persistence, Pub/Sub & Patterns

• ✅ Post 22: Cassandra Deep Dive — Wide-Column, Tunable Consistency & Write Path

Chapter 7 moves to the newest tier of the database landscape: NewSQL databases that promise SQL semantics at distributed scale, and vector databases built for AI applications.

🧭 What's Next — Chapter 7: NewSQL & Modern Databases

• Post 23: NewSQL — CockroachDB, PlanetScale, and Google Spanner each rethink what it means to be a distributed SQL database; this post explains how they work and what they actually deliver