After much help from this list, I've moved to a date-based table schema. Once a date has passed, there will be very little, if any, writing to that table. As such, I have a nightly process that goes through a performs a major compaction to move it down to a single table.
I've tried manipulating the chunk size for compaction, but there's very little change in the size of the final table. I'm currently using the LZ4 compressor. Does anybody have any tips for optimizing the longer term storage for "old" data? For example, will one of the other compressors be better for long-term storage?
