The original size-tiered strategy should work fine for use cases where the read/write ratio is less than 50:50, and thus is slanted more toward the write-heavy side. But if a table’s use case calls for it to be read much more often than written to, LeveledCompactionStrategy should make for a good fit.
Using the leveled strategy helps read-heavy workloads perform better, as it significantly increases the chance of a row existing in a single SSTable file. Let's assume that I have a long-lived table that is updated on a semi-frequent basis, and that I want to query for a specific row:
SELECT * FROM users_by_deptWHERE department='IT' AND username='tedk';
As this row (r) gets updated every time an employee's ...
