JSON Type Stress Test

---

A stress test of the JSON data type's path limits: what happens as the number of distinct paths per row grows past max_dynamic_paths, where the overflow goes, and what reading from that overflow costs.

📋 Overview

Every JSON column has a budget of dynamic paths — paths stored as real subcolumns. Paths beyond max_dynamic_paths are not rejected; they spill into shared data, which still queries correctly but far more expensively. This lab pushes a table to 50,000 fields per row, measures the dynamic/shared split at each setting, and times reads on both sides of the boundary.

Headline result: reading a shared path was 5.3× slower and used 3.4× more memory than reading a dynamic path on the same table.

🎯 Key Features

1. max_dynamic_paths sweep — 100 / 1,000 / 10,000 and an extreme 50,000-field run
2. Dynamic vs shared path distribution — JSONDynamicPaths() / JSONSharedDataPaths()
3. Worst-case field naming — a distinct field name on every row
4. Merge behaviour — how the path budget is resolved when parts merge
5. max_dynamic_types — the type-count analogue of the path budget
6. Read performance across the boundary — timing, bytes and memory

🚀 Quick Start

Prerequisites

• ClickHouse 25.10 or newer (the tests were run on ClickHouse Cloud 25.10.1.7113)
• clickhouse-client, or the web UI

Setup and Run

cd workload/json-stress-test

clickhouse-client --queries-file 01_setup.sql
clickhouse-client --queries-file 02_unique_merge_test.sql

03_performance_test.sql is a recorded result set, not a runnable script: it holds the measurements from the performance phase as comments. Read it rather than executing it.

📚 Test Details

Phase 1 — Path Budget (01_setup.sql)

Test	Setting	Fields per row	Dynamic	Shared
test_paths_100	max_dynamic_paths=100	150	100	50
test_paths_1000	max_dynamic_paths=1000	2,000	1,000	1,000
test_paths_10000	max_dynamic_paths=10000	15,000	10,000	5,000
test_paths_extreme	max_dynamic_paths=10000	50,000	10,000	40,000

Nothing fails. The excess simply moves to shared data — which is exactly why this limit is easy to cross without noticing.

Phase 2 — Unique Fields and Merges (02_unique_merge_test.sql)

• Test 2-1: a distinct field name on every row, the pathological case for path budgeting
• Test 2-2: merge behaviour, i.e. how the dynamic-path set is decided when parts combine
• Test 2-3: max_dynamic_types, the same budgeting idea applied to type variety

Phase 3 — Read Performance (03_performance_test.sql)

Measured on perf_test_large, JSON(max_dynamic_paths=5000), 6,100 rows × 5,000 fields, 2.10 MiB on disk:

Metric	Dynamic path	Shared path	Difference
Query time	32 ms	170 ms	5.3× slower
Memory	13.36 MiB	45.57 MiB	3.4× more
Data read	405.08 KiB	248.83 KiB	—

Note that the shared-path query read fewer bytes and was still far slower and heavier: the cost is in reconstructing values from shared data, not in I/O volume.

🔑 Key Learning Points

• Exceeding max_dynamic_paths is silent — paths spill to shared data instead of erroring
• Shared-path reads cost roughly 5× the time and 3× the memory of dynamic-path reads on the same data
• Fewer bytes read does not mean a cheaper query when values must be reconstructed from shared data
• Frequently queried fields must stay inside the dynamic-path budget; size max_dynamic_paths around them, not around the total field count
• JSONDynamicPaths() / JSONSharedDataPaths() are the tools for checking which side a field landed on
• A distinct field name per row defeats path budgeting entirely — treat unbounded key spaces as a schema problem, not a JSON-type setting problem
• 10,000 is the documented practical ceiling for max_dynamic_paths; the 50,000-field run stays at 10,000 dynamic and pushes 40,000 to shared

📂 File Structure

json-stress-test/
├── README.md                   # This document
├── 01_setup.sql                # Phase 1: max_dynamic_paths sweep
├── 02_unique_merge_test.sql    # Phase 2: unique fields, merges, max_dynamic_types
├── 03_performance_test.sql     # Phase 3: recorded performance results (read-only)
├── 01_previous_tests.sql       # Original combined script, superseded by 01–03
└── progress_log.md             # Run log with intermediate measurements

🔍 Related Labs

• local/releases/25.11 — Map aggregation, the typed alternative to semi-structured data
• workload/projection — projections for read patterns a single sort key cannot serve

📝 Notes

• Measurements come from ClickHouse Cloud 25.10.1.7113 and will differ on other hardware; the ratios are the point
• 01_previous_tests.sql is kept for provenance — it is the original single-file run that 01–03 were split out of, and re-running it recreates the same tables
• progress_log.md records an earlier 2,000-field run where query times were similar but memory still differed by 3.75×, which is a useful counterpoint to the headline numbers
• The tables are left in place for inspection; drop the json_stress_test database when finished

---

cd workload/json-stress-test

clickhouse-client --queries-file 01_setup.sql
clickhouse-client --queries-file 02_unique_merge_test.sql

json-stress-test/
├── README.md                   # 이 문서
├── 01_setup.sql                # Phase 1: max_dynamic_paths 스윕
├── 02_unique_merge_test.sql    # Phase 2: 고유 필드, 머지, max_dynamic_types
├── 03_performance_test.sql     # Phase 3: 성능 측정 결과 기록 (읽기 전용)
├── 01_previous_tests.sql       # 01–03으로 분리되기 전의 원본 통합 스크립트
└── progress_log.md             # 중간 측정치가 담긴 진행 로그