Apache Cassandra schema design begins with the queries the application must serve. The partition key determines data placement, while clustering columns determine row identity and order inside a partition. A reliable model keeps common reads to a small, known set of partitions, spreads traffic, and puts a measurable bound on every partition's growth.
- Write the access patterns, consistency level, result bound, and expected traffic before writing CQL.
- Choose partition keys that distribute both stored bytes and requests; high cardinality alone does not prevent a hot key.
- Use clustering columns for ordered slices inside a partition and include a tie-breaker when timestamps can collide.
- Bucket time-series partitions from measured rows, bytes, and query spans rather than a universal day or month rule.
- TTL expiry creates tombstones. TWCS can fit mostly immutable, expiring time-series data, but repair and late-write behavior still matter.
Turn access patterns into table contracts
For each query, record the equality keys, time or value range, sort order, maximum rows, page size, frequency, and consistency requirement. Cassandra denormalization often means writing the same business event to multiple query-specific tables. That is an explicit consistency and repair obligation, not a free join replacement.
| Access pattern | Partition | Clustering slice |
|---|---|---|
| Latest readings for one sensor and bucket | (sensor_id, bucket_start) | event_time DESC, event_id |
| One reading by immutable ID | event_id | None |
| Alerts for one site and bucket | (site_id, bucket_start) | severity, event_time DESC, event_id |
If an important query cannot name the partition key or a bounded set of partition keys, design another table or evaluate Cassandra 5.0 Storage-Attached Indexing for the exact filter workload. SAI is a filtering index, not a substitute for bounding large result sets or modeling primary access paths.
Partition and clustering keys
CREATE TABLE telemetry.readings_by_sensor_bucket (
sensor_id uuid,
bucket_start date,
event_time timestamp,
event_id timeuuid,
value double,
quality text,
PRIMARY KEY ((sensor_id, bucket_start), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC, event_id DESC);The double parentheses make sensor_id and bucket_start one compound partition key. Rows in that partition are ordered by time and then by the unique event identifier. Without a tie-breaker, two writes with the same complete primary key address the same row; they do not create two readings.
A partition key must balance several dimensions: stored bytes, row count, write rate, read rate, and the number of replicas or coordinators involved. A tenant ID can have many distinct values yet still be hot if one tenant dominates. Salt or bucket only when the application can enumerate the resulting partitions within a deliberate fan-out limit.
Choose a time bucket from evidence
Estimate a bucket before launch, then measure it. A useful model is:
rows_per_bucket = peak_rows_per_second × bucket_seconds
bytes_per_bucket = rows_per_bucket × measured_average_stored_row_bytesInclude primary-key and cell overhead, tombstones, indexes, and replication in capacity work. Use representative data and tools such as nodetool tablestats and sstablepartitions to observe partition size distributions. Cassandra exposes a large-partition warning threshold, but that threshold is an operational warning—not a design target or hard storage limit.
- Shorter buckets bound partitions and spread a sustained hot stream, but range queries touch more partitions.
- Longer buckets reduce fan-out, but increase per-partition bytes, compaction work, and hot-key concentration.
- Calendar buckets must be computed identically by every writer and reader, with an explicit time zone.
- Changing bucket granularity creates a schema/version boundary; readers may need to query both layouts during migration.
Query across buckets deliberately
SELECT event_time, event_id, value, quality
FROM telemetry.readings_by_sensor_bucket
WHERE sensor_id = ?
AND bucket_start = ?
AND event_time >= ?
AND event_time < ?
LIMIT ?;The application enumerates the finite bucket set, issues bounded requests with the driver, and merges ordered pages. Limit concurrency so a long time range cannot flood coordinators. Persist paging state with the query definition and treat an unbounded export as a batch workflow, not an interactive read.
TTL expiry still creates tombstones
A TTL is useful when retention is part of the data contract. Cassandra marks expired rows or cells with tombstones and removes eligible tombstones during compaction after the safety rules are satisfied. TTL does not avoid tombstones; it makes expiration predictable.
INSERT INTO telemetry.readings_by_sensor_bucket
(sensor_id, bucket_start, event_time, event_id, value, quality)
VALUES (?, ?, ?, now(), ?, ?)
USING TTL ?;Use default_time_to_live only when one retention period truly fits the table. Otherwise set TTL at the write path and test updates: writing a column again can change its expiration. Monitor tombstone scan warnings, read latency, dropped messages, pending compactions, disk usage, and the age and success of repairs.
Tombstones prevent deleted data on an unavailable replica from returning during repair. The grace period, repair schedule, hinted handoff, node outage policy, and compaction behavior form one safety design. Prove that all replicas are repaired within the chosen window before changing it.
Choose compaction for the write lifecycle
Cassandra 5.0 recommends Unified Compaction Strategy for most new workloads. TimeWindowCompactionStrategy remains a documented fit for data grouped by timestamp that is mostly immutable and expires with TTL. TWCS groups SSTables into time windows so fully expired windows can eventually be dropped efficiently.
ALTER TABLE telemetry.readings_by_sensor_bucket
WITH compaction = {
'class': 'TimeWindowCompactionStrategy',
'compaction_window_unit': 'DAYS',
'compaction_window_size': '3'
};The values above are illustrative, not defaults for every dataset. Apache's TWCS guidance suggests choosing a window size that yields roughly 20–30 windows across the retention period, then validating write rate, SSTable count, read amplification, and repair. Late writes and repair streams can mix old timestamps into newer SSTables and delay whole-SSTable expiry. Do not enable unsafe aggressive expiration as a routine tuning shortcut.
Filtering, indexes, and batches
ALLOW FILTERING means Cassandra cannot guarantee predictable work from the restrictions alone. It is not literally a full-cluster scan in every case, but it is a warning that cost may grow with data. Use it only after bounding and measuring the query, not to bypass schema design in an online request path.
Legacy 2i indexes are local to nodes and have significant limitations; Cassandra's documentation recommends SAI for most new indexing use cases. Test index selectivity, write cost, rebuild, backup, and degraded-node behavior.
A CQL batch is not a general bulk-loading or SQL transaction mechanism. Updates for one partition are isolated. Logged batches spanning partitions use the batch log and carry a performance penalty; reserve them for a real atomic completion requirement. Prefer asynchronous prepared writes for throughput when cross-partition atomic completion is not required.
Review checklist
- Every online query names one partition or a capped, enumerable set.
- Peak bytes and requests per partition are measured, not guessed.
- Clustering order matches range and sort requirements, with unique row identity.
- TTL, repair,
gc_grace_seconds, and compaction are designed together. - Multi-table writes have retry, reconciliation, and idempotency behavior.
- Load tests include hot tenants, late events, expiration, repair, compaction, and node loss.
Official primary sources
- CQL data definition, query-driven keys, and partitions
- Time Window Compaction Strategy
- Compaction and tombstones
- Cassandra indexing concepts
- CQL batches and data manipulation
Working with JusDB on Cassandra modeling
JusDB helps teams turn Cassandra access patterns into bounded partitions, test time-series retention and compaction, and monitor hot keys and tombstone pressure.
Explore JusDB Cassandra services → | Talk to a Cassandra engineer