Cassandra读密集型库：双列主键分表与三列主键单表选型咨询

Great question—Cassandra’s data modeling lives and dies by optimizing for your query patterns, especially when you’re dealing with read-heavy workloads. Let’s break this down into the two scenarios you’re asking about.

1. Two Small Independent Tables vs. One Large Table #

The answer here boils down to how your queries actually access the data:

• Go with one large table if every query needs to pull most of the fields in the table, or if there’s no clear split in which fields are accessed together. Since Cassandra stores data in rows, a single table avoids the need for any joins (which Cassandra is terrible at) and keeps related data colocated.
• Opt for two small tables if your queries fall into distinct groups that access separate sets of fields. For example, if you have one query pattern that pulls user profile data (name, email, signup date) and another that pulls their recent activity (logins, purchases), splitting these into two tables means each read operation only fetches the data it needs. This reduces I/O overhead significantly—smaller rows mean faster reads, especially in high-throughput scenarios. Plus, you avoid the pitfalls of wide rows (like increased memory usage for caching) if the large table would have dozens of rarely accessed fields.

2. Two Tables with 2-Column Primary Keys vs. One Table with 3-Column Composite Primary Key (Read-Heavy Scenario) #

This is where Cassandra’s primary key design becomes critical for read performance. Let’s start with a key rule: Cassandra reads are fastest when they can directly target a partition + clustering key combination without filtering.

In a read-heavy workload, the two independent 2-column primary key tables are almost always the better choice. Here’s why:

• Each table is purpose-built for a specific query pattern. Suppose you have:
  • Table 1: user_orders_by_id with primary key (user_id, order_id) (for fetching a single order by ID)
  • Table 2: user_orders_by_date with primary key (user_id, order_date) (for fetching all orders for a user on a specific date)
    If you merged these into a single table with primary key (user_id, order_id, order_date), trying to fetch all orders for a user on a date would require scanning every order_id in the user_id partition and filtering by order_date—a slow, resource-heavy operation, especially as the number of orders grows.
• Cassandra encourages denormalization (redundant data) as a tradeoff for read speed. The extra storage cost of having two tables is negligible compared to the performance gains of having each query hit an optimized table directly.
• Composite keys work best when your queries always use all parts of the key. If your read patterns don’t consistently use all three columns in the composite key, you’re forcing Cassandra to do unnecessary work to find the data you need.

Quick Recap #

• For small vs large tables: Align table size with query field access patterns.
• For primary key choices in read-heavy workloads: Prioritize tables optimized for each specific query—denormalize to avoid expensive scans.

内容的提问来源于stack exchange，提问作者Aman Kumar Sinha