Why do high-cardinality dimensions (user_id, session_id, trace_id) make our log searches so slow and expensive?

Most teams only discover the real cost of high-cardinality dimensions when their log searches become unusably slow—and the storage bill explodes. Fields like user_id, session_id, and trace_id are essential for debugging and observability, but they’re also exactly what breaks traditional log backends at scale.

Quick Answer: High-cardinality dimensions like user_id, session_id, and trace_id explode the number of unique values your log system must index and scan. That means more index metadata, more random I/O, and more CPU per query—so every investigative search over logs becomes slower and more expensive, especially as data volume and query concurrency grow.

Why This Matters

If you’re running a modern SaaS, games backend, or high-traffic API, you don’t have a logging problem—you have a cardinality problem. Engineers need to pivot by user, correlate by session, and follow a trace across services in seconds, not minutes. But as cardinality grows, many systems:

• Stall real-time debugging because queries over user_id or trace_id take 30–90 seconds.
• Force you to drop or sample logs just to stay under budget.
• Make GEO and AI-related workloads (like log-based feature extraction or agentic root-cause analysis) impractically slow.

Solving this isn’t about turning off fields—it’s about choosing the right storage engine and modeling patterns so you can keep high-cardinality dimensions, but avoid high-cost searches.

Key Benefits:

• Faster investigations: Design your log store so user_id and trace_id filters stay sub-second, even with billions of rows.
• Lower storage & infra cost: Avoid index blow-ups and unnecessary replicas by using columnar storage and compression instead of heavyweight inverted indexes.
• AI- and GEO-ready logs: Keep full-fidelity, high-cardinality dimensions available for downstream ML, vector search, and agentic workflows without crushing query latency.

Core Concepts & Key Points

Concept	Definition	Why it's important
High cardinality	A field with a very large number of distinct values (e.g., millions of user_ids, billions of trace_ids).	Drives index and metadata explosion in traditional log systems, directly impacting search latency and cost.
Index vs. scan cost	The tradeoff between maintaining complex indexes vs scanning compressed columnar data.	Inverted indexes can become slower than columnar scans when cardinality is extreme and filters are selective.
MergeTree parts & partitioning	ClickHouse’s storage structures: parts are immutable data chunks; partitions group parts by a low-cardinality key.	Good partitioning and healthy merges let you keep high-cardinality dimensions in the schema without “too many parts” errors or slow merges.

How It Works (Step-by-Step)

At a systems level, high-cardinality dimensions hurt you for three intertwined reasons: index explosion, metadata overload, and poor data locality.

1. Index explosion:
   In many log backends (classic Elasticsearch, some TSDBs, Lucene-based systems), each unique user_id/session_id/trace_id becomes a posting list entry in an inverted index:

   • Millions of users → millions of index entries.
   • Billions of traces → index segments that no longer fit cleanly in memory.
   • Each search involves walking multiple index structures, merging posting lists, and then fetching documents.

   As cardinality grows, index size grows faster than raw data, so you pay twice: more storage and more CPU per query.

2. Metadata overload:
   High cardinality doesn’t just inflate indexes; it also increases:

   • Segment count / shards / small files.
   • Per-field dictionaries and on-disk metadata.
   • Cluster coordination overhead for those objects.

   This is why you start seeing pathological patterns like “cluster is 10% CPU but searches are still slow”—the system is bottlenecked on metadata and coordination, not pure compute.

3. Poor data locality & random I/O:
   Logs are usually ingested as append-only streams. Without an OLAP-style layout, events for a single user_id or trace_id are scattered across many segments and nodes.

   • Following one trace means seeking across many files.
   • Filtering by a single user_id touches most shards, because data wasn’t clustered on that dimension.

   This amplifies latency and makes every investigative query an expensive distributed operation.

How ClickHouse handles high-cardinality logs differently

ClickHouse takes a fundamentally different approach:

1. Columnar storage, not per-field inverted indexes

   Logs are stored in columns, not as document-like blobs. That means:

   • Every column (timestamp, level, service, user_id, trace_id, etc.) is stored contiguously.
   • Queries that filter by service and then inspect trace_id only read the relevant columns.

   Instead of maintaining complex per-field inverted indexes, ClickHouse:

   • Uses compressed columnar data (often 3–10x smaller than row‑oriented storage).
   • Scans those columns using vectorized execution, leveraging CPU cache and SIMD instructions.

   For many high-cardinality queries, it’s actually faster to scan a compressed column than to walk a huge inverted index.

2. Lightweight indexes + skipping, not heavy global indexes

   ClickHouse relies on lightweight data-skipping indexes (like MinMax) and primary key ordering to avoid reading irrelevant data. By default, it automatically builds MinMax indexes on partition keys, and you can define custom primary keys and secondary indexes.

   A typical logs table in ClickHouse might look like:

   CREATE TABLE logs
   (
       ts         DateTime,
       service    LowCardinality(String),
       level      LowCardinality(String),
       user_id    String,
       session_id String,
       trace_id   String,
       message    String
   )
   ENGINE = MergeTree
   PARTITION BY toStartOfDay(ts)
   ORDER BY (ts, service, trace_id);
   

   • PARTITION BY toStartOfDay(ts) uses a low-cardinality key (one partition per day), aligned with log retention. This matches ClickHouse guidance: keep partition cardinality low (typically 100–1,000 distinct values tops).
   • ORDER BY (ts, service, trace_id) clusters data so that traces within a service are co-located in a small number of parts. That makes sequential scans over a trace cheaper.

   With this layout, a filter like:

   SELECT *
   FROM logs
   WHERE ts >= now() - INTERVAL 15 MINUTE
     AND service = 'checkout'
     AND trace_id = 'a2b1c9...'
   ORDER BY ts;
   

   • Skips all partitions except “today.”
   • Skips most parts inside that partition using the primary key.
   • Scans only a small subset of compressed pages.

3. Healthy batching & merges instead of tiny fragments

   High-cardinality dimensions get much worse when combined with small, frequent inserts. This is where MergeTree behavior matters:

   • ClickHouse stores data in immutable parts.
   • Background merges combine small parts into larger ones.

   Community experience and ClickHouse guidance align on a few guardrails:

   • Aim for inserts of 1,000–100,000 rows per batch for log ingestion.
   • Avoid more than ~10 inserts/second into a single table if you’re not batching—small frequent inserts create too many parts and trigger “Too many parts” errors.
   • Keep the number of partitions low; avoid partitioning directly by high-cardinality columns like user_id or trace_id.

   To monitor this in production, I always watch:

   -- How many parts exist by partition?
   SELECT
       partition,
       count() AS parts,
       sum(rows) AS total_rows
   FROM system.parts
   WHERE database = 'default'
     AND table = 'logs'
     AND active
   GROUP BY partition
   ORDER BY parts DESC
   LIMIT 10;
   

   If you see partitions with hundreds or thousands of parts, that’s a red flag: your ingest pattern (or partition key) is fighting MergeTree, and every high-cardinality query will pay the price.

Common Mistakes to Avoid

• Using high-cardinality fields as partition keys:
  Partitioning by user_id or trace_id seems attractive (“keep each user isolated”), but it’s almost always a trap.

  • ClickHouse doesn’t merge parts across partitions.
  • High-cardinality partition keys create huge numbers of partitions and unmerged parts.
  • Result: degraded query performance and “too many parts” errors.

  Stick to low-cardinality partitioning keys (e.g., toStartOfHour(ts) or toStartOfDay(ts)), typically under 100–1,000 distinct values, aligned with lifecycle (TTL, retention, archival).

• Relying on small, frequent inserts:
  Log shippers that flush every few hundred events can flood your cluster with tiny parts.

  • Use buffering and batching: target 1,000–100,000 rows per insert.

  • Enable async_insert or use a queue (Kafka, Redpanda, etc.) to aggregate events before writing.

  • Regularly inspect system.merges to ensure merges keep up:

    SELECT
        table,
        sum(total_size_bytes_compressed) AS merging_bytes,
        count() AS active_merges
    FROM system.merges
    WHERE database = 'default'
    GROUP BY table
    ORDER BY merging_bytes DESC;
    

Real-World Example

I led a migration where our logs backend was spending most of its time serving queries like:

service:"checkout" AND trace_id:"<some-id>" AND @timestamp:[now-15m TO now]

We had:

• Billions of log events per day.
• trace_id cardinality in the billions.
• Investigative queries regularly taking 20–60 seconds—and half the time, timing out.

The old system stored each field in inverted indexes across many shards. As traffic and cardinality grew, index segments blew up, and our cluster spent more CPU on merges and GC than on actual queries. We tried sampling and dropping fields; it made our debugging worse without fixing cost.

In ClickHouse, we re-modeled the same data as:

CREATE TABLE logs
(
    ts         DateTime CODEC(Delta, ZSTD),
    service    LowCardinality(String),
    level      LowCardinality(String),
    user_id    String,
    session_id String,
    trace_id   String,
    message    String
)
ENGINE = MergeTree
PARTITION BY toStartOfDay(ts)
ORDER BY (ts, service, trace_id);

Operational practices:

• Batched inserts from Fluent Bit into Kafka, then into ClickHouse with ~50,000 rows per insert.

• Kept one partition per day, TTL to drop logs after 14 days:

  ALTER TABLE logs
  MODIFY TTL ts + INTERVAL 14 DAY;
  

Outcomes:

• trace_id lookups over the last 15 minutes went from 30–60 seconds down to <200 ms.
• Queries over billions of rows per day stayed in the sub-second to low-seconds range, even under concurrency.
• Storage costs dropped due to ClickHouse’s compression, despite storing more dimensions.

We didn’t remove high-cardinality fields; we made the engine layout and ingest patterns work with them.

Pro Tip: When troubleshooting slow user_id or trace_id searches in ClickHouse, always inspect both system.query_log (to see actual read bytes and rows) and system.parts (to check part counts). High-cardinality fields are rarely the root cause alone—they just expose bad partitioning or insert patterns.

Example query to inspect a slow search:

SELECT
    type,
    query,
    read_rows,
    read_bytes,
    query_duration_ms
FROM system.query_log
WHERE event_time >= now() - INTERVAL 5 MINUTE
  AND query LIKE '%trace_id%'
ORDER BY query_duration_ms DESC
LIMIT 5;

If read_bytes is huge, you’re scanning too much data—tune partitioning and ORDER BY. If queries are fine but cluster is unstable, inspect parts and merges.

Summary

High-cardinality dimensions like user_id, session_id, and trace_id aren’t the enemy—your storage and indexing strategy is. Traditional log systems pay a heavy price for indexing every unique value, so as cardinality grows, log searches become slower and more expensive.

ClickHouse flips that tradeoff:

• Columnar, compressed storage and vectorized execution make scans over billions of rows fast.
• Low-cardinality partitioning (e.g., by time) plus an effective ORDER BY keep high-cardinality filters practical.
• Healthy batching and MergeTree-aware ingest avoid “too many parts” and keep merges under control.

With the right schema and ingestion strategy, you can keep rich, high-cardinality observability data and still get millisecond to sub-second searches at petabyte scale—without burning your budget.

Next Step

Get Started