Best real-time analytics database for high-cardinality group-bys and lots of concurrent users

Most teams hit a wall with real-time analytics long before they hit a wall with storage. The usual pattern: dashboards slow to a crawl when someone runs a high-cardinality GROUP BY (think user IDs, URLs, trace IDs), queries time out under load, and concurrency tanks as more users pile on. Choosing the right database for this pattern isn’t about a vague “fast OLAP engine”—it’s about how well it handles massive scans, high-cardinality aggregations, and dozens to hundreds of concurrent users without blowing up latency or cost.

Quick Answer: For workloads dominated by high-cardinality group-bys and lots of concurrent users, a column-oriented OLAP database with vectorized execution and strong compression—like ClickHouse—is usually the best fit. It’s built to scan billions of rows, aggregate on high-cardinality dimensions, and still deliver millisecond results to many users at once, especially when you batch inserts and model data with MergeTree engines correctly.

Why This Matters

Real-time analytics is now a product surface, not an internal report. When dashboards back product decisions, pricing, fraud detection, or ML/GenAI feedback loops, “please wait 30 seconds” isn’t acceptable. High-cardinality group-bys (GROUP BY user_id, GROUP BY session_id, GROUP BY trace_id) plus lots of concurrent users expose the weakest parts of traditional warehouses and general-purpose databases: row-based storage, heavyweight joins, and poor concurrency controls for read-heavy workloads.

Picking the right real-time analytics database here directly impacts:

• Whether you can let hundreds of people freely “slice and dice” production data without guardrails.
• Whether your AI and observability pipelines can keep up with events, logs, and feedback in real time.
• Whether costs stay predictable when query concurrency spikes.

Key Benefits:

• Blazing fast high-cardinality analytics: Columnar storage and vectorized execution let you aggregate billions of rows by user, trace, or item ID in milliseconds instead of seconds.
• Handles concurrency without falling over: A shared-nothing, OLAP-first architecture means many users can run complex queries in parallel without saturating the cluster.
• Cost-effective at scale: Compression and efficient scans reduce I/O and storage, turning “this would bankrupt us in a row store or warehouse” into a sustainable analytics tier.

Core Concepts & Key Points

Concept	Definition	Why it's important
Column-oriented OLAP	A storage and execution model where data is stored by column, and queries operate on compressed column segments in a vectorized fashion.	Critical for scanning billions of rows and doing GROUP BY on high-cardinality columns with millisecond latency and predictable CPU usage.
High-cardinality group-bys	Aggregations over columns with many distinct values (user IDs, trace IDs, URLs, item IDs).	This is where many warehouses and row stores fall over; you need an engine designed to aggregate many groups efficiently with low memory overhead.
MergeTree & batching	ClickHouse’s family of table engines that store data in parts and merge them in the background; insert batching means writing data in sizable chunks (1k–100k+ rows per insert).	Healthy MergeTree parts and merges keep ingestion efficient and prevent “Too many parts” errors that kill performance as concurrency and data volume grow.

How It Works (Step-by-Step)

At a high level, a real-time analytics database that excels at high-cardinality group-bys and concurrency does three things well: writes, storage, and reads. Here’s how that looks in a ClickHouse-style architecture.

1. Efficient Ingestion with Batching

   Your first bottleneck is rarely the query—it’s the write path.

   • Data arrives via streaming (Kafka, Kinesis, pub/sub) or batch.
   • You buffer records and insert them in batches of at least 1,000 rows—ideally 10,000–100,000+ rows per insert—into a MergeTree-family table.
   • ClickHouse writes data as “parts” on disk; large, well-sized parts compress better and are easier to merge.

   Example: batched inserts into a log analytics table:

   CREATE TABLE logs
   (
       ts          DateTime,
       trace_id    String,
       user_id     String,
       service     LowCardinality(String),
       status_code UInt16,
       latency_ms  UInt32
   )
   ENGINE = MergeTree
   PARTITION BY toDate(ts)
   ORDER BY (service, ts, trace_id);
   

   Then, from your pipeline, batch writes:

   INSERT INTO logs FORMAT JSONEachRow
   {"ts":"2026-04-12 12:00:00","trace_id":"t1","user_id":"u1","service":"checkout","status_code":200,"latency_ms":45}
   ...
   -- 10k+ rows at a time
   

   This prevents excessive small parts and keeps merges healthy.

2. Columnar Storage with Compression

   After ingestion:

   • Each column (ts, trace_id, user_id, …) is stored separately on disk.
   • ClickHouse applies best-in-class compression per column; repeated patterns (like service or status_code) compress extremely well.
   • When you run a query, only relevant columns are read, and they’re processed in CPU-friendly vectors.

   For a query like:

   SELECT
       user_id,
       count(*) AS requests,
       avg(latency_ms) AS avg_latency
   FROM logs
   WHERE ts >= now() - INTERVAL 5 MINUTE
   GROUP BY user_id
   ORDER BY requests DESC
   LIMIT 100;
   

   The engine touches just ts, user_id, and latency_ms. That’s millions to billions of rows scanned and aggregated in memory-efficient vectors, not row-by-row.

3. Concurrency & Query Execution

   Under heavy user load:

   • ClickHouse uses a shared-nothing, distributed design: data is sharded and replicated across nodes.
   • Vectorized execution and efficient aggregation (including combinators like groupBitmap* or uniq* functions) keep per-query resource usage low.
   • You use Cloud autoscaling or cluster sizing to match concurrency requirements while keeping latency in the millisecond–low-second range.

   You can observe behavior via system.query_log:

   SELECT
       query_kind,
       count(*) AS queries,
       quantile(0.5)(query_duration_ms) AS p50_ms,
       quantile(0.95)(query_duration_ms) AS p95_ms
   FROM system.query_log
   WHERE event_time >= now() - INTERVAL 5 MINUTE
     AND type = 'QueryFinish'
   GROUP BY query_kind;
   

   This gives you real evidence of how the engine behaves under concurrent load.

Common Mistakes to Avoid

• Using row stores or OLTP databases for analytics:

  • How to avoid it: Keep Postgres/MySQL for transactions, but send events and aggregates to an OLAP engine like ClickHouse for analytics. Use CDC or streaming to feed the analytics layer rather than overloading your OLTP with GROUP BY user_id on billions of rows.

• Assuming partitioning is a magic speed trick:

  • How to avoid it: Treat partitioning as a lifecycle/retention tool first. In ClickHouse, use low-cardinality partition keys (e.g., by day or month, sometimes by tenant) and avoid partitioning on high-cardinality fields like user_id. Too many partitions or parts can hurt performance and trigger “Too many parts” errors. Use system.parts to verify:

    SELECT
        table,
        partition,
        count() AS parts,
        sum(rows) AS total_rows
    FROM system.parts
    WHERE active
    GROUP BY table, partition
    ORDER BY parts DESC
    LIMIT 20;
    

    If you see partitions with thousands of tiny parts, adjust batching and ingestion before adding more partitions.

Real-World Example

I once helped migrate a high-cardinality observability workload—logs and metrics keyed by services, instances, user IDs, and trace IDs—from Elasticsearch to ClickHouse because queries like “top users by error rate in the last 5 minutes” were timing out under load.

The requirements:

• High-cardinality GROUP BY on user_id, trace_id, endpoint.
• Thousands of events per second, 24/7.
• Dozens of concurrent users running heavy queries via dashboards and ad-hoc UIs.

The architecture in ClickHouse:

• Ingestion: Kafka → batched inserts into a MergeTree table with at least 10k rows per insert.

• Schema: Columnar with LowCardinality(String) for dimensions like service name and endpoint; String for truly high-cardinality IDs.

• Partitioning: PARTITION BY toDate(ts) (simple daily partitions), ORDER BY (service, ts, trace_id) for good locality.

• Queries: High-cardinality group-bys looked like:

  SELECT
      user_id,
      countIf(status_code >= 500) AS errors,
      count() AS total,
      errors / total AS error_rate
  FROM logs
  WHERE ts >= now() - INTERVAL 15 MINUTE
  GROUP BY user_id
  ORDER BY errors DESC
  LIMIT 1000;
  

Results:

• For tens of billions of rows, those queries ran in the low hundreds of milliseconds.
• With many concurrent users, query p95 stayed well under 1 second, validated in system.query_log.
• Storage dropped significantly compared to Elasticsearch because of compression.

Pro Tip: If your high-cardinality group-bys are hitting memory limits, start by using approximate functions (uniq/uniqCombined) and carefully tuned max_bytes_before_external_group_by instead of blindly scaling hardware. You’ll often get the same insight with a fraction of the resource usage.

Summary

For workloads centered on high-cardinality group-bys and many concurrent users, the “best” real-time analytics database is one that’s columnar, OLAP-first, and proven at scale—ClickHouse is a strong match because it combines:

• Column-oriented storage and vectorized execution for millisecond-scale scans and aggregates on billions of rows.
• MergeTree ingestion that stays healthy under sustained load when you batch inserts (1,000–100,000+ rows) and keep partitions low-cardinality.
• Operational transparency through system tables and logs so you can prove performance, debug “too many parts,” and tune for concurrency instead of guessing.

Relational OLTP systems and many traditional warehouses can be great for transactions and batch reporting, but they rarely keep up with high-cardinality, real-time analytics at scale without painful workarounds and high cost.

Next Step

Get Started