I need to get the number of rows in a partition. Right now, I’m planning to just store a counter table and increment counters when new rows are made in the corresponding partition. But it made me wonder if SELECT COUNT (1) FROM table WHERE partition_key = ? would be optimized in this case (given that partitions are distributed), allowing me to avoid storing redundant data (and avoid some writes, but that doesn’t matter).
Does Scylla store the number of rows per partition? And would SELECT COUNT be efficient in this case?
Taking a gander at the source code for [cql3::functions::aggregate_fcts::make_count_function] (and countRows), it doesn’t look like it handles partitions in any special way (unless it is handled elsewhere). I guess I’ll go with Counters for now.
Indeed, ScyllaDB does not maintain a row count for partitions internally, instead COUNT() will invoke the regular COUNT code-path which will request all rows from the replicas and count them on the coordinator.
Normally, this is a very expensive operation, because a full scan on a table is expensive. However, when invoked on a single partition, it should not be particularly expensive.
I would recommend you to test the performance and impact of this query and decide based on the results whether you really need a row-count column.