ClickHouse at petabyte scale.

Every SOC I've worked in eventually arrives at the same sentence: "we need to collect everything," meaning network logs, endpoint telemetry, cloud audit trails, application events, and a fresh source every quarter. The sentence is cheap to say, but what rarely gets costed out in the meeting is the day "everything" turns into 5 petabytes a day.

That figure is not a stress-test hypothetical. It is Netflix's steady state: 10.6 million events per second on average, peaking at 12.5 million, across more than 40,000 microservices serving 300 million subscribers. A security estate is smaller, but the engineering problem at the top of the curve is the same one, and Netflix has already published its answers.

I have felt the other end of this curve, the small end where the SIEM can't answer. During one incident an analyst came to me certain something was wrong and unable to prove it. The evidence was there; the SIEM infrastructure just couldn't run the queries the investigation needed across the duration and depth of data involved. It took over a month, most of it raw data-wrangling, and the fix was to pull the relevant data into custom key-value stores inside the SIEM so the long-window, deep queries would actually finish. The analyst's instinct was right the whole time; what stood between them and proving it was an engine that couldn't interrogate its own data at the depth the investigation demanded. That is the performant pillar stated as a month of a person's life, and it is the thing a columnar engine over lakehouse storage exists to prevent.

Huntress, running 3 million endpoints, moved off Elastic to ClickHouse and cut its cost 93%, and a number that large isn't a tuning win so much as the signature of an architectural mismatch, which the three subsections below break into the three places the mismatch lives.

Document-oriented storage overhead

Elasticsearch stores data as JSON documents with inverted indices for full-text search, and because every field is indexed by default the storage overhead climbs fast, so a 500-byte security event becomes 2–5 KB stored, a 4–10× overhead.

Shard management overhead

As data grows you need thousands of shards, and each shard is a Lucene index carrying overhead for segment files, merge operations, refresh cycles, and 2–3 GB heap memory minimum. At TB/day scale 1,000+ shards per cluster is common, which means 2–3 TB RAM minimum, and shard rebalancing during failures drags the cluster toward instability. Huntress saw query latency degrade from 3s to 30s as shard count grew, plus 10–30 second JVM garbage collection pauses.

Heap pressure at scale

Elasticsearch is Java-based, so it leans on JVM heap memory for the field-data cache (aggregations on high-cardinality fields), request caches, and shard metadata, and aggregations on IP addresses, user IDs, and file paths build field-data structures that get large quickly. The practical max heap is 31–32 GB per node, because beyond that compressed pointers are disabled, and GC pauses grow with heap size.

When to migrate

None of this is a reason to rip Elasticsearch out by reflex. It stays the right tool under roughly 1 TB/day, when full-text search across unstructured text is genuinely the workload, when the team already carries deep Elastic expertise, and when ECS plus the Elastic Security rule content is the detection backbone you'd otherwise have to rebuild.

The case to migrate sharpens as those conditions invert. Above 1 TB/day, with structured or semi-structured logs (JSON, Syslog, CEF) dominating, where aggregation queries matter more than full-text relevance, and where the symptoms above are already showing (query latency creeping up, shard management eating an engineer's week), the math moves. I'd put the threshold concretely: once Elastic infrastructure clears $15–30K/month, the ClickHouse footprint that carries the same ingest runs about 2–4× cheaper on the cost table later in this piece (the Huntress estate landed at 14×, but that is the high end, not the median you should plan around).

Security data decays in value on a curve you can almost set your watch by. The first week is live: active investigations that need sub-second response. Days 7 to 30 are incident follow-up, queried often but not constantly. The 30-to-90 window is trend analysis and compliance reporting. Past 90 days it is forensics and legal holds, the data you must keep and will rarely touch.

Most teams flatten that curve into a single tier, either an expensive SIEM holding cold data at hot prices or a slow data lake serving hot queries at cold latency. The temperature-tier pattern keeps hot data in ClickHouse and ages cold data into Iceberg behind one read contract, so the analyst sees a single table and the storage bill follows the access pattern instead of the volume. Holding all 5 PB in ClickHouse for a year would be untenable on cost alone; moving data to Iceberg after 30 days drops the per-byte storage cost roughly an order of magnitude (Netflix's own framing is "do the least amount of work") while the cold tier stays queryable through the same engine.

The architecture pattern:

ClickHouse isn't Netflix's monolithic database. It's a specialized query engine layered on top of Iceberg's lakehouse foundation. Iceberg provides storage durability, ACID guarantees, and schema evolution. ClickHouse provides sub-second query performance for specific workload patterns (scheduled reports, high-throughput aggregations).

Here is the comparison at a scale a large enterprise SOC actually hits: 5 TB/day of ingest, 1.825 PB stored over a year. The ranges are wide because pricing is, and I've kept the assumptions visible below the table rather than buried.

Assumptions: schema-on-read SIEM pricing from community reports ($3–6/GB/day enterprise). Elastic from public calculator (hot tier 30 days + warm 335 days). ClickHouse self-hosted: 10–15× c5.4xlarge instances + S3 + 2–3 FTE ops. ClickHouse Cloud: vendor public estimates.

Read off the table, ClickHouse comes in 5–27× under a schema-on-read SIEM and 2–4× under Elastic at 5 TB/day, with the spread driven by self-hosted versus managed and by where each vendor's tiering lands. The gap is roughly linear in volume, so at 50 TB/day the same architecture is a $2–5M-a-year line item rather than a rounding error. I'd treat the low end of each range as the planning number; the high end assumes self-hosting and the 2–3 FTE of operations that comes with it.

A ClickHouse materialized view trades storage for query time on a pattern you run repeatedly. The acceleration is real, but I want to put a measured number on it rather than wave at a range. The worked example below is a concrete one: an auth-failure aggregation that scans 50 GB and 50M raw events in 3–10 seconds collapses to a 500 MB pre-aggregated scan in 50–200 ms, a 20–100× speedup for a storage overhead of about 1%. The economics only work when the pattern actually repeats, so that repetition is the thing you're really deciding on.

-- Base table (raw security events)
CREATE TABLE security_events (
    timestamp DateTime,
    source_ip String,
    user_id String,
    event_type String,
    payload String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, source_ip);

-- Materialized view (pre-aggregated authentication failures)
CREATE MATERIALIZED VIEW auth_failures_hourly
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp_hour, source_ip, user_id)
AS SELECT
    toStartOfHour(timestamp) as timestamp_hour,
    source_ip,
    user_id,
    countIf(event_type = 'auth_failure') as failure_count,
    countIf(event_type = 'auth_success') as success_count
FROM security_events
GROUP BY timestamp_hour, source_ip, user_id;

That SummingMergeTree definition is the whole mechanism: it pre-rolls the hourly failure and success counts as events land, so the dashboard reads the 500 MB view instead of re-scanning the 50 GB base table. The 3–10 seconds becomes 50–200 ms for the 1% storage cost noted above. The judgment call is which patterns deserve a view, and that splits cleanly.

Materialize when

• Repeated dashboard queries (SOC dashboards refreshing every 5 minutes = 288 times/day).
• High-frequency aggregations (queries scanning billions of events repeatedly).
• Known query patterns (MITRE ATT&CK dashboards: 30+ queries on same raw data).

Don't materialize when

• Exploratory threat hunting (unknown patterns, ad-hoc queries).
• Rare queries (compliance reports run monthly).
• Low-volume data (MV overhead exceeds benefit).

Worked economics for one auth-failure dashboard at 288 refreshes/day run $86.40/day ($2,592/month) without the MV and $2.94/day ($88/month) with it, which is a 97% reduction on a single dashboard, and across 20–30 production dashboards that comes to about $600K/year in savings from materialized views for known query patterns.

Netflix's likely approach: materialized views for high-frequency dashboards (auth failures, service health, error rates) plus raw queries for exploratory analysis (fingerprinting new log types, ad-hoc threat hunting).

Moving security logs off a SIEM into a columnar engine forces one structural choice. Flatten the schema and you lose the hierarchical semantics (I argue against doing this by default in the flattening anti-pattern essay); preserve the structure and you pay for it in query complexity. Hydrolix is built around the second answer (log data, high cardinality, nested by design), so it's the right foil for ClickHouse here. Four differences decide most cases.

Architectural differences

The first is how the two handle the read/write contention that bites during an incident. Hydrolix is Kubernetes-native and stateless, with ingest, query, and merge scaling as separate pools. ClickHouse shares resources across reads and writes, so a heavy MergeTree merge can starve the very queries an analyst is running. When logs are flooding in mid-incident, exactly when you're querying hardest, Hydrolix holds query latency steady where ClickHouse can wobble.

The second is cardinality. Hydrolix is designed for "hundreds of thousands of columns" with full indexing as the default posture, while ClickHouse reaches comparable ground only with deliberate codec selection per high-cardinality field. Security logs are about as high-cardinality as data gets (IPs, usernames, file paths, hashes), so Hydrolix arrives tuned for the workload and ClickHouse asks you to tune it.

The third is nesting. Hydrolix stores arrays of maps in catch-all fields and queries into them with JSON Pointer syntax, leaving flattening optional. ClickHouse has Nested types, but the query gets more involved as the structure deepens. CloudTrail and Azure AD records, which nest several levels, read naturally in Hydrolix without an explicit flattening step.

The fourth runs the other way and favors ClickHouse. Its JOIN support is full SQL, dictionary and distributed joins included. Hydrolix offers native joins too, but timestamp-based and fussy about aliases and exact type matching. For multi-table correlation, the bread and butter of detection engineering, ClickHouse is the more flexible engine, though the lab's scored join bench (Tier B, single host, 10M–60M-row tables) adds a caveat worth carrying: the run's only DNF was ClickHouse's own native MergeTree table on one of the TPC-H-derived joins, over 300 seconds on both attempts, while the same engine over Iceberg answered the same query in 1.35 seconds. The mechanism has since been instrumented (EXPLAIN diagnostic, 2026-06-10): with no column statistics on the native table, the engine's greedy join reordering chose a plan with a measured 1.2-billion-row intermediate, and with the reordering disabled the same query answered in 5.3 seconds. The follow-up closed it: materializing statistics on the six tables (one ALTER TABLE command, ~22 s) flips the plan and the same query answers in 0.675 seconds on default settings — a reminder that full join support and predictable join planning are different properties, and that the gap between them can be one maintenance command nobody told you to run.

When to choose which

ClickHouse is the call when the engine has to serve more than security (observability, security, and business analytics on one platform), when complex SQL joins are part of the work, when you can live with schema optimization as a tax, and when Iceberg and lakehouse integration plus a mature ecosystem matter. Hydrolix earns the slot in the narrower world where the workload is 100% logs, cardinality is extreme (millions of unique IPs, usernames, or hashes a day), deeply nested JSON has to survive without flattening, query performance can't dip during ingestion spikes, and cost-effective long-term retention is the point of the exercise. The two lists barely overlap, and that's what makes the choice tractable, because you're matching the engine to the shape of the workload rather than running a head-to-head.

One asymmetry decides where I'd land by default. ClickHouse has Netflix at 5 PB/day and Huntress at 3 million endpoints as Tier A production validation. Hydrolix's architectural claims are reasonable and I haven't found them contradicted, but I also haven't found a public petabyte-scale case study to put behind them, so I hold them at Tier C. Until that evidence exists, default to ClickHouse for production decisions and reserve Hydrolix for the genuinely log-only, nested-structure-heavy case it was built for.

Netflix didn't publish its logging optimizations to be generous. It published them because they validate a pattern the observability and security worlds are both converging on from opposite directions: a columnar engine over lakehouse storage now does the job a schema-on-read SIEM used to, at petabyte scale, for a fraction of the bill. Four things from this piece are worth carrying out of it.

1. The performance and cost gap is large but workload-dependent, so anchor it to a measurement rather than a banner. The cleanest number in this essay is Huntress's: 93% off the bill, terabytes compressing 20–50×, sub-second queries where Elastic took 30 seconds. The cost table puts that at roughly 2–4× under Elastic and up to 27× under a schema-on-read SIEM at 5 TB/day, though your own multiplier depends on how column-narrow your queries are.
2. Optimize at the three points that actually bind. Parsing: generated lexers beat regex (216 µs → 23 µs). Write path: a native protocol beats JDBC at high event rates. Query path: data layout beats clever query rewriting (tag sharding, 3 s → 700 ms).
3. Tiering stops being optional once cold data is the majority of your volume. Keep hot data fast, age cold data to Iceberg, and put one read contract over both so the analyst never sees the seam.
4. Scale changes which decisions are premature. Choices you can defer at 1 TB/day are mandatory at 100 TB/day, and Netflix's organizing principle, "do the least amount of work" per query, is the one that survives the jump.

The part I keep coming back to is how little of this is novel research. Designing a security data platform at this scale today is mostly reading what Netflix already wrote down, confirming Huntress paid the bills it claims to have paid, and copying the parts that fit your workload.