clickhouse.guru
News

ClickHouse 26.7: EXPLAIN ANALYZE, leaner joins and a stricter S3 credential model

26.7 finally gives you EXPLAIN ANALYZE, makes hash joins smaller and faster, speeds up decompression on every scan, and changes two defaults you must plan for: S3 credentials and insert deduplication.

Source: clickhouse.com

Why it matters: 26.7 is a release you can feel without changing a single query. Decompression, hash joins, GROUP BY and window functions all got cheaper by default, and EXPLAIN ANALYZE closes the long-standing gap with other databases when you need to know where a query actually spent its time. It is also a release with sharp upgrade edges: queries that read S3 with the server’s own cloud identity stop working unless you opt back in, and servers still configured with a legacy insert-deduplication mode refuse to start.

Everything below is taken from the official 26.7 changelog (released 2026-07-22) and the release post. The changelog lists 35 new features, 116 performance improvements, 133 improvements, 27 experimental features, 12 backward-incompatible changes and 349 bug fixes. PR numbers link to ClickHouse/ClickHouse on GitHub. None of the snippets here were run for this article: they use syntax newer than the 26.4 build the rest of this site tests against, so check them on your own 26.7 server.

EXPLAIN ANALYZE

EXPLAIN ANALYZE (#106586, #110668) executes the query and renders the actual execution metrics in the familiar plan format. According to the release post this includes planning time, execution time and parallelism information per step.

EXPLAIN ANALYZE
SELECT event_type, count()
FROM events
WHERE event_time >= now() - INTERVAL 1 DAY
GROUP BY event_type;

Until now you assembled this picture from EXPLAIN PIPELINE, system.query_log and system.processors_profile_log. The practical habit to build: when a query is slow, run EXPLAIN ANALYZE first and compare rows read per step with what the estimate-only EXPLAIN indexes = 1 promised. A step that reads far more than the index analysis predicted points at a filter that is not using the primary key.

Performance you get for free

Most of the performance section is on by default. The ones worth knowing because they change what you measure after an upgrade:

  • Decompression. ZSTD decompression on AArch64 uses NEON for columns with small match offsets such as fixed-width integers; the changelog quotes up to roughly 2.3x for a UInt64 column on AWS Graviton 4 (#108049). The Delta codec decodes 1.5 to 5 times faster for 8/16/32-bit types, making scans of Delta-compressed columns up to 20% faster (#110189). gzip, zlib and deflate move to libdeflate: about 1.4 to 1.5x faster decompression for .gz files, HTTP, url and s3 data and the Parquet GZIP codec (#108074). If your cluster is CPU-bound on scans of compressed integer columns, this is the first thing to re-benchmark.
  • Hash joins. The right-hand side of a hash join now stores an 8-byte index-based row reference, so entries of ALL joins are as small as those of ANY joins (#107189). On 100 to 300 million row parallel_hash joins the median query was about 12% faster with about 15% less peak memory, and INNER joins on UInt64 keys gained the most (about 21% faster, 38% less memory). LowCardinality(String) keys are handled natively (#107264), and lock contention in parallel_hash was reduced (#108938).
  • Joins that prune the left side. With enable_join_runtime_filters_index_analysis, a join can use the primary key or skip indexes of the left table to prune granules (#109085). The release post highlights the combined effect with a GROUP BY example it reports as 313x faster with 592x less memory; treat that as a best case and measure your own shape. A new dpsub join-order algorithm provides optimal plans for more join shapes (#107351).
  • Aggregation. allow_aggregate_partitions_independently is now on by default: when the GROUP BY key suits the partition key, each partition is aggregated independently and the global merge is skipped, with runtime heuristics that back off when the partition layout would hurt (#105128). LIMIT is pushed into aggregation-in-order for early termination (#104859). The -If variants of groupBit* and the variance family got vectorized kernels, up to 4x faster (#110461).
  • Window functions use much less memory on wide tables with SELECT *, and quantile*, uniq* and groupArray windows with OVER () are faster (#108688). Filters on PARTITION BY columns are pushed below the window (#110114).
  • Regular expressions for simple patterns are JIT-compiled to native code through LLVM, with automatic fallback to RE2 for anything outside the supported subset (#108004, setting compile_regular_expressions). The release post reports about 2.5x for matching.
  • Pruning. Partition minmax bounds now feed primary-key analysis when a key column is also an input of the partition key (#103480). IS NOT DISTINCT FROM and IS TRUE use primary-key and minmax indexes like = (#110006). A LIKE pattern without wildcards uses an exact key range (#107077).
  • Native Arrow reader and writer. The implementation that bypasses the Apache Arrow library is now the default for Arrow and ArrowStream (#106522).
  • A restored regression. Reading many small files from object storage had lost prefetching and fell back to synchronous reads; that is fixed (#108872). If your small-file ingestion from S3 got slower after an earlier upgrade, this is the fix.

Features worth trying

  • Projections with WHERE (#102347) materialize only the rows that match the predicate, and the optimizer uses them when the query’s WHERE implies the projection’s. That is a cheap way to accelerate one hot slice of a table without paying for a full copy of it; check the CREATE TABLE documentation for the exact definition syntax.

  • Query pushdown to external databases (#107740). The mysql, postgresql and sqlite table functions and engines accept a query (a subquery or query('SELECT ...')) and send it to the remote database as is. The structure is inferred from the result and the table is read-only. Use it when the remote database should do the aggregation instead of shipping rows.

  • url() scheme dispatch (#106093): s3://, gs://, az://, hdfs:// and file:// URLs given to url or the URL engine are routed to the matching storage engine, and Remote and RemoteSecure exist as real table engines now (#106189).

  • AT TIME ZONE and AT LOCAL (#106092) are plain syntax for toTimeZone, which makes ported SQL work unchanged.

  • Unified SYSTEM STOP/START/PAUSE/CANCEL/REFRESH across Kafka, RabbitMQ, NATS, S3Queue, AzureQueue and refreshable materialized views, plus ... ALL BACKGROUND forms (#107476).

  • Query parameters as setting values: SELECT ... SETTINGS max_threads = {threads:UInt64} (#108760).

  • The built-in /docs page serves a searchable, version-matched copy of the reference from system.documentation (#108345), and the Play UI gained query tabs (#107826) and per-column color coding (#108873).

  • Vector search with QBit. QBit accepts Int8, takes an optional stride so a search can read only the first dimensions (useful for Matryoshka embeddings), and gains dotProductTransposed and randomHadamardTransform for rotating embeddings before quantization (#108105, #108103, #108100, #108227). Rescoring now computes the exact distance only for rows the vector index returned (#105591).

  • Geospatial: a GeoJSON output format that writes one feature per row (#108065), geoToUTM and geoToMGRS and their inverses (#108939), and geometryIntersectCartesian / geometryIntersectSpherical for any geometry type (#110062).

  • Text indexes accept a postprocessor expression applied to each token, such as lower (#98939).

Experimental, worth watching

The ZXC codec (#110620) is an asymmetric LZ codec: slow compression, very fast decompression, ratio between LZ4 and ZSTD. It needs allow_experimental_codecs = 1 and suits write-once, read-many columns. The Quantize vector codec family with a two-stage approximate search (#108565), plan-based parallel replicas (#108504), distributed FINAL reads (#108148), Iceberg manifest compaction through OPTIMIZE TABLE ... MANIFEST (#98178) and executable UDF “drivers” (#105131) are all opt-in. Writing to DeltaLake tables was promoted to Beta (#107034).

Bug fixes you should know about

The changelog lists 349 fixes. The ones that justify not sitting on an older minor:

  • Silent data loss. A race between ALTER TABLE ... RENAME COLUMN and a concurrent OPTIMIZE ... FINAL or background merge could replace the renamed column’s data with defaults (#104822). Async inserts with deduplication could register each insert_deduplication_token in every partition a coalesced flush touched, silently dropping later inserts (#111049). S3Queue / AzureQueue skipped objects when Azure returned an empty listing page with a continuation token (#109761).
  • Wrong results. Primary-key pruning with a wider FixedString constant dropped matching rows (#111106); reversed (DESC) key columns without a final mark pruned granules incorrectly (#109901); join runtime filters returned wrong results on JSON columns (#107663); NULL values inserted from Arrow/ORC into LowCardinality(Nullable(...)) columns became empty strings, a regression from 26.5 (#107532).
  • Security. SQL injection in the MySQL wire protocol (SHOW TABLE STATUS LIKE, SET) (#108474); an ssl_certificate wildcard that matched multi-label names, letting the holder of a deeper-subdomain certificate authenticate as the wildcard user (#108472); unauthenticated probing of table existence and replication status on the interserver port (#99854); S3 301 redirects followed without the remote_url_allow_hosts check (#108466); secrets visible in EXPLAIN output and system.query_views_log (#108386, #108214).
  • Crashes. A column-type confusion when reading Iceberg tables with equality delete files crashed the server (#109551); parallel_view_processing had been ignored since 25.10 (#109170).

Plan before you upgrade

Work through the backward-incompatible list in order of how likely it is to break production.

  1. S3 credentials from user SQL (#106855). s3() and friends no longer pick up the server’s own cloud identity (environment variables, IMDS/IRSA, instance profile, AWS config files, STS role assumption, GCP metadata) for requests that originate in user SQL. Such a request now needs explicit credentials or NOSIGN; named collections default use_environment_credentials to 0. To get the old behavior back you need both use_environment_credentials = 1 on the request and the new setting s3_allow_server_credentials_in_user_queries. Persistent S3 / S3Queue tables and dynamic S3 disks whose definition depends on those credentials are loaded anonymously at startup instead of aborting it (s3_load_table_anonymously_if_credentials_restricted), which means a table that “works” after restart but returns access errors until you re-credential it. Grep your DDL for S3 tables without explicit keys before you upgrade.
  2. Insert deduplication (#108361). ClickHouse now always uses the unified deduplication hash for sync and async inserts. If you ever set the server setting insert_deduplication_version to old_separate_hashes or compatible_double_hashes, the server will not start on 26.7. The documented path is to run a release that supports compatible_double_hashes first, long enough to cover your deduplication window (one hour by default for replicated tables via replicated_deduplication_window_seconds; count-based for non-replicated tables with non_replicated_deduplication_window), then remove the setting before upgrading. If you never set it, ignore this.
  3. toTime now returns Time (#108729); use_legacy_to_time = 1 or toTimeWithFixedDate keeps the old semantics.
  4. **/ glob now also matches zero directory levels, so data/**/file.txt matches data/file.txt (#97676). Expect file counts from globs to go up.
  5. zip / zipx backups on object storage are rejected for both BACKUP and RESTORE (#101770); use tar.gz.
  6. AggregatingMergeTree rejects dimension columns outside the sorting key at CREATE (#108087); existing tables still load, and allow_dimensions_outside_sorting_key = 1 restores the old behavior. Those columns were silently collapsed to an arbitrary value during merges, so this is worth fixing rather than overriding.
  7. DateTime64 range grows from 1900-2299 to 0000-9999 (#107907). Out-of-range values are computed instead of clamped, so results can change if you relied on saturation. With precision 8 or 9 the upper bound stays at 2262-04-11.
  8. Rotated system log tables (for example system.text_log_0) are marked table_readonly (#95079). Servers older than 26.3 fail to start with those tables, so drop the rotated system.*_log_N tables or reset the setting before any downgrade past 26.3.
  9. Removals: the deprecated snowflakeToDateTime* / dateTimeToSnowflake* functions (use the *ID variants, #108711); config-based workload scheduling, replaced by CREATE RESOURCE and CREATE WORKLOAD (#108286); the remote mode of hasColumnInTable (#110881); and the XML-file naiveBayesClassifier models in favor of a NAIVE_BAYES dictionary layout (#108773).

A reasonable upgrade routine for 26.7: run SHOW CREATE over every table that uses an S3 or URL engine, search the server config for insert_deduplication_version, and replay a representative slice of production queries against a staging server with EXPLAIN ANALYZE on the join- and aggregation-heavy ones. The performance wins are real but uneven, and EXPLAIN ANALYZE is now the cheapest way to see which of them reach your workload.

release26.7

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.