"I'm also not too much in favor of triggering major compactions, because it mostly have a nasty effect (create one huge sstable)."
If that is the case, why can't major compactions create many, non-overlapping SSTables? In general, it seems to me that non-overlapping SSTables have all the advantages of big SSTables (i.e. you know exactly where the data is) without the disadvantages that come with being big. Why doesn't Cassandra take advantage of that in a major way?