Cassandra won't try and load a partition into memory. It doesn't work that way. The only way you would get behavior like that is by setting "allow filtering" to on. Allow filtering is a dedicated keyword for "I know I shouldn't do this but I'm going to anyway". If you're trying to run those types of queries, use a different database. If someone is making you use a transactional database without joins for analytical load, get a different job because that's a nightmare.
Also, your partitions should never get that large. If you're designing your tables in such away that the partitions grow unbounded, there's an issue. There are lots of ways to ensure that the cardinality of partitions grows as the dataset grows. And you actually control this behavior by managing the partitioning. It's really easy to grok the distribution of data in on disk if you think about how it's keyed.
You've basically listed a bunch of examples of what happens when you don't use a wide columnar store correctly. If you're constantly fixing downed nodes, you're probably running the cluster on hardware from Goodwill.
I huge partition is often spread across multiple sstables, and often has tombstone issues if the large partition consists of a large number of column keys or any regular update cycle, which is often the case for hot rows.
In that case the overhead of processing and collecting all the parts of the data you need spread across different sstables and then do tombstones can lead to a lot of memory stress.
Also, your partitions should never get that large. If you're designing your tables in such away that the partitions grow unbounded, there's an issue. There are lots of ways to ensure that the cardinality of partitions grows as the dataset grows. And you actually control this behavior by managing the partitioning. It's really easy to grok the distribution of data in on disk if you think about how it's keyed.
You've basically listed a bunch of examples of what happens when you don't use a wide columnar store correctly. If you're constantly fixing downed nodes, you're probably running the cluster on hardware from Goodwill.
This is a pretty good list of what not to do with Cassandra, or any similar database. https://blog.softwaremill.com/7-mistakes-when-using-apache-c...