Expanding compact logs in ClickHouse with plain SQL

Log lines are repetitive: the wording comes from a format string in code, and only the values change from line to line. 10x compacts logs at the edge: each line ships as a short hash plus its values, and the wording is stored once, a measured 63.7% volume reduction on the public OTel-demo sample, lossless. This post is about the read side in ClickHouse: a plain SELECT returns the original lines, byte for byte, through one Apache 2.0 SQL file, ClickHouse Cloud included. Measuring what that read-back costs surfaced three ClickHouse lessons worth knowing on their own.

A log line is printed by a format string in code, and only the values change from line to line. 10x compacts each line at the edge: the engine swaps the line, before it ships, for its format string's hash plus the values. Here is a fluentd line as the forwarder reads it, and the compact event that ships in its place:

2025-10-02 00:17:22 +0000 [info]: #0 starting fluentd worker pid=18 ppid=6 worker=0
~R}>PZj;Jdp,1759364242000,0000,fluentd,18,6,0

The leading ~ marks the row as compact. The segment up to the first comma, R}>PZj;Jdp, is the engine's internal template hash, the join key into the template dictionary (the queryable pattern ID, tenx_hash, is a separate field), and the rest are the line's values, in the order their slots appear in the template. The fixed words of the message are not in the row at all; they are stored once, in the template, keyed by that hash.

That compact row is what lands in ClickHouse, at 63.7% less volume measured on the public 215 MB OTel-demo sample counting the template dictionary, because the read side rebuilds the original line. Round-tripping that sample returned all 197,430 lines byte-identical; the boundary sits at the file read, where bytes that are not valid UTF-8 are substituted before the engine tokenizes, the same substitution any UTF-8 log reader makes. This post is about that read side: how a plain SELECT against a view returns the original lines, what a full-table expansion costs, and three pieces of ClickHouse behaviour that surfaced while measuring it.

The whole integration is one SQL file, Apache 2.0, at github.com/log-10x/clickhouse-app: a dictionary serving parsed templates, two views, and six lambda functions. ClickHouse Cloud blocks executable UDFs, so the integration uses none, and the same file installs with one client command on self-hosted ClickHouse, Altinity Cloud, and ClickHouse Cloud alike. The engine that produces compact events is the paid product and is not in that repo.

How a compact row becomes a log line

A dictionary serves the parsed templates, pre-split into the literal text and the slots between it. A view joins each event's hash to its template, and lambda functions splice the row's values back into the slots. An ordinary SELECT decoded_log against the view returns the original text, with no query rewriter in the loop.

Expanding the whole table is not free. On a 137,418-row test table, at ClickHouse default settings, the full-table expansion takes 548 ms with a 41 MiB peak, so the guidance is to filter first on the plainly stored columns, container, templateHash, or the raw encoded_log text, and expand only the rows that survive. One thing a filter on encoded_log cannot do is match the template's fixed words, which are not in the row; a search over message wording runs against the expanded column.

A lambda that captures an array copies it once per mapped element

A natural way to write the expansion core indexes into arrays from inside a lambda, and in ClickHouse that construction is quadratic. That version is in the harness as install-buggy.sql:

CREATE OR REPLACE FUNCTION tenx_inflate_core AS (literals, slots, values) ->
    concat(
      arrayStringConcat(
        arrayMap(i ->
          concat(literals[i],
            if(i <= length(slots),
               tenx_substitute_slot(if(i <= length(values), values[i], ''), slots[i]),
               '')),
          range(1, length(literals))),
        ''),
      literals[length(literals)]);

The lambda maps over range(1, length(literals)) and reaches literals, slots, and values by subscript. A ClickHouse lambda that references an outer column captures that column, and ClickHouse evaluates the lambda by replicating every captured column once per element of the mapped array. A row whose template has N literals maps over about N indices while carrying three captured arrays of about N elements each, so expanding that one row materializes N copies of its own N-element arrays.

The test table makes the quadratic term concrete. Expanding all 137,418 rows involves 800,397 array elements of necessary work; with the capture in place, ClickHouse materialized 189,949,597, and one template with 818 literals accounted for 97% of that total. At default settings the full-table expansion did not complete at all: it aborted out of memory at a 7.2 GiB peak.

Passing the arrays as arrayMap arguments removes the quadratic term

arrayMap accepts multiple array arguments and walks them element-wise, so arrays passed as arguments are read in place rather than replicated. The arguments must have equal lengths, which arrayResize provides. The change is one line per function:

CREATE OR REPLACE FUNCTION tenx_inflate_core AS (literals, slots, values) ->
    concat(
      arrayStringConcat(
        arrayMap((lit, slot, value) ->
            concat(lit, tenx_substitute_slot(value, slot)),
          arrayResize(literals, length(literals) - 1),
          arrayResize(slots,    length(literals) - 1, ''),
          arrayResize(values,   length(literals) - 1, '')),
        ''),
      literals[length(literals)]);

With the arrays passed as arguments, the same full-table expansion completes in 548 ms with a 41 MiB peak, at default settings, and the output is byte-identical: both versions expand all 137,418 rows to the same SHA-256 over the same 29,065,810 bytes. Timings move with the machine; the collapse from a 7.2 GiB abort to a 41 MiB peak does not.

The template that dominates the quadratic term is not pathological data. Its 818 literals come from a 21 KB multi-line container startup dump the engine folds into a single event. Those events are 0.2% of rows and 20.5% of expanded bytes; once the per-row cost is linear, they cost what their content costs.

count() will not benchmark a view

A count() never reads the expanded column, so the optimizer removes the expansion from the plan and answers from part-level metadata. On this table the pruned count reads 1 row and 24 bytes, and its plan contains no expansion node at all; the resulting timing measures scan bookkeeping, not the view.

-- A count against the view is answered from part metadata.
SELECT count() FROM tenx.events;
-- EXPLAIN: no formatDateTimeInJodaSyntax, no arrayMap, no dictGetOrDefault
-- query_log: read_rows = 1, read_bytes = 24

-- A benchmark of the view has to force the expansion and read its output:
SELECT sum(length(decoded_log)) FROM tenx.events;
-- EXPLAIN: the expansion functions appear in the plan
-- query_log: read_rows = 137,418; 548 ms, 41 MiB peak

A benchmark of any view with an expensive derived column has to read that column, and EXPLAIN says whether it did.

Replacing a SQL function does not update the views built on it

ClickHouse inlines a SQL UDF's body into a view's stored AST at CREATE VIEW time. CREATE OR REPLACE FUNCTION changes what new queries and new views compile, but an existing view keeps the body it inlined when it was created. Changing the behaviour of a function a view depends on takes dropping and recreating the view; replacing the function alone leaves the view running its original copy.

Preserving the original timestamp format costs tens of milliseconds

The default view renders every timestamp in one constant ISO 8601 format, which keeps formatDateTimeInJodaSyntax on its vectorized path. A second view, tenx.events_native, preserves each template's original timestamp format by dispatching over the 17 observed formats with multiIf, one constant call per branch. On the same test table the default view expands it in 548 ms with a 41 MiB peak and the format-preserving view in 600 ms with 78 MiB; install.sql's own header records medians of 571 and 658 ms over seven runs. The peaks are the figures that repeat between runs, the gap stays in the tens of milliseconds either way, and the view to pick is the one downstream consumers need.

Run it yourself

The container, the test data, and the queries are published at github.com/log-10x/benchmarks, under clickhouse-inflate. One command pins ClickHouse 25.8.28.1 in an 8 GiB container, loads the 137,418 rows, installs the quadratic version, and reproduces the figures above: the pruned count reading 1 row and 24 bytes, the quadratic expansion aborting at a 7.2 GiB peak, the same expansion finishing in 548 ms and 41 MiB once the arrays are passed as arguments, and the two outputs hashing identical. The quadratic term is printed from the test data itself: 800,397 array elements of necessary work against 189,949,597 materialized. ClickHouse caps itself at 0.9 of the RAM it can see, so the abort threshold depends on the machine; the pinned 8 GiB container is what makes the 7.2 GiB peak reproducible.