Overview
ClickHouse 25.12 release introduces significant performance optimizations including faster Top-N queries using granule-level data skipping indexes (5-10x speedup), a redesigned lazy reading execution model using join-style materialization (75x faster), and a more powerful DPsize join reordering algorithm. The release also promotes the text index to beta status and adds new SQL functions including dictGetKeys, non-constant IN clauses, and HMAC for webhook authentication.
What You'll Learn
1
How ClickHouse uses granule-level min/max metadata to skip irrelevant data in Top-N queries, achieving 5-10x speedups
2
How the join-style lazy materialization model replaces row-by-row lookups to achieve 75x faster query execution for large LIMIT values
3
How the DPsize dynamic programming algorithm explores more join orders than greedy approaches to produce more efficient execution plans
4
How to use ClickHouse's beta text index with tokenizers for full-text search queries
5
How to use the HMAC function to validate webhook signatures and build secure webhook ingestion pipelines in ClickHouse
Prerequisites & Requirements
- Understanding of SQL query execution, including ORDER BY, LIMIT, and JOIN operations
- Familiarity with ClickHouse's MergeTree storage engine and granule-based data organization
- Basic understanding of query optimization concepts like data skipping indexes and join reordering(optional)
- ClickHouse server version 25.12 or later
- Experience writing analytical SQL queries with joins, aggregations, and ordering
Key Questions Answered
How does ClickHouse 25.12 make Top-N queries faster with data skipping indexes?
ClickHouse 25.12 compares the current Top-N threshold against granule-level min/max metadata to skip entire granules before any data is read. This works both statically for simple Top-N queries and dynamically for filtered queries where the threshold tightens during execution. In tests, this reduced data read by one to two orders of magnitude and sped up queries by 5-10x, with even larger gains on large tables and cold cache.
What is lazy materialization in ClickHouse and how was it improved in 25.12?
Lazy materialization defers reading non-ordering columns until the final Top-N result set is known. Before 25.12, remaining columns were fetched row by row, creating N × M individual column reads. In 25.12, a join-style execution model performs a single batched lookup that joins row identifiers back to the base table, achieving 75x faster performance. The default limit was raised from 10 to 10,000 rows.
What is the DPsize join reordering algorithm in ClickHouse?
DPsize is a classic dynamic programming algorithm for join reordering that constructs optimal join orders bottom-up. It starts with single-table plans, builds optimal pairs, then three tables, and so on, always reusing the cheapest subplans found so far. Unlike greedy algorithms, DPsize explores many more possible join orders, producing more efficient execution plans at the cost of higher optimizer time. It is enabled via the query_plan_optimize_join_order_algorithm setting.
How does ClickHouse's text index work and what tokenizers are available?
ClickHouse's text index (now in beta) supports full-text search using tokenized inverted indexes. Available tokenizers include splitByNonAlpha, splitByString, ngrams, sparseGrams, and array. Queries use hasToken, hasAllTokens, and hasAnyTokens functions. The index only activates when complete tokens can be extracted from search terms—using LIKE with wildcards won't trigger it unless spaces surround the search term.
How can ClickHouse be used as a webhook endpoint with HMAC signature validation?
ClickHouse 25.12 introduces an HMAC function for message authentication. You create a staging table that captures HTTP headers via getClientHTTPHeader, then use a materialized view that validates the signature by comparing it against HMAC('SHA256', raw_payload, 'secret_key'). Only rows with valid signatures are forwarded to the production table, enabling ClickHouse to serve as a secure webhook receiver.
What is the dictGetKeys function in ClickHouse 25.12?
dictGetKeys is a new function that performs reverse lookups on ClickHouse dictionaries, returning all keys that match a given attribute value. For example, it can find all LocationIDs for a specific borough in a taxi zone dictionary. The function automatically creates a per-query cache for fast bulk lookups, controlled by the max_reverse_dictionary_lookup_cache_size_bytes setting.
What changed with non-constant IN clauses in ClickHouse 25.12?
Before 25.12, the IN clause required constant lists as its second argument, throwing an UNSUPPORTED_METHOD error for dynamic expressions. In 25.12, non-constant lists are now supported, allowing conditional expressions like ternary operators to generate the IN list at query time. For example, you can write WHERE column IN (condition ? [value1] : [value2]) to dynamically filter based on other column values.
How does DPsize compare to greedy join reordering in ClickHouse performance?
On the TPC-H eight-table join benchmark at scale factor 100, DPsize produced a plan approximately 4.7% faster than greedy reordering (2.66s vs 2.70s). DPsize delayed joining the customer table until last, reducing intermediate data. The performance gap grows with query complexity—more tables, larger size differences between relations, and less obvious join orders amplify DPsize's advantage over greedy approaches.
Key Statistics & Figures
Top-N query speedup with data skipping indexes
5-10x
ClickHouse 25.12 Top-N queries with granule-level data skipping
Data read reduction with Top-N optimization
1-2 orders of magnitude
Amount of data read by Top-N queries in tests
Skip index performance on large table
Under 0.2 seconds on 50 billion rows
Community member testing on production tables
Lazy reading speedup (new vs old)
75x faster
Join-style lazy materialization vs row-by-row materialization on LIMIT 100,000 query
Lazy reading speedup (new vs no lazy reading)
14x faster
Join-style lazy materialization vs eager reading all columns
Query execution time improvement (lazy reading)
From 38.7 seconds to 0.513 seconds
SELECT * FROM hits ORDER BY EventTime LIMIT 100000 with lazy materialization
Default lazy materialization limit increase
From 10 to 10,000
query_plan_max_limit_for_lazy_materialization default value change
DPsize vs greedy join reordering speedup
4.7% faster
Eight-table TPC-H join query at scale factor 100
New features in release
26
ClickHouse 25.12 release
Performance optimizations in release
31
ClickHouse 25.12 release
Bug fixes in release
129
ClickHouse 25.12 release
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Clickhouse
Primary analytical database system being released and optimized
Query Language
SQL
Query language used throughout all examples and demonstrations
Storage Engine
Mergetree
ClickHouse's primary storage engine with granule-based data organization
Benchmark
Tpc-h
Standard join benchmark used to demonstrate DPsize join reordering
Benchmark
Clickbench
Real-world web analytics benchmark where ClickHouse is fastest
Benchmark
Jsonbench
JSON query benchmark referenced for Top-N query prevalence
Cloud Infrastructure
AWS EC2
m6i.8xlarge instances used for benchmarking (32 cores, 128 GB RAM)
Security
Hmac
New function for webhook signature validation and message authentication
Database
Postgresql
Referenced as another database system that uses DPsize join reordering
Security
Openssl
Used in webhook demo to generate HMAC signatures for testing
Key Actionable Insights
1Upgrade to ClickHouse 25.12 to automatically benefit from granule-level data skipping on Top-N queries. The optimization applies both statically and dynamically, reducing data reads by 1-2 orders of magnitude and speeding up ORDER BY ... LIMIT N queries by 5-10x without any query changes.This is especially impactful for dashboards, monitoring systems, and ranking reports that frequently use Top-N patterns. Benefits are largest on large tables and cold cache scenarios.
2Increase your LIMIT values confidently with the new lazy materialization model. The default query_plan_max_limit_for_lazy_materialization has been raised from 10 to 10,000, and the join-style execution model makes large LIMIT values 75x faster than the previous row-by-row approach.Previously, lazy reading with large LIMIT values was slower than eager reading due to per-row overhead. The new batched join approach eliminates this limitation, making it viable for queries returning thousands of rows.
3Enable DPsize join reordering for complex multi-table INNER JOIN queries by setting query_plan_optimize_join_order_algorithm='dpsize,greedy'. This explores more join orderings than greedy alone and falls back gracefully.The benefit grows with query complexity. For simple joins, greedy may suffice, but for queries joining many tables with varying sizes, DPsize can find significantly better execution plans. Currently experimental and limited to INNER JOINs.
4When using the text index for full-text search, prefer hasToken, hasAllTokens, and hasAnyTokens functions over LIKE patterns. The text index only activates when complete tokens can be extracted from the search term, meaning LIKE '%term%' won't use it.If you must use LIKE, add spaces around the search term (e.g., '% OpenAI %') to allow token extraction. Note this may return fewer results since it requires the term to appear as a standalone token rather than a substring.
5Use the new HMAC function to build secure webhook ingestion pipelines directly in ClickHouse. Combine staging tables, materialized views, and HMAC signature validation to filter authenticated payloads without external middleware.This requires enabling allow_get_client_http_header in the ClickHouse profile configuration. The pattern uses a staging table for all incoming payloads and a materialized view that forwards only signature-verified records to a production table.
6Leverage dictGetKeys for reverse dictionary lookups when you need to find all keys matching a specific attribute value. This enables efficient 'find all X where attribute = Y' queries on dictionaries without scanning the entire dataset.The function includes an automatic per-query cache, making bulk reverse lookups fast. Cache size is configurable via max_reverse_dictionary_lookup_cache_size_bytes.
Common Pitfalls
1
Using LIKE '%term%' patterns with the text index will not trigger index usage. The text index only activates when complete tokens can be extracted from the search term. Without surrounding spaces, ClickHouse falls back to a full scan, which is dramatically slower.
Use hasToken, hasAllTokens, or hasAnyTokens functions instead. If you must use LIKE, add spaces around the term (e.g., '% OpenAI %') to enable token extraction, but note this may return fewer results.
2
Setting query_plan_max_limit_for_lazy_materialization too high or to 0 (unlimited) in versions before 25.12 will cause severe performance degradation. The old row-by-row lazy materialization creates N × M individual column lookups, which for LIMIT 100,000 with 104 columns means approximately 10 million scattered reads.
In 25.12, the default was raised to 10,000 safely because the join-style model eliminates per-row overhead. For pre-25.12 versions, stick with the default of 10 or disable lazy reading for large LIMIT values.
3
Assuming DPsize always produces a faster plan than greedy join reordering. DPsize explores more join orders and is more optimizer-time expensive. For simple queries with few tables, the greedy algorithm may produce an equally good plan with less optimization overhead.
Use query_plan_optimize_join_order_algorithm='dpsize,greedy' to try DPsize first with automatic fallback to greedy. The impact of DPsize grows with query complexity, number of joined tables, and size differences between relations.
4
When creating a text index in ClickHouse 25.12, you can no longer use the 'default' tokenizer value. You must explicitly specify a tokenizer such as 'splitByNonAlpha', 'splitByString', 'ngrams', 'sparseGrams', or 'array'. Failing to specify one will result in an error.
This is a breaking change from the 25.9 introduction of text index v3. Review and update any existing text index definitions when upgrading to 25.12.
5
Forgetting to enable allow_get_client_http_header in the ClickHouse profile configuration when building webhook ingestion pipelines with HMAC validation. Without this setting, the getClientHTTPHeader function won't be able to read the signature from incoming HTTP request headers.
This is a security-sensitive setting that must be explicitly enabled in the profiles configuration. Ensure it is only enabled in profiles that need webhook functionality.
Related Concepts
Data Skipping Indexes
Granule-level Min/Max Metadata
Lazy Materialization
Query Execution Plans
Join Reordering Algorithms
Dynamic Programming In Query Optimization
Full-text Search Indexes
Inverted Indexes And Tokenization
Hash-based Join Algorithms
Hmac Message Authentication
Materialized Views
Clickhouse Dictionaries
Mergetree Storage Engine
Read-in-order Execution
Vectorized Query Execution