Partition Keys & Data Modeling in ScyllaDB

Question

In Scylla, data is stored by partition key. If I query a large table with many partition keys, is it equivalent to executing multiple queries against the table? For example, suppose I have the following table:

key1 : val1
key2 : val2
key3 : val3

Where each of the 3 keys (key1..3) is a different partition key.

If I execute the following query against the table:

SELECT * from table.

Scylla, will presumably need to execute this query 3 times - on 3 different partitions since each row is stored on a different partition. It seems inefficient, as it means the query will be executed once per partition. Suppose the data was partitioned into 100 partitions (100 keys), will the query need to be executed 100 times to complete? (and by extension, the query will only be as fast as the slowest server?)

If this is true, then querying 1 row from 3 separate tables (e.g, where each row has a different partition key), should have identical performance as when querying 3 rows from one table where each of 3 three rows has a different partition key? In other words, whether the data is modeled as part of one table or multiple tables, doesn't really matter. What matters is whether two or more rows share the same partition key?

What happens when we query 3 different tables were each row has the same partition key, is this as efficient as querying 3 rows from one table where all of the rows have the same partition key?

Any guidance in evaluating performance expectations in the 3 scenarios described above would be very helpful.

Thanks!

Answer 1

As you noted, the query SELECT * FROM table is not a query in an individual partition, but rather a whole-table scan. A whole-table scan is "costly" in the sense that it will need to read all the data in the table (if you run it to completion), but it is not as inefficient as you thought it might be:

Scylla or Cassandra do not begin such a query by looking for the list of extant partition keys - and then querying each of those individually. Instead, Scylla and Cassandra have a deterministic order for the partition keys, so-called "token" order (you can think of the partition key's "token" as a hash function). Individual server nodes hold contiguous ranges of these tokens, so scanning the entire table is achieved by scanning each of these contiguous token ranges (also called "vnodes") - each of which is implemented efficiently by an individual node efficiently reading data sequentially from its own disk. So you can have a million or even a billion partitions, and SELECT * FROM table for reading the entire table will still involve mostly-sequential reads from disk - not a million or billion seeks to individual partitions.

Another comment I feel compelled to make is that if you are thinking about having just 3 partitions, and worrying about increasing the number to 100, you are misunderstanding data modeling in Scylla (and Cassandra). In fact, having 100 partitions is still too few. You should have a lot more than 100 partitions. The more, the better. The reason is that if you have only a few huge partitions, the data will not be evently distributed between nodes and shards (CPUs). If you have just 3 partitions and 100 CPUs, since each partition is owned by one CPU (in Cassandra, one server), you'll only have 3 out of the 100 CPUs working, which is certainly not a good idea. Having a million partitions is much better than having just 3.

Answer 2

In the future, please try to ask only one question per question.

SELECT * from table

Without the ability to determine an exact partition, the driver will choose a node in the cluster to send the query. This node becomes a "coordinator" for this query. It then sends requests out to each node in the cluster, and builds the result set. Once complete, the coordinator returns the result set back to the driver. In this particular case, it has to poll all nodes in the cluster to find only 3 rows...not terribly efficient.

This is why unbound queries really aren't a good idea in the Cassandra/Scylla world, as that one node becomes responsible for polling the data from all of the other nodes. In a large cluster, large data scenario, it is not unheard of for a node acting as a coordinator to slow down, or even crash.

If this is true, then querying 1 row from 3 separate tables (e.g, where each row has a different partition key), should have identical performance

I assume from reading this, that a partition key is being provided as a part of each query's WHERE clause. Querying a single, specific row from 3 separate tables will be faster. Basically, it won't need to exhaustively check every node in the cluster. The driver can simply hash the three partition keys, and know exactly where to go to get the data. The three queries will perform even faster in this scenario if the driver is using a token-aware load balancing policy, as there is no need for a single node to act as coordinator, skipping one network trip.

What happens when we query 3 different tables were each row has the same partition key, is this as efficient as querying 3 rows from one table where all of the rows have the same partition key?

This will perform similarly to previous scenario, where three distinct queries will be run. The fact that they all go to the same partition shouldn't make much of a difference, other than the same nodes will be used to serve the data.

For additional reference, here's a link to a Fault Tolerance diagram from Scylla's docs. It provides more, visual detail on the paths of read and write operations, as well as the effects of replication factor, consistency level, and multiple nodes.