Why JSON Storage Efficiency Matters in ClickHouse
ClickHouse is a high-performance analytical database designed for fast queries on large datasets. JSON columns, which can be deeply nested and have many paths, are increasingly common as applications ingest semi-structured data. However, storing each JSON typed path as separate files quickly becomes a bottleneck, especially on object storage systems where thousands of small files cause high request overhead and degrade performance.
This article explores a recent improvement in ClickHouse's JSON column storage model that introduces a SHARED modifier for typed paths. This change significantly reduces the number of files and object storage requests while preserving efficient read access.
The Problem: File Explosion with JSON Typed Paths
Prior to this change, each typed path in a JSON column was stored as its own set of files within MergeTree parts. For example, a JSON column with hundreds of typed paths would generate thousands of files per data part. This file explosion leads to several issues:
- High object storage request overhead: Object stores like S3 charge per request, so thousands of small files cause increased costs and latency.
- Performance degradation: Managing and opening many small files slows down query execution.
- Operational complexity: Large numbers of files complicate maintenance and increase metadata management overhead.
The root cause is that the storage model treats each typed path as an independent data stream, resulting in many small files instead of consolidated storage.
The Solution: Introducing SHARED Typed Paths
To address this, the PR adds a SHARED modifier for JSON typed paths. Instead of creating separate files for each path, all SHARED paths within a data part are grouped into a constant set of shared files.
How It Works
Shared files per part: All
SHAREDtyped paths write their data into the same set of files rather than separate files per path.Marks file for indexing: A marks file stores offsets within granules that index the data for each path. This allows the system to locate the data for a particular path efficiently.
Efficient partial reads: Because the marks file provides offsets, queries can read only the relevant portions of the shared files, preserving fine-grained access.
Compatibility: The approach maintains compatibility with existing features such as
ALTERoperations, ensuring seamless integration.
Technical Details
The marks file acts as a directory mapping each path's data offsets within the shared files. When a query requests data for a specific path, ClickHouse uses the marks to seek to the correct position in the shared files and reads only the necessary bytes. This avoids loading all paths' data and keeps query performance high.
This design cleverly balances the trade-off between file count reduction and read efficiency. By consolidating many small data streams into shared files indexed by offsets, it reduces overhead without sacrificing the ability to perform selective reads.
Takeaway: Consolidate Small Data Streams to Improve Storage Efficiency
This improvement highlights an important engineering principle when dealing with many small data substreams: consolidating storage into shared files indexed by offsets can drastically reduce file system or object storage overhead.
Especially in cloud environments where object storage request costs and latencies matter, reducing file counts minimizes operational costs and improves performance. At the same time, maintaining an index of offsets preserves the ability to read data selectively and efficiently.
This balance is crucial for production-grade systems handling complex data types like JSON, which can easily generate many small data streams.
Conclusion
The addition of SHARED typed paths in ClickHouse's JSON type storage model is a practical and elegant solution to the problem of excessive file counts for wide JSON columns. By grouping paths into shared files and indexing offsets with a marks file, ClickHouse reduces object storage overhead and improves query performance without losing fine-grained read capabilities.
This change exemplifies how thoughtful storage design can address real-world scalability challenges in analytical databases.
Credit: This article is based on the merged PR "Add SHARED typed paths to the JSON type" in the ClickHouse repository.
Read the full breakdown on nossletter.tech.
Originally published on Nossletter.
Top comments (0)