I want to build an architecture of database that allows me get all data for previous month in one heavy query without performance issues for new inserts for current month. I thought that I can make partition key by month. But, as I understand this key will create 1 partition for example for June and will be inserting all new data to it. And even if I have 1000 nodes, Cassandra will be using only 1 node for inserting. I will lose line scaling of performance. Maybe I am not right and Cassandra creates a small partitions depends on nodes amount ?
1 Answers
Your understanding is mostly correct - at given month, only N nodes will be working (where N is the replication factor).
The actual design of the primary key will heavily dependent on how you are accessing that data besides that heavy pattern. If it's only the access pattern, you can use any partition key that will give you a good data distribution. And then for your heavy query you can just do the full scan of the data using Spark, DSBulk or even custom implementation like this - anyway with huge amount of data you won't expect milliseconds response.
P.S. Also, if it's the only query you need, I would argue that you may not need Cassandra - push the data into Kafka or something like that, and dump to Parquet/Delta Lake on other side - it will be much more cost efficient to keep data with too much compute.
Related