ClickHouse 26.9: LIMIT AFTER/UNTIL, incremental refreshable views and ZSTD by default

Why it matters: 26.9 changes two things that every installation has: the default compression is now ZSTD(3) instead of LZ4 for network traffic and for larger MergeTree parts, and the pre-analyzer query path can no longer be switched back on. Around that sit practical SQL additions (LIMIT ... AFTER / UNTIL, a spilling DISTINCT, incremental refreshable views) and a token mechanism that lets you hand an application a short-lived, scoped credential.

Sources: the official 26.9 changelog (released 2026-09-21) and the release post, which counts 56 new features, 135 performance optimizations and 464 bug fixes. PR numbers link to ClickHouse/ClickHouse. These features are newer than the 26.4 build used for this site’s tested examples, so the snippets below are quoted from the changelog and were not run here.

Features worth trying

• LIMIT by boundary condition (#99508). Following the stream order, so use ORDER BY to define it. The changelog examples:

  SELECT number FROM numbers(10) ORDER BY number LIMIT 3 AFTER number >= 5;      -- 5, 6, 7
  SELECT number FROM numbers(10) ORDER BY number LIMIT UNTIL number >= 3;        -- 0, 1, 2
  SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 2 UNTIL number >= 6;  -- 2, 3, 4, 5
  SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 6) ALL;         -- 2, 3, 6, 7

  It is the clean way to cut a session out of an ordered event stream, for example “the rows after a login until the next logout”, without a window-function detour.

• Incremental refreshable materialized views with REFRESH ... APPEND INCREMENTAL (#114152): only rows committed to the source table since the previous refresh are appended, instead of re-reading the whole source. Into an Iceberg target the refresh is exactly-once, because the cursor is committed atomically inside the snapshot summary (#114823). This is the feature to look at if you run a full-refresh view that grows heavier every day on append-only data.

• DISTINCT can spill to disk like aggregation and sorting do (#116569). Set max_bytes_before_external_distinct or max_bytes_ratio_before_external_distinct; by default spilling starts once the query uses half the available memory under the applicable limit. High-cardinality DISTINCT that used to end in a memory-limit error now degrades to slower instead.

• CREATE TOKEN (#116957) generates a random secret, adds it to the current user as an additional authentication method and returns it with its expiry, for example CREATE TOKEN VALID FOR INTERVAL 30 DAY GRANTS (SELECT ON db.table). The token is tied to its user, limited in time and privileges, and stops working when the user is deleted; without an explicit lifetime it lives for create_token_default_ttl_seconds (30 minutes by default). It needs the new CREATE TOKEN privilege. The GRANTS (...) clause is also available on CREATE USER and ALTER USER ... ADD IDENTIFIED, limiting a session to the intersection with the listed grants.

• Smaller additions: SQL-standard linear regression aggregates regr_slope, regr_intercept, regr_r2 and friends (#119347); bracket syntax for JSON subcolumns, json['key1']['key2'] (#107039); MergeTree table-size limits max_table_size_rows and max_table_size_bytes_* (#115069); the max_tables database setting; system.session_query_ids for finding “the queries I just ran” (#110536); a keyValuePairs text-index tokenizer that answers map['key'] = 'value' from the index (#115579); a jemalloc fragmentation profiler in system.jemalloc_sampled_allocations (#115456); S3Queue mode = 'exclusive' for single-server ingestion without Keeper coordination (#110552); and BACKUP / RESTORE of WORKLOAD and RESOURCE entities (#108431).

• Promoted: Nullable(Tuple(...)) is generally available and on by default (#119222), and the ai* functions (aiGenerate, aiClassify, aiExtract, aiEmbed, aiSimilarity and others) are beta and no longer need allow_experimental_ai_functions (#116227).

Performance

• min, max and count without GROUP BY or filters can be answered directly from per-part column statistics, collected automatically for suitable columns through the auto_statistics_types MergeTree setting, without reading data (#115738).
• Joins. An alternative row-major payload layout for hash joins speeds up output reconstruction (enable_hash_join_row_store, on by default, #104884). query_plan_lift_predicate_across_join copies equi-join filter conjuncts across MergeTree inputs for better primary-key pruning; the changelog reports some queries 70 to 80 times faster (#108377). IEJoin now picks its two key conditions by estimated selectivity.
• Aggregation. The adaptive GROUP BY falls back to the ordinary algorithm when rare keys or aggregate arguments are too wide to copy profitably (#115755), and the old sharded aggregator is removed in its favor.
• Text search. LIKE and ILIKE patterns can use text indexes (#114728), and LIKE ... ESCAPE can use skip indexes. Phrase search stores positions in packed blocks and reads only the blocks that cover candidate rows.
• Random numbers and UUIDs. rand, rand64, randomString, generateUUIDv4, generateUUIDv7 and friends generate bytes with a reduced-round AES in counter mode (#116133). The native protocol batches end-of-query control messages into one socket write, saving a round trip per query for high-latency clients.

Plan before you upgrade

1. Default compression is ZSTD(3) (#108786, #119825). Client/server and server/server traffic uses it uniformly. For MergeTree column data the built-in default is size-aware: LZ4 for parts below 100 MB and ZSTD(3) at or above it, judged by the part size known at write time (for merges and mutations, the size of the source parts). Auxiliary files such as checksums.txt, StripeLog streams and the files of Set and Join engines switch to ZSTD(3). Expect better ratios and a modest CPU increase; if your CPUs were already the bottleneck on large merges, benchmark before rolling out. Rollback: compatibility = '26.8' or network_compression_method = 'LZ4' for the network, CODEC(LZ4) on columns or the server <compression> default for MergeTree data. Reading stays correct either way because compressed frames are self-describing.
2. The analyzer cannot be disabled (#118629). enable_analyzer and allow_experimental_analyzer are obsolete, setting them to 0 is rejected, and compatibility no longer reverts them. Check profiles, users.xml and client scripts for either name.
3. Removals that can stop a server from starting or break DDL. WINDOW VIEW and WATCH are removed (#114747); drop window views before upgrading, because a server finding one in its metadata fails to start. The CatBoost integration is removed (catboostEvaluate, system.models, SYSTEM RELOAD MODEL); revoke SYSTEM RELOAD MODEL from users and roles beforehand. The WasmEdge engine for WebAssembly UDFs is removed, and a server configured with webassembly_udf_engine = 'wasmedge' will not start. The read_resource / write_resource disk options are replaced by CREATE RESOURCE (#115286).
4. Privilege checks tightened. BACKUP ... TO Disk(...) and RESTORE ... FROM Disk(...) now need WRITE ON DISK / READ ON DISK (#117406); reading a File source with rename_files_after_processing needs WRITE ON FILE as well (#117895). Per-codec settings enable_<codec>_codec replace allow_experimental_codecs, which no longer enables anything.
5. Result changes. runningConcurrency is now treated as non-deterministic to fix wrong results under LEFT JOIN and lazy evaluation (#114403), so it can no longer appear in a sorting key, partition key or text-index preprocessor, and queries may be slower. GROUP BY ALL validation of Variant / Dynamic keys can be turned off with validate_group_by_all_key_types = 0. The interface and http_method columns of system.query_log and related tables are now Enum8.

If you do one thing before upgrading, search your metadata for window views and your configs for enable_analyzer and wasmedge: those are the items that fail loudly at startup or on the first query. Everything else is a performance or behavior change you can measure on a staging server using the compatibility setting.