Apache Cassandra股票数据模型：主键/分区键/聚类键选型咨询

Let’s break this down step by step—Cassandra’s performance lives or dies by aligning your primary key design with your actual query patterns, so getting this right is critical.

Scenario 1: Filter by Stock_Symbol + Optional Time Range #

Your core query always starts with Stock_Symbol, with possible time range filters. Here’s the optimal design:

Primary Key Definition #

PRIMARY KEY ((Stock_Symbol), Timestamp)

Why this works: #

• Partition Key: Stock_Symbol (wrapped in parentheses to mark it as the sole partition key). With 30,000 unique stock symbols, this creates 30,000 partitions—right in Cassandra’s sweet spot (10k-100k partitions avoids both hotspots from too few partitions and overhead from too many). Each partition holds all price data for a single stock, making it easy to target exactly the data you need.
• Clustering Key: Timestamp. This sorts all price entries for a stock by time within the partition. When you add a time range filter (e.g., WHERE Stock_Symbol = 'AAPL' AND Timestamp >= '2024-01-01'), Cassandra can scan only the relevant range within the stock’s partition—no cross-node lookups needed, which is blazingly fast.

Edge Case Note: #

If a single stock generates massive amounts of data (e.g., high-frequency tick data exceeding 10GB per stock over time), you can add a time bucket (like year-month) to the partition key to split large partitions:

PRIMARY KEY ((Stock_Symbol, year_month), Timestamp)

But for most use cases, the basic design will work perfectly.

Scenario 2: Filter by Exchange_ID → Stock_Symbol (With 2 Exchanges) #

The big red flag here is only 2 possible Exchange_ID values—never use Exchange_ID alone as a partition key! That would create just 2 partitions, leading to crippling hotspots (all writes/queries hitting the same few nodes). Here’s the fix:

Primary Key Definition #

PRIMARY KEY ((Exchange_ID, Stock_Symbol), Timestamp)

Why this works: #

• Composite Partition Key: (Exchange_ID, Stock_Symbol) combines both fields to create unique partitions. With 2 exchanges × 30,000 stocks, you get 60,000 partitions—still well within the optimal range. This eliminates hotspots entirely, as each (exchange, stock) pair gets its own partition.
• Clustering Key: Timestamp again sorts entries by time within each partition, supporting efficient time range queries just like scenario 1.
• Aligns with Write Order: You mentioned writes follow the same order as filters (Exchange_ID → Stock_Symbol → Timestamp). This matches the primary key’s structure, so writes are naturally grouped into partitions without unnecessary shuffling, boosting write performance.

Optional: Supporting "All Stocks in an Exchange" Queries #

If you need to occasionally query all stocks under a single exchange, you’ll need a separate table (Cassandra favors denormalization for query performance). For this secondary use case, use:

PRIMARY KEY ((Exchange_ID, year_month), Stock_Symbol, Timestamp)

Adding a time bucket here prevents the exchange-level partition from growing too large (since 2 exchanges × monthly buckets keeps partition sizes manageable).

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