Apache Cassandra Technology Expert

How to Approach Tasks

When you receive a request:

1. Classify the request:

   • Architecture/internals -- Load references/architecture.md
   • Performance diagnostics -- Load references/diagnostics.md
   • Configuration/operations -- Load references/best-practices.md
   • Version-specific feature -- Route to the version agent
   • Comparison with other databases -- Route to parent ../SKILL.md

2. Determine version -- Ask if unclear. Behavior differs significantly across versions (e.g., SAI only in 5.0+, virtual tables only in 4.0+, UCS only in 5.0+).

3. Analyze -- Apply Cassandra-specific reasoning. Reference the partition model, the distributed write/read paths, compaction mechanics, and consistency trade-offs as relevant.

4. Recommend -- Provide actionable guidance with specific cassandra.yaml parameters, CQL statements, nodetool commands, or JVM tuning flags.

5. Verify -- Suggest validation steps (nodetool tablestats, nodetool tpstats, CQL tracing, system table queries).

Core Expertise

Distributed Architecture

Cassandra is a masterless (peer-to-peer) distributed database built on Amazon Dynamo and Google Bigtable principles:

• No single point of failure -- Every node is identical. No master/slave distinction. Any node can coordinate any request.
• Consistent hashing -- Data is distributed across a token ring. Each partition key is hashed (Murmur3 by default) to a token, which maps to a node range.
• Vnodes (virtual nodes) -- Each physical node owns multiple token ranges (default num_tokens: 256 in 3.x, 16 recommended in 4.x+). Vnodes enable automatic load balancing and faster streaming during topology changes.
• Replication factor (RF) -- Each keyspace defines how many copies of data exist. RF=3 is the standard production setting.
• Replication strategies:
  • SimpleStrategy -- Places replicas on consecutive nodes in the ring. Single-datacenter only.
  • NetworkTopologyStrategy -- Places replicas across racks and datacenters. Required for multi-DC deployments.
• Gossip protocol -- Nodes exchange state information (load, status, schema version, tokens) every second via a peer-to-peer gossip protocol. Detects node failures via the phi accrual failure detector.
• Snitch -- Determines the topology (rack, datacenter) of each node. Types include GossipingPropertyFileSnitch (recommended for production), PropertyFileSnitch, Ec2Snitch, GoogleCloudSnitch.

Data Modeling

Cassandra data modeling is fundamentally different from relational modeling. It is query-driven, not entity-driven:

Methodology (Chebotko diagram approach):

1. Start with the application queries (access patterns)
2. Design one table per query pattern
3. Choose the partition key to distribute data evenly and satisfy the query
4. Choose clustering columns to sort data within a partition
5. Denormalize aggressively -- duplication is expected and correct

Partition key design rules:

• The partition key determines which node stores the data (via hash)
• High cardinality is essential -- avoid hot partitions
• A partition should target < 100MB (ideal < 10MB for low-latency reads)
• Compound partition keys ((col_a, col_b)) distribute data across more partitions
• Time-bucketing: for time-series data, bucket by day/hour in the partition key to bound partition growth

Clustering columns:

• Define the sort order within a partition (ASC or DESC)
• Enable efficient range queries within a partition
• Multi-column clustering allows hierarchical sorting: CLUSTERING ORDER BY (date DESC, id ASC)

Example -- time-series sensor data:

CREATE TABLE sensor_readings (
    sensor_id    text,
    day          date,
    reading_time timestamp,
    value        double,
    PRIMARY KEY ((sensor_id, day), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);

-- Query: latest readings for sensor X on a specific day
SELECT * FROM sensor_readings
WHERE sensor_id = 'sensor-42' AND day = '2025-03-15'
LIMIT 100;

Anti-patterns to avoid:

• Using a low-cardinality column as the sole partition key (e.g., status, country)
• Unbounded partition growth (no time-bucketing in time-series data)
• Querying without the full partition key (triggers scatter-gather across all nodes)
• Over-relying on secondary indexes for high-cardinality lookups
• Using ALLOW FILTERING in production queries

CQL Language Reference

Data Definition:

-- Create keyspace with NetworkTopologyStrategy
CREATE KEYSPACE my_ks WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'dc1': 3, 'dc2': 3
} AND durable_writes = true;

-- Create table
CREATE TABLE my_ks.users (
    user_id    uuid PRIMARY KEY,
    email      text,
    name       text,
    created_at timestamp
);

-- Create table with compound partition key and clustering
CREATE TABLE my_ks.events (
    tenant_id  text,
    event_date date,
    event_time timestamp,
    event_id   uuid,
    payload    text,
    PRIMARY KEY ((tenant_id, event_date), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC, event_id ASC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_size': 1,
                    'compaction_window_unit': 'DAYS'}
  AND default_time_to_live = 7776000;  -- 90 days

-- Alter table
ALTER TABLE my_ks.users ADD phone text;
ALTER TABLE my_ks.users DROP phone;

-- Drop table
DROP TABLE IF EXISTS my_ks.users;

Data Manipulation:

-- INSERT (upsert semantics -- always overwrites)
INSERT INTO users (user_id, email, name, created_at)
VALUES (uuid(), '[email protected]', 'Alice', toTimestamp(now()));

-- INSERT with TTL (auto-expire after 86400 seconds)
INSERT INTO users (user_id, email, name, created_at)
VALUES (uuid(), '[email protected]', 'Temp User', toTimestamp(now()))
USING TTL 86400;

-- INSERT with explicit timestamp
INSERT INTO users (user_id, email, name, created_at)
VALUES (uuid(), '[email protected]', 'Bob', toTimestamp(now()))
USING TIMESTAMP 1700000000000000;

-- UPDATE
UPDATE users SET name = 'Alice Smith' WHERE user_id = some-uuid;

-- UPDATE with TTL
UPDATE users USING TTL 3600 SET email = '[email protected]' WHERE user_id = some-uuid;

-- DELETE
DELETE FROM users WHERE user_id = some-uuid;

-- DELETE specific column
DELETE email FROM users WHERE user_id = some-uuid;

-- BATCH (use only for atomicity on same partition, NOT for performance)
BEGIN BATCH
    INSERT INTO users (user_id, email, name) VALUES (uuid(), '[email protected]', 'X');
    INSERT INTO user_by_email (email, user_id) VALUES ('[email protected]', some-uuid);
APPLY BATCH;

Querying:

-- SELECT with full partition key (efficient)
SELECT * FROM events WHERE tenant_id = 'acme' AND event_date = '2025-03-15';

-- Range query on clustering column
SELECT * FROM events
WHERE tenant_id = 'acme' AND event_date = '2025-03-15'
  AND event_time >= '2025-03-15 08:00:00'
  AND event_time < '2025-03-15 17:00:00';

-- Paging
SELECT * FROM events
WHERE tenant_id = 'acme' AND event_date = '2025-03-15'
LIMIT 1000;

-- Token-based full-table scan (for analytics/export)
SELECT * FROM users WHERE token(user_id) > -9223372036854775808
  AND token(user_id) <= 9223372036854775807;

-- COUNT (expensive -- scans partition)
SELECT COUNT(*) FROM events WHERE tenant_id = 'acme' AND event_date = '2025-03-15';

Consistency Levels

Cassandra offers tunable consistency per query. With RF=3:

Strong consistency formula: R + W > RF

• QUORUM reads + QUORUM writes: 2 + 2 = 4 > 3 -- consistent
• ONE read + ALL write: 1 + 3 = 4 > 3 -- consistent but fragile
• ONE read + ONE write: 1 + 1 = 2 < 3 -- NOT consistent (stale reads possible)

Multi-DC standard: LOCAL_QUORUM for both reads and writes. This gives strong consistency within each datacenter while tolerating the loss of an entire remote DC.

Compaction Strategies

Compaction merges SSTables to reclaim space, remove tombstones, and consolidate data:

STCS (default):

• Triggers when min_threshold (default 4) SSTables of similar size exist
• Temporary space overhead: needs space for input + output SSTables (~50-100% temporary)
• Best for write-dominated workloads where reads are less frequent
• Drawback: large SSTables may never get compacted with small ones

LCS:

• L0 = memtable flushes; L1 = max 10 SSTables of 160MB each; L2 = max 100 of 160MB; etc.
• Each level is 10x the size of the previous level
• A read hits at most one SSTable per level
• Higher write amplification (each datum rewritten ~10x across levels)
• Best for read-heavy workloads with frequent updates

TWCS:

• Groups SSTables into time windows (e.g., 1-hour or 1-day buckets)
• Within a window, uses STCS-like compaction
• Once a window closes, SSTables in that window are compacted into one and never touched again
• When TTL expires for an entire window, the SSTable is simply dropped
• Critical: Do not use with data that lacks TTL or gets updated across windows

Selection guidance:

Write-heavy, rarely read          --> STCS
Read-heavy, frequent updates      --> LCS
Time-series with TTL              --> TWCS
Cassandra 5.0+                    --> UCS (replaces all three)
Mixed workload (uncertain)        --> Start with STCS, measure, then switch

Write Path

The write path is designed for maximum throughput:

1. Coordinator receives write -- Any node can coordinate
2. Coordinator determines replicas -- Uses the partitioner and replication strategy
3. Write sent to replicas in parallel
4. On each replica: a. Write to the commit log (sequential append on disk) -- durability guarantee b. Write to the memtable (in-memory sorted structure) c. Acknowledge to coordinator
5. Coordinator waits for consistency level acknowledgments, then responds to client
6. Memtable flush: When memtable reaches memtable_cleanup_threshold or commitlog space limit, it is flushed to an immutable SSTable on disk
7. Compaction merges SSTables in the background

Key performance characteristics:

• Writes are always sequential I/O (commit log append + SSTable flush)
• No read-before-write (unlike RDBMS UPDATE)
• Writes at CL=ONE are acknowledged as soon as a single replica writes to its commit log + memtable
• Hinted handoff: if a replica is down, the coordinator stores a hint and replays it when the node recovers

Read Path

The read path is more complex due to the LSM-tree storage:

1. Coordinator receives read -- Determines replicas using the partitioner
2. Coordinator sends read request to the fastest replica (by snitch dynamic latency) and digest requests to enough replicas to satisfy the consistency level
3. On each replica: a. Check the memtable (most recent data) b. Check the row cache (if enabled -- rarely used in production) c. For each SSTable (newest to oldest):
   • Check the bloom filter -- probabilistic "definitely not here" or "maybe here"
   • Check the partition index summary (in-memory sampling of partition index)
   • Read the partition index from disk to find the exact position
   • Check the compression offset map to find the compressed chunk
   • Read and decompress the data d. Merge results from memtable and all SSTables (last-write-wins by timestamp)
4. Coordinator compares full data response with digest responses
5. If digests match, return result. If not, trigger a read repair in the background.

Read performance levers:

• Bloom filter: false-positive rate tunable via bloom_filter_fp_chance (default 0.01 = 1%)
• Key cache: caches partition index entries (default ON, 100MB or 5% of heap)
• Compaction: fewer SSTables = fewer lookups per read
• Partition size: smaller partitions = faster reads

Tombstones and TTL

Cassandra cannot delete data in place (distributed, immutable SSTables). Instead, it writes a tombstone -- a marker that says "this data is deleted":

Types of tombstones:

• Cell tombstone -- Deletes a single column
• Row tombstone -- Deletes an entire row
• Range tombstone -- Deletes a range of clustering keys within a partition
• Partition tombstone -- Deletes an entire partition
• TTL expiry -- Automatically generates a tombstone when TTL expires

gc_grace_seconds (default 864000 = 10 days):

• Tombstones are kept for this duration to ensure all replicas see the deletion
• After gc_grace_seconds, compaction can purge the tombstone
• If a node is down longer than gc_grace_seconds, it may resurrect deleted data (zombie data)
• Critical: Never set gc_grace_seconds to 0 unless you have a single replica or a custom repair strategy

Tombstone warnings:

# cassandra.yaml
tombstone_warn_threshold: 1000     # warn in logs when reading > 1000 tombstones
tombstone_failure_threshold: 100000 # fail the query when reading > 100000 tombstones

Tombstone storm scenarios:

• Deleting large ranges of data without considering compaction timing
• Using wide partitions with heavy delete patterns
• TTL expiring on large amounts of data simultaneously
• SELECT * on a partition with mostly deleted data -- must scan through all tombstones

Mitigation strategies:

1. Design data models to avoid deletes (use TTL with TWCS instead)
2. Keep partitions small (< 100MB, < 100K rows ideal)
3. Run targeted repairs before and after bulk deletes
4. Monitor tombstone counts via nodetool tablestats (look at Average tombstones per slice)
5. Adjust gc_grace_seconds downward (but never below your repair interval)

Secondary Indexes and Materialized Views

Secondary Indexes (legacy SASI and 2i):

• Built as hidden local tables on each node
• Queries must still hit all nodes that could hold matching data (scatter-gather)
• Suitable for low-cardinality columns with a known partition key in the WHERE clause
• Avoid for: high-cardinality columns, queries without partition key, high-throughput production queries

Materialized Views (MV):

• Server-maintained denormalized tables
• Automatically updated when the base table changes
• Known issues: consistency bugs, repair complications, performance overhead
• Recommendation: Avoid in production. Use client-side denormalization with BATCH writes to multiple tables instead.

Storage Attached Indexes (SAI, Cassandra 5.0+):

• Replaces SASI and legacy 2i
• Column-level index stored alongside SSTable data
• Efficient for both equality and range queries on non-primary-key columns
• Much better performance and reliability than legacy secondary indexes
• See 5.0/SKILL.md for details

Lightweight Transactions (LWT)

Cassandra provides linearizable consistency via a Paxos-based protocol:

-- INSERT if not exists (compare-and-set)
INSERT INTO users (user_id, email, name)
VALUES (uuid(), '[email protected]', 'Alice')
IF NOT EXISTS;

-- UPDATE with condition
UPDATE users SET email = '[email protected]'
WHERE user_id = some-uuid
IF email = '[email protected]';

-- DELETE with condition
DELETE FROM users WHERE user_id = some-uuid
IF name = 'Alice';

LWT internals (4-round-trip Paxos):

1. Prepare -- Propose a ballot number to replicas
2. Promise -- Replicas promise to accept this ballot (or reject if a higher ballot exists)
3. Propose -- Send the actual mutation with the ballot
4. Commit -- Finalize the mutation

Performance implications:

• 4x latency of a normal write (4 network round-trips vs 1)
• Contention under high concurrency (Paxos ballot conflicts)
• Use LOCAL_SERIAL / SERIAL consistency levels for LWT reads
• Guidance: Use sparingly. If you need many LWT operations, consider whether Cassandra is the right database for that workload. Typical use cases: unique constraints, account creation, leader election.

Security

Authentication:

# cassandra.yaml