Why are our dashboard queries timing out when multiple teams hit the same analytics table at once?

When dashboard queries start timing out the moment multiple teams hit the same analytics table, you’re usually looking at a mix of concurrency, scan cost, and poor workload isolation—not a single “slow query” bug.

Quick Answer: Your dashboards are timing out because a few heavyweight queries are contending for the same CPU, I/O, and memory on a shared analytics table, often scanning far more data than necessary. In ClickHouse, this typically shows up as large full-table scans, unbounded group-bys, and under-provisioned clusters that can’t keep up with concurrent dashboard loads and ad-hoc exploration. The fix is to optimize query patterns, size and isolate workloads appropriately, and monitor system tables to catch bottlenecks early.

Why This Matters

When dashboards stall under concurrent load, teams stop trusting the data platform. Product managers revert to exports and spreadsheets, SREs lose observability during incidents, and data scientists throttle back on analysis to avoid “breaking prod.” Timeouts aren’t just latency problems; they’re a reliability and trust problem for every downstream decision that depends on your analytics table.

Key Benefits:

• Consistent millisecond dashboards: By optimizing queries and schema for ClickHouse’s columnar engine, you keep dashboards responsive even when dozens of users and services hit the same analytics table at once.
• Predictable performance under concurrency: Workload isolation and capacity planning prevent one team’s heavy exploration from causing another team’s timeouts.
• Lower cost at higher scale: Instead of overprovisioning blindly, you use compression, efficient batching, and targeted indexes to support more queries per core and per GB of storage.

Core Concepts & Key Points

Concept	Definition	Why it's important
Concurrent workload	Multiple queries (dashboards, APIs, ad-hoc SQL) running at the same time against shared tables and infrastructure.	Dashboard timeouts rarely come from one query; they appear when query concurrency spikes and the cluster can’t keep up.
Scan and aggregation cost	The CPU, I/O, and memory required to read and aggregate columns (often billions of rows) for each query.	Inefficient filters, missing projections/indexes, and wide scans increase per-query cost, which compounds under concurrency.
Workload isolation	Separating interactive dashboards, critical APIs, and heavy batch/ML queries onto different resources or clusters.	Prevents “noisy neighbor” effects where one team’s expensive queries cause another team’s dashboards to time out.

How It Works (Step-by-Step)

In practice, “why are our dashboard queries timing out when multiple teams hit the same analytics table at once?” usually decomposes into a few concrete mechanisms. Here’s how I approach this as someone who’s run ClickHouse as a shared analytics backend for many teams.

1. Inspect what’s actually running

First, you need to see what queries are doing when timeouts occur.

In ClickHouse, start with system.query_log and system.processes:

-- Current live queries
SELECT query_id, user, query, read_rows, read_bytes, memory_usage, elapsed
FROM system.processes
ORDER BY elapsed DESC;

-- Recently completed and failed queries
SELECT
  event_time,
  type,
  query_id,
  user,
  query_duration_ms,
  read_rows,
  read_bytes,
  result_rows,
  result_bytes,
  exception
FROM system.query_log
WHERE event_time > now() - INTERVAL 15 MINUTE
  AND is_initial_query = 1
ORDER BY query_duration_ms DESC;

Patterns to look for:

• Queries reading hundreds of millions or billions of rows for a single dashboard widget.
• Repeated full-table scans with weak filters (e.g., queries missing WHERE event_time >= now() - INTERVAL 15 MINUTE for real-time dashboards).
• Long-running group-bys with exploding cardinality (e.g., GROUP BY user_id on a table with hundreds of millions of distinct IDs).

Under concurrency, even “acceptable” 2–3 second queries can time out if ten of them run in parallel and saturate CPU.

2. Check how the data is stored and merged

For MergeTree tables, fragmentation and excessive small parts can degrade performance and make concurrency painful.

Start with system.parts:

SELECT
  database,
  table,
  count() AS active_parts,
  sum(rows) AS total_rows,
  median(rows) AS median_rows_per_part
FROM system.parts
WHERE active = 1
GROUP BY database, table
ORDER BY active_parts DESC
LIMIT 10;

Red flags:

• Very high part counts (tens of thousands+) for a single table.
• Very low median_rows_per_part (tiny parts) indicating small frequent inserts.

Small frequent inserts (above ~10 inserts/second) can cause “Too many parts” errors and keep merges constantly busy, stealing CPU from queries. Even before you hit the error, merges competing with queries for CPU and I/O can push dashboards over timeout thresholds.

What’s behind this?

• Ingest patterns with row-at-a-time writes or micro-batches.
• Lack of batching guidance to upstream services (ideal batch size: 10,000–100,000 rows per insert, with at least 1,000 as a lower bound).

3. Diagnose resource contention and limits

Timeouts under load are often not “mystery slow queries.” They’re queries hitting resource ceilings.

Key angles:

• CPU saturation: All cores busy, queries queue or slow down.
• Memory pressure: Aggregations spill to disk, or group-bys hit limits.
• Disk I/O contention: Heavy reads + merges + background tasks on the same volume.

In ClickHouse Cloud, the console surfaces CPU and memory usage; in self-managed setups, your system metrics plus system tables help:

-- Top memory users among running queries
SELECT
  query_id,
  user,
  query,
  memory_usage,
  read_rows,
  elapsed
FROM system.processes
ORDER BY memory_usage DESC
LIMIT 10;

If you see many queries hitting high memory usage with large group-bys, consider settings like:

• max_bytes_before_external_group_by
• max_bytes_before_external_sort

These allow ClickHouse to spill to disk instead of failing outright—but at the cost of latency. Under concurrency, that extra I/O easily converts into timeouts.

Also keep an eye on heavyweight background work:

-- Active merges
SELECT
  database, table, elapsed, progress, total_size_bytes_compressed
FROM system.merges
ORDER BY elapsed DESC;

-- Active mutations
SELECT
  database, table, mutation_id, command, parts_to_do, is_done
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time;

A huge mutation or long-running merge on a multi-terabyte table can distort latency for every team hitting that table.

4. Analyze query patterns and dashboard design

Once you understand the mechanical constraints, you nearly always end up back at query patterns.

Common dashboard anti-patterns:

• Unbounded time ranges – defaulting to “All time” or 30/90 days on panels that refresh every 10–30 seconds.
• High-cardinality group-bys – grouping on raw user IDs, trace IDs, or full URLs instead of aggregating to a coarser level.
• Nested, repeated queries – multiple panels issuing very similar but not identical queries, preventing ClickHouse from benefiting from cache or shared computation.
• Huge IN lists – dashboards generating queries with thousands of literal values or tags in an IN clause.

In ClickHouse, even with blazing-fast columnar scans, if each query reads billions of rows and does high-cardinality grouping, concurrency will quickly push you over the edge.

Typical fixes:

• Normalize dashboards to bounded lookbacks: e.g., 15 minutes, 1 hour, 24 hours, and only allow long lookbacks on explicit user action.
• Pre-aggregate for common views (e.g., hourly or minutely stats) in separate tables, so dashboards don’t repeatedly compute the same heavy analytics from raw events.
• Use LowCardinality for dimension columns where appropriate to reduce memory and speed up group-bys.
• For logs/metrics/observability, design the schema to keep hot paths efficient: think WHERE timestamp >= now() - INTERVAL 15 MINUTE with indexes and good primary keys.

5. Improve schema, indexing, and projections

ClickHouse performance under concurrency hinges on how efficiently each query can skip data and aggregate over what’s left.

Key levers:

• Primary key design: Choose a primary key that matches your most common filter predicates (time + a small number of dimensions). This is what drives data skipping.
• Partitioning: Use partitioning for lifecycle (e.g., by day or month), but don’t over-partition by high-cardinality dimensions. Over-partitioning can increase parts, hurt merges, and reduce performance.
• Materialized views and pre-aggregations: Build MV pipelines to maintain aggregated tables optimized for dashboards.
• Projections (where applicable): Define projections that pre-sort and organize data around frequent query shapes.

Example: a typical events table for dashboards:

CREATE TABLE events
(
  event_time       DateTime,
  org_id           UInt64,
  user_id          UInt64,
  event_type       LowCardinality(String),
  properties       String
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (org_id, event_time);

If your dashboards frequently filter by org_id and time, this ordering reduces the amount of data scanned per query. Under concurrency, that reduction is exactly what keeps aggregate CPU low and avoids timeouts.

6. Isolate workloads and size clusters for concurrency

When multiple teams share an analytics table, you have two choices:

• Tune everything to work peacefully on one cluster.
• Or isolate workloads so dashboards aren’t competing with heavy batch jobs or ML/GenAI experimentation.

Common approaches:

• ClickHouse Cloud with multiple services: Put latency-sensitive dashboards in one service and heavy, long-running queries in another. Both can read from the same underlying data via replication or pipeline logic, but they don’t fight for the same cores.
• Reader/writer separation: One cluster for ingestion and heavy transformations, another for read-heavy dashboard traffic.
• Query quotas and profiles: Use users, roles, and settings profiles to cap per-user resource usage (e.g., memory limits, max threads) so one team can’t cause global timeouts.

When sizing for concurrency, start from your peak QPS × average query cost:

• Measure average read_rows, read_bytes, and query_duration_ms in system.query_log for dashboard queries.
• Multiply by peak concurrent users and panels per dashboard.
• Use that to estimate how many cores you need to keep 95th percentile latency well under your dashboard timeout threshold (often 5–30 seconds, but your goal should be seconds or sub-second).

In ClickHouse Cloud, you can adjust service sizes and autoscaling based on these observed metrics; in self-managed deployments, this informs how many nodes and cores you provision.

Common Mistakes to Avoid

• Treating partitioning as a universal speed trick: Using high-cardinality partition keys (like user_id or org_id) explodes the number of partitions and parts, hurts merges, and can actually make queries slower—especially cross-partition queries. Treat partitioning as a lifecycle/management tool first (e.g., daily partitions).
• Ignoring ingest patterns and “too many parts”: Allowing upstream services to send tiny, frequent inserts leads to fragmented storage, constant merges, and “Too many parts” errors. Batch to at least 1,000 rows per insert (ideally 10,000–100,000) to keep MergeTree healthy under concurrent dashboards.

Real-World Example

A team I worked with had a single “events” table backing:

• Product dashboards for PMs
• SRE incident dashboards
• Ad-hoc analysis notebooks for data science

At low concurrency, queries ran in 1–2 seconds. But when an incident hit and half the company opened dashboards, everything started timing out.

What we found in system.query_log:

• Dashboard queries were scanning billions of rows each because the UI defaulted to “Last 30 days” for every panel.
• The ingestion pipeline was writing hundreds of inserts per second, creating tens of thousands of tiny parts in system.parts.
• A large schema change (ALTER TABLE ...) had kicked off a huge mutation that was still running, competing for CPU with queries.

The fix came in layers:

1. Dashboard design: We changed defaults to 1-hour and 24-hour ranges, with explicit controls for longer lookbacks.
2. Schema and ingest: We reworked ingestion to batch 10,000–50,000 rows per insert and confirmed healthier part sizes in system.parts.
3. Workload isolation: We moved ad-hoc and heavy analytical queries to a separate ClickHouse Cloud service, leaving dashboards alone on their own cluster.

After this, peak-incident load went from consistent timeouts to sub-second to low-second responses, even with dozens of concurrent users.

Pro Tip: Before making schema or cluster changes, capture a 30–60 minute slice of system.query_log during a peak period, filter for dashboard users, and group by query shape and read_rows. This gives you a baseline of which dashboards and teams are actually driving concurrency and timeouts—and prevents you from optimizing for the wrong workload.

Summary

When multiple teams hit the same analytics table and dashboards start timing out, the root cause is usually predictable: expensive query patterns running on fragmented data, under concurrency, with no workload isolation. In ClickHouse, the path out is equally systematic—inspect system.query_log, system.parts, system.merges, and system.mutations; fix ingest batching and schema alignment; constrain dashboards to sane time ranges; and, where needed, isolate critical dashboards from heavyweight exploration.

With those pieces in place, you can keep delivering millisecond-to-second query latencies on billions of rows, even when many teams rely on the same ClickHouse-backed analytics tables.

Next Step

Get Started