Common Mistakes when Designing Schema for Log Data in ClickHouse®

TL;DR: We took the OTel-logs schema from ClickHouse Inc.’s reference benchmark and applied four rounds of changes — type tightening for ResourceAttributes, codec hygiene, using optimize_row_order with a re-insert, and optimizing the data ingestion pipeline. The results:

• Compressed footprint: 39.5 GiB → 16.8 GiB (−57%) on the same 1B-row slice. • ResourceAttributes column: 16.1 GiB → 60 MiB (−99.6%, 270× smaller). • End-to-end benchmarks Q1–Q9 (warm cache): 0.43 s → 0.36 s (−16%).

Reference benchmark code: ClickHouse/TextBench

Improving tables in ClickHouse doesn’t always require a major redesign. A few simple changes to data types, compression, and row ordering can significantly reduce storage use and speed up queries. In this article, we walk through some common schema design mistakes and show how to fix them.

Our starting point

TextBench is a reference full-text search benchmark built by ClickHouse to create a fair, reproducible benchmark to measure query latency and storage efficiency for the full-text search workloads typical for analyzing log data. The benchmark uses 50 1B-row synthetic OpenTelemetry logs.

We set out to optimize the schema used in the ClickHouse example, improving query performance and storage usage. We applied four rounds of changes:

• Type-tightening the ResourceAttributes column
• Applying good codec hygiene, including using ZSTD(3) on heavy columns only
• Using optimize_row_order with a re-insert
• Optimizing the data ingestion pipeline

We’ll cover our rationale for the changes we made and show our results for each round, then wrap it up with an overview of everything we accomplished with the final redesign.

Hardware setup and original schema

For hardware, we used an m8g.8xlarge instance and 2.9 TiB gp3 storage with 1500 MB/s throughput and 16000 IOPS. The volume was provisioned slightly above the m8g.8xlarge EBS limit of 1250 MB/s, but that’s the closest available size. As in the original benchmark, we used ClickHouse version 26.3.

Here’s the baseline schema, copied verbatim from the reference benchmark:

We use a 1B-row slice loaded via:

We disabled native_reader_v3 here because it was 2-3× slower than Arrow-based. It happens due to the shared budget of memory for fetched compressed chunks of Parquet files and uncompressed and deserialized chunks with columns. Uncompressed data uses most of the memory budget, which blocks prefetching of next chunks. The final part is 39.46 GiB compressed / 642.45 GiB uncompressed, just over 16× smaller.

Here’s the per-column footprint (note the logarithmic scale in the chart):

Figure 1. Relative sizes of columns

Three columns hold 84% of the disk space after compression:

Figure 2. Most of the data is in three columns

Throughout this article we follow the reference benchmark’s comparison rules:

• Same on-disk order: (ServiceName, Timestamp) as both ClickHouse sort key and Elasticsearch index sort.
• Same compression algorithm (ZSTD).
• Full-text inverted index only on Body; all other string fields are exact-match only.

There’s one further constraint, implied rather than stated: no extra structures beyond the original columns and the single text index on Body.

The query we use to inspect per-column footprints is shown once here and reused implicitly throughout. Replace <table> with the table name in question.

Step 1: Type-tightening the ResourceAttributes column

A useful first step on any new dataset is counting distinct values per column:

Figure 3. Unique values per column

ResourceAttributes is the largest column on disk (16 GiB) yet has only ~2,000 distinct full-map values across a billion rows — it describes per-source/per-pod metadata, and there are only a few thousand distinct pods. The Map(LowCardinality(String), String) type does dictionary-encode keys, but the value side is a plain String and the type doesn’t exploit repetition at the whole-map level.

We have three options:

• Map(LowCardinality(String), LowCardinality(String)) — adds a value-side dictionary; preserves attrs[‘key’] access semantics.
• LowCardinality(String) — flatten the map to a serialized string; loses key-level access but treats the whole map as a single low-cardinality value.
• Hash-keyed lookup table — store a fingerprint per row, keep the full attributes in a separate table joined on the fingerprint. This is the most invasive and most efficient option, but it doesn’t stay within the constraints of the benchmark. We didn’t explore it here.

Note on option 1: there is a known performance issue with Map(LowCardinality, …) types when bucket serialization is used — see ClickHouse#103804. It’s not a factor in this article because it’s not enabled by default.

We test options 1 and 2 by adding shadow columns:

Storage

Let’s look at the disk space we’re using and the filter latency:

Figure 4. Disk space and filter latency

The collapse in uncompressed size for the flat variant (359 → 1.87 GiB) reflects how LowCardinality stores per-row data: a small index per row plus one shared dictionary per part. system.parts_columns.column_data_uncompressed_bytes reports just the dictionary + column file’s bytes, not the dictionary expanded out to one entry per row.

Read path

Whole-column scan (SELECT count() FROM otel_logs_lc WHERE NOT ignore(<column>)):

Figure 5. Queries are up to 65x faster

Facultatively (they are not part of the original benchmark), we can do a quick look at queries or more precise filters that we can expect from a system that uses log data. We also will disable cached predicates by using the session setting use_query_condition_cache = 0:

Exact map lookup: Map(LC, String)

Same access pattern, smaller column: Map(LC, LC)

Flattened: substring search on the serialized form: LowCardinality(String) + LIKE

Figure 6. 92% fewer bytes processed

Caveat on the LIKE query. LIKE ‘%k8s.pod.name%<value>%’ matches both substrings in order anywhere in the serialized value — it is not strictly equivalent to attrs['k8s.pod.name'] = '<value>'. On this dataset the counts coincide because no other key contains the literal k8s.pod.name, but for general use it’s best to go with a stricter pattern such as '%"k8s.pod.name":"<value>"%' that mirrors the serialized representation.

Recovering exact-match and grouping on the flattened column

Flattening to LowCardinality(String) doesn’t actually prohibit us from taking advantage of per-key access — it just moves it from a typed operator to a string-parsing one. ClickHouse has extractKeyValuePairs(s, key_delim, pair_delim, quote) which parses the serialized map back into a Map at query time. Two patterns are useful.

1. Strict exact-match. Combine the cheap LIKE with an extractKeyValuePairs lookup. The LIKE is a coarse pre-filter that does the bulk of the work against the LC dictionary; the parse only runs on rows that survive it.

-- 0.289 s, 2.00 GB processed

This is exact-match-equivalent to the original attrs[‘k8s.pod.name’] = ‘<value>’ on the Map:

-- 13.86 s, 25.4 GB processed

The LIKE version is a more complicated query, but its ~50× lower latency than the baseline Map(LC, String) (0.289 s vs. 13.86 s) makes it worth the trouble.

2. Grouping by an extracted key. Naively, call extractKeyValuePairs per row:

-- 7.49 s, 2.00 GB, 2.15 GiB peak memory

But the parse is the expensive step, and the LC dictionary means most rows share the same serialized value. Group by the whole serialized column first (cheap — it’s a dictionary-index group-by), aggregate to a partial state, then parse and re-aggregate on the much smaller grouped result:

-- 0.233 s, 428.71M rows, 857 MB

The parse now runs once per distinct serialized map (thousands), not once per row (hundreds of millions). It returns the same 56-row result.

Side-by-side on the wider ‘cart’ deployment query:

Figure 7. The combined techniques are 86× faster

For the rest of this article we’ll keep LowCardinality(String) for ResourceAttributes. The access pattern is recoverable — it just looks different at query time, and the two-level pattern above is faster than any of the typed-Map variants. If you’d rather keep the native attrs[‘key’] syntax, Map(LC, LC) is the drop-in choice: still 6× smaller and 4–6× faster than the baseline, with no caller-side change.

Step 2. Applying good codec hygiene

The original schema used ZSTD(1) on every column. Applying an expensive codec to a column that already compresses well costs CPU on both write and read paths for negligible storage gain. The cost compounds for analytical queries, which read only a subset of columns — every byte of unnecessary decompression on a hot column is pure overhead.

Strip ZSTD from small / well-compressing columns

Heuristic: drop the codec on any columns that have a small impact on the total compressed size of the table. In our case we will choose all columns under ~1 GiB compressed. They contribute under 10% of total compressed size combined, but their decompression cost is paid by every full-row scan.

Be aware that codec changes are lazy; we have to use OPTIMIZE to force a rewrite. We also fixed ScopeName, which was a String in the baseline despite having only 21 distinct values.

Figure 8. Removing expensive codecs on small columns increases storage usage slightly

Storage went up slightly, but we’ll make up for it with CPU time. Here’s a WHERE NOT ignore(*) full-row scan:

Figure 9. Smaller columns that use the default codec perform better

The pattern: the smaller the read set, the bigger the relative win — exactly because the small columns we just decoded out were the ones paying ZSTD overhead.

Test ZSTD levels per column

It’s a common misconception that higher ZSTD levels are uniformly slower and uniformly smaller. Levels toggle different heuristics; on log-shaped data, level 3 can be both smaller and faster than level 1.

The reference Elasticsearch config uses best_compression, which maps to ZSTD level 3 since 8.16. For an apples-to-apples comparison that’s the level which actually should be used in the original article.

— Re-write only the changed columns, skipping the others:

Figure 10. Per-column scan times – size is nearly unchanged, but scan time is not

For this dataset, ZSTD(3) is unambiguously better on LogAttributes. On the high-cardinality ID columns it’s neutral or slightly larger on disk but consistently faster to decode. We keep ZSTD(3) for the read-time win.

Step 3. Exploit optimize_row_order

The benchmark constrains us to use ORDER BY (ServiceName, Timestamp), but ClickHouse can refine the row order within sort-key groups to improve compression locality. The setting is optimize_row_order = 1.

After this — no change. ResourceAttributes is still 1.28 GiB; total is still 24.24 GiB.

The OPTIMIZE FINAL trap

optimize_row_order is applied during merges of newly-inserted parts. OPTIMIZE FINAL over existing parts that were inserted under the old setting will not retroactively reorder them. To realize the win on existing data, re-insert through INSERT … SELECT:

Figure 11. Reorder rows, then re-insert

ResourceAttributes collapses because rows with identical full-map values now cluster within granules, dramatically improving compression locality. The high-cardinality ID columns barely move — their values are randomly generated, so sorting them in different order will not help much.

The text_idx shrinks too

Row reordering improves compression on the text index for Body as well — adjacent rows now share more tokens, which the inverted-list compressor exploits. Querying system.data_skipping_indices:

Figure 12. text_idx shrinks too

That’s a 38% reduction on the index itself, on top of the column-data wins above. Folded into the end-to-end on-disk total (system.tables.total_bytes, which counts column data + marks + primary index + skip indexes):

Figure 13. Our results so far

Note: optimize_row_order does work very well in the current example, because there are a lot of rows which share the same ORDER BY (ServiceName, Timestamp) within one granule, where Timestamp is rounded to seconds.

With a correctly tuned ORDER BY, it likely will not improve very much, because a good ORDER BY should use all available byte “space” provided by index_granularity.

Step 4. Default ingestion pipeline

Three small adjustments compound into a meaningfully cheaper ingestion path.

1. Don’t materialize heavy skip indexes on insert

Parts produced by INSERT and the first few generations of merges are short-lived — they will be re-merged within seconds in any cluster with active ingest. Indexes built on them get rebuilt on every merge. Two session settings control this:

Disabling materialization on insert defers the work to the first merge and any consecutive merge afterwards. Indexes still build automatically — just later. The corresponding *_skip_indexes_on_merge merge_tree settings exist, but using them disables automatic index materialization entirely; you then have to run ALTER TABLE … MATERIALIZE INDEX … manually.

To audit the cost of index materialization, look at ProfileEvents['MergeTreeDataWriterSkipIndicesCalculationMicroseconds'] in system.part_log.

2. Force Compact parts on insert

Insert-time parts pack better as Compact (one file for all columns) than Wide (at least two and for fancy types can be many files per column), because Compact has lower filesystem overhead per part — and we’re going to throw these parts away in the next merge anyway.

This forces 0-level parts (those produced by inserts) into Compact format; the next merge produces 1-level Compact or Wide parts as usual, which is decided by merge_tree settings:

3. A note on benchmark reproducibility

The reference benchmark drives ingest with clickhouse-client --query 'INSERT … FORMAT Parquet', which reads, decompresses and deserializes Parquet on the client side and ships ClickHouse-native format bytes over the wire. Our INSERT … SELECT FROM s3(...) does that work on the server. End-to-end ingest timings are therefore not directly comparable — the server is doing more work per row in our setup.

End-to-end results

Final schema

Here’s our final optimized schema:

We use ReplicatedMergeTree rather than MergeTree because the production target cluster runs replicated tables; the storage and CPU conclusions above carry over unchanged. ResourceAttributes is collapsed to LowCardinality(String) — this loses attrs['key'] access. If your queries need it, swap to Map(LowCardinality(String), LowCardinality(String)) and accept the ~2.7 GiB final column size in exchange for native key access.

Altinity.Cloud overrides the default max_bytes_to_merge_at_max_space_in_pool from 150 GiB to 50 GiB, which keeps worst-case merge times bounded.

Benchmark – Q1-Q9

We run the nine queries in the TextBench repo. Caches are dropped before cold runs (SYSTEM DROP MARK CACHE; SYSTEM DROP UNCOMPRESSED CACHE; SYSTEM DROP PAGE CACHE; … see the repo for the full list). It’s not equal to the “Cold” cache benchmark done in the original blog, but close to that.

Each cell is the median of three runs; the table reports the sum of medians across Q1–Q9.

Performance

Here are the performance numbers:

Figure 14. Performance benchmark summary

Per-query numbers and the query files are in the fork.

Storage

Figure 15. Storage benchmark summary

Recap

Here’s the high-level summary of the problems we solved:

Problem: Map columns treated as opaque

The fix: Map(LC,LC) or flatten to LowCardinality(String):

Figure 16. Results of using LowCardinality

Problem: ZSTD applied uniformly

The fix: Strip codec under ~1 GiB; use ZSTD(3) on heavy columns:

Figure 17. Using proper codec hygiene

Problem: optimize_row_order not exploited

The fix: Enable and re-insert through INSERT … SELECT:

Figure 18. Using optimize_row_order and reinserting

Problem: Default ingestion pipeline

The fix: Skip indexes on insert, compact on insert

Figure 19. Using an optimized ingestion pipeline

Conclusion

We began with a schema from the original ClickHouse Inc. benchmark post. Over 3 iterations of non-invasive optimizations, none of which broke the original benchmark rules, we reduced the table size by 2× and sped up query execution. Truth be told, these steps merely baseline what should be done for any table of significant volume. The benchmark claims to have realistic data, but the schema is not realistic at all for heavy-duty production systems. Still, it was useful for showing some basic steps and techniques we do when we begin to optimize a schema.

Lessons:

• Codecs are a double-edged sword. Heavy codecs pay off only where the volume of compressed data justifies decode/encode code cost. Safe rule: apply ZSTD (and especially higher levels) to the largest columns of the table. Small or already-repetitive columns get squeezed for marginal disk gain while every scan eats CPU on decompression. Choosing a compression level is also not that simple — higher level doesn’t mean slower or better compression. Test compression settings per column; don’t apply a single codec and level blanket-style across all columns.
• Know your data. The largest column in the original table, ResourceAttributes at 16 GiB, held only ~2000 full-map distinct values. Quite an expensive mistake. And you will pay its cost many times: storage, CPU on write + merge, CPU on every read and even filtering. This is also an indicator of major structural weakness of this table. It wasn’t designed with actual production-like usage in mind.
• ORDER BY is one of the most important decisions you can make. The fact that optimize_row_order alone delivered 31% total table size (and index size) reduction is not a victory, but an indicator. An indicator that the relationship / similarity / locality of data wasn’t properly exploited when designing the schema of the table. When you design tables, one of your main targets should be to squeeze as much of these regularities from your data as possible and build your schema around them, so ClickHouse can see and use them at runtime. Yes, here we were limited with what we can do with ORDER BY by the benchmark rules — but even the smallest thing we had access to achieved significant gains.

Appendix: something we tried, but didn’t help

index_granularity vs min/max_compress_block_size

For tables with rather heavy rows — or particularly heavy individual fields — it can pay to lower index_granularity from the default 8192 down to ~512. Below 512 is doable, but that needs careful benchmarking: the primary index size grows in memory and scan cost can rise. Skip-index granularities should be adjusted in proportion.

ClickHouse also supports dynamic granularity calculation, so verify what you actually got from system.parts:

Compression effectiveness depends on both granule size and compression-block size (min_compress_block_size / max_compress_block_size, see ClickHouse#55201 for context). These are adjustable per column:

On a separate log-style table at index_granularity = 512:

Figure 20. Adjusting the min/max_compress_block_size didn’t help

Less relevant for fields stored independently (per-column files, tuple subcolumns, JSON dynamic paths), since the compress-block limit applies per subcolumn.