ClickHouse 26.6: hypothetical indexes, first continuous queries, and a data-loss footgun closed

Why it matters: 26.6 gives tuning work a safer loop. You can ask the planner what a skip index would do before you build it, the server documents itself from inside SQL, and the first building block of streaming queries is in. It also contains a handful of defaults that bite on upgrade: a different insert-deduplication hash, an AVX2 requirement for the standard x86 build, and an error where a REPLACE PARTITION used to silently empty a partition.

The facts below come from the official 26.6 changelog (released 2026-06-25) and the release post. The release lists 50 new features, 78 performance improvements, 99 improvements, 15 experimental features, 10 backward-incompatible changes and 366 bug fixes in the notable set. PR numbers link to ClickHouse/ClickHouse. These features are newer than the 26.4 build this site uses for its tested examples, so the snippets below are quoted from the changelog or adapted from it and were not run here; try them on a 26.6 or later server.

Hypothetical skip indexes

#104608 adds CREATE HYPOTHETICAL INDEX ... ON t (expr) TYPE ..., a session-scoped virtual skip index, and EXPLAIN WHATIF SELECT ..., which estimates the skip ratio and cost without materializing anything. Defined indexes show up in system.hypothetical_indexes.

CREATE HYPOTHETICAL INDEX idx_url ON events (url) TYPE bloom_filter(0.01) GRANULARITY 4;

EXPLAIN WHATIF
SELECT count() FROM events WHERE url = 'https://example.com/checkout';

This answers the question that makes skip indexes expensive to try: “will this actually skip granules for my data, or will it just cost me merge time and disk?” The cost of a wrong guess used to be a full ALTER TABLE ... MATERIALIZE INDEX on a large table. Treat the output as an estimate: it tells you which index type and granularity are worth building for a query shape, not what the final latency will be. A later release adds an allowlist for the index types WHATIF may use, so if a type you try is rejected, check the changelog of your version.

Continuous queries over MergeTree

#105114 adds continuous queries over MergeTree tables, implemented as a series of snapshot reads. The changelog describes it as the first step toward general streaming queries, and that is the right expectation: think “observe new rows of this table incrementally” rather than a complete stream-processing engine. The release post describes the syntax as a STREAM keyword on the query. If you currently poll a table on a schedule to pick up new rows, this is the feature to follow over the next releases.

The server documents itself

system.documentation (#107463) collects the reference documentation of functions, table engines, data types, settings and formats into one table, rendered as Markdown.
help <name> in clickhouse-client and clickhouse-local (also /help, man, /man) renders that documentation in the terminal with highlighted SQL, shows all matches for ambiguous names and suggests similar names for typos (#108042).
New catalog tables: system.constraints (#105337), system.dictionary_layouts (#106182), system.dictionary_sources (#106184), system.data_skipping_index_types (#106186) and system.disk_types (#106187).
An embedded /schema page in clickhouse-server draws the dependency graph between tables, materialized views, refreshable materialized views, dictionaries, distributed tables and views (#105276). It is the quickest answer to “what feeds this table?” on an unfamiliar database.
clickhouse-local can turn into a server with SYSTEM START LISTEN and SYSTEM STOP LISTEN, accepting TCP and HTTP (#101143).

Operations and tuning features

Projection build control. materialize_projections_on_insert = 0 makes inserts skip building projection parts, which helps insert throughput on tables with many projections; materialize_projections_on_merge = 1 lets a merge rebuild a projection that is missing from all its source parts (#100993). Merges still only combine parts that share the same set of projections, so mixing the two across a table’s life needs some care.
Refreshable materialized views can chain: REFRESH DEPENDS ON triggers a refresh from another refreshable view’s refresh instead of a clock (#104440).
allow_tuple_element_aggregation (immutable, off by default, set at table creation) makes SummingMergeTree, AggregatingMergeTree and CoalescingMergeTree flatten Tuple columns and aggregate each leaf like a top-level column during merges (#98039).
SYSTEM RESTART DISK <name> reloads a disk’s in-memory metadata and rescans parts of read-only replicas on shared plain_rewritable storage, so a read-only replica sees another server’s writes on demand (#106645).
Memory reservations for workloads in the workload scheduler (#82414) and TTL nodes in Keeper, opt in through the create_ttl feature flag (#100397).
load_balancing gains hostname_longest_common_prefix and hostname_longest_common_suffix, for clusters whose hostnames encode the data center with variable-length numeric parts, where nearest_hostname and the Levenshtein strategy pick the wrong replica (#107360).
Dynamic memory limit. MemoryWorker can recompute the hard limit from RSS and the kernel’s available memory so ClickHouse leaves room for co-located processes (memory_worker_dynamic_hard_limit, #104964).
Audit trail. Certificate-based sessions now record the client certificate’s subject, serial, issuer and validity in system.session_log (#107679), and the server detects which AI coding agent (Claude Code, Cursor, Codex, Gemini CLI, Goose and others) drove a clickhouse-client or clickhouse-local session, recording it in the new client_agent column of system.query_log, system.query_thread_log and system.processes (#106619). That column is the way to measure how much of your query load is agent-generated before you decide how to govern it.

SQL and compatibility

ALTER TABLE ... ADD ENUM VALUES appends enum members without restating the existing ones (#93830). ALTER ... ADD/MODIFY COLUMN accepts a trailing NULL / NOT NULL like CREATE (#106150). SELECT * LIKE 'pattern' and ILIKE choose columns by name, which is useful on wide tables (#104569), and LIKE ... ESCAPE finally allows a custom escape character (#99774). PostgreSQL-style expr OP SOME(array) and OP ALL(array) are rewritten to has, arrayExists or arrayAll (#105129); LOCALTIME, LOCALTIMESTAMP, date_part and richer EXTRACT arrived as well (#106139, #105127, #106227), along with the aliases min_by/max_by, REGEXP_SUBSTR and SESSION_USER (#105712, #105122, #106081). arrayTopK and arrayBottomK return the K largest or smallest elements in one call (#104563).

Geospatial and data lakes

GeoJSON as an input format reads FeatureCollection documents into the native Geometry type, one row per feature (#98124).
MVTEncodeGeom, MVTEncode and MVTBoundingBox produce Mapbox Vector Tiles directly from SQL, with PostGIS aliases ST_AsMVTGeom and ST_AsMVT (#106107). With FORMAT PNG (#74691) a query can also return an image.
Writing to Azure Data Lake Storage Gen2 (#105406), system.iceberg_files for per-file metadata of the current snapshot (#104415), an external_id for S3 role assumption, the cross-account confused-deputy protection (#106941), and message_queue_disable_insertion so Kafka, RabbitMQ and NATS tables on read-only standbys do not double-consume (#104911).

Performance

Keeper is about twice as fast overall, thanks to better batching and pipelining of messages to the leader and of log appends (#101757).
Per-query overhead for simple SELECTs dropped; the changelog example, SELECT count() FROM hits from one connection, is roughly 50% faster (#104513).
Long primary keys. Index analysis time now depends on the filter’s key columns, not on the length of the key, so extending a sorting key has negligible extra analysis cost for queries that filter on a few of its columns (#91836).
Wide tables. Analysis of nested SELECT * over a table with about 1200 columns is about 50 times faster (#106957).
Encryption functions. encrypt, decrypt and the aes_* MySQL variants are up to an order of magnitude faster, recovering what the 24.4 move from BoringSSL to OpenSSL 3.x cost (#107339).
LIMIT BY got several optimizations (pushdown into sort, in-order execution, per-partition execution), enable_join_transitive_predicates is on by default (#103724), and the lowest-level sorts use ports of the Rust standard library’s ipnsort and driftsort (#106650).
Enum memory is up to 10 times smaller (#95668), and a PREWHERE ... IN (subquery) on the primary key now prunes granules instead of scanning (#102570).

Experimental, worth watching

Multi-stage distributed query execution (#106020): the planner splits a plan into stages connected by scatter, broadcast, gather and shuffle exchanges, with distributed shuffle and broadcast hash joins, shuffle aggregation and distributed sort. Off by default.
/webterminal is now a production feature, controlled by enable_webterminal (#106255).
dphyp join reordering for inner joins, with a bound on the search (query_plan_optimize_join_order_max_searched_plans, #98798).
aiEmbed generates embeddings through an LLM API from inside SQL (#102922), and quantizeBFloat16ToInt8 / dequantizeInt8ToBFloat16 give an 8-bit embedding codec for vector search (#108102).
An OOM canary for Linux: a sacrificial child process the OOM killer targets first, which sheds memory pressure by purging allocator caches and cancelling queries and merges (oom_canary_enable, needs cgroup v2 evidence, #101942).
Nullable(Tuple(...)) reached Beta behind enable_nullable_tuple_type (#107754); descending sort keys (ORDER BY (time DESC, key)) are now always supported (#106440).
WebAssembly UDFs gain an AssemblyScript ABI (#104606) next to the existing ROW_DIRECT and BUFFERED_V1 ABIs (#105574 adds buffer serialization and a persisted webassembly_udf_enable_fuel setting). The practical point: before this, a string-handling UDF meant hand-managing buffers in C or Rust; with AssemblyScript you write a typed function over string, and the sandbox gives the guest only its own linear memory, no filesystem and no network. WASM UDFs remain experimental and gated by allow_experimental_webassembly_udf.
Also experimental: PromQL histogram_quantile (#103477), a lazy posting-list apply mode for text indexes (#100035), packed_skip_index_max_bytes for bundling small skip-index files (#105321), and the pipeline-based use_reader_executor read path (#106570).

Bug fixes that justify upgrading

The changelog lists 366 fixes. The severe ones:

Data loss or corruption. Parquet, ORC and Arrow writes to HDFS silently dropped the last buffer of every file since 26.1, including the Parquet footer, so files became unreadable (#103268). REPLACE, MOVE or DETACH PARTITION on a plain MergeTree partition with unapplied lightweight UPDATE patches silently lost data and is now rejected (#107386). REPLACE PARTITION could resurrect replaced-out parts after a restart (#107623).
A wrong count with the default cache. With use_query_condition_cache = 1 (the default), a query shaped like PREWHERE pk_prefix = X WHERE non_pk IN (...) against a column with a bloom-filter skip index could poison the cache for the pk_prefix = X predicate, so later queries under-counted. It affected all 26.x releases (#105686). If you saw unexplained low counts on 26.x, this is a candidate.
Wrong results. Pruning with monotonically decreasing ORDER BY functions (#106136), LEFT/RIGHT ANTI JOIN returning wrong right-key values (#105278).
Security. Two path-traversal holes in the replicated part-fetch protocol that let a malicious replica write outside the part directory (#107606), an unauthenticated memory-exhaustion DoS on the MySQL protocol port (#107599), and system.query_log leaking privilege strings across users (#106425).
Crashes. A segfault reachable by any user with CREATE rights through CREATE TABLE ... TO INNER UUID without an engine (#105579), and a restart crash loop from a refreshable view whose DEPENDS ON name matched a temporary table or CTE (#107156).

Plan before you upgrade

Insert deduplication changes (#107886). The default of the server setting insert_deduplication_version moves from compatible_double_hashes to new_unified_hash, so deduplication works on the whole inserted block per insert. A retry of the same insert still deduplicates; two different inserts that produce an identical part no longer cross-deduplicate, and reordered inserts of the same rows no longer deduplicate. Set compatible_double_hashes to restore the old behavior. If you upgrade from a release that still defaulted to old_separate_hashes, run compatible_double_hashes for at least your longest deduplication window first. A follow-up fix corrected wrong hashes for String and Array columns (#107915).
x86 builds need AVX2 (#105019). The default build targets x86-64-v3 (Intel Haswell or newer, AMD Excavator or newer). Older CPUs need the amd64compat build. Check grep -c avx2 /proc/cpuinfo on every host, including old replicas and CI runners, before you roll out.
REPLACE PARTITION ... FROM an empty source is now an error (#104939). It used to remove the destination partition and write nothing, a data-loss footgun. It now fails with BAD_ARGUMENTS. allow_replace_partition_from_empty_source = 1 or compatibility = '26.5' restores the old behavior; if you really mean to empty the partition, say DROP PARTITION. Audit any ETL job that swaps partitions in.
Setting rename. show_data_lake_catalogs_in_system_tables became show_remote_databases_in_system_tables; at its default of 0 it now also hides MySQL and PostgreSQL databases from system.tables, system.columns and system.completions (#104416). The old name remains an alias.
CLI change. --echo takes an optional boolean, so a positional query right after a bare --echo is now read as its value; write --echo --query "..." or --echo=false (#106191).
Removals and stricter checks. allow_experimental_query_deduplication is gone (#99398), the experimental KQL functions array_sort_asc and array_sort_desc are gone (#108101), icebergHash and icebergBucket reject 128/256-bit integers and Decimal256 instead of truncating into colliding hashes (#105866), SYSTEM INSTRUMENT uses arguments instead of parameters (#103854), and nested Dynamic / Variant are rejected in min/max aggregates and minmax indexes (#105468).

Of these, the deduplication change and the AVX2 requirement are the two that fail quietly or loudly in production without a code change on your side. Test deduplication by replaying a retry storm against staging, and verify CPU flags before the first host is touched.