ClickHouse 26.9

Released 2026-09-21 · verified here against 26.9.1.1629

26.9 is the release after an LTS, and it spends that position on cleanup: the changelog has 17 backward incompatible changes against 27 new features, and several of the removals are things that had been deprecated for years. The analyzer can no longer be switched off. CatBoost, WINDOW VIEW and WasmEdge are gone. The default compression codec changed.

This lab takes four of them that you can see working in a single container.

Quick start

./00-setup.sh              # brings up ClickHouse 26.9 via local/oss-docker
./01-limit-after-until.sh
./02-keyvaluepairs-index.sh
./03-zstd-default.sh       # writes and merges ~26M rows, allow a couple of minutes
./04-create-token.sh

Each NN-*.sh pipes the matching .sql into clickhouse-client. Read the SQL — the comments are the lab. 04-create-token.sh is the exception: it adds connections of its own, for the reason given in lab 04 below.

01 · LIMIT ... AFTER / UNTIL

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

Every LIMIT ClickHouse had before this counted positions. These four forms cut the stream at a row that satisfies a condition, following the stream order — so ORDER BY is what defines "before" and "after":

The reason to learn them is that WHERE cannot say this. WHERE tests each row on its own, so it keeps the rows that pass after the interesting one too:

rows:  1 ok   2 ok   3 error   4 ok   5 error   6 ok

WHERE lvl != 'error'         → [1, 2, 4, 6]
LIMIT UNTIL lvl = 'error'    → [1, 2]

"What happened up to the first error" has no WHERE spelling. It used to need a window function or a self-join to find the boundary row first.

ALL is the log reader's form. LIMIT 2 AFTER lvl = 'error' ALL opens a window at every error and returns their union — the error and its next line, for all of them, in one query. Overlapping windows are merged, not repeated: LIMIT 3 AFTER number IN (2, 3) ALL gives [2,3,4,5], not seven rows.

It stops reading. This is not a filter applied after the scan. Over 100 million rows with max_block_size = 1000, LIMIT UNTIL number >= 10 reports read_rows = 1000 in system.query_log — one block.

Two things to know before you use them:

• The condition column does not have to be in the SELECT list. It is evaluated against the stream, not the projection.
• OFFSET does not combine with these forms — LIMIT 3 AFTER cond OFFSET 1 is a syntax error. Nest the query and apply a positional LIMIT outside it.

And the asymmetry when nothing matches is the right one: AFTER has no window to open, so it returns nothing; UNTIL never finds a place to stop, so it returns everything.

02 · keyValuePairs text index tokenizer

CREATE TABLE kvp (
    ts    DateTime,
    attrs Map(String, String),
    INDEX idx_attrs attrs TYPE text(tokenizer = keyValuePairs) GRANULARITY 1
) ENGINE = MergeTree ORDER BY ts;

Observability schemas put the variable part of a row in a Map(String, String). Until 26.9 the only ways to make attrs['service'] = 'checkout' fast were to guess the hot keys in advance and promote them to real columns, or to read the map column on every query. keyValuePairs indexes the map itself: each entry becomes one token of key and value joined together.

What it buys. Two million rows, tenant clustered 100k rows to a value:

The indexed count() never opens the map column at all — the plan node is ReadFromTextIndexCount, not ReadFromMergeTree. A text index knows how many rows carry a token, so a count is answered out of the index. For a query that needs an actual column it goes back to being a skip index: 13/245 granules survive, with a PREWHERE to recheck them.

The limitation to plan around. The token is the key and the value concatenated with a separator — EXPLAIN indexes = 1 shows tokens: ["tenantt7\f"]. Only whole-value equality can be looked up. Four reasonable-looking predicates compile fine and scan every granule:

IN is the trap. It means the same thing to a reader as the OR above it and prunes nothing, so a fast query gets slow the day somebody tidies it into an IN list. That is worth a comment in the schema.

There is also no way past the map['key'] = 'value' form — hasToken(attrs, …) rejects a Map argument outright. The equality is the whole interface.

This is the third release running to extend text indexes: 26.8 added the japanese, chinese and icu tokenizers, and 26.9 also gives splitByRegexp an extract argument, so tokens(s, 'splitByRegexp', '[a-z]=([0-9]+)', 1) returns the capture groups rather than the separators.

03 · ZSTD(3) as the default codec

26.9 changes what ClickHouse compresses with when you do not say. It is listed under backward incompatible changes even though nothing breaks — every compressed frame is self-describing, so old data keeps reading.

Three separate things changed, and conflating them is how people end up surprised:

The size rule has a consequence that is easy to read past. It is judged by "the part size known at write time (the size of the source parts for a merge or a mutation)" — and a plain INSERT does not know how big its part will be. So freshly inserted data is LZ4 whatever its size. ZSTD(3) arrives later, when merges have accumulated enough of it. Measured on the same table:

                 compressed    ratio
before merge      111.70 MiB    7.69     two 6.5M-row inserts
after merge        21.53 MiB   39.88     merged, source parts ~112 MB

No rows were removed and nothing in the table definition changed. A 6.5M-row insert on its own stays at ~8x; only the merge crosses the threshold.

The operational reading: on a new cluster your newest data is LZ4 and your settled data is ZSTD(3), and disk usage keeps falling behind you as merges catch up. Capacity planning off a day-one measurement will overshoot.

Rolling it back is not one switch. compatibility = '26.8' does restore LZ4 for network_compression_method — the lab prints ZSTD, LZ4, LZ4 for 26.9, 26.8 and 25.1. It does not restore the MergeTree column default; a merge run under compatibility = '26.8' still comes out at 39.88x. For column data the levers are CODEC(LZ4) on the column or table, or the server-level <compression> default.

Two things that are not part of this change, worth knowing before you go hunting a regression: marks_compression_codec and primary_key_compression_codec have defaulted to ZSTD(3) for several releases already.

system.parts.default_compression_codec reports the table's declared default, not what the size rule chose — it says LZ4 even for a table whose every column carries an explicit CODEC(ZSTD(3)). Measure the ratio instead.

04 · CREATE TOKEN and scoped GRANTS

CREATE TOKEN VALID FOR INTERVAL 30 DAY GRANTS (SELECT ON demo.events);

An authentication method can now carry its own grant list. A session that logs in with that method gets the intersection of the user's privileges and the listed ones, so one user can hand out credentials strictly weaker than itself without a second user, a second role, or a proxy in front.

CREATE TOKEN is the self-service form — it generates the secret, attaches it to the current user, and returns it with its deadline:

token:       WzZjScwypyT3AiodtEFOyI1YslQFSrEm
valid_until: 2026-10-23 11:01:32

ALTER USER u ADD IDENTIFIED WITH … VALID UNTIL … GRANTS (…) is the administrator's form, doing the same to somebody else with a secret you pick.

Two things stop CREATE TOKEN working, and both look like bugs the first time:

• The user must live in a writable access storage. The default user of the official Docker image is defined in users.xml, which is read-only: Code: 495. Cannot update user 'default' in users_xml because this storage is readonly. SQL-defined users are fine. This is why lab 04's .sql uses the ALTER USER form and the runner does the CREATE TOKEN half.
• A token is an additional authentication method, and no_password cannot co-exist with any other: Code: 36. The user needs a real credential first.

Also, the privilege needs the ON *.* form — GRANT CREATE TOKEN TO app is a syntax error, GRANT CREATE TOKEN ON *.* TO app is not.

What the scope actually does, with app granted SELECT ON demo.* and a token scoped to SELECT ON demo.events:

The obvious escalation is closed. A session authenticated with a scoped method cannot mint another token: "the current session is authenticated with a method which limits the access rights with the GRANTS clause, and such sessions cannot add authentication methods to an existing user."

Operational notes, all demonstrated in the lab:

• Expiry is checked at login, not at issue. VALID UNTIL '2020-01-01' is accepted without complaint and then refuses every login with AUTHENTICATION_FAILED. A deploy script that computes the deadline in the wrong timezone gets no error where you would want one.
• The default TTL without a VALID clause is create_token_default_ttl_seconds — 1800, thirty minutes. Short on purpose.
• There is no DROP TOKEN. ALTER USER u IDENTIFIED WITH … (without ADD) replaces the whole method list, which revokes every token at once. Revoking one and keeping the rest means re-issuing the rest, so plan one token per consumer.
• SHOW CREATE USER is the only place the scope is visible. system.users shows nothing but a list of identical sha256_password entries.

Also in 26.9, not covered by these labs

The changelog runs to 17 backward incompatible changes, 27 new features, 28 experimental features and 135 performance improvements. In rough order of how likely they are to affect you:

Removals and breaking changes

New features

The changelog also lists a zero-argument year() returning the current year. It is not in 26.9.1.1629 — SELECT year() still resolves to toYear and fails with NUMBER_OF_ARGUMENTS_DOESNT_MATCH. Expect it in a later build.

Experimental

Verified

Every query in the four .sql files was run against 26.9.1.1629 in the container this lab sets up, and every number, plan fragment and error code quoted above is from those runs. 04-create-token.sh was run end to end too.

Two things were worked around while writing, both noted in the SQL:

• UNION ALL does not expose its column aliases to a trailing ORDER BY, so the comparison tables in labs 01, 03 and 04 wrap the union before sorting. This is the same trap the 26.8 lab hit.
• A refused login raises an exception, which would stop a .sql file at that line. Lab 04 therefore keeps its .sql exception-free and puts the ACCESS_DENIED and AUTHENTICATION_FAILED demonstrations in the .sh runner, one connection each.

One changelog claim did not hold on this build and is flagged above: the zero-argument year().