Hello everyone, I did not find any discussions about that topic and would like to ask if there any considerations to introduce the "PER PARTITION ORDER" functionality. It's a duplication of Scylla question, but now for Cassandra https://forum.scylladb.com/t/per-partition-local-ordering/3412. I am also not so experienced from the cassandra code implementation point of view, but according to my knowledge it should make sense.
Let me introduce the use case. Data model: CREATE TABLE data( device_id TEXT, sensor_id TEXT, time TIMESTAMP, value BLOB, PRIMARY KEY((device_id, sensor_id), time) ) Queries: Give me the first and the last value for all sensors within deviceId. Problem: Within the device it's possible to have 10k of sensors or more and if we wanted to get a "snapshot" (e.g. list of sensors with values having the max timestamp) then it may take lots of round trips for small request-response. Therefore we can use the "IN" clause here, grouping keys based on the replica node (e.g. batch node aware read). 1. First point SELECT * FROM data WHERE deviceId = 'mydevice' and sensor_id IN (‘s1’, ‘s2’, ‘s3’) PER PARTITION LIMIT 1 Here we can get the first point for each partition and don’t care about “global” ordering, so the resulting rows won’t be sorted by clustering key and natural order will be applied only locally within each partition. 2. Last point SELECT * FROM data WHERE deviceId = 'mydevice' and sensor_id IN (‘s1’, ‘s2’, ‘s3’) ORDER BY time DESC PER PARTITION LIMIT 1 It’s not possible to use IN and ORDER BY together with paging enabled. The reason is that Cassandra applies order “localy” within each partition, but also applies it “globally” across the resulting rows that makes cassandra store the result in-memory to apply “global” sorting. But if I don’t care about “global” ordering and only want to specify ordering within each partition that introduces performance overhead. What if to introduce "PER PARTITION ORDER" statement? In most of the use cases it should not introduce much benefits, because we're limited to the number of keys in the IN clause (by default 100), so the result should not be big enough to do not fit into the memory, but maybe someone have another use case when PER PARTITION LIMIT more than 1 or payload is big enough.
