Skip to content

Latest commit

 

History

History
181 lines (136 loc) · 6.85 KB

ClickHouse_Complete_Benchmark_Report.md

File metadata and controls

181 lines (136 loc) · 6.85 KB

ClickHouse Complete JSON vs Variant vs Typed Columns Benchmark Report

Executive Summary

This comprehensive benchmark compares four different approaches for handling JSON data in ClickHouse:

  1. JSON Baseline: Pure ClickHouse JSON Object type
  2. Typed Columns: Extracted fields + JSON fallback (what we incorrectly called "variants")
  3. Pure Variants: Only typed columns, no JSON fallback
  4. True Variant Columns: Actual ClickHouse Variant type columns

Test Configuration

  • Dataset: Bluesky social media events (1M records for approaches 1-3, 50K for approach 4)
  • Data Size: ~485MB uncompressed JSON
  • Queries: 5 analytical queries testing different access patterns
  • Hardware: ClickHouse 25.6.1 on macOS

Performance Results

Query Execution Times (seconds)

Query JSON Baseline Typed Columns Pure Variants True Variants
Q1: Event distribution 0.099 0.094 0.096 0.096
Q2: Event + user stats 0.092 0.109 0.109 0.102
Q3: Hourly patterns 0.092 0.096 0.103 0.095
Q4: Earliest posters 0.093 0.100 0.098 0.100
Q5: Activity spans 0.093 0.101 0.103 0.094
Average 0.094 0.100 0.102 0.097

Storage Efficiency

Approach Records Storage Size Size per 1M Records
JSON Baseline 1,000,000 35.25 KiB 35.25 KiB
Typed Columns 1,000,000 240.06 MiB 240.06 MiB
Pure Variants 1,000,000 84.30 MiB 84.30 MiB
True Variants 50,000 9.52 MiB ~190.4 MiB

Key Findings

🏆 Performance Winner: JSON Baseline

  • Fastest average performance: 0.094 seconds
  • Most consistent: Minimal variance across query types
  • Best storage efficiency: Exceptional 35.25 KiB for 1M records

📊 Detailed Analysis

1. JSON Baseline (Winner)

Strengths:

  • ✅ Fastest overall performance (6% faster than typed columns)
  • ✅ Exceptional storage compression (6,800x better than typed columns)
  • ✅ Consistent performance across all query types
  • ✅ Schema flexibility - handles any JSON structure

Use Cases:

  • Analytics workloads with varied query patterns
  • Datasets with evolving schemas
  • Storage-constrained environments

2. Typed Columns (Field Extraction)

Strengths:

  • ✅ Best for simple aggregations (Q1: 5% faster than JSON)
  • ✅ Predictable performance for extracted fields
  • ✅ Hybrid approach - typed columns + JSON fallback

Weaknesses:

  • ❌ Storage overhead (6,800x larger than JSON)
  • ❌ Slower complex queries (Q2, Q5: 15-18% slower)
  • ❌ Schema rigidity for extracted fields

Use Cases:

  • Known access patterns on specific fields
  • High-frequency simple aggregations
  • Mixed query workloads needing both speed and flexibility

3. Pure Variants (Typed Only)

Strengths:

  • ✅ Better storage than typed columns (65% smaller)
  • ✅ No JSON parsing overhead for extracted fields

Weaknesses:

  • ❌ No schema flexibility
  • ❌ Slowest overall performance (8% slower than JSON)
  • ❌ Limited to predefined schema

Use Cases:

  • Well-defined, stable schemas
  • Storage efficiency important but some typed benefits needed

4. True Variant Columns

Strengths:

  • ✅ Flexible type system - single column, multiple types
  • ✅ Runtime type checking with variantType() and variantElement()
  • ✅ Good performance (3% slower than JSON baseline)

Weaknesses:

  • ❌ Complex query syntax with variant functions
  • ❌ Limited real-world testing (smaller dataset)
  • ❌ Storage overhead vs JSON baseline

Use Cases:

  • Fields that legitimately need to store different types
  • Schema evolution where field types change
  • Union-type semantics required

Storage Deep Dive

Why JSON Baseline Wins Storage

The remarkable storage efficiency of JSON baseline (35.25 KiB vs 240+ MiB) is due to:

  1. ClickHouse JSON compression: Advanced algorithms optimize JSON storage
  2. No data duplication: No extracted columns + original JSON
  3. Columnar efficiency: JSON Object type benefits from ClickHouse's columnar storage
  4. Schema-aware compression: ClickHouse detects patterns in JSON structure

Storage Trade-offs

  • JSON: Minimal storage, maximum flexibility
  • Typed Columns: 6,800x storage cost for predictable field access
  • Pure Variants: 2,400x storage cost, no flexibility
  • True Variants: 5,400x storage cost, type flexibility

Query Pattern Analysis

Simple Aggregations (Q1)

  • Typed Columns win: Direct column access avoids JSON parsing
  • Improvement: 5% faster than JSON baseline
  • Cost: 6,800x storage overhead

Complex Analytics (Q2-Q5)

  • JSON Baseline wins: Optimized JSON path operations
  • ClickHouse JSON optimization: Very efficient for complex queries
  • Typed columns slower: Mixed access patterns reduce benefits

Recommendations

Choose JSON Baseline When:

  • ✅ Storage efficiency is critical
  • ✅ Query patterns are varied and unpredictable
  • ✅ Schema flexibility is important
  • ✅ Consistent good performance is preferred over peak optimization

Choose Typed Columns When:

  • ✅ Specific fields are accessed frequently in simple aggregations
  • ✅ Storage cost is acceptable for performance gains
  • ✅ Hybrid flexibility is needed (some fields typed, some JSON)

Choose Pure Variants When:

  • ✅ Schema is well-defined and stable
  • ✅ Storage efficiency is important but some structure needed
  • ✅ No need for JSON fallback flexibility

Choose True Variant Columns When:

  • ✅ Fields genuinely need to store different types
  • ✅ Runtime type checking is required
  • ✅ Union-type semantics are needed

Conclusion

JSON Baseline emerges as the surprising winner, delivering:

  • Best overall performance (0.094s average)
  • Exceptional storage efficiency (35.25 KiB)
  • Maximum schema flexibility
  • Consistent performance across query types

Key Insight: ClickHouse's JSON optimizations are so effective that the overhead of field extraction and storage duplication outweighs the benefits for most analytical workloads.

When to deviate: Only extract fields to typed columns when you have proven high-frequency access patterns that justify the 6,800x storage cost and accept 6% performance reduction for complex queries.

True Variant columns provide genuine value when you need union-type semantics, but come with query complexity and storage overhead.

Methodology Notes

  • Each query run 3 times, best time recorded
  • Fair comparison with equivalent data volumes where possible
  • True Variants tested with 50K records due to loading constraints
  • Storage measurements from ClickHouse system tables
  • All tests on same hardware and ClickHouse version