Overview
ClickHouse 26.1 is a major release featuring 25 new features, 43 performance optimizations, and 176 bug fixes. Key highlights include end-to-end deduplication of asynchronous inserts with materialized views, new syntax for indexing projections, full Variant type support across all functions, faster DISTINCT on LowCardinality columns, text index improvements with sparseGrams tokenizer, and an open-source Kubernetes operator.
What You'll Learn
1
How to use end-to-end deduplication for asynchronous inserts with materialized views in ClickHouse
2
How to use the new indexing projection syntax to reduce storage overhead
3
How to introspect MergeTree internals and Keeper state using new system tables and table functions
4
How to use the sparseGrams tokenizer to improve text index query performance
5
Why deduplication is scoped per table and how it ensures consistency across base tables and materialized views
Prerequisites & Requirements
- Understanding of ClickHouse MergeTree engine and data parts
- Familiarity with materialized views and incremental aggregation
- Basic understanding of asynchronous insert batching and idempotent operations
- ClickHouse 26.1 or later installed
- Understanding of ClickHouse Keeper (ZooKeeper alternative) for replication coordination(optional)
Key Questions Answered
How does ClickHouse 26.1 handle deduplication of asynchronous inserts with materialized views?
ClickHouse 26.1 extends deduplication to work end-to-end across asynchronous inserts and their dependent materialized views. During buffer flush, ClickHouse computes one deduplication hash per source INSERT. Both the base table and materialized view target table independently check these hashes against their deduplication state in Keeper, filtering out duplicate mini-blocks before writing data, ensuring retries never produce duplicate data in any table.
What is the new indexing projection syntax in ClickHouse 26.1?
ClickHouse 26.1 introduces a simplified projection syntax: PROJECTION name INDEX column TYPE basic, replacing the previous verbose SELECT _part_offset ORDER BY column syntax. This creates lightweight projections that store only the sorting key plus a _part_offset pointer back into the base table, reducing storage overhead while providing secondary index functionality.
What is the mergeTreeAnalyzeIndexes table function in ClickHouse?
mergeTreeAnalyzeIndexes is a new table function in ClickHouse 26.1 that shows how primary and data skipping indexes are applied at query time. For a given query, it returns the exact row ranges within each data part that will be scanned after index filtering, making it possible to understand data pruning, debug index effectiveness, and see which parts and ranges survive filtering.
How does the sparseGrams tokenizer improve ClickHouse text index performance?
The sparseGrams tokenizer finds substrings where border n-gram hashes are strictly greater than internal ones, using CRC32 hashing. During search, the query engine selects the longest ngrams from the search string and ignores covered shorter ones, using fewer but more specific tokens. In benchmarks on the Hacker News dataset, sparseGrams achieved approximately 36% faster query times compared to the splitByNonAlpha tokenizer.
Why were asynchronous inserts unsafe with materialized views before ClickHouse 26.1?
Before 26.1, deduplication for asynchronous inserts only worked on the source/base table. When tables had dependent incremental materialized views, the transformed data written by those views could still be duplicated on retries, because the materialized view target tables did not independently track and filter duplicate mini-blocks. This made async inserts unsafe in pipelines relying on views for aggregation.
What new system tables and monitoring tools does ClickHouse 26.1 add for Keeper?
ClickHouse 26.1 adds the system.zookeeper_info system table for inspecting Keeper cluster state including size, latency, leadership status, and data volume. It also introduces an embedded web dashboard for visual monitoring, health checks, and storage management, plus an HTTP API for programmatic cluster inspection and command execution.
Does ClickHouse 26.1 support Variant type in all functions?
Yes, ClickHouse 26.1 extends Variant type support to all functions. Previously, using Variant values in functions like length() or comparison operators would throw ILLEGAL_TYPE_OF_ARGUMENT errors. Now functions transparently handle Variant columns, applying operations to the appropriate underlying type variant, making Variant a fully first-class data type.
What is the ClickHouse open-source Kubernetes operator?
ClickHouse 26.1 announces an official open-source Kubernetes operator that supports automated cluster provisioning, vertical and horizontal scaling, and configuration management. This addresses long-standing community requests for a native Kubernetes deployment solution for ClickHouse clusters running at scale.
Key Statistics & Figures
New features in release
25
ClickHouse 26.1 release
Performance optimizations
43
ClickHouse 26.1 release
Bug fixes
176
ClickHouse 26.1 release
Text index query time improvement with sparseGrams
~36% faster
Minimum query time of 0.883 seconds with sparseGrams vs 1.392 seconds with splitByNonAlpha on Hacker News dataset
sparseGrams secondary index storage overhead
16.19 GiB vs 2.00 GiB
Compared to splitByNonAlpha tokenizer on ~28.7 million Hacker News records
Hacker News dataset benchmark size
28,737,557 rows
Used for text index tokenizer comparison benchmarks
sparseGrams insert time
1162.247 seconds
Inserting ~28.7 million rows with sparseGrams tokenizer vs 120.358 seconds with splitByNonAlpha
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Clickhouse
Core OLAP database system being released as version 26.1
Storage Engine
Mergetree
Primary ClickHouse storage engine family for data parts, indexing, and merges
Storage Engine
Summingmergetree
Specialized MergeTree engine used for incremental aggregation in materialized view example
Coordination Service
Clickhouse Keeper
ZooKeeper alternative used for deduplication state tracking and replication coordination
Orchestration
Kubernetes
Container orchestration platform supported by the new open-source ClickHouse operator
Query Language
SQL
Used for all ClickHouse examples, schema definitions, and system introspection
Data Type
Qbit
Vector embedding data type with tunable search precision, promoted to beta status
Coordination Service
Zookeeper
Referenced as the predecessor coordination service that Keeper replaces
Key Actionable Insights
1Enable asynchronous insert deduplication with materialized views to build safe, retry-friendly ingestion pipelines. With ClickHouse 26.1, you can now use async inserts in pipelines that include dependent materialized views without risking duplicate data, making your ingestion architecture both performant and correct.This is especially important for real-world pipelines where network failures, timeouts, or node crashes force clients to retry inserts. Deduplication is scoped per table, so both base tables and materialized view targets independently filter duplicates.
2Adopt the new indexing projection syntax (PROJECTION name INDEX column TYPE basic) to simplify schema definitions. This cleaner syntax replaces the verbose SELECT _part_offset ORDER BY pattern while providing the same lightweight secondary index functionality with minimal storage overhead.This builds on the projection-as-secondary-index feature introduced in ClickHouse 25.6/25.11, which stores only the sorting key plus a _part_offset pointer rather than complete data copies.
3Use the mergeTreeAnalyzeIndexes table function to debug and optimize query performance by understanding exactly which data ranges survive index filtering. This provides granular visibility into how your primary and data skipping indexes prune data during query execution.This complements the existing mergeTreeIndex function which shows index file contents. Together they let you see both what's stored in indexes and how those indexes are applied at query time.
4Consider the sparseGrams tokenizer for text indexes when your workload involves searching through large text columns. While it requires significantly more storage and ingestion time, it can deliver approximately 36% faster query times by using fewer, more specific tokens during search.The tradeoff is substantial: in the Hacker News benchmark, sparseGrams used 16.19 GiB for secondary indices versus 2.00 GiB for splitByNonAlpha. Evaluate whether the query performance gain justifies the storage increase for your use case.
5Use the new system.zookeeper_info system table and embedded Keeper web dashboard for operational monitoring and debugging of your ClickHouse Keeper cluster. This provides visibility into cluster size, latency, leadership status, and data volume through SQL, a web UI, or HTTP API.Having multiple access methods (SQL, browser, HTTP API) means different team members can use their preferred interface for cluster operations and troubleshooting.
6Leverage the new files column in system.parts to monitor on-disk part layout and understand schema complexity. This can help diagnose issues with insert behavior, merge performance, and query execution related to part structure.The files count varies by part and is influenced by the number of columns, index types, and other metadata stored alongside the compressed data.
Common Pitfalls
1
Using asynchronous inserts with materialized views on ClickHouse versions before 26.1 can lead to duplicate data in materialized view target tables. While the source table correctly deduplicates retried async inserts, the transformed data written by materialized views was not deduplicated, making pipelines unsafe when retries occurred.
Upgrade to ClickHouse 26.1 to get end-to-end deduplication across both base tables and materialized views. Deduplication is scoped per table, so each table independently filters duplicates.
2
Choosing the sparseGrams tokenizer without understanding the storage tradeoff can lead to unexpected disk usage. In the Hacker News benchmark, sparseGrams consumed 16.19 GiB for secondary indices compared to just 2.00 GiB for splitByNonAlpha, and total table size was 23 GiB vs under 9 GiB. Insert time also increased from ~120 seconds to ~1162 seconds.
Evaluate whether the ~36% query performance improvement justifies the 8x increase in secondary index storage and 10x increase in ingestion time for your specific workload and data volume.
3
Not batching inserts before sending them to ClickHouse leads to expensive creation and merging of too many small data parts. ClickHouse achieves high throughput by writing independent data parts without global synchronization, but this means each insert creates a new part that must later be merged.
Use asynchronous inserts for server-side batching if client-side batching is impractical. Asynchronous inserts buffer individual INSERTs in memory and flush them as a single batch.
4
Debugging index effectiveness without the mergeTreeAnalyzeIndexes function can leave you guessing about whether your primary key and skip indexes are actually pruning data efficiently. Previously, you could only inspect index file contents but not how they were applied at query time.
Use the new mergeTreeAnalyzeIndexes table function to see exact row ranges scanned per data part after index filtering, enabling precise optimization of your table's primary key and index configuration.
Related Concepts
Asynchronous Inserts And Server-side Batching
Insert Deduplication And Idempotent Operations
Incremental Materialized Views
Mergetree Storage Engine Internals
Data Parts And Background Merges
Lowcardinality Column Optimization
Projections As Secondary Indexes
Full-text Search Indexing And Tokenization
Vector Search With Qbit Embeddings
Clickhouse Keeper Coordination
Kubernetes Operator For Database Deployment
Variant Data Type
Query Introspection And Observability