A partitioning key is the part of a primary key that decides which partition holds a row. In a simple primary key, it is the first column: in PRIMARY KEY (restaurant, ordered_at, food), restaurant is the partitioning key, while ordered_at and food are clustering keys that sort rows inside that partition. Rows for the same restaurant stay together; rows for different restaurants belong to different partitions. section 2.2.2 section 2.3.1
Use the partitioning key in a query’s WHERE clause whenever possible. ScyllaDB hashes the supplied value, finds the node that owns the partition, and contacts that owner and its replicas. A query without it may need to scan every partition, involving far more nodes and creating a serious scaling risk. The practical target is one query reading one partition. section 2.3.2 section 2.5
Reviews for one restaurant
To fetch reviews for Ernie’s Eats, filter by restaurant: SELECT * FROM initial.food_reviews WHERE restaurant = 'Ernie''s Eats';. This targets one partition. Filtering only by ordered_at can find a matching row in a tiny dataset, but it cannot be routed directly and produces an ALLOW FILTERING warning because it must inspect all partitions. section 2.3.2
Figure 3.8 Partitions are mapped to places on a hash ring.
The hash-ring view shows why a partitioning-key value can route data: its hash maps the partition to a position owned by a node. section 3.3.1
Choose partitioning keys from required access patterns, not just from what uniquely identifies an entity. If article summaries must be loaded by author, date, and score, separate denormalized tables can partition the same summary by author ID, date bucket, and score. Each table then supports its intended lookup without scanning unrelated partitions. section 3.2.1 section 3.2.2
A useful design check balances efficient reads with even distribution:
- Start with the queries the application must serve, then choose a table whose partitioning key each query can provide. section 3.1 section 3.3.2
- Avoid concentrating all rows and traffic in one partition, which can unevenly load nodes and reduce fault tolerance. section 3.1 section 5.1.3
- Avoid partitions so narrow that assembling a result requires many queries, including empty partitions. section 5.1.2
- Bucket similar values when needed—for example, group dates into a larger time period—to tune partition size against the number of reads. section 5.1.3
The partitioning key also explains the cluster’s layout. ScyllaDB hashes each key value to a token on a hash ring; nodes own slices of that ring, often through multiple virtual nodes. Equal keys map to the same location, while differing keys almost always map elsewhere. This distribution helps spread data and lets a coordinator route requests to the relevant nodes. section 3.3 section 3.3.1
Article-summary tables
Three tables can store similar article-summary data for three read paths: PRIMARY KEY (author_id, id) supports summaries by author; PRIMARY KEY (date_bucket, id) supports recent summaries by bucketed date; PRIMARY KEY (score, id) supports summaries by score. The first key column changes because the intended query changes; id remains a clustering key and is ordered descending for recent results first. section 5.3.2
