SELECT * FROM orders WHERE status = 'PENDING';
InvalidRequest: Cannot execute this query as it might involve data filtering
and thus may have unpredictable performance. If you want to execute this query
despite the performance unpredictability, use ALLOW FILTERING
Coming from SQL, this looks absurd: the database is refusing a basic WHERE. Adding ALLOW FILTERING makes the error go away, and sometimes takes the cluster down with it. Here's what ScyllaDB (and Cassandra, which uses the same CQL rules) is telling you.
How data is laid out
A table's partition key decides which node stores a row: it's hashed to a token, and the token determines the replicas. The clustering columns sort rows within a partition.
CREATE TABLE orders (
customer_id uuid,
created_at timestamp,
order_id uuid,
status text,
total decimal,
PRIMARY KEY ((customer_id), created_at, order_id)
) WITH CLUSTERING ORDER BY (created_at DESC, order_id ASC);
Efficient queries restrict the partition key (= or IN) and then clustering columns in order:
SELECT * FROM orders WHERE customer_id = ?; -- one partition
SELECT * FROM orders customer_id ? created_at ;