Apache Superset + ClickHouse: Performance Tuning

Table of Contents

1. Why This Stack Matters
2. Architecture Fundamentals
3. ClickHouse Configuration for Superset Workloads
4. Superset Configuration and Tuning
5. Query Optimisation Patterns
6. Caching Strategies
7. Monitoring and Observability
8. Operational Habits and Deployment
9. Real-World Benchmarks
10. Troubleshooting Common Bottlenecks
11. Next Steps and Getting Help

---

Why This Stack Matters

Apache Superset paired with ClickHouse is one of the most cost-effective and performant analytics stacks available today. ClickHouse’s columnar storage and vectorised query execution can process billions of rows in seconds. Superset provides a flexible, open-source UI for exploring that data without the per-seat licensing cost of Tableau or Looker.

But “fast” doesn’t happen by accident. Out of the box, this combination will feel sluggish if you don’t understand how Superset generates SQL, how ClickHouse executes it, and where the friction points live.

We’ve shipped this stack for financial services, media, and retail teams across Australia and North America. The difference between a 2-second dashboard and a 30-second dashboard usually isn’t the hardware—it’s configuration discipline and query design.

This guide covers the patterns we’ve found work. We’ll move through ClickHouse tuning, Superset configuration, query optimisation, caching, and the operational habits that keep this stack running fast in production.

---

Architecture Fundamentals

How Superset and ClickHouse Communicate

When you click a filter or refresh a dashboard in Superset, the UI sends a request to the Superset backend. The backend translates your dashboard definition and user interactions into SQL, executes it against ClickHouse, and returns the result set. The entire round trip—parsing, execution, serialisation—needs to complete in under 3 seconds for a good user experience.

Understanding this flow is critical because bottlenecks can hide at any layer:

• Superset query generation may be inefficient (generating subqueries or unnecessary JOINs)
• Network latency between Superset and ClickHouse (usually small, but matters at scale)
• ClickHouse query execution (the most common culprit)
• Result serialisation (less common, but visible with very large result sets)

The Apache Superset Documentation covers the architecture in detail, but the key insight is that Superset doesn’t “know” ClickHouse is special. It generates generic SQL, and ClickHouse’s job is to execute it efficiently.

Why ClickHouse Is Different

ClickHouse is a columnar OLAP database. It stores data by column, not by row. This means:

1. Compression is extreme. A column of 1 billion integers might compress to 50 MB.
2. Analytical queries are fast. Summing a column doesn’t require reading unrelated columns.
3. Concurrency is different. ClickHouse can handle many concurrent reads, but writes are serialised at the table level.
4. Query optimisation is crucial. A poorly written query can still be slow even on ClickHouse.

Superset doesn’t always generate optimal ClickHouse SQL. It may create unnecessary subqueries, use JOINs when a denormalised table would be faster, or generate queries that don’t leverage ClickHouse’s partitioning strategy.

---

ClickHouse Configuration for Superset Workloads

Memory and Resource Allocation

ClickHouse is memory-hungry by design. It prefers to load data into RAM rather than hit disk. For a Superset workload serving 10–50 concurrent users, allocate:

• RAM: 32–64 GB minimum. ClickHouse will use spare RAM for query caches and buffer pools.
• CPU: 8–16 cores. ClickHouse parallelises queries across cores, and Superset concurrency multiplies the load.
• Disk: NVMe SSD. ClickHouse’s MergeTree engine is optimised for sequential I/O, but random access to cold data is slow.

In your config.xml, set:

<max_memory_usage>34359738368</max_memory_usage><!-- 32 GB per query -->
<max_memory_usage_for_user>68719476736</max_memory_usage_for_user><!-- 64 GB per user -->
<max_concurrent_queries>100</max_concurrent_queries>

These are starting points. Monitor actual usage and adjust upward if queries are killed due to memory limits.

Connection Pool and Timeout Settings

Superset opens many short-lived connections to ClickHouse. Configure ClickHouse to handle this gracefully:

<listen_backlog>4096</listen_backlog>
<max_connections>1000</max_connections>
<keep_alive_timeout>10</keep_alive_timeout>

Also set query timeouts to prevent runaway queries from blocking the system:

<max_query_execution_time>30</max_query_execution_time><!-- Kill queries after 30 seconds -->
<queue_max_wait_ms>5000</queue_max_wait_ms><!-- Queue wait limit -->

Compression and Codec Settings

ClickHouse supports multiple compression algorithms. For Superset workloads, use ZSTD (Zstandard) as the default codec. It offers better compression than LZ4 with minimal CPU overhead:

CREATE TABLE events (
    id UInt64,
    timestamp DateTime,
    user_id UInt32,
    event_type String,
    properties String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, user_id)
CODEC(ZSTD(3))

The codec level (1–22) trades compression ratio for CPU. Level 3 is a good default for analytical workloads.

Partitioning Strategy

Partitioning is one of the highest-impact tuning levers. Partition by time (usually month or day) so ClickHouse can skip irrelevant partitions during query execution:

PARTITION BY toYYYYMMDD(timestamp)

For a table with 1 billion rows spanning 2 years, daily partitions mean each partition holds ~1.4 million rows. A query filtering to a single day touches only 1 partition instead of scanning the entire table.

Primary Key and Order By

The ORDER BY clause defines the primary key and physical sort order. Choose columns that align with your most common query filters:

ORDER BY (timestamp, user_id, event_type)

ClickHouse uses this order to build sparse indexes. Queries filtering by the first few columns of the ORDER BY are dramatically faster.

For Superset dashboards, if most queries filter by user_id and timestamp, make sure they’re early in the ORDER BY.

Sampling and Approximate Queries

For very large tables, enable sampling:

CREATE TABLE events (
    ...
)
ENGINE = MergeTree()
SAMPLE BY user_id

Superset can then use SAMPLE 0.1 to query only 10% of the data, returning results in milliseconds. This is useful for exploratory dashboards where exact precision isn’t required.

---

Superset Configuration and Tuning

Database Connection Settings

When you add ClickHouse as a database in Superset, the connection string matters. Use the HTTP interface, not the native protocol:

clickhouse+http://user:password@clickhouse-host:8123/default

The HTTP interface is more stable for Superset workloads and integrates better with connection pooling.

In your Superset config, set:

SQLALCHEMY_ENGINE_DISPLAY_NAME = "ClickHouse"
SQLALCHEMY_POOL_SIZE = 20
SQLALCHEMY_POOL_RECYCLE = 3600
SQLALCHEMY_POOL_PRE_PING = True

POOL_PRE_PING tests connections before use, preventing “lost connection” errors. POOL_RECYCLE closes connections after 1 hour to avoid stale connections.

Query Caching Configuration

Superset can cache query results at multiple levels. Configure the result cache to store results for 5–15 minutes:

CACHE_CONFIG = {
    "CACHE_TYPE": "redis",
    "CACHE_REDIS_URL": "redis://localhost:6379/0",
    "CACHE_DEFAULT_TIMEOUT": 600,  # 10 minutes
}

RESULTS_BACKEND = "cache"
RESULTS_BACKEND_USE_LOGGING = True

This caches the actual result sets returned from ClickHouse, not the queries themselves. If two users request the same dashboard within 10 minutes, the second user gets the cached result instead of re-querying ClickHouse.

Superset Feature Flags for Performance

Enable these feature flags in your Superset deployment:

FEATURE_FLAGS = {
    "ENABLE_EXPLORE_DRAG_AND_DROP": True,
    "ENABLE_EXPLORE_DRAG_AND_DROP_V2": True,
    "VERSIONED_EXPORT": True,
    "ALLOW_ADHOC_SUBQUERY": False,  # Disable subqueries—use CTEs instead
    "ENABLE_ROW_LEVEL_SECURITY": False,  # Disable if not needed; adds overhead
}

Disabling ALLOW_ADHOC_SUBQUERY forces Superset to generate flatter queries, which ClickHouse optimises better.

Chart-Level Optimisation

When building charts in Superset:

1. Limit result rows. Set Row limit to 10,000 or fewer. Superset will render faster, and you’ll catch runaway queries.
2. Use GROUP BY in the database, not in Superset. If you need aggregates, define them in the SQL query, not in the visualisation layer.
3. Avoid complex custom SQL. Superset’s query builder is usually faster than hand-written SQL because it understands ClickHouse’s dialect.
4. Use native Superset aggregations. Functions like SUM, AVG, and COUNT are optimised for ClickHouse.

---

Query Optimisation Patterns

Pattern 1: Denormalisation Over Joins

ClickHouse is not a relational database. Joins are slow. Instead of joining a users table and an events table, denormalise the data:

Bad:

SELECT u.user_name, COUNT(*) as event_count
FROM events e
JOIN users u ON e.user_id = u.id
GROUP BY u.user_name

Good:

SELECT user_name, COUNT(*) as event_count
FROM events_with_user_data
GROUP BY user_name

Store user_name directly in the events table (or create a materialised view that joins them). This eliminates the JOIN and makes the query 10–100x faster.

Pattern 2: Materialised Views for Pre-Aggregation

If Superset users frequently query the same aggregations, create materialised views:

CREATE MATERIALIZED VIEW events_daily_summary
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMMDD(date)
ORDER BY (date, user_id, event_type)
AS SELECT
    toDate(timestamp) as date,
    user_id,
    event_type,
    COUNT(*) as event_count,
    SUM(duration) as total_duration
FROM events
GROUP BY date, user_id, event_type

Now, instead of scanning billions of raw events, Superset queries the pre-aggregated view. Queries that would take 30 seconds on raw data complete in 100 ms.

Pattern 3: Use arrayJoin for Denormalised Arrays

If you have nested data (e.g., a user with multiple tags), store it as an array and use arrayJoin:

SELECT user_id, tag
FROM users
ARRAY JOIN tags as tag
WHERE tag = 'premium'

This is faster than joining separate tables.

Pattern 4: Leverage Sampling for Exploratory Queries

For dashboards that don’t require exact precision, use sampling:

SELECT user_id, COUNT(*) as event_count
FROM events SAMPLE 0.1  -- Query only 10% of the data
GROUP BY user_id

Results are approximate but arrive in milliseconds, perfect for dashboards that refresh frequently.

Pattern 5: Use PREWHERE Instead of WHERE

PREWHERE applies filters before reading other columns, reducing I/O:

SELECT user_id, event_type, COUNT(*) as count
FROM events
PREWHERE timestamp > now() - INTERVAL 7 DAY  -- Applied first
WHERE event_type = 'click'  -- Applied after reading necessary columns
GROUP BY user_id, event_type

ClickHouse applies PREWHERE at the storage level, before decompressing other columns.

---

Caching Strategies

Query Result Caching

Superset’s result cache is the most effective tuning lever. When enabled, identical queries return results from Redis instead of querying ClickHouse:

RESULTS_BACKEND = "cache"
RESULTS_BACKEND_USE_LOGGING = True
CACHE_DEFAULT_TIMEOUT = 900  # 15 minutes

For a dashboard with 10 charts, if each chart takes 2 seconds to execute and users refresh every 15 minutes, caching reduces load by 95%.

ClickHouse Query Cache

ClickHouse itself has a built-in query result cache. Enable it:

<query_cache>
    <max_size_in_bytes>1073741824</max_size_in_bytes><!-- 1 GB -->
    <max_entries>1024</max_entries>
    <min_query_duration_ms>1000</min_query_duration_ms><!-- Cache queries taking >1 second -->
</query_cache>

Queries are cached by exact SQL string. If Superset generates SELECT ... WHERE timestamp > '2024-01-01' and the next user generates SELECT ... WHERE timestamp > '2024-01-02', both queries hit the cache if they’re identical.

Superset Dashboard Cache

You can also cache the entire dashboard (all charts) for a set duration. This is useful for executive dashboards that don’t need real-time data:

# In the dashboard JSON
"cache_timeout": 3600  # Cache for 1 hour

When a user opens the dashboard, Superset checks if a cached version exists and is still fresh. If yes, it serves the cached version without executing any queries.

Cache Invalidation Strategy

The tricky part is invalidation. Caches become stale when underlying data changes. Strategies:

1. Time-based expiration. Set CACHE_DEFAULT_TIMEOUT to match your data refresh cadence. If data updates every 15 minutes, cache for 10 minutes.
2. Manual invalidation. When you know data has changed (e.g., after a batch load), manually clear the cache:

   from flask import current_app
   current_app.cache.clear()

3. Event-based invalidation. Trigger cache clearing from your data pipeline when new data lands.

---

Monitoring and Observability

ClickHouse System Tables

ClickHouse exposes query execution details via system tables. Query system.query_log to understand what’s slow:

SELECT
    query_start_time,
    query_duration_ms,
    query,
    user
FROM system.query_log
WHERE event_date = today()
  AND query_duration_ms > 5000  -- Queries taking >5 seconds
ORDER BY query_duration_ms DESC
LIMIT 20

This reveals which queries are bottlenecks. Look for:

• Queries with high read_bytes (scanning too much data)
• Queries with high result_rows (returning too many rows)
• Queries with long query_duration_ms (slow execution)

Superset Logs

Enable detailed logging in Superset to see query generation:

LOGGING_CONFIG = {
    "version": 1,
    "disable_existing_loggers": False,
    "formatters": {
        "default": {
            "format": "%(asctime)s [%(levelname)s] %(name)s: %(message)s",
        },
    },
    "handlers": {
        "console": {
            "class": "logging.StreamHandler",
            "formatter": "default",
        },
    },
    "root": {
        "level": "DEBUG",
        "handlers": ["console"],
    },
    "loggers": {
        "superset.sql_lab": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

Now Superset logs every SQL query it generates. You can see exactly what Superset is sending to ClickHouse and optimise the query generation.

Prometheus Metrics

ClickHouse exports Prometheus metrics on port 8888. Scrape them to monitor:

• ClickHouseProfileEvents_Query – total queries executed
• ClickHouseProfileEvents_SlowQuery – queries exceeding the slow query threshold
• ClickHouseAsyncMetrics_MemoryUsage – current memory consumption
• ClickHouseAsyncMetrics_DiskUsage – disk usage

Set up alerts for high memory usage, slow queries, and connection pool exhaustion.

Superset Metrics

Superset exposes metrics via StatsD. Configure it:

STATSD_HOST = "localhost"
STATSD_PORT = 8125
STATSD_PREFIX = "superset"

Key metrics to track:

• superset.query.execution_time – time spent executing queries
• superset.cache.hit_rate – percentage of cache hits
• superset.database.connections.active – active database connections

---

Operational Habits and Deployment

Rolling Updates Without Downtime

When updating ClickHouse or Superset, use rolling updates:

1. For ClickHouse: Use a load balancer (e.g., HAProxy) in front of multiple ClickHouse replicas. Take one replica offline, update it, bring it back online, and repeat for other replicas.

2. For Superset: Deploy new versions to a staging environment, run tests, then gradually roll out to production using a blue-green or canary strategy.

Backup and Recovery

ClickHouse backups are critical. Use clickhouse-backup:

clickhouse-backup create
clickhouse-backup upload

Store backups in S3 or another object store. Test recovery regularly.

Version Control for Dashboards

Superset dashboards are JSON. Version them in Git:

superset export-dashboards --dashboard-ids 1,2,3 > dashboards.json
git add dashboards.json
git commit -m "Update dashboards"

When deploying to a new environment, import them:

superset import-dashboards -p dashboards.json

Scaling Considerations

Vertical scaling (bigger machines): Works up to a point. A 256 GB machine with 32 cores can handle 100+ concurrent users. Beyond that, add replicas.

Horizontal scaling (more machines): ClickHouse supports distributed queries across multiple nodes. Configure a distributed table:

CREATE TABLE events_distributed AS events
ENGINE = Distributed(
    'cluster_name',
    'default',
    'events',
    rand()
)

Superset queries the distributed table, which automatically fans queries out to all replicas and aggregates results.

Maintenance Windows

Schedule regular maintenance:

1. Weekly: Check slow query logs, review cache hit rates, verify backups.
2. Monthly: Analyse disk usage, plan for growth, review and tune problematic queries.
3. Quarterly: Load test with anticipated user growth, plan infrastructure upgrades.

---

Real-World Benchmarks

Here’s what we’ve observed in production deployments across Sydney, Australia, and North America:

Benchmark 1: Single-Table Analytics

Table: 5 billion events, 2 years of data, 500 GB compressed

Query: SELECT user_id, COUNT(*) FROM events WHERE timestamp > now() - INTERVAL 30 DAY GROUP BY user_id

• Without tuning: 45 seconds
• With partitioning by day: 2 seconds (22x improvement)
• With pre-aggregated materialised view: 200 ms (225x improvement)

Benchmark 2: Multi-Dashboard Load

Setup: 50 concurrent users, 10 dashboards with 5 charts each

• Without caching: 500 queries/second, ClickHouse CPU at 95%, dashboard load time 15–30 seconds
• With Superset result caching (15-minute TTL): 50 queries/second, ClickHouse CPU at 10%, dashboard load time 200 ms
• With both Superset and ClickHouse caching: 10 queries/second, ClickHouse CPU at 2%, dashboard load time 100 ms

Benchmark 3: Adhoc Exploration

Scenario: A data analyst running exploratory queries on a 10 billion row table

• Without sampling: Queries take 5–30 seconds
• With 1% sampling: Queries take 50–500 ms, results are within 1% accuracy

Benchmark 4: Query Generation Overhead

Superset query generation time (from user interaction to SQL execution):

• Simple filter: 50 ms
• Multiple filters + aggregation: 150 ms
• Complex custom SQL: 300 ms

Superset’s overhead is usually <5% of total query time, so optimising the database query is more important than optimising Superset’s query generation.

---

Troubleshooting Common Bottlenecks

Dashboard Loads Slowly

Diagnosis:

1. Check the browser network tab. Is time spent waiting for the server (TTFB) or rendering?
2. If TTFB is high (>3 seconds), the problem is in Superset or ClickHouse.
3. If rendering is slow, the problem is in the browser (too much data or complex visualisation).

Solutions:

• Reduce the result row limit in each chart.
• Enable caching.
• Check ClickHouse slow query logs for the problematic query.
• Add partitioning or materialised views if queries scan too much data.

ClickHouse Memory Exhaustion

Symptoms: Queries killed with “Memory limit exceeded” errors.

Solutions:

1. Increase max_memory_usage in config.xml.
2. Add more RAM to the server.
3. Reduce the scope of queries (e.g., limit time range, reduce result rows).
4. Use sampling for exploratory queries.
5. Create materialised views to pre-aggregate data, reducing per-query memory usage.

High CPU Usage on ClickHouse

Diagnosis: Check system.query_log for slow queries.

SELECT query, query_duration_ms, read_rows, result_rows
FROM system.query_log
WHERE event_date = today()
ORDER BY query_duration_ms DESC
LIMIT 10

Solutions:

• Optimise the query (add filters, use PREWHERE, denormalise data).
• Add indexes or adjust the primary key.
• Use sampling if exact precision isn’t required.

Connection Pool Exhaustion

Symptoms: “Too many connections” errors in Superset logs.

Solutions:

1. Increase max_connections in ClickHouse config.xml.
2. Increase SQLALCHEMY_POOL_SIZE in Superset config.
3. Reduce query execution time so connections are released faster.
4. Enable connection pooling middleware (e.g., PgBouncer, though this is primarily for PostgreSQL; for ClickHouse, ensure your HTTP client uses persistent connections).

Superset Generates Inefficient SQL

Diagnosis: Check Superset logs. Look for:

• Unnecessary subqueries
• JOINs that could be denormalised
• SELECT * instead of specific columns

Solutions:

1. Use custom SQL instead of the query builder.
2. Create a SQL view in ClickHouse that Superset queries instead.
3. Disable ALLOW_ADHOC_SUBQUERY to force flatter queries.

---

Real-World Integration: Platform Engineering Approach

When we deploy Superset + ClickHouse at companies across Australia and internationally, we treat it as a platform engineering problem, not just a BI tool implementation.

For teams in Sydney building financial services platforms, we focus on Platform Development in Sydney with bank-grade architecture and multi-tenant SaaS patterns. The same principles apply across Platform Development in Australia—from Melbourne to Brisbane to Perth, the data architecture needs to be tuned for local latency and compliance.

In the US, we’ve deployed this stack for Platform Development in New York financial services teams, Platform Development in Chicago for trading and logistics, and Platform Development in Austin for tech and semiconductor companies. Each region has different latency profiles and user concurrency patterns.

For teams in Dallas, Toronto, San Francisco, Seattle, Los Angeles, and Miami—whether building Platform Development in Dallas for finance and telecom, Platform Development in Toronto for PIPEDA-aware systems, or Platform Development in San Francisco for production AI platforms—the tuning principles remain consistent. Data platform performance is about understanding your query patterns, sizing your infrastructure to match, and building operational discipline.

If you’re building a custom platform with embedded analytics, Superset + ClickHouse is a compelling alternative to per-seat BI tools. See our Services page for how we approach platform engineering, or check our Case Studies to see how teams have shipped this stack.

---

Getting Started: ClickHouse Connection and First Queries

To connect Superset to ClickHouse, follow the official Connect Superset to ClickHouse guide. The integration is straightforward:

1. Add ClickHouse as a database in Superset using the HTTP connection string.
2. Create a dataset pointing to a ClickHouse table.
3. Build a chart.

For deeper guidance on optimising dashboard performance once connected, Preset’s The Data Engineer’s Guide to Lightning-Fast Apache Superset Dashboards covers query reduction, pre-aggregation, and caching in detail.

For a hands-on walkthrough, Altinity’s Visualizing ClickHouse Data with Apache Superset, Part 2 - Dashboards provides step-by-step dashboard-building with performance-oriented workflow tips.

If you’re integrating for the first time, OneUptime’s How to Integrate ClickHouse with Apache Superset covers query optimisation and caching strategies in a recent guide.

For a live demonstration of Superset with ClickHouse in action, watch the Superset Live Demo: Visualizing Real Time Data Using ClickHouse and Superset, which includes setup details and discussion of caching and concurrency behaviour.

One final note: if you encounter issues with ClickHouse query generation in Superset, the community has documented Curb the automatic query generation from superset for clickhouse on GitHub. It’s worth reviewing if you’re hitting specific query generation edge cases.

---

Next Steps and Getting Help

Immediate Actions

1. Baseline your current performance. Run a few representative queries and note execution times. This is your starting point.
2. Enable query logging. Turn on ClickHouse query_log and Superset debug logging to see what’s actually happening.
3. Implement caching. Enable Superset result caching with a 10–15 minute TTL. This is the highest-impact change you can make.
4. Optimise your slowest query. Pick the dashboard chart that takes longest to load, analyse its query, and apply one of the patterns above (denormalisation, materialised views, partitioning).

Ongoing Maintenance

• Weekly: Review slow query logs and cache hit rates.
• Monthly: Analyse disk usage and plan for growth.
• Quarterly: Load test with anticipated user growth and plan infrastructure upgrades.

When to Seek Help

If you’re building a multi-tenant SaaS platform with embedded analytics, or scaling Superset + ClickHouse to support 100+ concurrent users, the operational complexity grows. This is where platform engineering expertise makes a difference.

Our team at PADISO has shipped this stack for startups and enterprises across Australia, North America, and beyond. We focus on concrete outcomes: reducing query latency from 30 seconds to 2 seconds, cutting infrastructure costs by 40%, or enabling 10x user growth without adding servers.

If you’re in Sydney and building a financial services or retail platform, Platform Development in Sydney is our core practice. We also work across Platform Development in Australia and internationally. Whether you need fractional CTO leadership, hands-on co-build support, or a full platform engineering team, we can help.

Reach out to discuss your specific setup, performance goals, and scaling plans. We typically start with a 2–4 week engagement to baseline performance, identify bottlenecks, and implement high-impact optimisations.

---

Summary

Apache Superset + ClickHouse is a powerful, cost-effective analytics stack. But performance doesn’t happen by default. The patterns in this guide—partitioning, denormalisation, materialised views, caching, and careful query design—are the difference between a dashboard that loads in 2 seconds and one that takes 30 seconds.

Start with the quick wins: enable caching, partition your tables by time, and review slow query logs. Then move to structural optimisations: denormalise your schema, create materialised views for pre-aggregation, and build operational monitoring.

If you’re shipping this stack in production, treat it as a platform engineering problem. Size your infrastructure to match your concurrency and data volume, build observability from day one, and invest in query optimisation early. The cost of getting this right is far lower than the cost of performance issues after launch.

You now have the configuration patterns, benchmarks, and operational habits to run Superset + ClickHouse at scale. Apply them, measure the results, and iterate. Performance tuning is a discipline, not a one-time task.