We're going to use ClickHouse to speed up our waste management reports for large scale time intervals (a year or so). An idea is to store a daily data in ClickHouse, like a garbage volume collected for a day etc. That should make summary reports much faster than current PostgreSQL implementation.
But volume report should also include today's data. It's still one row, but its values are incrementing during the day. What's the best way to do that?
- Make two requests, to ClickHouse for past data an to Postgres for today, and join them after that.
- Refresh today's data in ClickHouse every hour.
- Refresh today's data in ClickHouse immediately when it's coming.
- Use VersionedCollapsingMergeTree.
What's the best strategy?