ClickHouse on EKS Measured Benchmark Quiz
- In the benchmark, the one-hour
namespace + timestamprange count (Q1) finished in 4ms against a 100M-row table. What is the key reason?- A) The result was already stored in the query cache
- B) Pruning via PARTITION BY (day) and ORDER BY (namespace, timestamp) meant only 16,385 of 100M rows were read
- C) The gp3 volume's 3,000 IOPS was high enough
- D) The LowCardinality type vectorized the comparisons
Show Answer
Answer: B) Pruning via PARTITION BY (day) and ORDER BY (namespace, timestamp) meant only 16,385 of 100M rows were read
Explanation: When query conditions line up with the partition key (daily) and the sorting key (namespace, timestamp), ClickHouse prunes at granule granularity using the primary index. In this benchmark the query counted 59,916 rows but read only 16,385 (0.016%): granules that lie entirely inside the primary-key range are counted from the index (a ClickHouse 24.6+ optimization), and only the partial granules at the range edges are decompressed. Most of ClickHouse's speed comes from this "don't read it" design.
- In the per-column compression results, trace_id compressed at 1.0× (incompressible) while namespace compressed about 201×. What explains the difference?
- A) Only the trace_id column was missing a compression codec
- B) namespace strings are shorter
- C) trace_id had few repeat patterns and barely compressed with LZ4 in this run, while namespace is low-cardinality and the first ORDER BY key, so identical values run in long streaks
- D) Only namespace gets a separate dictionary file
Show Answer
Answer: C) trace_id had few repeat patterns and barely compressed with LZ4 in this run, while namespace is low-cardinality and the first ORDER BY key, so identical values run in long streaks
Explanation: This LZ4 result was about 3.07 GiB raw → 3.08 GiB compressed. Hex stores only four bits of information per byte, so other codecs or binary representations can still reduce it. namespace has only 10 distinct values and is the first sort key, so identical values are stored in contiguous runs: 95.72 MiB collapsed to 488.63 KiB (201×). This is why "how do we store IDs" is the biggest storage-cost lever in a log schema.
- Which statement matches the measured LZ4 (default) vs ZSTD(3) comparison?
- A) ZSTD(3) won on both storage and scan speed
- B) ZSTD(3) storage was 47% smaller, but the CPU-bound full scan was about 1.9× slower than LZ4
- C) The storage difference between the codecs was within 5%
- D) LZ4 was smaller on disk but slower to scan
Show Answer
Answer: B) ZSTD(3) storage was 47% smaller, but the CPU-bound full scan was about 1.9× slower than LZ4
Explanation: Measured: LZ4 7.82 GiB (1.97×) vs ZSTD(3) 4.16 GiB (3.7×) — 47% less storage — while the warm LIKE '%timeout%' full scan slowed from 2.63 s to 4.9 s. LZ4 for recent partitions plus TTL-driven ZSTD recompression is an option to validate against the real data and CPU/I/O balance.
- The
LIKE '%timeout%'full scan (Q3) took 31.5 s when bypassing the page cache. What bottleneck does this number point to?- A) ClickHouse's string-search algorithm is inefficient
- B) Reading the ~4 GiB compressed message column ÷ 31.5 s ≈ 130 MiB/s — consistent with a volume/instance EBS throughput constraint, but not enough to identify the sole bottleneck
- C) The m5.xlarge's 4 vCPUs were saturated
- D) EBS CSI driver overhead dominated
Show Answer
Answer: B) Reading the ~4 GiB compressed message column ÷ 31.5 s ≈ 130 MiB/s — consistent with a volume/instance EBS throughput constraint, but not enough to identify the sole bottleneck
Explanation: The same query served from the page cache finished in 2.63 s (CPU-bound, ~38M rows/s). The 12× slowdown on direct disk reads is measured evidence that full-scan performance can be a volume-throughput setting rather than a database property — gp3 throughput can be provisioned up to 2,000 MiB/s for an extra fee (reaching it requires at least 8,000 IOPS as well) — but on this m5.xlarge the instance's approximately 137 MiB/s baseline and burst state must also be checked. Review node and volume capacity together for sustained demand.
- What did adding a bloom filter skip index change for the trace_id point lookup (Q5), per the measurements?
- A) Lookup time 1.13 s → 0.036 s (31×), rows read 100M → 1.08M (98.9% skipped), index size about 1.5% of the table
- B) Lookup time was unchanged; only memory usage dropped
- C) Lookup time halved and the index consumed 30% of the table size
- D) Same effect as changing the ORDER BY key, and storage shrank too
Show Answer
Answer: A) Lookup time 1.13 s → 0.036 s (31×), rows read 100M → 1.08M (98.9% skipped), index size about 1.5% of the table
Explanation: The bloom_filter(0.01) GRANULARITY 4 skip index has no false negatives for membership: a negative result permits skipping the group, while a positive may be a false positive requiring extra reads. Data read dropped from 3.82 GiB to 42.6 MiB; the index itself is 119.7 MiB (1.5% of the table) and MATERIALIZE took about 20 seconds.
- What caveat must accompany the benchmark's ingest figure (~940K rows/s)?
- A) It was measured with three replicas
- B) It was measured via in-server INSERT…SELECT, so it does not directly measure external network/parsing ingestion throughput
- C) It wrote to memory only, never to disk
- D) Compression was disabled during the measurement
Show Answer
Answer: B) It was measured via in-server INSERT…SELECT, so it does not directly measure external network/parsing ingestion throughput
Explanation: The rows were generated inside the server and inserted directly, which is more favorable than an external client pushing TSV/Native data. Sorting, compression, and disk writes are all included, but data-generation CPU is included too, so the number is not a mathematical ceiling for external ingestion. Measure the intended client, batching, concurrency and format separately.
- Q4 (duration p50/p99 per namespace) scans all 100M rows yet finishes in about 1 second warm. Which property makes that possible?
- A) The result was precomputed in a materialized view
- B) Column-oriented storage — it reads only duration_ms (289 MiB) and namespace (0.5 MiB), not the table's full 7.8 GiB
- C) The quantile function reads only 8,192 disk rows and never processes the remaining rows
- D) Partition pruning limited it to one day of data
Show Answer
Answer: B) Column-oriented storage — it reads only duration_ms (289 MiB) and namespace (0.5 MiB), not the table's full 7.8 GiB
Explanation: A row-oriented database would have to read entire rows for this query; a columnar engine reads only the columns the aggregation needs. The scan covered the whole time range (so no partition pruning — D is wrong), and quantile processes all input rows while using a reservoir of at most 8,192 samples for an approximation. Sampling does not remove the input-column scan. Evaluate quantileExact separately when exact values are required.
- Under this guidebook's decision framework, when is running ClickHouse self-hosted on EKS (rather than a managed service) especially defensible?
- A) When there is no platform team and no database operations experience
- B) When a standard managed service suffices and there is a single tenant
- C) When managed offerings are limited or carry a large cost multiple, and a platform team can own operator-based operations including backup and restore rehearsals
- D) When the data is small — under 100 GiB
Show Answer
Answer: C) When managed offerings are limited or carry a large cost multiple, and a platform team can own operator-based operations including backup and restore rehearsals
Explanation: This is the managed-vs-self-hosted framework from the Database section overview, not from the benchmark article itself. ClickHouse is Apache-2.0 with a mature community operator (Altinity), which is what makes self-hosting a realistic option — the precondition is that operator plus a team to run it. Conversely, with scarce operations staffing or an adequate managed option, managed wins — and a team operating a raw StatefulSet must implement the same lifecycle and recovery responsibilities itself.