ClickHouse Projection Custom Settings Lab

---

A hands-on laboratory for learning how to configure custom settings (especially index_granularity) for ClickHouse Projections.

🎯 Purpose

This lab provides practical experience with Projection custom settings in ClickHouse: - Understanding how to apply custom settings to Projections (25.12+ feature) - Comparing different index_granularity values for Projections - Measuring the impact of granularity on point query performance - Learning best practices for choosing granularity based on query patterns

Whether you're optimizing point query performance with Projections or choosing the right granularity for different access patterns, this lab offers structured exercises with real data and measurable results.

📁 File Structure

projection-customsettings/
├── README.md                      # This file
├── 01-setup.sql                   # Database and table creation
├── 02-add-projections.sql         # Create projections with custom settings
├── 03-granularity-comparison.sql  # Compare different granularity values
├── 04-performance-tests.sql       # Performance testing queries
├── 05-monitoring.sql              # Query and projection monitoring
└── 99-cleanup.sql                 # Cleanup script

🚀 Quick Start

Execute all scripts in sequence:

cd workload/projection-customsettings

clickhouse-client < 01-setup.sql
clickhouse-client < 02-add-projections.sql
clickhouse-client < 03-granularity-comparison.sql
clickhouse-client < 04-performance-tests.sql
clickhouse-client < 05-monitoring.sql

# Cleanup when done
clickhouse-client < 99-cleanup.sql

📖 Detailed Lab Steps

1. Environment Setup

clickhouse-client < 01-setup.sql

What it does: - Creates projection_granularity_test database - Creates events table with default granularity (8192) - Inserts 1 million sample event records

Expected time: ~30-60 seconds

---

2. Create Projections with Custom Settings

clickhouse-client < 02-add-projections.sql

What it does: - Creates basic projection (inherits base table granularity) - Shows syntax for custom settings (25.12+ only) - Examples of multiple projections with different granularity values - Materializes projections

Expected time: ~1-2 minutes

Version Note: - ClickHouse 25.10 and below: WITH SETTINGS syntax not supported - ClickHouse 25.12+: WITH SETTINGS syntax available

---

3. Granularity Comparison Analysis

clickhouse-client < 03-granularity-comparison.sql

What it does: - Uses existing granularity_test database for comparison - Tests G=256, 1024, 4096, 8192 tables - Analyzes storage overhead and index statistics - Calculates marks and granule efficiency

Expected time: < 10 seconds

---

4. Performance Testing

clickhouse-client < 04-performance-tests.sql

Test scenarios: - Point query performance tests (single ID lookup) - Range query performance tests - Aggregation query comparison - EXPLAIN analysis for execution plans

Key metrics to observe: - Query execution time - Rows read per query - Marks scanned - Index efficiency

---

5. Monitoring and Analysis

clickhouse-client < 05-monitoring.sql

What it monitors: - Recent query performance from query_log - Projection usage statistics - Parts and merge activity - Projection materialization status

---

6. Cleanup

clickhouse-client < 99-cleanup.sql

Removes all test data and tables.

🔍 Key Concepts

Projection Custom Settings (25.12+)

Starting with ClickHouse 25.12, you can specify custom settings for individual projections:

ALTER TABLE events
ADD PROJECTION user_lookup (
    SELECT * ORDER BY user_id, event_time
) WITH SETTINGS (
    index_granularity = 256
);

Index Granularity Effects

Granularity	Marks	Index Overhead	Point Query	Range Scan
256	Many	High (~0.09%)	Very Fast	Slow
1024	Medium	Medium (~0.02%)	Fast	Medium
4096	Few	Low (~0.005%)	Medium	Fast
8192	Very Few	Very Low (~0.004%)	Slow	Very Fast

Recommended Settings by Query Pattern

• Point Query (single key lookup): 256~512
• Small Range Query: 512~1024
• Medium Range Query: 1024~2048
• Large Range Scan: 4096~8192
• Full Table Scan: 8192~16384

📊 Expected Results

Point Query Performance (player_id = 500000)

G=256:  ~256 rows read
G=8192: ~8192 rows read
→ 32x performance difference

Storage Overhead (2M rows)

G=256:  7,814 marks, 66.34 MiB, 0.09% index overhead
G=8192:   245 marks, 54.02 MiB, 0.004% index overhead

💡 Best Practices

Multi-Projection Strategy

For tables with diverse query patterns, create multiple projections:

-- Projection 1: Point Query optimization
ADD PROJECTION user_lookup (...) WITH SETTINGS (index_granularity = 256);

-- Projection 2: Session analysis optimization
ADD PROJECTION session_analysis (...) WITH SETTINGS (index_granularity = 512);

-- Projection 3: Aggregation optimization
ADD PROJECTION event_stats (...) WITH SETTINGS (index_granularity = 2048);

When to Use Smaller Granularity

✅ Good for: - Frequent point queries by specific keys - Low-latency requirements - High-cardinality keys - User/session lookup patterns

❌ Not suitable for: - Primarily analytical/scan queries - Memory-constrained environments - Very large tables (billions of rows)

⚠️ Version Compatibility

• 25.10 and below: WITH SETTINGS syntax not supported
• Projections inherit base table's granularity
• 25.12 and above: WITH SETTINGS syntax supported
• Each projection can have independent granularity

🔧 Troubleshooting

Projection Not Created

Check ClickHouse version:

SELECT version();

If version < 25.12, use basic projection syntax without WITH SETTINGS.

High Memory Usage

Monitor index memory consumption:

SELECT
    table,
    name as projection_name,
    formatReadableSize(sum(primary_key_bytes_in_memory_allocated)) as index_memory
FROM system.projection_parts
WHERE database = 'projection_granularity_test'
GROUP BY table, name;

🛠 Prerequisites

• ClickHouse server (local or cloud)
• ClickHouse client installed
• Basic SQL knowledge
• Sufficient disk space (~200MB for test data)

📚 Reference

• ClickHouse Projections
• Index Granularity
• Performance Optimization

📝 License

MIT — same as the rest of the repository.

👤 Author

Ken (ClickHouse Solution Architect) Created: 2025-01-09

---

projection-customsettings/
├── README.md                      # 이 파일
├── 01-setup.sql                   # 데이터베이스 및 테이블 생성
├── 02-add-projections.sql         # 커스텀 설정으로 Projection 생성
├── 03-granularity-comparison.sql  # 다양한 granularity 값 비교
├── 04-performance-tests.sql       # 성능 테스트 쿼리
├── 05-monitoring.sql              # 쿼리 및 Projection 모니터링
└── 99-cleanup.sql                 # 정리 스크립트

cd workload/projection-customsettings

clickhouse-client < 01-setup.sql
clickhouse-client < 02-add-projections.sql
clickhouse-client < 03-granularity-comparison.sql
clickhouse-client < 04-performance-tests.sql
clickhouse-client < 05-monitoring.sql

# 완료 후 정리
clickhouse-client < 99-cleanup.sql

clickhouse-client < 01-setup.sql

clickhouse-client < 02-add-projections.sql

clickhouse-client < 03-granularity-comparison.sql

clickhouse-client < 04-performance-tests.sql

clickhouse-client < 05-monitoring.sql

clickhouse-client < 99-cleanup.sql

ALTER TABLE events
ADD PROJECTION user_lookup (
    SELECT * ORDER BY user_id, event_time
) WITH SETTINGS (
    index_granularity = 256
);

G=256:  약 256 rows 읽음
G=8192: 약 8192 rows 읽음
→ 32배 성능 차이

G=256:  7,814 marks, 66.34 MiB, 0.09% 인덱스 오버헤드
G=8192:   245 marks, 54.02 MiB, 0.004% 인덱스 오버헤드

-- Projection 1: Point Query 최적화
ADD PROJECTION user_lookup (...) WITH SETTINGS (index_granularity = 256);

-- Projection 2: Session 분석 최적화
ADD PROJECTION session_analysis (...) WITH SETTINGS (index_granularity = 512);

-- Projection 3: 집계 최적화
ADD PROJECTION event_stats (...) WITH SETTINGS (index_granularity = 2048);

SELECT version();

SELECT
    table,
    name as projection_name,
    formatReadableSize(sum(primary_key_bytes_in_memory_allocated)) as index_memory
FROM system.projection_parts
WHERE database = 'projection_granularity_test'
GROUP BY table, name;