Tiered storage is now generally available in Aiven for ClickHouse®. Combine the speed of ClickHouse with the cost optimization of object storage for a powerful data warehouse solution.
Nick Rycar
|RSS FeedSubscribe for the latest news and insights on open source, Aiven offerings, and more.
ClickHouse® combines its columnar design, industry leading compression, and blazing fast queries to make it one of the most performant data warehouse solutions available. Aiven for ClickHouse® takes this a step further with tiered storage, now generally available in the Aiven console. Enabling tiered storage allows you to optimize your costs by using object storage in tandem with existing SSD storage for the best of both worlds.
Tiered storage can be enabled on a per-table basis either from within the Aiven console, or on the command line with the ClickHouse client. Once enabled, data will be distributed between SSDs and object storage based on your defined data threshold.
By default, once an Aiven for ClickHouse instance's SSDs hit 80% capacity, data will be automatically moved to object storage. This behavior can be further refined by creating an explicit TTL, where all data older than a user-defined interval is moved to object storage on a recurring basis. Data can also be manually moved to object storage as needed for any one-off use cases.
At any point you can check the distribution of data in your Aiven for ClickHouse tables from within the Aiven console. You can also query this data via the ClickHouse client like so:
SELECT database, table, disk_name, formatReadableSize(sum(data_compressed_bytes)) AS total_size, count(*) AS parts_count, formatReadableSize(min(data_compressed_bytes)) AS min_part_size, formatReadableSize(median(data_compressed_bytes)) AS median_part_size, formatReadableSize(max(data_compressed_bytes)) AS max_part_size FROM system.parts GROUP BY database, table, disk_name ORDER BY database ASC, table ASC, disk_name ASC
Which should provide output similar to:
So, when would you want to use tiered storage in your environment? Any time you might want to trade query performance for cost optimization. For example: logs.
Aiven for ClickHouse is already a great option for logs. There's an OpenTelemetry ClickHouse exporter, for example, that can be combined with the ClickHouse plugin in Aiven for Grafana to visualize queries and create logging dashboards very quickly. The trick becomes keeping that logging solution cost effective.
Often an organization will have a retention policy that requires them to keep logs for months or years, but the most of the queries and analytics they run only apply to the most recent data in the table. As such, keeping the entirety of their logs on SSDs could get very costly without diminishing returns, as the vast majority of the data they're storing is rarely accessed.
The solution in this case would be to determine roughly how far back in time the average query is interested in, and define that as a TTL for triggering tiered storage. Any data older than that TTL is stored on object storage and still accessible, if slightly less performant, and the latest data is kept on SSDs so that investigating emergent issues is as fast as possible.
You can learn more about tiered storage in the Aiven for ClickHouse documentation.
If you have any questions about configuring tiered storage on your services, don't hesitate to reach out to support@aiven.io.
