ClickHouse 25.9 New Features Lab

---

A hands-on laboratory for learning and testing ClickHouse 25.9 new features. This directory focuses on verified and working features newly added in ClickHouse 25.9 (released 2025-09-25; 25 new features, 22 performance optimizations, 83 bug fixes).

📋 Overview

ClickHouse 25.9 includes automatic join optimization, full-text search indexes, streaming secondary indexes, and new array functions.

🎯 Key Features

1. Automatic Global Join Reordering - Statistics-based join optimization
2. New Text Index - Experimental full-text search capabilities
3. Streaming Secondary Indices - Faster query startup with incremental index reading
4. arrayExcept Function - Efficient array filtering

🚀 Quick Start

Prerequisites

• macOS (with Docker Desktop)
• oss-docker environment setup
• 8GB+ RAM recommended
• 10GB+ disk space

Setup and Run

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

# 2. Run tests for each feature
./01-join-reordering.sh      # Automatic join reordering
./02-text-index.sh           # Text index (full-text search)
./03-streaming-indices.sh    # Streaming secondary indexes
./04-array-except.sh         # arrayExcept function

What ./00-setup.sh Does

The setup script performs the following: - Configure ClickHouse 25.9 using oss-docker - Start ClickHouse on port 8123 - Verify installation - Display connection information

Manual Execution (SQL only)

To execute SQL files directly:

# Connect to ClickHouse client
cd ../../oss-docker
./client.sh 8123

# Execute SQL file
cd ../releases/25.9
source 01-join-reordering.sql

📚 Feature Details

1. Automatic Global Join Reordering (01-join-reordering)

What it does: Automatically reorders multi-table joins based on table statistics and data volumes

Test Content: - Creates tables of different sizes (countries: 100, products: 10K, orders: 1M, customers: 50K) - Tests manual vs automatic join reordering - Demonstrates 4-way join optimization - Shows EXPLAIN output for join plans

Execute:

./01-join-reordering.sh

Key Benefits: - Optimal join order automatically selected - Reduced memory usage - Faster query execution - No manual query rewriting needed

Example:

-- Enable automatic join reordering
SET query_plan_join_swap_table = 'auto';

SELECT
    c.continent,
    p.category,
    count() AS orders
FROM orders o
JOIN products p ON o.product_id = p.product_id
JOIN countries c ON o.country_id = c.country_id
GROUP BY c.continent, p.category;

---

2. Text Index - Full-Text Search (02-text-index)

What it does: Provides experimental full-text search capabilities with streaming-friendly design

Test Content: - Creates articles table with 10,000 records - Tests full-text search queries - Multi-term search patterns - Category and time-based text search - Author and tag analysis

Execute:

./02-text-index.sh

Key Benefits: - Streaming-friendly architecture - Efficient skip index granules - Fast text matching - Better than simple LIKE queries

Example:

CREATE TABLE articles
(
    article_id UInt64,
    title String,
    content String,
    INDEX content_idx content TYPE text(tokenizer = 'default') GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY article_id;

-- Search for articles
SELECT title, content
FROM articles
WHERE content LIKE '%ClickHouse%';

---

3. Streaming Secondary Indices (03-streaming-indices)

What it does: Reads indices incrementally alongside data scanning for faster query startup

Test Content: - Creates events table with 5M records - Compares streaming vs traditional index reading - LIMIT query optimization - Time-series analysis - User behavior patterns

Execute:

./03-streaming-indices.sh

Key Benefits: - Faster query startup - Early query termination with LIMIT - Incremental index reading - Reduced memory overhead

Example:

-- Enable streaming indices
SET use_skip_indexes_on_data_read = 1;

CREATE TABLE events
(
    event_id UInt64,
    user_id UInt32,
    event_type String,
    INDEX user_idx user_id TYPE minmax GRANULARITY 4,
    INDEX type_idx event_type TYPE set(100) GRANULARITY 4
)
ENGINE = MergeTree()
ORDER BY event_id;

-- Query benefits from streaming indices
SELECT *
FROM events
WHERE user_id BETWEEN 1000 AND 2000
LIMIT 100;

---

4. arrayExcept Function (04-array-except)

What it does: New function to filter arrays by removing elements from another array

Test Content: - Basic array filtering - User permission management - Product feature filtering - Tag management system - Security rule filtering

Execute:

./04-array-except.sh

Key Benefits: - Cleaner syntax than manual filtering - Efficient array operations - Works with any data type - Maintains element order

Example:

-- Remove specific elements from array
SELECT arrayExcept([1, 2, 3, 4, 5], [2, 4]) AS result;
-- Result: [1, 3, 5]

-- Permission management
SELECT
    user_name,
    arrayExcept(all_permissions, revoked_permissions) AS active_permissions
FROM user_permissions;

🔧 Management

ClickHouse Connection Info

After running ./00-setup.sh:

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

Useful Commands

cd ../../oss-docker

# Check status
./status.sh

# Connect to CLI
./client.sh 8123

# Stop ClickHouse
./stop.sh

Data Verification

# Check tables
docker exec -it clickhouse-25-9 clickhouse-client -q "SHOW TABLES"

# Check version
curl http://localhost:8123/

📂 File Structure

25.9/
├── README.md                       # This document
├── 00-setup.sh                     # ClickHouse 25.9 installation script
├── 01-join-reordering.sh           # Join reordering runner
├── 01-join-reordering.sql          # Join reordering SQL
├── 02-text-index.sh                # Text index runner
├── 02-text-index.sql               # Text index SQL
├── 03-streaming-indices.sh         # Streaming secondary indices runner
├── 03-streaming-indices.sql        # Streaming secondary indices SQL
├── 04-array-except.sh              # arrayExcept runner
└── 04-array-except.sql             # arrayExcept SQL

📊 Test Data Summary

Test	Table	Rows	Description
Join Reordering	countries	100	Country dimension
Join Reordering	products	10,000	Product catalog
Join Reordering	orders	1,000,000	Order facts
Join Reordering	customers	50,000	Customer dimension
Text Index	articles	10,000	Article content
Streaming Indices	events	5,000,000	User events
arrayExcept	Various	Various	Permission/feature data

🔍 Feature Status

Feature	Status	Setting
Join Reordering	Stable	query_plan_join_swap_table = 'auto' (default)
Text Index	Experimental	Built into table definition
Streaming Indices	Stable	use_skip_indexes_on_data_read = 1
arrayExcept	Stable	N/A (built-in function)

🆕 What's New in 25.9

• Automatic global join reordering — reorders join graphs across tables
• Streaming secondary indices — interleaves index checks with data reads
• New text index — streaming-friendly, granule-structured full-text index
• arrayExcept — difference between two arrays
• Iceberg ALTER UPDATE and DROP TABLE — wider data lake write support
• Iceberg metadata log system table — introspect Iceberg metadata
• ORC and Avro for Iceberg — additional data lake file formats
• Unity catalog on Azure — Azure-hosted Unity catalog
• Distributed INSERT SELECT for data lakes — distributed lake ingestion
• Boolean settings without an argument — SET describe_compact_output;
• S3 storage class — specify classes such as INTELLIGENT_TIERING

🔍 Additional Resources

• ClickHouse 25.9 Release Blog
• ClickHouse 25.9 Release Call
• ClickHouse Documentation
• ClickHouse GitHub Releases

🎓 Learning Path

For Beginners

1. Start with arrayExcept (easiest) - Array filtering basics
2. Try Streaming Indices (intermediate) - Index optimization
3. Explore Text Index (intermediate) - Full-text search implementation
4. Advanced: Join Reordering (advanced) - Complex join optimization

For Advanced Users

1. Join Reordering - Complex analytical query optimization
2. Text Index - Search functionality implementation
3. Streaming Indices - Performance tuning

🚨 Important Notes

• All features verified on ClickHouse 25.9.7.56
• Experimental Features: Join Reordering and Text Index are experimental in 25.9
• Settings Required: Experimental features must be enabled with appropriate SET commands
• Performance Testing: Perform performance tests with actual data scale
• Production Use: Thorough testing required before production deployment

Key Settings: - SET query_plan_join_swap_table = 'auto'; - Enable join reordering - SET use_skip_indexes_on_data_read = 1; - Enable streaming indices

🧹 Cleanup

To stop and remove ClickHouse 25.9:

cd ../../oss-docker
./stop.sh

# Optional: Delete data
./cleanup.sh

📄 License

MIT — same as the rest of the repository.

🤝 Contributing

Issues and pull requests welcome! Please refer to the main repository for contribution guidelines.

📬 Support

For questions or issues: 1. Check this README and test output 2. Review ClickHouse official documentation 3. Create an issue in the main repository

---

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

# 2. 각 기능별 테스트 실행
./01-join-reordering.sh      # 자동 조인 재정렬
./02-text-index.sh           # 텍스트 인덱스 (전문 검색)
./03-streaming-indices.sh    # 스트리밍 보조 인덱스
./04-array-except.sh         # arrayExcept 함수

# ClickHouse 클라이언트 접속
cd ../../oss-docker
./client.sh 8123

# SQL 파일 실행
cd ../releases/25.9
source 01-join-reordering.sql

./01-join-reordering.sh

-- Enable automatic join reordering
SET query_plan_join_swap_table = 'auto';

SELECT
    c.continent,
    p.category,
    count() AS orders
FROM orders o
JOIN products p ON o.product_id = p.product_id
JOIN countries c ON o.country_id = c.country_id
GROUP BY c.continent, p.category;

./02-text-index.sh

CREATE TABLE articles
(
    article_id UInt64,
    title String,
    content String,
    INDEX content_idx content TYPE text(tokenizer = 'default') GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY article_id;

-- Search for articles
SELECT title, content
FROM articles
WHERE content LIKE '%ClickHouse%';

./03-streaming-indices.sh

-- Enable streaming indices
SET use_skip_indexes_on_data_read = 1;

CREATE TABLE events
(
    event_id UInt64,
    user_id UInt32,
    event_type String,
    INDEX user_idx user_id TYPE minmax GRANULARITY 4,
    INDEX type_idx event_type TYPE set(100) GRANULARITY 4
)
ENGINE = MergeTree()
ORDER BY event_id;

-- Query benefits from streaming indices
SELECT *
FROM events
WHERE user_id BETWEEN 1000 AND 2000
LIMIT 100;

./04-array-except.sh

-- Remove specific elements from array
SELECT arrayExcept([1, 2, 3, 4, 5], [2, 4]) AS result;
-- Result: [1, 3, 5]

-- Permission management
SELECT
    user_name,
    arrayExcept(all_permissions, revoked_permissions) AS active_permissions
FROM user_permissions;

cd ../../oss-docker

# Check status
./status.sh

# Connect to CLI
./client.sh 8123

# Stop ClickHouse
./stop.sh

# Check tables
docker exec -it clickhouse-25-9 clickhouse-client -q "SHOW TABLES"

# Check version
curl http://localhost:8123/

25.9/
├── README.md                       # 이 문서
├── 00-setup.sh                     # ClickHouse 25.9 설치 스크립트
├── 01-join-reordering.sh           # 조인 재정렬 실행 스크립트
├── 01-join-reordering.sql          # 조인 재정렬 SQL
├── 02-text-index.sh                # 텍스트 인덱스 실행 스크립트
├── 02-text-index.sql               # 텍스트 인덱스 SQL
├── 03-streaming-indices.sh         # 스트리밍 보조 인덱스 실행 스크립트
├── 03-streaming-indices.sql        # 스트리밍 보조 인덱스 SQL
├── 04-array-except.sh              # arrayExcept 실행 스크립트
└── 04-array-except.sql             # arrayExcept SQL

cd ../../oss-docker
./stop.sh

# Optional: 데이터 삭제
./cleanup.sh