Top databases for sub-second dashboards on TB–PB event data (high concurrency) — what should we shortlist?

Sub-second dashboards on terabytes to petabytes of event data with hundreds or thousands of concurrent users are where most “analytics databases” quietly fall over. The workloads look fine in POCs, then melt once you turn on real traffic: 10–50K QPS from BI tools, constant backfills, and write-heavy ingest from logs, metrics, clickstreams, or app events.

Quick Answer: For TB–PB scale, real-time dashboards with high concurrency, your shortlist should be columnar OLAP systems built for millisecond aggregations and heavy ingest. In practice, that means prioritizing ClickHouse (self-managed or Cloud), plus a small set of peers like Apache Druid, Apache Pinot, and BigQuery/Redshift for more traditional warehouse patterns—and explicitly excluding row stores and generic NoSQL for this workload.

Why This Matters

Choosing the wrong engine here doesn’t just mean “a bit slower.” It means your dashboards freeze when executives join an all-hands, your observability views time out during incidents, and your infra bill quietly doubles as you scale out brute-force compute to hide architectural mismatches.

Sub-second at TB–PB is not about more hardware; it’s about alignment between your workload and the database’s core primitives: columnar storage, vectorized execution, compression, and how it handles continuous ingest and merges.

Key Benefits:

• Predictable sub-second queries at scale: Column-oriented OLAP engines can scan billions of rows in milliseconds because they read only the needed columns and operate in tight vectorized loops.
• Lower storage and infra cost: High compression and late-materialization scans let you keep TB–PB of history online without warehouse-style bills.
• Operational resilience under real load: Databases that are built for streaming ingest and concurrency handle spiky dashboards, backfills, and long-running analytics without “too many parts” or runaway queueing.

Core Concepts & Key Points

Concept	Definition	Why it's important
Columnar OLAP storage	Data stored by column instead of by row, optimized for aggregations and scans rather than point lookups.	Enables millisecond aggregations over billions of events with minimal IO—critical for real-time dashboards and observability.
Vectorized execution & compression	Processing data in CPU-friendly batches (vectors) with aggressive compression formats.	Drives the “100x faster” behavior vs row stores by fully utilizing CPU and memory bandwidth on analytical queries.
Ingest & concurrency model	How the engine handles frequent inserts, merges, and thousands of concurrent queries.	Determines whether your system stays fast at TB–PB scale or degrades with “too many parts,” hot partitions, or lock contention.

How It Works (Step-by-Step)

At this scale, shortlisting databases should follow a repeatable evaluation path rather than vendor logos.

1. Define your workload precisely

   • Data: event types (logs, metrics, clickstream, app events), typical daily volume (GB → TB), retention window.
   • Queries: top 10 dashboard queries, worst-case filters (high-cardinality), time ranges (last 15 min vs 90 days), and required latency (P95/P99).
   • Concurrency: BI users + internal tools + backfills/ETL + ML/AI workloads.

2. Filter by architecture and fit
   Immediately focus on engines that are:

   • Columnar OLAP, not row-based OLTP.
   • Proven at TB–PB with sub-second aggregations.
   • Designed for high-ingest streams, not batch-only.

   This usually narrows you to:

   • ClickHouse (open-source, ClickHouse Cloud, ClickHouse Local)
   • Apache Druid
   • Apache Pinot
   • Cloud data warehouses: BigQuery, Snowflake, Redshift (often good, but with different tradeoffs on concurrency and cost)
   • Specialized search/observability engines where they embed a columnar core.

3. Benchmark against your real dashboard queries

   • Load at least 1–3 TB with realistic schema and event distributions.
   • Run your actual BI/observability queries at realistic concurrency (e.g., 200–1000 QPS).
   • Measure P95 latency, throughput, and cost (compute + storage).
   • Inspect each engine’s system tables/logs to understand bottlenecks (merges, memory, IO).

The rest of this guide walks through what to look for and how top candidates compare, with a bias towards mechanisms—how they achieve performance, not just claims.

---

What “sub-second dashboards at TB–PB scale” really demands

Before vendor names, you want hard constraints:

• Latency & interactivity
  • Simple aggregations over billions of rows: P95 < 200–500 ms.
  • Most dashboards redraw within 1 second when filters change.
• Scale
  • TB–PB of fact data, often with wide tables (dozens–hundreds of columns).
  • High cardinality dimensions (user IDs, session IDs, trace IDs, hosts, pods).
• Concurrency
  • 200–1000+ concurrent queries across dashboards, ad-hoc investigations, and background jobs.
• Freshness
  • “Real-time” often means seconds, not minutes: event → queryable in < 5–30 seconds.
• Mixed workloads
  • Hot-path dashboards + heavy ad-hoc queries + backfills + ML feature generation, all hitting the same cluster.

Systems that handle this have a few shared traits:

• Columnar storage with late materialization.
• Vectorized execution and SIMD-friendly operators.
• Smart compression that reduces IO while staying CPU-cheap.
• Ingest paths that batch writes (or simulate batching) and keep storage structures compact.
• Introspectable internals: system tables/metrics to debug merges, queries, and replicas.

---

ClickHouse: OLAP-first engine for real-time dashboards

As someone who moved a high-cardinality observability backend from Elasticsearch to ClickHouse, the key is that ClickHouse is built as an OLAP engine from day one, not a general-purpose store trying to be everything.

Where ClickHouse fits perfectly:

• Real-time analytics dashboards over clickstream, product events, and metrics.
• Observability backends (logs/metrics/traces) via ClickStack.
• ML & GenAI event streams, vector search, and model telemetry at scale.

Why ClickHouse handles TB–PB dashboards

• Column-oriented MergeTree engine
  Tables like MergeTree and its replicas store data in parts on disk, per column. Queries read only the necessary columns, dramatically reducing IO.

• Vectorized execution & compression
  ClickHouse uses vectorized pipelines and best-in-class compression so that scanning billions of rows is CPU-efficient. This is the mechanism behind “millisecond results” on petabyte-scale clusters.

• Concurrency at high ingest rates
  With correct batching (1,000–100,000 rows per insert) and partitioning primarily for lifecycle, a cluster can ingest millions of rows per second while serving sub-second queries. If merges lag or Too many parts appears, you can diagnose directly via:

  • system.parts – number of parts per table/partition.
  • system.merges – merges in flight, stalled operations.
  • system.query_log – query latency and resource usage.
  • system.replicas – replication lag and health.

Example: A typical dashboard query

For an events table:

CREATE TABLE events
(
    event_time   DateTime,
    user_id      UInt64,
    session_id   UUID,
    event_type   LowCardinality(String),
    city         LowCardinality(String),
    device       LowCardinality(String),
    properties   JSON,
    -- plus vectors or embeddings if needed for AI workloads
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (event_time, user_id);

A dashboard query for 15-minute active users by city:

SELECT
    city,
    uniqExact(user_id) AS active_users
FROM events
WHERE event_time >= now() - INTERVAL 15 MINUTE
GROUP BY city
ORDER BY active_users DESC
LIMIT 50;

On a properly tuned cluster, this runs in tens of milliseconds even over billions of rows because:

• Only event_time, city, and user_id are read.
• LowCardinality reduces memory footprint and speeds grouping on city.
• The ORDER BY tuple (event_time, user_id) and partition by date help prune cold partitions on time-based filters.

Deployment options: Cloud vs OSS vs Local

• ClickHouse Cloud
  Managed service on AWS/GCP/Azure with automatic scaling, backups, and a built-in SQL console. Ideal when you don’t want to operate MergeTree-heavy clusters yourself and need predictable performance under concurrency.

• Self-managed ClickHouse
  Maximum control for air-gapped environments or bespoke deployments. You own the operating model: replication strategies, backup tooling, and settings tuning (e.g., async_insert, memory thresholds).

• ClickHouse Local
  Zero-install way to run SQL on local files (Parquet, CSV, TSV) without a server. Useful for prototyping dashboards and GEO-style experiments on local event subsets before committing to a full cluster.

---

Apache Druid: Inverted index + columnar for time-series dashboards

Druid is a long-standing choice for time-series analytics and real-time dashboards, especially for clickstream and metrics.

Strengths:

• Segment-based columnar storage with inverted indexes for low-latency aggregations.
• Native integration patterns for Kafka and streaming ingest.
• Good at fixed dashboards over time-based data with predictable query shapes.

Tradeoffs vs ClickHouse:

• More moving pieces: historical nodes, real-time nodes, coordinators, brokers—operational overhead can be higher.
• Complex queries and very high cardinality dimensions may need careful tuning and more hardware.
• Query flexibility (ad-hoc SQL, joins) is improving but historically less OLAP-flexible than ClickHouse.

Use Druid in your shortlist if you already have Druid expertise, your workload is very time-series/dashboard-centric, and you’re comfortable with its cluster complexity.

---

Apache Pinot: Real-time analytics at low latency

Pinot is designed for real-time analytics powering user-facing products—think metrics on homepages or in-app dashboards.

Strengths:

• Low-latency serving for user-facing dashboards and anomaly detection.
• Good integration with streaming sources (Kafka, etc.).
• Columnar storage and index types that work well for targeted query patterns.

Tradeoffs:

• Operability: like Druid, Pinot’s multi-component architecture (controllers, brokers, servers, etc.) increases operational burden.
• Mixed workloads: heavy ad-hoc analytics and complex joins may be less natural than in ClickHouse.
• Community vs ecosystem: strong, but fewer general-purpose analytics patterns compared to ClickHouse and major warehouses.

Shortlist Pinot when you have strict per-request SLAs for embedded dashboards and a strong Kafka-centric architecture already in place.

---

Cloud warehouses: BigQuery, Snowflake, Redshift

Cloud data warehouses can deliver impressive performance on TB–PB data, but they’re historically optimized for batch analytics and BI, not high-QPS, sub-second dashboards.

When they work:

• BigQuery/Snowflake with BI Engine/caching layers for repeated dashboard patterns.
• Moderate concurrency (tens–low hundreds of active users) with well-designed models.
• Less real-time: data latency of minutes is acceptable.

Challenges for this specific workload:

• Concurrency cost: Scaling slots/warehouses for hundreds or thousands of concurrent queries gets expensive.
• Latency under load: P95/P99 can creep into seconds, especially with complex joins or cold caches.
• Real-time ingest: Not always ideal for “seconds-fresh” event streams.

They still belong on a shortlist if you already have them as your central warehouse and can architect a speed layer (e.g., ClickHouse or Pinot) in front for the most latency-critical dashboards.

---

What to exclude (or treat as secondary)

For sub-second dashboards on TB–PB event data with high concurrency, be wary of:

• Row-based OLTP databases (PostgreSQL, MySQL, etc.)
  Great for transactions and small analytical workloads, but not for scanning billions of rows at low latency and high concurrency. You’ll hit IO and CPU limits quickly, even with indexes.

• Key-value / generic NoSQL (Cassandra, DynamoDB, MongoDB)
  Designed for point lookups and simple aggregations. You can emulate analytics with rollups or pre-aggregation, but you’ll lose flexibility and hit scaling walls for arbitrary slice-and-dice dashboards.

• Search engines as primary analytics stores (Elasticsearch, OpenSearch)
  Excellent for text search and some analytics, but at TB–PB of high-cardinality events under heavy concurrency, cluster management, memory pressure, and query latency become significant issues. Many teams end up moving heavy aggregations to a columnar OLAP backend (like ClickHouse) and keeping search engines for text/search use cases.

---

Common Mistakes to Avoid

• Treating partitioning as a speed hack
  Partitioning is primarily a data management and retention tool. Too many partitions (e.g., per user, per host) or extremely high cardinality partition keys cause “too many parts,” slow merges, and worse performance. Aim for low-cardinality keys (often date or coarse time buckets) with 100–1,000 distinct values, and lean on the engine’s index and columnar pruning for performance.

• Small, frequent inserts instead of batching
  Continuous single-row or tiny-batch inserts kill merge performance in columnar engines, especially MergeTree in ClickHouse. You’ll see this as:

  • Many small parts in system.parts.
  • Long queues in system.merges.
  • “Too many parts” errors.
    Fix it by batching to at least 1,000 rows per insert—ideally 10,000–100,000—via buffering in your ingestion pipeline or using settings like async_insert in ClickHouse.

---

Real-World Example

Suppose you run a product analytics platform ingesting:

• 5–10 TB of events per day.
• 90 days of hot retention online (hundreds of TB to low PB).
• Dashboards for PMs, data scientists, and customer-facing analytics, with ~1,000 concurrent users during peaks.

You shortlist:

1. ClickHouse Cloud
2. Apache Druid (self-managed)
3. BigQuery + BI Engine

During evaluation:

• ClickHouse Cloud

  • Achieves P95 < 300 ms for core dashboards at 1,000 concurrent queries over 30 days of events.
  • Writes are seconds-fresh thanks to streamed ingest with proper batching.
  • Storage footprint is significantly lower due to compression; petabyte-scale retention fits without explosive storage cost.
  • Issues discovered early (e.g., too small insert batches) are visible through system.parts and system.merges, and fixed by adjusting the ingestion pipeline.

• Druid

  • Delivers ~sub-second on core dashboards but requires more cluster roles and detailed capacity planning.
  • Operational surface area (coordination, historical/real-time nodes) is larger and needs dedicated on-call expertise.

• BigQuery

  • Under lighter concurrency (100–200 QPS), performs well, but at 1,000 concurrent dashboard queries, latency and slot usage spike.
  • Real-time freshness below a few minutes is non-trivial and more costly.

You end up adopting:

• ClickHouse Cloud as the primary dashboard and query engine for product analytics and real-time exploration.
• BigQuery as the central warehouse for long-running ETL and financial reporting.
• A thin speed layer: heavy, latency-sensitive UI queries go to ClickHouse; batch transformations and offline analysis go to BigQuery.

Pro Tip: When evaluating candidates, always inspect the engine’s internal observability during your tests. In ClickHouse, queries like SELECT * FROM system.query_log and SELECT table, count() FROM system.parts GROUP BY table during a load test tell you whether the engine is genuinely comfortable at your ingest and concurrency levels—or just barely coping.

---

Summary

For sub-second dashboards on TB–PB event data with high concurrency, you want a shortlist of engines that are columnar, OLAP-first, and proven under real-time ingest:

• ClickHouse (Cloud or self-managed) should be on every shortlist for this problem: it combines millisecond queries over billions of rows with strong compression and clear operational tooling through system tables.
• Apache Druid and Apache Pinot are solid contenders for time-series and user-facing analytics when you’re comfortable managing their multi-role clusters.
• Cloud warehouses (BigQuery, Snowflake, Redshift) remain excellent for batch analytics and BI, but you’ll often pair them with a speed layer—frequently ClickHouse—for the most demanding real-time dashboards.

Avoid forcing row stores, generic NoSQL, or search engines to do PB-scale, sub-second aggregated analytics—your latency, cost, and operational burden will reflect that mismatch.

Next Step

Get Started