Skip to content
SimplyMe
Go back

Cassandra Anti-Patterns: The Unbounded Partition Trap in Activity Feeds

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:

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:

  1. Consistent Hashing & Token Placement: Cassandra hashes the Partition Key (user_id) using Murmur3Partitioner to 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.

  2. 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.

  3. 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:

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:

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

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?

  1. ((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.
  2. 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:

  1. Table-level or Row-level TTL:
    ALTER TABLE user_activities_bucketed
    WITH default_time_to_live = 7776000; -- 90 days in seconds
  2. 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

DimensionBad Schema (user_id)Fixed Schema ((user_id, time_bucket))
Partition SizeUnbounded (GBs over time)Bounded (< 50 MB per bucket)
Node DistributionProne to hot spotsEvenly distributed across token ring
Read LatencyDegrades over timeConsistent low-millisecond latencies
GC / Heap ImpactSevere risk of OOMMinimal heap footprint
Compaction CostExpensive compaction debtClean, 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.


Share this post:

Next Post
Secure Inline: Encrypting Secrets Inside Obsidian Notes, Not Around Them