ClickHouse 26.2 New Features Lab

---

A hands-on laboratory for learning and testing ClickHouse 26.2 new features. This directory focuses on verified and working features newly added in ClickHouse 26.2 (released 2026-02-26).

📋 Overview

ClickHouse 26.2 expands the SQL surface area with number-theory utilities, a new 128-bit hash function, and an introspectable text-index tokenizer catalog. The release also makes the text index GA, promotes the QBit vector type to GA, and adds distributed vector search.

🎯 Key Features

1. primes() Table Function + system.primes — prime numbers as a first-class SQL source
2. xxh3_128 Hash Function — 128-bit non-cryptographic hash for deduplication, sharding, and joins
3. system.tokenizers Catalog — discover every tokenizer available to text indexes from SQL

🚀 Quick Start

Prerequisites

• macOS (with Docker Desktop)
• oss-docker environment

Setup and Run

# 1. Install and start ClickHouse 26.2
cd local/releases/26.2
./00-setup.sh

# 2. Run feature tests
./01-primes-function.sh
./02-xxh3-128-hash.sh
./03-system-tokenizers.sh

Manual Execution (SQL only)

cd ../../oss-docker
./client.sh 8123

cd ../local/releases/26.2
source 01-primes-function.sql

📚 Feature Details

1. primes() Table Function & system.primes (01-primes-function)

New Feature: A new table function primes(n) returns the first n prime numbers, and system.primes exposes an unbounded stream of primes — a single column named prime of type UInt64. (#92776)

Test Content: - Reading from system.primes with LIMIT and WHERE - Generating bounded prime sequences via primes(n) - Twin-prime pair detection with self-JOIN on row number - Prime-gap distribution analysis - Composite-number identification via NOT IN (primes) - Building a materialized prime cache table - Nearest-prime lookup for hash-table sizing

Key Learning Points: - Both primes() and system.primes use a single column named prime (not number) - system.primes is unbounded — always pair with LIMIT or a WHERE prime < X predicate - primes(n) is bounded and is the safer choice when you know the upper bound - Combine with rowNumberInAllBlocks() to access ordinal position

Use Cases: - Hash-table bucket sizing (use the next prime ≥ desired capacity) - Modular arithmetic and number-theory experiments - Generating test/synthetic data with deterministic distinctness - Teaching and demonstrations

---

2. xxh3_128 Hash Function (02-xxh3-128-hash)

New Feature: 128-bit XXH3 hash returning UInt128, doubling the hash space of the existing xxHash64 at comparable throughput. (#96055)

Test Content: - Basic hashing and UInt128 result type inspection - Hex / decimal representations side-by-side with xxHash64 - Content-fingerprint deduplication via MATERIALIZED column - Hash-based shard mapping - Collision test on 10,000 random strings - JOIN on 128-bit fingerprints

Key Learning Points: - Result type is UInt128 (32 hex chars vs xxHash64's 16) - Same input → same hash (deterministic, no salt) - Use as a MATERIALIZED column for cheap, indexable content fingerprints - Practical collision probability is negligible at typical data sizes - The function name is xxh3_128, while the existing 64-bit equivalent is xxHash64 (note the camel-case asymmetry)

Use Cases: - Deduplication where 64-bit collision risk is unacceptable - Sharding keys for very large key spaces - Content-addressable joins across tables - Idempotency keys / cache lookups

---

3. system.tokenizers Catalog (03-system-tokenizers)

New Feature: system.tokenizers system table lists every tokenizer registered for use with text indexes and the tokens() function. (#96753)

Test Content: - Listing all tokenizers from system.tokenizers - Calling tokens(s, tokenizer_name [, params]) with different tokenizers (splitByNonAlpha, splitByString, ngrams) - Creating a text index with an explicit tokenizer - Full-text search via hasToken driven by the index - Token count per document - EXPLAIN indexes = 1 to confirm the skip index is used - Side-by-side comparison of tokenizer output

Key Learning Points: - Available tokenizers include splitByNonAlpha, splitByString, ngrams, ngrambf_v1, tokenbf_v1, sparseGrams, sparse_grams, array - text indexes are now GA in 26.2 (no compatibility flag required) - tokens(string, tokenizer_name) lets you preview what the index will store before building it - Tokenizer choice directly determines what queries the index can accelerate

Use Cases: - Choosing the right tokenizer before building a large text index - Validating tokenization behavior against multilingual or structured text - Diagnosing why a text-index query is or isn't being skipped

---

🔧 Management

ClickHouse Connection Info

• Web UI: http://localhost:8123/play
• HTTP API: http://localhost:8123
• TCP: localhost:9000
• User: default (no password)

Useful Commands

cd ../../oss-docker
./status.sh
./client.sh 8123
docker logs clickhouse-26-2
./stop.sh
./stop.sh --cleanup

📂 File Structure

26.2/
├── README.md                     # This document
├── 00-setup.sh                   # ClickHouse 26.2 installation script
├── 01-primes-function.sh         # primes()/system.primes test runner
├── 01-primes-function.sql        # primes()/system.primes SQL
├── 02-xxh3-128-hash.sh           # xxh3_128 test runner
├── 02-xxh3-128-hash.sql          # xxh3_128 SQL
├── 03-system-tokenizers.sh       # system.tokenizers test runner
└── 03-system-tokenizers.sql      # system.tokenizers SQL

🆕 What's New in 26.2

• Text index is now GA — no more allow_experimental_* flag, survives backup restores
• QBit data type GA — quantized bit-packed vectors for approximate nearest-neighbor search
• Distributed vector search — vector indexes can now be sharded across replicas
• TOTP authentication — time-based one-time passwords as a server-side auth method
• primes() and system.primes — prime numbers as a SQL source
• xxh3_128 — 128-bit non-cryptographic hash
• system.tokenizers, system.user_defined_functions, system.jemalloc_stats, system.jemalloc_profile_text, system.fail_points — five new introspection tables
• OPTIMIZE … DRY RUN PARTS — simulate merges without committing
• lazy_load_tables database setting — defer table-engine instantiation to first access
• ClickStack observability UI — bundled directly with the server
• Google BigLake catalog integration — query BigLake-managed Iceberg tables

🔍 Additional Resources

• Release Presentation: ClickHouse 26.2 Community Call
• Release Video: YouTube
• ClickHouse Documentation: docs.clickhouse.com
• Release Notes: Changelog 2026

📝 Notes

• Each script can be executed independently
• Test data is generated within each SQL file
• Cleanup is commented out by default for inspection
• All features verified on ClickHouse 26.2.19.43

📄 License

MIT — free to learn from and modify.

---

Happy Learning! 🚀

For questions or issues, see the main clickhouse-hols README.

---

# 1. ClickHouse 26.2 설치 및 시작
cd local/releases/26.2
./00-setup.sh

# 2. 각 기능별 테스트 실행
./01-primes-function.sh
./02-xxh3-128-hash.sh
./03-system-tokenizers.sh