Aiven for ClickHouse® 26.3 LTS: Full-Text Search, Async Inserts, and Materialized CTEs

Aiven for ClickHouse® 26.3 is now available in Early Availability. This upstream Long-Term Support release makes full-text search generally available, enables asynchronous inserts by default, introduces materialized common table expressions, and brings a wide range of JSON and query-performance improvements. The upstream 26.3 release includes 27 new features and 40 performance optimizations.

Customers on Aiven for ClickHouse 25.8 can upgrade directly to 26.3 through the Aiven Console. Customers on 25.3 must first upgrade to 25.8. Downgrades are not supported, so we recommend testing the upgrade on a fork before upgrading production. A fork is an independent service restored from a backup, where you can replay representative queries and writes without affecting the source service. For the behavioral and compatibility changes to review, see Upgrade to 26.3 in Aiven for ClickHouse.

Here's what matters for your workloads.

Full-Text Search Is Generally Available

Native text indexes are generally available in ClickHouse 26.3. They provide a new data skipping inverted index for fast token-based searches and can reduce the amount of data scanned by search and observability workloads.

ALTER TABLE logs
    ADD INDEX msg_idx message TYPE text(tokenizer = 'splitByNonAlpha');

ALTER TABLE logs MATERIALIZE INDEX msg_idx;

SELECT count() FROM logs WHERE hasToken(message, 'timeout');
Loading code...

Asynchronous Inserts by Default

Starting with 26.3, async_insert is enabled by default. The server automatically batches small inserts into fewer, larger parts. If your ingestion path sends frequent small inserts—for example, from event collectors, IoT devices, or per-request logging—this can improve throughput and reduce the number of parts without requiring client changes.

Buffered rows become visible when they are flushed. With the default wait_for_async_insert = 1, the client waits for the flush result; setting it to 0 returns before persistence and can hide later insertion errors. Insert deduplication defaults also changed between 25.8 and 26.3.

If you need to rely on the earlier synchronous insert behavior while validating the upgrade, we recommend explicitly setting async_insert = 0 on your inserts.

-- 26.3 batches small inserts automatically; no client change needed
INSERT INTO events VALUES (...);

-- Force a specific insert to be synchronous
INSERT INTO events SETTINGS async_insert = 0 VALUES (...);

-- Restore the earlier setting defaults while validating the upgrade
SET compatibility = '25.8';
Loading code...

Materialized CTEs

WITH x AS MATERIALIZED (...) evaluates a common table expression once into a temporary table instead of evaluating it at every reference. This can reduce repeated work in dashboards and reports that reference the same expensive subquery several times. Materialized CTEs are experimental and disabled by default; enable them with enable_materialized_cte = 1 and benchmark the memory and performance tradeoffs for your workload.

SET enable_materialized_cte = 1;

WITH high_value AS MATERIALIZED (
    SELECT user_id, sum(amount) AS total
    FROM orders GROUP BY user_id HAVING total > 10000
)
SELECT 'count' AS metric, count() FROM high_value
UNION ALL
SELECT 'avg_total', avg(total) FROM high_value;   -- evaluated once, referenced twice
Loading code...

Optional Faster Map Lookups

ClickHouse 26.3 introduces optional bucketed Map storage. With map_serialization_version = 'with_buckets', a single-key lookup reads the relevant bucket instead of the entire Map. Upstream benchmarks report 2–49× faster lookups depending on Map size and workload.

Aiven retains the basic Map serialization format by default. Test the bucketed format on a fork before enabling it for production tables.

-- Aiven default is 'basic'; enable bucketing per table to test it
ALTER TABLE metrics MODIFY SETTING map_serialization_version = 'with_buckets';

SELECT labels['host'] FROM metrics WHERE ts > now() - INTERVAL 1 HOUR;
Loading code...

JSON Keeps Getting Better

JSONExtract* functions work directly on the JSON type, not only on JSON strings.
Up to ~8× lower memory in upstream measurements when reading a few subcolumns of large JSON objects.
Lazy type hints: Experimental metadata-only changes can add or modify JSON type hints without rewriting historical data. Enable this behavior with allow_experimental_json_lazy_type_hints = 1 and test it before production use.

-- JSONExtract* now works directly on the JSON type, no string cast
SELECT JSONExtractString(payload, 'user', 'name') FROM events;
Loading code...

Native Geospatial Support

ClickHouse 26.3 ships a native Geometry type with WKB and WKT input/output, replacing the earlier String alias. Aiven for ClickHouse 26.3 includes the geospatial reader functions (readWKT, readWKB) and area and perimeter functions for both Cartesian and spherical calculations, so you can store and process geometries with type safety instead of parsing strings by hand.

CREATE TABLE zones (id UInt32, shape Geometry) ENGINE = MergeTree ORDER BY id;

-- Use INSERT ... SELECT so the value is stored as a POLYGON. The
-- VALUES form can store it as a MULTILINESTRING, which has area 0.
INSERT INTO zones SELECT 1, readWKT('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');

SELECT id, areaCartesian(shape) AS area FROM zones;   -- area = 1
Loading code...

Smarter JOINs

Automatic JOIN reordering now covers ANTI, SEMI, and FULL joins when table statistics are enabled, extending the cost-based reordering introduced across the 25.x series. The optimizer can choose a more efficient build side without requiring query rewrites. RIGHT and FULL OUTER joins also run faster and use less memory.

Column statistics feed this reordering. Aiven for ClickHouse 26.3 does not collect them by default (auto_statistics_types is empty), so enable them per table where the optimizer would benefit:

ALTER TABLE orders MODIFY SETTING auto_statistics_types = 'minmax, uniq';
Loading code...

Quality-of-Life Improvements

Pretty EXPLAIN: EXPLAIN pretty = 1, compact = 1 renders more readable query plans.
naturalSortKey(): Sorts strings the way people expect, such as file2 before file10.
Skip indexes for OR conditions: Skip indexes now prune granules for complex disjunctive predicates, not just simple key filters, so more WHERE ... OR ... queries benefit from data skipping.
Fractional and negative LIMIT/OFFSET: LIMIT 0.25 selects a fraction of rows and negative values count from the end, so sampling and tail queries no longer need a subquery to compute row counts.
ALP codec for floats: The experimental CODEC(ALP, ZSTD) can improve floating-point compression for suitable datasets. Experimental codecs are disabled by default.
Lower-memory TTL deletes: Vertical merges now apply to TTL DELETE, reducing memory usage on wide tables.
Fewer S3Queue listing requests: Ordered mode lists only new objects using StartAfter, reducing ListObjects calls for queues with long histories.

Experimental Features to Watch

ClickHouse 26.3 previews additional capabilities that remain experimental upstream. Evaluate them on a fork before relying on them in production:

QBit vector type. Bit-sliced vector storage with approximate distance functions (L2DistanceTransposed, cosineDistanceTransposed) for tunable accuracy-versus-speed vector search. The QBit type is available in Aiven for ClickHouse 26.3 (allow_experimental_qbit_type = 1), but it is still experimental upstream, so validate it before production use.
Polyglot SQL transpiler. Translates queries written in other SQL dialects into ClickHouse SQL. It is disabled by default in Aiven for ClickHouse (allow_experimental_polyglot_dialect = 0).

Before You Upgrade

The upgrade from 25.8 to 26.3 spans seven upstream releases and includes on-disk format and behavioral changes. Take these steps:

Review the compatibility changes. Read Upgrade to 26.3 in Aiven for ClickHouse for the changes that require attention and their mitigations.
Fork first. Fork your service, upgrade the fork, and replay representative workloads and external integrations before upgrading production. Confirm that the fork's backup point is recent enough for your validation.
Check removed features. Aiven automatically scans a running 25.8 service for top-level and nested Object('json') columns and blocks the upgrade until they are migrated to JSON. Manually check for LIVE VIEW, Lazy databases, DEFLATE_QPL and ZSTD_QAT codecs, and experimental text indexes created before 25.12.

26.3 writes parts that older versions can't read, and async inserts can interact with session_timezone. Details and mitigations are in Upgrade to 26.3 in Aiven for ClickHouse.

Get Started

Create a new 26.3 service, or safely test the upgrade of an existing 25.8 service by forking it from the Aiven Console. If your service runs 25.3, upgrade it to 25.8 first. For complete instructions, see Upgrade to 26.3 in Aiven for ClickHouse.

Stay updated with Aiven

Subscribe for the latest news and insights on open source, Aiven offerings, and more.

Subscribe to RSS

Table of contents

Full-Text Search Is Generally Available
Asynchronous Inserts by Default
Materialized CTEs
Optional Faster Map Lookups
JSON Keeps Getting Better
Native Geospatial Support
Smarter JOINs
Quality-of-Life Improvements
Experimental Features to Watch
Before You Upgrade
Get Started