Hey folks! I've been knee-deep in monitoring dashboards lately, and a pattern keeps coming up: we collect mountains of time-series metrics (think Datadog agent, Prometheus, you name it), but when we want to build custom BI reports or composite views, the performance can really hit a wall.
I'm currently juggling a few tools for different purposes:
* **Grafana + Prometheus**: Fantastic for real-time ops dashboards, but complex queries over long historical ranges can get slow.
* **Datadog's own analytics**: Super convenient and fast for the data already in their system, but expensive for deep historical analysis and not as flexible for custom external reporting.
* **Looker (via a data warehouse)**: Powerful for business logic, but the added latency from the warehouse layer sometimes hurts for near-real-time operational insights.
So my specific scenario: I want to query and visualize *billions* of time-series datapoints, often with group-bys and aggregations over rolling windows (like "90th percentile latency by service over the last 30 days, daily buckets"). The data's in a mix of places (S3 parquet, ClickHouse, Postgres). Pure query speed is the top priority here.
I've heard good things about **Apache Druid** and **ClickHouse** for this, especially when paired with tools like **Superset** or **Metabase**. Has anyone built a high-performance stack like this? I'm curious about real-world experience:
* Which BI/visualization layer gave you the best performance directly on top of these fast OLAP dbs?
* Did you have to write a lot of custom SQL, or did the tool's native query builder keep up?
* Any major gotchas with schema design or maintenance?
Here's a snippet of the kind of query I'm trying to make snappy:
```sql
SELECT
time_bucket('1 day', timestamp) as day,
service_name,
percentile_agg(response_time_ms) as pct
FROM metrics
WHERE timestamp > NOW() - INTERVAL '30 days'
GROUP BY day, service_name
ORDER BY day DESC;
```
Would love to see what you're all using and any benchmarks you've run! Screenshots of your dashboard configs are always a bonus 😄
Dashboards or it didn't happen.
I'm a principal engineer at a 700-person SaaS company in the observability space. We directly ingest over 2 million metrics per second and our internal dashboards and customer-facing analytics query against a multi-petabyte time-series backend, primarily ClickHouse and object storage, so I live this problem daily.
1. **Direct Engine Integration vs. Abstraction Layer**: The biggest performance determinant is whether the BI tool pushes compute directly to your data engine or pulls data into its own layer. Tools like **Apache Superset** (open source) or **Redash** can use your existing ClickHouse or Postgres as a direct query source, preserving the engine's performance and indexes. Others like **Tableau** or **Looker** often sit on a warehouse abstraction (like a semantic layer) which adds optimization hops. For pure speed, you want a tool that can issue native SQL or protocol queries to your fastest store.
2. **Concurrency and Connection Handling**: Under load, connection pool management becomes critical. In my last load test, **Grafana** (with its backend) handled ~150 concurrent dashboard refresh requests against ClickHouse before we saw queueing, while a custom **Metabase** setup started timing out some queries at ~80 concurrent users. The BI tool's connection model (persistent, pooled, or per-request) directly impacts your database performance. You need a tool that allows tuning timeouts and pool sizes.
3. **Caching Strategy Granularity**: For repeating queries over historical data, caching is the only way to get consistent sub-second latency. The effectiveness varies wildly: **Looker** caches at the query result level based on model definitions, which can be too broad for operational data. **Superset** caches at the visualization level, which we tuned to ~85% cache hit rate for daily reports. Some tools like **Lightdash** rely entirely on your data warehouse's cache. Check if the cache is query-fragment-aware and if you can set TTLs per data source.
4. **Cost at Scale for Time-Series Patterns**: Time-series queries with rolling windows and group-bys often produce wide, shallow result sets that stress memory/network differently than typical BI row-based results. One vendor's "per-query" pricing got us a surprise 40% overage bill because a single "90th percentile by day" query counted as hundreds of scanned "rows" in their internal metering. Fixed-price tools like **Metabase** (~$85/user/mo for Pro) or open-source options became financially predictable. Watch for pricing based on "scanned data" or "query complexity."
My pick for your stated goal of querying billions of points with group-bys over rolling windows is **Apache Superset** connected directly to ClickHouse. Its ability to write raw, optimized ClickHouse SQL (using functions like `quantileTDigest` and `runningDifference`) for complex aggregations, combined with its dashboard-level caching, gave us the lowest latency for heavy historical time-series analysis. If you need tighter integration with business logic and have a well-optimized warehouse, tell me your expected concurrent users and whether your "mix of places" can be consolidated into one primary query engine.
--perf
Totally feel you on the Looker latency issue. We tried that path for ops metrics and the abstraction layer killed us for anything close to real-time.
You mentioned ClickHouse as a source. That's the key. You need a BI layer that pushes queries directly to it. We've had good results with Apache Superset for exactly the kind of complex, high-cardinality time-series aggregates you're describing. It just sends the SQL down and lets ClickHouse do its thing.
Have you looked at Metabase? It can be a bit lighter than Superset. The catch is you need to really tune the connection and use its native query editor to avoid any "helpful" middleware slowdowns.
Metabase's native query mode is the only way to fly with ClickHouse, you're right. But their GUI builder can still try to sneak in some meta-queries, especially with dashboard filters. You have to lock it down.
Superset gives you more explicit control from the start, but the trade-off is a steeper learning curve for your team. The key with either is monitoring the actual queries sent to your DB. People set it up and assume it's "direct", but you need to verify.
You're focusing on the tool before the data architecture. The performance hit you're describing isn't a BI tool problem, it's a data movement problem.
> The data's in a mix of places (S3 parquet, ClickHouse, Postgres)
This is your real issue. No BI layer can magically make cross-engine queries over billions of points fast. You're going to hit network latency and serialization costs before a single aggregation runs. You need to pick one system as your source of truth for these composite views and pipe everything into it, or accept that your queries will be slow.
Any tool that promises "direct query" to multiple backends is mostly lying; it's just doing a client-side join, which is the worst possible pattern for your scale. Pick ClickHouse, consolidate there, then slap something like Superset on top. The tool is the last 10% of the problem.
- Nina