When transitioning from relational databases (PostgreSQL, MySQL) to distributed wide-column stores like Apache Cassandra or ScyllaDB, developers frequently bring relational habits into CQL (Cassandra Query Language). In SQL, creating a simple (user_id, timestamp) index is trivial and scales well with B-Trees.
In Cassandra, doing the same for high-throughput activity logs is one of the most dangerous architectural anti-patterns you can push to production.
Let’s analyze this common table definition, dissect why it fails under load, and implement the correct bucketing strategy.
The Problematic Table
Consider this schema designed to store user events or audit trails:
-- ANTI-PATTERN: Partisi tumbuh tanpa batas (Unbounded Partition)
CREATE TABLE user_activities_bad (
user_id uuid,
activity_id timeuuid,
activity_type text,
payload text,
PRIMARY KEY (user_id, activity_id)
);
At first glance, it looks reasonable:
user_idis the Partition Key.activity_id(timeuuid) is the Clustering Key, sorting records chronologically per user.
So, what makes this an anti-pattern?
Anatomy of the Failure: How Cassandra Stores Partitions
To understand why this breaks down, we must examine how Cassandra distributes and reads data on disk:
-
Consistent Hashing & Token Placement: Cassandra hashes the Partition Key (
user_id) usingMurmur3Partitionerto determine which replica nodes own the partition. All rows sharing the same partition key reside on the exact same physical node replica set. A single partition cannot span across multiple nodes. -
Unbounded Temporal Growth: Unlike static profile data, activity logs are append-heavy and grow indefinitely. As a user browses, clicks, purchases, and triggers events over months or years, all new rows append to that exact same partition.
-
The “Power User” / Heavy Hitter Anomaly: While inactive users might only accumulate a few hundred rows, highly active users, enterprise accounts, or automated bots can easily generate hundreds of thousands or millions of events.
Production Consequences: The Cascade of Failure
When partitions grow without boundaries, several critical failure modes emerge:
1. Violating the 100 MB Partition Limit
Cassandra’s internal architecture is optimized for small, bounded partitions. The industry rule of thumb is:
- Target partition size: < 10 MB to 50 MB
- Absolute maximum limit: 100 MB
- Cell / Row count: < 100,000 cells per partition
Partitions exceeding 100 MB cause significant performance degradation.
2. JVM Heap Exhaustion & Garbage Collection (GC) Freezes
When reading data from an oversized partition (e.g., retrieving an activity feed or slice of rows), Cassandra must read the partition index and traverse SSTable slices. Deserializing large row collections puts immense pressure on the JVM Heap, resulting in:
- Stop-the-world GC pauses (often 5–30+ seconds).
- Client-side read timeouts (
ReadTimeoutException). - In severe cases, JVM node crashes via
OutOfMemoryError.
3. Disk Hotspotting and Cluster Imbalance
Because a partition lives on a single replica set, nodes holding heavy-hitter partitions accumulate disproportionately large amounts of disk storage and I/O load. This defeats the primary goal of horizontal scaling.
4. Compaction Bottlenecks & Nodetool Repair Hell
- Compaction: Large partitions must be merged across SSTables during Size-Tiered or Leveled Compaction. Merging multi-gigabyte partitions requires huge temporary disk overhead and saturates disk I/O.
- Repairs: Running
nodetool repaircomputes Merkle trees over token ranges. When partitions are oversized, Merkle tree resolution differences force massive streaming transfers across nodes, often timing out repeatedly.
5. The Tombstone Minefield
If you attempt to clean up old events via DELETE or use Cassandra TTL (Time-To-Live), every deleted or expired row generates a tombstone.
When a query reads recent records from that user, the storage engine still has to scan through thousands of tombstone markers. Once it exceeds tombstone_failure_threshold (default 100,000 in Cassandra), the query aborts immediately.
The Solution: Time-Window Bucketing
To fix the unbounded partition, we introduce a bucket into the Partition Key. This creates a Composite Partition Key, ensuring that rows are capped within a predictable time slice.
The Correct Schema
CREATE TABLE user_activities_bucketed (
user_id uuid,
time_bucket text, -- e.g., '2026-11' (monthly) or '2026-W46' (weekly)
activity_id timeuuid,
activity_type text,
payload text,
PRIMARY KEY ((user_id, time_bucket), activity_id)
) WITH CLUSTERING ORDER BY (activity_id DESC);
What Changed?
((user_id, time_bucket), activity_id): The partition key is now(user_id, time_bucket). Every month (or week), a new partition is created. Even if a user generates 50,000 events a month, each partition stays comfortably within safe memory and disk boundaries.WITH CLUSTERING ORDER BY (activity_id DESC): Events are stored on disk in reverse chronological order. Reading the most recent activities is an instant O(1) read from the head of the partition.
Application Query Strategy
With bucketing in place, how does your service fetch data?
Fetching the Latest Events
Most feeds only display the user’s latest 10–20 activities:
SELECT activity_id, activity_type, payload
FROM user_activities_bucketed
WHERE user_id = ? AND time_bucket = '2026-11'
LIMIT 20;
Paginating Across Time Boundaries
If the user had only 5 activities in November and requested 20, the application executes a fallback query against the previous bucket:
SELECT activity_id, activity_type, payload
FROM user_activities_bucketed
WHERE user_id = ? AND time_bucket = '2026-10'
LIMIT 15;
Because bucket names follow a deterministic pattern (YYYY-MM or YYYY-Www), calculating past buckets in code is simple integer or date arithmetic.
Bonus: Pairing with TTL and TWCS
If your business rules only require retaining activity logs for a set period (e.g., 90 days), combine bucketing with:
- Table-level or Row-level TTL:
ALTER TABLE user_activities_bucketed WITH default_time_to_live = 7776000; -- 90 days in seconds TimeWindowCompactionStrategy(TWCS):ALTER TABLE user_activities_bucketed WITH compaction = { 'class': 'TimeWindowCompactionStrategy', 'compaction_window_unit': 'DAYS', 'compaction_window_size': '7' };
With TWCS, Cassandra groups SSTables by time window. Once all rows in an SSTable expire past their TTL, Cassandra simply drops the entire SSTable file from disk without executing expensive compaction merges.
Summary Checklist
| Dimension | Bad Schema (user_id) | Fixed Schema ((user_id, time_bucket)) |
|---|---|---|
| Partition Size | Unbounded (GBs over time) | Bounded (< 50 MB per bucket) |
| Node Distribution | Prone to hot spots | Evenly distributed across token ring |
| Read Latency | Degrades over time | Consistent low-millisecond latencies |
| GC / Heap Impact | Severe risk of OOM | Minimal heap footprint |
| Compaction Cost | Expensive compaction debt | Clean, predictable TWCS compaction |
When designing time-series tables in Cassandra or ScyllaDB, always bound your partition keys. Time-window bucketing transforms volatile, unbounded partitions into resilient, highly scalable distributed feeds.