Case Study: DENIC cut a key query from about five minutes to roughly 30 seconds with ClickHouse
Key results
The challenge
DENIC, the registry for .de domains, is increasingly focused on data analytics and built a dedicated analytics cluster. After saving several million records, a key query selecting domain states associated with holder updates took about five minutes and was worsening as data volumes grew.
The solution
Working with ClickHouse support, DENIC restructured its tables and query using ReplicatedReplacingMergeTree, filtering complete time series with GLOBAL IN, LIMIT 1 BY, and ARRAY JOIN to avoid expensive full-table scans.
The results, in context
DENIC reported that the query runtime was optimized from about five minutes to roughly 30 seconds, and that this level held even with several hundred million records imported and thousands of daily holder updates. Figures are quoted from the published account and dated.