Cassandra计量数据分区键选型及相关技术问题咨询

Hey there! Let's break down your Cassandra schema questions one by one—since you're working with time-series sensor data, this is a super common scenario where getting the partition key right makes all the difference for performance and scalability.

1. Which field should be the partition key? #

For your use case (needing to compare consecutive temperature readings from the same device/room), the best approach is to use a composite partition key that combines a device/room identifier with a bucketed timestamp. Here's why:

• Your core query pattern is likely: "Get the last N temperature readings for device X/room Y to calculate the difference from the previous one." Grouping data by device_id or room ensures all related readings are stored on the same node, making these queries fast and efficient.
• Adding a bucketed timestamp (like day or hour, derived from the full timestamp field) prevents partitions from growing indefinitely (more on that in question 3). For example, a partition key like (device_id, date_bucket) where date_bucket is the date in YYYY-MM-DD format will keep each partition's size manageable.

If you only need to query by room instead of individual devices, swap device_id with room in the composite key.

2. Using timestamp as the partition key: Will all load concentrate on the same node? #

Absolutely—this would create a severe hot node problem. Here's the breakdown:

• Every 15 minutes, you're receiving data from all your devices. If timestamp is the partition key, all those readings will land in the same partition (since they share the same timestamp value).
• Cassandra maps partitions to nodes using hash values. Since all these writes target the same partition, every 15-minute batch of data will hit the single node responsible for that partition's hash range.
• Over time, this node will bear the brunt of all write traffic, while other nodes sit underutilized. Even without replication, this creates an unsustainable load imbalance that will hurt performance and reliability.

3. Using device_id/room as the partition key: Unbounded partitions? Can TTL fix this? #

Yes, using just device_id or room as the partition key will lead to unbounded partitions. Each device/room generates data every 15 minutes, so over months or years, the partition will grow larger and larger. Cassandra performs best when partitions are between 1-10 GB; beyond that, you'll see slower queries, compaction issues, and increased risk of node failures.

Setting a time-to-live (TTL) on your data helps, but it's not a complete solution on its own:

• TTL will automatically delete old data, which prevents the partition from growing infinitely. However, if your TTL is long (e.g., 1 year), the partition will still contain a year's worth of data (35k+ readings per device), which could push the partition size over recommended limits if you have additional metadata fields.
• A better fix is pairing device_id/room with a bucketed timestamp (as suggested in question 1). This splits the data into smaller, time-bound partitions (e.g., one per device per day). You can still apply TTL to these partitions to automatically expire old buckets, keeping your cluster healthy and performant long-term.

内容的提问来源于stack exchange，提问作者Sander Cobbaert