ClickHouse 25.7 New Features Lab

---

A hands-on laboratory for learning and testing ClickHouse 25.7 new features. This directory focuses on verified and working features newly added in ClickHouse 25.7 (released 2025-07-24).

📋 Overview

ClickHouse 25.7 includes revolutionary SQL UPDATE/DELETE optimization, AI-powered SQL generation, count() aggregation optimization, JOIN performance improvements, and massive bulk UPDATE performance enhancements.

🎯 Key Features

1. SQL UPDATE and DELETE Operations - Up to 1000x faster updates/deletes with lightweight patch-part mechanism
2. AI-Powered SQL Generation - Natural language SQL generation using OpenAI/Anthropic API (?? prefix)
3. count() Aggregation Optimization - 20-30% faster aggregation performance and reduced memory usage
4. JOIN Performance Improvements - Up to 1.8x faster JOIN operations
5. Bulk UPDATE Performance - Up to 4000x faster bulk updates compared to PostgreSQL

🚀 Quick Start

Prerequisites

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

Setup and Run

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

# 2. Run tests for each feature
./01-sql-update-delete.sh
./02-count-optimization.sh
./03-join-performance.sh
./04-bulk-update.sh

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.7
source 01-sql-update-delete.sql

📚 Feature Details

1. SQL UPDATE and DELETE Operations (01-sql-update-delete)

New Feature: UPDATE/DELETE using lightweight patch-part mechanism (up to 1000x faster)

Test Content: - Single record UPDATE performance - Conditional bulk UPDATE (WHERE clause) - UPDATE with complex expressions - Lightweight DELETE operations (up to 1000x faster) - Inventory management system practice - Large-scale table updates with 10 million rows

Execute:

./01-sql-update-delete.sh
# Or
cat 01-sql-update-delete.sql | docker exec -i clickhouse-25-7 clickhouse-client --multiline --multiquery

Key Learning Points: - Patch-part mechanism: Stores only changes without rewriting entire parts - Optimized updates with ALTER TABLE UPDATE syntax - Selective updates with WHERE conditions - Conditional value setting with CASE statements - Complex update logic using subqueries - System resource efficiency with asynchronous execution

Real-World Use Cases: - Real-time inventory management and pricing adjustments - E-commerce promotions and discount application - Data quality improvement and correction tasks - User profile and settings updates - Batch data processing and ETL pipelines - GDPR-compliant data deletion

Performance Comparison: - Traditional approach: Requires full part rewrite (slow) - ClickHouse 25.7: Patch-part applies only changes (up to 1000x faster)

---

2. count() Aggregation Optimization (02-count-optimization)

New Feature: Optimized count() aggregation function (20-30% faster with reduced memory usage)

Test Content: - Basic count() performance measurement - count() optimization with GROUP BY - count(DISTINCT) performance improvements - Large dataset aggregation (10 million rows) - Multi-dimensional aggregation scenarios - Memory efficiency testing

Execute:

./02-count-optimization.sh

Key Learning Points: - count() is the fastest aggregation function (utilizes metadata) - count() is more efficient than count(column) - 20-30% performance improvement with GROUP BY - Memory optimization for count(DISTINCT) - Conditional aggregation with countIf() - count() used with window functions

Real-World Use Cases: - Real-time analytics dashboards - Large-scale event tracking and user behavior analysis - Multi-dimensional business intelligence reporting - Log aggregation and monitoring systems - E-commerce conversion rate analysis - IoT data processing and device telemetry

Optimization Tips: - Use count() when possible (instead of count(column)) - Set appropriate indexes and ORDER BY keys - Limit query scope using partitions - Pre-aggregate with Materialized Views

---

3. JOIN Performance Improvements (03-join-performance)

New Feature: Up to 1.8x faster JOIN operation performance

Test Content: - INNER JOIN performance (up to 1.8x faster) - LEFT JOIN optimization - Multi-table JOIN (3 or more tables) - JOIN and GROUP BY combination - Complex analytical queries (subqueries, CTEs) - Large dataset JOIN (millions to tens of millions of rows)

Execute:

./03-join-performance.sh

Key Learning Points: - Hash table build and probing optimization - Improved memory efficiency for large JOINs - Enhanced multi-table JOIN query planning - Optimal ordering of JOIN conditions - RIGHT vs LEFT table selection strategy - CTE (Common Table Expression) utilization

Real-World Use Cases: - E-commerce customer and order analysis - Multi-dimensional business intelligence dashboards - Customer segmentation and cohort analysis - Supply chain and inventory management reporting - Financial reporting and transaction reconciliation - Marketing attribution and campaign performance analysis

Performance Optimization: - Place smaller table as RIGHT table - Use appropriate data types for JOIN keys - Don't SELECT unnecessary columns - Filter data before JOIN with WHERE conditions

---

4. Bulk UPDATE Performance (04-bulk-update)

New Feature: Up to 4000x faster bulk updates compared to PostgreSQL

Test Content: - Large-scale bulk UPDATE (10 million rows) - UPDATE with complex WHERE conditions - Multiple column simultaneous UPDATE - Conditional UPDATE using CASE statements - UPDATE using aggregation results - Performance comparison and benchmarking

Execute:

./04-bulk-update.sh

Key Learning Points: - Patch-part mechanism eliminates need for full table rewrite - Can update millions of rows in seconds - Efficient processing of complex conditional updates - JOIN-like UPDATE using subqueries - System load minimization with asynchronous mutations - ALTER TABLE UPDATE syntax utilization

Real-World Use Cases: - Real-time inventory synchronization and pricing adjustments - Retail: Daily closing price updates and promotions - Finance: Bulk account balance adjustments - Gaming: Player statistics and leaderboard updates - IoT: Device configuration and status bulk updates - Data migration: Legacy system modernization - Data quality: Bulk correction and normalization

Performance Comparison: | Database | Million Row Update Time | Technology | Performance | |----------|------------------------|----------|------------| | ClickHouse 25.7 | Seconds | Patch-part mechanism | 4000x faster | | PostgreSQL | Hours | Full row rewrite | Baseline |

---

5. AI-Powered SQL Generation (Feature Description)

New Feature: Natural language to SQL conversion using OpenAI or Anthropic API

Description: ClickHouse 25.7 provides AI-powered SQL generation that converts natural language to SQL using the ?? prefix.

Usage:

-- Using OpenAI API
SET openai_api_key = 'your-api-key';
?? show me top 10 customers by revenue

-- Using Anthropic API
SET anthropic_api_key = 'your-api-key';
?? calculate monthly growth rate for each product category

Key Features: - Automatic conversion of natural language to SQL queries - Automatic table schema analysis - Generation of complex aggregation and JOIN queries - Shortened learning curve and improved productivity - Non-developers can perform data analysis

Limitations: - Requires OpenAI or Anthropic API key - API call costs incur - Network connection required - Generated query validation recommended

Real-World Use Cases: - Self-service analytics for business analysts - Rapid prototyping and exploratory analysis - Education and SQL learning assistance - Draft generation for complex queries

Note: This feature requires an API key, so this lab does not provide executable tests. Please refer to the examples above to test on your own.

🔧 Management

ClickHouse Connection Info

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

Useful Commands

# Check ClickHouse status
cd ../../oss-docker
./status.sh

# Connect to CLI
./client.sh 8123

# View logs
docker logs clickhouse-25-7

# Stop
./stop.sh

# Complete removal
./stop.sh --cleanup

📂 File Structure

25.7/
├── README.md                      # This document
├── 00-setup.sh                    # ClickHouse 25.7 installation script
├── 01-sql-update-delete.sh        # SQL UPDATE/DELETE test execution
├── 01-sql-update-delete.sql       # SQL UPDATE/DELETE SQL
├── 02-count-optimization.sh       # count() optimization test execution
├── 02-count-optimization.sql      # count() optimization SQL
├── 03-join-performance.sh         # JOIN performance test execution
├── 03-join-performance.sql        # JOIN performance SQL
├── 04-bulk-update.sh              # Bulk UPDATE test execution
└── 04-bulk-update.sql             # Bulk UPDATE SQL

🎓 Learning Path

For Beginners

1. 00-setup.sh - Understand environment setup
2. 02-count-optimization - Start with basic aggregation functions
3. 01-sql-update-delete - Learn UPDATE/DELETE basics

For Intermediate Users

1. 03-join-performance - Understand JOIN optimization
2. 04-bulk-update - Learn bulk data processing
3. Explore AI-Powered SQL features (API key required)

For Advanced Users

• Combine all features for real production scenarios
• Analyze query execution plans with EXPLAIN
• Performance benchmarking and optimization
• Design real-time data pipelines

💡 Feature Comparison

ClickHouse 25.7 vs Previous Versions

Feature	Before 25.7	ClickHouse 25.7	Improvement
UPDATE/DELETE	Slow (full rewrites)	Patch-part mechanism	Up to 1000x faster
count() aggregation	Standard performance	Optimized	20-30% faster
JOIN operations	Good performance	Enhanced	Up to 1.8x faster
Bulk UPDATE	Slow for large datasets	Highly optimized	Up to 4000x vs PostgreSQL
SQL Generation	Manual only	AI-powered (optional)	Natural language to SQL

UPDATE Performance Comparison

Operation	Traditional RDBMS	ClickHouse 25.7	Performance Gain
Single row UPDATE	Milliseconds	Microseconds	100-1000x
Bulk UPDATE (1M rows)	Hours	Seconds	4000x
Complex conditional UPDATE	Very slow	Fast	1000x

🆕 What's New in 25.7

• Lightweight UPDATE — patch-part based updates for MergeTree (#82004)
• AI-powered SQL generation — natural language to SQL in the client (#83314)
• Financial functions — XIRR, IRR, XNPV, NPV (#81599)
• NumericIndexedVector — Roaring-bitmap compressed vector structure (#74193)
• Iceberg writes — INSERT support for Iceberg tables (#82692)
• SZ3 lossy compression codec — for Float32 / Float64 columns (#67161)
• Complex types in Iceberg schema evolution — nested type evolution (#73714)
• polygonIntersectsCartesian / Spherical — polygon intersection predicates (#81882)
• colorSRGBToOkLCH / colorOkLCHToSRGB — colour space conversion (#83679)
• _part_granule_offset — new MergeTree virtual column (#82341)
• max_waiting_queries — workload setting bounding the query queue (#81250)
• Reading Iceberg data files by field id — field-id based resolution (#83653)

🔍 Additional Resources

• Official Release Blog: ClickHouse 25.7 Release
• ClickHouse Documentation: docs.clickhouse.com
• Release Notes: Changelog 2025
• GitHub Repository: ClickHouse GitHub

📝 Notes

• All features verified on ClickHouse 25.7.8.71
• Each script can be executed independently
• Read and modify SQL files directly to experiment
• Test data is generated within each SQL file
• Cleanup is commented out by default
• Thorough testing recommended before production use
• AI-Powered SQL feature requires an API key

🔒 Security Considerations

When using UPDATE/DELETE operations: - Test carefully in production environment - UPDATE without WHERE clause affects entire table - Perform bulk updates after backup - Monitor mutation queue

When using AI-Powered SQL: - Manage API keys securely as environment variables - Validate generated SQL before execution - Access control for sensitive data - Monitor API usage and costs

⚡ Performance Tips

UPDATE/DELETE optimization: - Limit scope with appropriate WHERE conditions - Efficient filtering using partition keys - Utilize index keys in WHERE conditions - Monitor mutation status (system.mutations table)

count() optimization: - Use count() when possible (instead of count(column)) - Leverage indexes with appropriate ORDER BY keys - Limit query scope using partitions - Pre-aggregate with Materialized Views

JOIN optimization: - Place smaller table as RIGHT table - Use appropriate data types for JOIN keys - Filter data before JOIN with WHERE - Remove unnecessary columns

Bulk UPDATE optimization: - Adjust batch size for memory efficiency - Perform bulk updates during off-peak hours - Distribute updates by partition - Monitor progress

🚀 Production Deployment

Migration Strategy

1. Validate in test environment - Test all UPDATE/DELETE queries - Performance benchmarking - Establish rollback plan

2. Gradual rollout - Start with small tables - Monitor and measure performance - Immediate rollback on issues

3. Monitoring - Check mutation queue status - Monitor resource usage - Track query performance

Best Practices

-- Check mutation status
SELECT *
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time DESC;

-- Monitor running queries
SELECT
    query_id,
    user,
    query,
    elapsed,
    memory_usage
FROM system.processes
WHERE query NOT LIKE '%system.processes%';

-- Table part status
SELECT
    partition,
    name,
    rows,
    bytes_on_disk
FROM system.parts
WHERE table = 'your_table'
  AND active = 1;

🤝 Contributing

If you have improvements or additional examples for this lab: 1. Register an issue 2. Submit a Pull Request 3. Share feedback

📄 License

MIT — free to learn from and modify.

---

Happy Learning! 🚀

For questions or issues, please refer to the main clickhouse-hols README.

---

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

# 2. 각 기능별 테스트 실행
./01-sql-update-delete.sh
./02-count-optimization.sh
./03-join-performance.sh
./04-bulk-update.sh

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

# SQL 파일 실행
cd ../releases/25.7
source 01-sql-update-delete.sql

./01-sql-update-delete.sh
# 또는
cat 01-sql-update-delete.sql | docker exec -i clickhouse-25-7 clickhouse-client --multiline --multiquery

./02-count-optimization.sh

./03-join-performance.sh

./04-bulk-update.sh

-- OpenAI API 사용
SET openai_api_key = 'your-api-key';
?? show me top 10 customers by revenue

-- Anthropic API 사용
SET anthropic_api_key = 'your-api-key';
?? calculate monthly growth rate for each product category

# ClickHouse 상태 확인
cd ../../oss-docker
./status.sh

# CLI 접속
./client.sh 8123

# 로그 확인
docker logs clickhouse-25-7

# 중지
./stop.sh

# 완전 삭제
./stop.sh --cleanup

25.7/
├── README.md                      # 이 문서
├── 00-setup.sh                    # ClickHouse 25.7 설치 스크립트
├── 01-sql-update-delete.sh        # SQL UPDATE/DELETE 테스트 실행
├── 01-sql-update-delete.sql       # SQL UPDATE/DELETE SQL
├── 02-count-optimization.sh       # count() 최적화 테스트 실행
├── 02-count-optimization.sql      # count() 최적화 SQL
├── 03-join-performance.sh         # JOIN 성능 테스트 실행
├── 03-join-performance.sql        # JOIN 성능 SQL
├── 04-bulk-update.sh              # Bulk UPDATE 테스트 실행
└── 04-bulk-update.sql             # Bulk UPDATE SQL

-- Mutation 상태 확인
SELECT *
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time DESC;

-- 실행 중인 쿼리 모니터링
SELECT
    query_id,
    user,
    query,
    elapsed,
    memory_usage
FROM system.processes
WHERE query NOT LIKE '%system.processes%';

-- 테이블 파트 상태
SELECT
    partition,
    name,
    rows,
    bytes_on_disk
FROM system.parts
WHERE table = 'your_table'
  AND active = 1;