how 100 rows cost us 3.4 gb of ram in clickhouse

We run AgentEye, our one-stop observability platform for AI agents in production. It ingests agent telemetry — model calls, tool calls, hook invocations, and errors — into ClickHouse, and our Rust server serves it back to a Next.js dashboard.

For the first few weeks, nothing looked odd. Traffic was light, a handful of sessions at a time, and the dashboard was quick. The kind of quick that lets you not think about read paths at all.

Then we shipped.

More users, more agents, more sessions per user, more dashboard tabs left open on a second monitor "just to keep an eye on things." Ingest volume climbed, the table grew heavy, and one quiet read path — the one nobody had touched since the day it was written — turned a 4 GiB database pod into a crater. It didn't degrade gracefully. It hit a threshold and crashed.

This is the post-mortem: what the query was doing, why it stayed invisible until scale dragged it into the light, and the one-line mental model that fixed it.

Columnar databases

Row stores like Postgres lay data out row by row. Every column of a row sits contiguous on disk, so touching one field means reading the whole row whether you wanted the rest or not. That's fine when you're fetching single records constantly, like loading one user's profile. But when you're scanning millions of rows just to aggregate 3 columns out of 30, things start falling apart.

Columnar databases — ClickHouse, BigQuery, and Snowflake are all in this camp — flip that around. Each column gets stored and compressed on its own, separate from the rest of the row.

That gets you two things. First, I/O scales with the columns a query actually touches, not the row width. Project 4 narrow columns out of a 30-column table and only those 4 get read off disk. The other 26 don't matter, no matter how fat they are.

Second, compression gets far better. Values of the same type sitting next to each other — a column of timestamps, a column of event types — compress tighter than a row's worth of mixed types crammed together. That's part of why columnar engines are built for analytical workloads: crunch millions of rows fast, at the cost of being slower for one-off single-row lookups.

ClickHouse + MergeTree

ClickHouse is a columnar OLAP database, and MergeTree is the engine doing the heavy lifting underneath. The core idea: nothing ever gets edited in place. Every insert just creates a new part — basically a folder of column files sitting on disk. A background process slowly merges these smaller parts into bigger ones over time, which is where the name comes from.

Each part is sorted internally by whatever the table's ORDER BY key is. And that sort key does double duty: it also powers the index. Instead of indexing every single row (which would be expensive), ClickHouse only indexes every Nth row — by default every 8192nd row. Each chunk of rows between those index points is called a granule.

Here's why that matters. If your query filters or sorts using that same sort key, ClickHouse can look at the index and skip entire granules it knows won't match, without ever reading them off disk. That's called granule pruning, and it's the whole reason ClickHouse is fast. But if your query sorts or filters on something that doesn't match the sort key — like running ORDER BY ts DESC on a table that's actually sorted by (session_id, ts, dedup_key) — there's nothing to skip anymore. ClickHouse has to read everything. Full scan.

One more piece: ReplacingMergeTree. This variant is built for tables where the same row might get inserted twice (updates, retries, whatever), and it's supposed to keep only the latest version. But it doesn't clean up duplicates immediately — it waits for a background merge to do that. If you can't wait and need duplicates gone right now, you add FINAL to your query, which forces ClickHouse to deduplicate on the spot instead of relying on a merge.

The symptom: a read-light dashboard was OOM-killing ClickHouse

The two endpoints that fell over sit behind the most trivial views in the product: an events stream and an errors list. Both render single-line rows — a timestamp, an event type, a short label, an error flag. Payload-light on the wire and, on paper, a rounding error against our query budget.

At low cardinality, this held up. The table was small, poll concurrency was near zero, and the query returned inside single-digit milliseconds, so it cleared review and ran untouched in production for months.

As ingest volume grew, the same query began driving ClickHouse's resident memory toward the pod's 4 GiB ceiling until the kernel's OOM killer reaped the process, taking both endpoints down with it. The scheduler restarted it, the dashboard's pollers reconnected, and it fell over again.

The signature is unmistakable: query cost scaled with table size, not with the number of rows returned. A LIMIT 100 whose cost climbs as the table grows clearly isn't reading just 100 rows off disk — it's touching far more underneath. The bug was there from day one; rising volume simply pushed it past the point where it became visible.

The query that ate production

Both endpoints resolved to the same handler, GET /events, which issued roughly this:

SELECT id, session_id, agent_id, event_type, ts, payload, environment
FROM agenteye.events FINAL
ORDER BY ts DESC
LIMIT 100

It reads as harmless. LIMIT 100, no joins, no aggregation. But two tokens in that statement carry the entire cost on our schema: payload in the projection, and FINAL on the table.

The events table is a ReplacingMergeTree, partitioned by month, with a sort key of (session_id, ts, dedup_key):

CREATE TABLE agenteye.events (
  id UInt64, org_id UUID, session_id String, agent_id String,
  event_type String, ts DateTime64(6), /* … promoted columns … */
  payload String,          -- raw event JSON: base64 frames, prompts, tool output
  dedup_key FixedString(64)
) ENGINE = ReplacingMergeTree(ingested_at)
ORDER BY (session_id, ts, dedup_key);

Two properties of that DDL turn the query pathological:

• The projection selects payload, a wide String column. In a columnar engine, cost is driven by the columns you touch, not the rows you return. payload holds the raw event JSON, which for an agent tracer means base64-encoded frames, long prompts, and full tool outputs. It is the largest column in the table by orders of magnitude — and the one column the list view never renders.
• ORDER BY ts DESC doesn't align with the sort key. The primary key is prefixed on session_id, so a global ts DESC sort can't be answered by a sorted-range read. Combined with no WHERE predicate, there's no primary-key prefix to prune parts or granules against, so the engine has nothing to skip.

So LIMIT 100 bounds the output, but nothing bounds the input. The query decompresses the fattest column in the table across every part, then trims to 100 rows.

Why one ClickHouse FINAL query needed 3.4 GB

Two multipliers compound.

1. payload is 99.9% of the table

To reproduce under load, we built a stress corpus: 500 heavy agent sessions (24,394 events, ~7 GB of raw payloads) layered onto existing dev data, for 74,180 events at 6.53 GiB compressed. The payload column alone accounted for 6.50 GiB of that. Every other column the list actually needs — ts, id, event_type, and the label — summed to roughly 2 MiB.

A columnar engine like ClickHouse is designed to read only the columns a query references. By projecting payload, we defeated that property on the one column the view never renders. (We're not alone in over-collecting: a 2025 Sawmills report found 84% of companies use under a quarter of the telemetry they ingest. We were decompressing the other three-quarters on every poll.)

2. FINAL reconciles at query time

ReplacingMergeTree only collapses duplicate keys during background merges, "at an unknown time." FINAL forces that reconciliation at query time, reading across every overlapping part rather than the rows the LIMIT implies. The docs are explicit about the cost: it requires extra compute and memory because merge-time processing must happen in memory. Measured, our LIMIT 100 read 92,512 rows, decompressing payload across the full part set to hand back 100.

The cost of a single poll

One request for "the newest 100 events" resolved to:

The production pod is capped at 4 GiB. A single query consumed 85% of the entire memory budget, and ClickHouse's per-query max_memory_usage throws rather than spills to disk, so there's no graceful degradation path once a query breaches the ceiling. One concurrent poll of the same endpoint is enough to exhaust what remains.

The death spiral: where scale turns a slow query into an outage

This is where a slow query becomes an outage, and it's pure concurrency arithmetic. Both dashboard pages polled GET /events every 5 seconds, and every open browser tab is an independent poller. The offered load was never one 3.4 GiB query. It was N of them in flight, where N scales with open tabs, and each one reaches for 3.4 GiB against a 4 GiB pod.

We reproduced it with 3 concurrent requests, run twice (6 total) against the old endpoint. The failure cascaded:

• Each query held ~3.4 GiB resident, and under memory contention, latency degraded from ~6 s to 17–20 s.
• The server's ClickHouse client hit its ~15 s socket timeout and dropped the connection.
• ClickHouse, still streaming a result set into a now-dead socket, aborted with Code 210: Broken pipe.
• Net result: 0 of 6 requests succeeded. p50 latency 15,097 ms. ClickHouse peak memory 12.8 GB.

That last figure is the whole failure mode. Our dev box has a ~14 GiB server-wide memory limit that barely absorbed the spike. The 4 GiB production pod has no such headroom: it breaches its cgroup limit and the OOM killer reaps the process outright.

And the failure is self-amplifying. When the pod dies, the dashboard's auto-retry fires again in 5 seconds, so the freshly-scheduled pod comes back up straight into the same request storm and re-OOMs before it can drain the backlog. The retry cadence meant to add resilience instead guarantees the outage sustains itself: a slow query promoted to a self-reinforcing crash loop.

The fix: stop reading what you don't render

The fix is almost tautological: the list view never displays payload, so the read path should never project it. We added a purpose-built endpoint, GET /events/summary, whose projection is scoped to exactly the columns the table renders.

The catch: two of those rendered fields — a one-line summary label (model name or tool invoked) and an error flag — were historically derived from payload. Computing them at read time means parsing JSON per row, which drags the wide column back into the projection. So we moved that work to write time.

Two columns, computed once at ingest and materialized:

ALTER TABLE agenteye.events ADD COLUMN summary  String DEFAULT '';
ALTER TABLE agenteye.events ADD COLUMN is_error UInt8  DEFAULT 0;

On ingest, the Rust server computes the display label and error flag (a faithful port of the logic the dashboard previously ran client-side) and writes them inline. Reads become a trivial column fetch: no JSON parse, no payload, no per-row branching. This is the general lever: when reads dominate writes, pay the cost once on write instead of on every read. Each event is ingested once; the list backing it is polled thousands of times, and that asymmetry only widens with scale.

Rows written before the columns existed carry the DEFAULT, so we skipped a 50k+ row backfill and resolved them at read time against already-promoted columns, still without touching payload:

SELECT
  id, session_id, agent_id, event_type, ts, environment,
  error_type, output_tokens,
  if(summary != '', summary,
     coalesce(
       multiIf(
         event_type IN ('tool_use','tool_result'),         tool_name,
         event_type IN ('model_request','model_response'),  model,
         event_type IN ('hook_triggered','hook_completed'), hook_name,
         event_type = 'error',                              error_type,
         NULL),
       event_type)) AS summary,
  if(is_error = 1, 1,
     toUInt8(event_type = 'error' OR error_type IS NOT NULL)) AS is_error
FROM agenteye.events FINAL
WHERE org_id = {org_id}
ORDER BY ts DESC, id DESC
LIMIT 100

Note we kept FINAL. It was never the bottleneck. Once the projection drops the 6.5 GiB column, FINAL reconciles a handful of narrow columns and its cost collapses into the noise. The villain was always payload decompressed through FINAL, not FINAL itself. We also relaxed the poll interval from 5 s to 10 s, halving steady-state read pressure for free.

The results: from falling over at 3 to cruising at 100

Same 100 rows, same screen, same corpus. Only the projection changed:

Two to three orders of magnitude on every axis, and not one byte of new hardware. We didn't scale the pod, tune a memory setting, or shard the table. We stopped asking the database to decompress a photo album to render a list of timestamps.

But the single-query numbers were never the point. The endpoint failed under concurrency, so that's where the fix had to prove itself:

The old path collapsed at 3 in-flight requests. The new one sustained 100 at a p50 of 359 ms while holding ClickHouse to 1.5 GB, comfortably inside the 4 GiB pod. That's a 33× jump in concurrency at roughly an eighth of the peak memory, on identical infrastructure.

Which is the difference between a dashboard that survives a demo and one that survives production. AgentEye now ingests millions of agent events per day, fanning out across thousands of concurrent sessions with every dashboard tab polling in parallel, and the read path that used to OOM-kill the pod at 3 requests doesn't register on the memory graph.

The same query pattern that was a crash loop at low volume is now a rounding error at high volume.

What we'd tell past us

If we could leave a note on this endpoint the day it was written, it'd be short.

• Read only what you render. A hot path should touch the smallest thing that answers the question. SELECT * on a table with one giant column is a memory bomb with the pin already pulled, and scale is just the hand that lets go.
• "Fine at low volume" isn't fine. The query that returns in a blink on a small table is the same query that OOM-kills the pod on a big one. Low traffic hides costs that scale with your data. It never removes them.
• Push work to write time when reads dominate. Compute-on-ingest is paid once. Compute-on-read is a bill that arrives on every poll, and it grows with your success.
• FINAL isn't the villain. It re-scans overlapping parts to dedupe in memory, but narrow the columns underneath it and the cost quietly disappears.
• Bound the blast radius. The full-payload view still exists. It's just scoped to a single session now, not an unbounded firehose over the whole table.

None of this was exotic. It was a wide column, a hot read path, and a scale threshold we hadn't crossed yet. The fix shipped no new hardware and read almost embarrassingly simple in the diff. That's usually how these go: the outage feels enormous while you're in it, and the lesson underneath turns out to be something you already knew, just hadn't been forced to take seriously yet. Now we have.

FAQ

Why is SELECT … FINAL slow in ClickHouse?

FINAL forces a ReplacingMergeTree (and similar engines) to deduplicate at query time instead of waiting for a background merge. Per the docs, it requires extra compute and memory because the merge-time processing has to happen in memory. It also reads across overlapping data parts, so it can scan far more rows than your LIMIT implies. The cost scales with the size of the columns it must decompress, which is why projecting a wide column through FINAL is where it hurts most.

Did you remove FINAL to fix the memory blow-up?

No. We kept FINAL and instead stopped projecting the wide payload column. Once the query reads only narrow columns, FINAL reconciles them cheaply. The problem was never FINAL on its own — it was pulling a 6.5 GiB column through it on every poll.

How do you show a summary and error flag without reading the payload?

We precompute them at ingest into dedicated summary (String) and is_error (UInt8) columns, so reads never parse JSON. Rows written before those columns existed fall back to already-promoted columns (model, tool_name, error_type) at read time, still without touching payload, so no backfill was required.