Technology deep-dive

ClickHouse at petabyte scale.

Netflix ingests 5 PB/day at 10.6 million events per second. Their ClickHouse optimization journey — fingerprinting, native protocol, tag sharding — is a blueprint for petabyte-scale security analytics. Huntress applied it and cut Elastic costs by 93%.

Reading time: 30 minutes. Evidence tier: A (Netflix production case study with measured optimization results; Huntress migration data). Hydrolix comparison section is Tier C (vendor documentation, architectural analysis).

The scale problem

5 PB/day. 10.6 million events per second. 40,000 microservices.

If you run a Security Operations Center, you've probably heard someone say "we need to collect everything." Network logs, endpoint telemetry, cloud audit trails, application events. The list grows every quarter. What they don't mention is what happens when "everything" becomes 5 petabytes per day.

That's not a hypothetical. It's what Netflix ingests, averaging 10.6 million events per second (peaking at 12.5 million), across 40,000+ microservices supporting 300 million subscribers.

Why ClickHouse

Why columnar wins at security analytics.

When a security analyst investigates an incident, they ask questions like "show me all authentication failures from this IP in the last hour" or "which users accessed this sensitive resource in the past week?" or "aggregate event counts by source, grouped by hour, for the last 30 days." These are analytical queries over large datasets that scan millions of rows but touch only a few columns.

Row-oriented databases (PostgreSQL, MySQL) read entire rows from disk even if you only need 2 of 50 columns. At petabyte scale, that's catastrophically inefficient.

Columnar databases (ClickHouse, DuckDB, Apache Druid) store data by column. Need only timestamp and source_ip? Read only those columns. Compression ratios are 10–100× better. Queries are 5–100× faster.

Most security teams are stuck between three options: a traditional SIEM (great analytics, terrible at scale — processing 1 TB/day costs $1.5M+ annually with Splunk), Elasticsearch (works under 1 TB/day, architectural overhead spirals beyond), or a custom data lake (powerful, expensive to operate). The modern data stack — columnar databases plus lakehouse storage — replaces all three for petabyte-scale security data.

The Elastic problem

Why Elasticsearch struggles past 1 TB/day.

Huntress (3 million endpoints) migrated from Elastic to ClickHouse and achieved a 93% cost reduction. That's not a marginal improvement — it's a fundamental architectural mismatch.

Document-oriented storage overhead

Elasticsearch stores data as JSON documents with inverted indices for full-text search. Every field is indexed by default, creating massive storage overhead. A 500-byte security event becomes 2–5 KB stored — 4–10× overhead.

Shard management overhead

As data grows you need thousands of shards. Each shard is a Lucene index with overhead for segment files, merge operations, refresh cycles, and 2–3 GB heap memory minimum. 1,000+ shards per cluster is common at TB/day scale, meaning 2–3 TB RAM minimum. Shard rebalancing during failures causes cluster instability. Huntress experienced query degradation from 3s to 30s as shard count grew, plus 10–30 second JVM garbage collection pauses.

Heap pressure at scale

Elasticsearch is Java-based, using JVM heap memory for field-data cache (aggregations on high-cardinality fields), request caches, shard metadata. Aggregations on IP addresses, user IDs, file paths create massive field-data structures. Practical max heap is 31–32 GB per node (beyond this, compressed pointers are disabled). GC pauses increase with heap size.

When to migrate

Keep Elasticsearch if: under 1 TB/day ingestion, full-text search is critical, team has deep Elastic expertise, ECS plus Elastic Security rules are core workflow.

Migrate to ClickHouse if: over 1 TB/day, structured / semi-structured logs (JSON, Syslog, CEF) dominate, aggregation queries beat full-text search, query performance is degrading, shard management is consuming ops time, cost exceeds $15–30K/month for Elastic infrastructure. ClickHouse is likely 5–10× cheaper.

Netflix's three optimizations

Three bottlenecks, three fixes.

Daniel Muino, a Netflix engineer, presented their ClickHouse optimization journey at a ClickHouse meetup in late 2024. Three bottlenecks stood between them and petabyte-scale performance. Here's how they fixed each one.

1. Fingerprinting: 216 µs → 23 µs

Log fingerprinting (categorizing log types for routing and analysis) was too slow. Initial attempts used regex and machine learning models. Neither scaled. The fix: generate lexers using JFlex (a Java tool that produces optimized tokenizers). Instead of pattern matching at runtime, precompile parsing logic into efficient state machines. Result: 8–10× throughput, fingerprinting time from 216 µs to 23 µs. The lesson for security: generated parsers beat regex for EDR / Sysmon / CloudTrail normalization.

2. Serialization: JDBC → native protocol

Initial implementation used JDBC batch inserts. JDBC adds overhead — connection pooling, SQL parsing, protocol translation. At 10M+ events/sec, overhead compounds. Netflix switched to RowBinary first (better), then reverse-engineered the ClickHouse Go client and built a custom native protocol encoder generating LZ4-compressed blocks directly. The lesson for security: generic APIs (HTTP, JDBC) become bottlenecks at high event rates. Native protocols or Kafka connectors are required.

3. Query performance: 3 s → 700 ms via tag sharding

Netflix stores tags (key-value metadata like region=us-west-2, service=auth-api) as maps. Queries filtering by tags required linear scans through map structures. At petabyte scale, a single query could take 3+ seconds.

Fix: shard tag maps into 31 smaller maps (one per tag-key namespace). Instead of scanning one massive map, query only the relevant shard.

Result: filtering query 3 s → 1.3 s (2.3× faster). Filter plus projection: 3 s → 700 ms (4.3× faster). Data scanned reduced 5–8×. The lesson for security: security data is rich with metadata (source IPs, ports, usernames, hashes). Storing as JSON blobs or maps is intuitive but slow. Partition metadata by access pattern. Don't store everything in one giant map.

Hot + cold tiering

Match storage cost to query frequency.

Security data has a decay curve. 0–7 days: active investigations, sub-second response. 7–30 days: recent incident follow-up, moderate frequency. 30–90 days: trend analysis, compliance reporting, lower frequency. 90+ days: forensics, legal holds, rare queries.

Most security teams store everything in one tier (expensive SIEM, or slow data lake). Netflix's approach optimizes for access patterns, not storage uniformity. Storing 5 PB in ClickHouse for one year would be prohibitively expensive. Tiering to Iceberg after 30 days cuts storage costs 10–50× while maintaining query capability.

The architecture pattern:

Raw Data → Apache Iceberg Tables (S3)
  ↓
  ├─→ ClickHouse (scheduled analytics, fingerprinting, tag queries)
  └─→ Trino/Presto (ad hoc investigations, complex joins)

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).

Security translation

Where Netflix patterns apply directly.

EDR telemetry at scale

Modern EDR agents collect process execution, file access, network connections, registry modifications, DLL loads. 50,000 endpoints × 1,000 events/endpoint/day = 50M events/day, roughly 500 GB/day compressed. Netflix's fingerprinting lesson (216 µs → 23 µs) applies to EDR log parsing — use generated parsers instead of regex for normalization. ClickHouse advantage: queries like "all PowerShell executions with network activity" scan only 2 columns, not entire event records.

Network flow data (NetFlow, Zeek, Suricata)

10 Gbps × 24 hours = roughly 100M flows/day, 1 TB/day uncompressed. Netflix's tag sharding (3 s → 700 ms) applies to flow metadata — shard by source_ip, dest_ip, port for faster threat hunting. Aggregations like "top 100 internal hosts by external connections" complete in under a second versus 30+ seconds in Elasticsearch.

Cloud audit logs (CloudTrail, Azure Activity, GCP Audit)

5,000 AWS accounts × 10,000 API calls/day = 50M events/day, roughly 200 GB/day. Netflix's hot + cold tiering is critical — CloudTrail older than 30 days is rarely queried, tier to Iceberg for compliance retention. Incident investigations query the last 7 days (hot tier, sub-second). Compliance audits query a year+ (cold tier, Iceberg).

Application security logs (WAF, API Gateway, authentication)

1,000 microservices × 100K requests/day = 100M events/day, roughly 300 GB/day. Netflix's native protocol lesson applies — use ClickHouse Kafka connector for real-time WAF log ingestion. Queries like "failed auth attempts by user, last 24 hours" complete instantly for active incident response.

The common pattern: security analytics equals aggregations over high-cardinality fields (IPs, usernames, file hashes, process names). This is exactly where ClickHouse excels and Elasticsearch struggles.

Cost

5 TB/day: ClickHouse vs. Splunk vs. Elastic.

Real numbers. Annual cost at 5 TB/day ingestion (1.825 PB/year stored):

Platform                  Annual cost
─────────────────────────────────────────
Splunk Enterprise         $2.74M – $5.48M
Elastic Cloud             $438K – $876K
ClickHouse (self-hosted)  $142K – $200K
ClickHouse Cloud          $200K – $300K

Assumptions: Splunk 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.

ClickHouse is 5–27× cheaper than Splunk and 2–4× cheaper than Elastic at 5 TB/day. Savings scale linearly — at 50 TB/day, ClickHouse saves $2–5M annually.

Huntress migration

93% cost reduction in 8 months.

Huntress (3M endpoints) migrated from Elastic to ClickHouse in 2024. Cost: $70K/month → $5K/month (93% savings, $780K annually). Infrastructure: 1M EPS sustained on 3× 16-core 16GB RAM servers. Compression: terabytes compress to dozens of gigabytes (20–50× ratio). Performance: sub-second queries on billions of events.

The phased timeline:

Phase 1: planning and POC (2 months). Architecture design, vendor evaluation, ClickHouse POC. Migrate one data source (EDR logs). Validate ClickHouse queries are 5–10× faster than Elastic.

Phase 2: parallel run (3 months). Dual-write to Elastic and ClickHouse, validate data consistency. Migrate analyst dashboards. Train SOC team. Success criteria: SOC team prefers ClickHouse for 80%+ investigative queries.

Phase 3: cutover and optimization (3 months). Stop Elastic ingestion. Tune ClickHouse (compression codecs, partitioning, tiering). 90%+ cost reduction validated. Elastic cluster decommissioned.

Total: 8 months from POC to full production cutover. Team: 2 FTE (1 data engineer, 1 security engineer). The migration is gradual, not disruptive. Parallel run builds confidence before decommission.

Materialized views

When to accelerate vs. when to query raw.

ClickHouse materialized views provide 10–100× query acceleration for repeated patterns but require careful economic analysis at petabyte scale.

-- 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;

Query raw table: scan 50 GB auth logs, aggregate 50M events = 3–10 seconds. Query materialized view: scan 500 MB pre-aggregated data = 50–200 ms (20–100× faster). Storage overhead: +1%.

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: without MV, $86.40/day ($2,592/month). With MV, $2.94/day ($88/month). That's 97% reduction on a single dashboard. At 20–30 production dashboards, $600K/year savings via 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).

Threat hunting patterns

What queries actually look like.

Pattern 1: behavioral analytics over time windows

-- Detect users with 10+ failed logins followed by successful login (credential stuffing)
SELECT user_id, COUNT(*) as failed_attempts
FROM auth_logs
WHERE event_time > now() - INTERVAL 1 HOUR
  AND auth_result = 'failure'
GROUP BY user_id
HAVING failed_attempts >= 10
  AND user_id IN (
    SELECT user_id FROM auth_logs
    WHERE event_time > now() - INTERVAL 1 HOUR
      AND auth_result = 'success'
  );

ClickHouse: under 500 ms on 1B+ auth events. Elasticsearch equivalent: 30+ seconds.

Pattern 2: rare event detection

-- Find rarely-seen processes executed on multiple hosts (lateral movement)
WITH process_frequency AS (
  SELECT process_name, COUNT(DISTINCT host_id) as host_count
  FROM edr_logs
  WHERE event_time > now() - INTERVAL 7 DAYS
  GROUP BY process_name
)
SELECT * FROM process_frequency
WHERE host_count >= 5
  AND process_name NOT IN (SELECT process FROM known_good_processes)
ORDER BY host_count DESC;

Aggregations over billions of EDR events complete in seconds, enabling interactive threat hunting.

Hydrolix trade-offs

Where a log-specific architecture wins.

When migrating security logs from a SIEM to a columnar database, you face a fundamental trade-off: flatten the schema and lose hierarchical semantics (see the flattening anti-pattern essay for the full treatment), or preserve structure and accept query complexity. Hydrolix is purpose-built for log data with high cardinality and nested structures. It's worth comparing where each architecture wins.

Architectural differences

Decoupled components. Hydrolix runs Kubernetes-native, stateless architecture where ingest, query, and merge scale independently. ClickHouse MergeTree operations can starve query resources (reads/writes share resources). Security impact: Hydrolix maintains consistent query performance during heavy ingestion (incident response while logs are flooding in).

High cardinality optimization. Hydrolix designed for "hundreds of thousands of columns" with full indexing. ClickHouse high-cardinality fields require careful codec selection. Security impact: security logs are extreme-cardinality (IPs, usernames, file paths, hashes) — Hydrolix optimized for this by default.

Nested data handling. Hydrolix uses catch-all fields storing arrays of maps, JSON Pointer syntax for nested queries, flattening optional. ClickHouse supports Nested types but query complexity rises. Security impact: CloudTrail / Azure AD logs with deeply nested structures query naturally in Hydrolix without explicit flattening.

JOIN support. Hydrolix has native JOIN between tables (timestamp-based, requires aliases and exact type matching). ClickHouse has full SQL JOIN support including dictionary joins and distributed joins. Security impact: ClickHouse more flexible for complex multi-table correlations.

When to choose which

Choose ClickHouse if: multi-use case platform (observability + security + business analytics), complex SQL joins required, schema optimization acceptable, Iceberg / lakehouse integration critical, mature ecosystem needed.

Choose Hydrolix if: 100% log data workload, extreme high cardinality (millions of unique IPs / usernames / hashes daily), deeply nested JSON to preserve without flattening, consistent query performance during ingestion spikes is critical, long-term cost-effective retention is the primary goal.

Evidence gap: ClickHouse has Netflix (5 PB/day) and Huntress (3M endpoints) production validation at Tier A. Hydrolix architecture claims are reasonable but lack equivalent public case studies at petabyte scale (Tier C). For now, default to ClickHouse for production decisions unless your workload is specifically log-only with nested-structure-heavy queries.

Decision framework

Should you migrate?

Use ClickHouse if

Collecting over 5 TB/day (or will be soon).
Analysts run aggregation queries (counts, groupings, time-series).
Need sub-second query response for investigations.
Have engineering capacity to operate infrastructure (ClickHouse isn't push-button).
Data is structured or semi-structured (JSON logs, not binary blobs).

Stick with traditional SIEM if

Under 1 TB/day and budget isn't a constraint.
Heavily rely on out-of-the-box detection rules and dashboards.
Team prefers managed services.
Need full-text search across unstructured data.

Consider DuckDB if

Need ClickHouse-like performance but at smaller scale (10s of GB to TBs, not PBs).
Want edge analytics (queries run where data lives, not centralized).
Team is familiar with SQL but not distributed databases.

Jake Thomas at Okta runs DuckDB at 7.5 trillion records for security analytics. Different architecture, comparable scale.

Takeaways

What Netflix's scale teaches security architects.

Netflix didn't share their logging optimizations for altruism. They validated an architectural pattern that observability and security communities are converging on: modern data stacks (columnar databases plus lakehouse storage) are replacing traditional SIEMs for petabyte-scale security data.

Columnar databases beat traditional SIEMs at scale. ClickHouse outperforms Splunk and Elasticsearch by 5–100× on analytical queries; cost is 23–42× cheaper than Elastic and Datadog at equivalent scale.
Optimize where it matters. Ingestion: generated parsers (216 µs → 23 µs). Write path: native protocols beat generic APIs. Query path: data layout beats clever algorithms (tag sharding 3 s → 700 ms).
Hot + cold tiering isn't optional at scale. Don't store everything in one tier. Match storage cost to query frequency. Unified query API hides complexity from analysts.
Petabyte scale changes everything. What works at 1 TB/day fails at 100 TB/day. Architectural decisions that seem premature become mandatory. "Do the least amount of work" becomes the guiding principle.

If you're designing a security data platform today, Netflix's ClickHouse journey isn't just inspiration — it's a blueprint.