GitHub user skalva200 created a discussion: Optimize Dashboard Filter
Performance via Precomputed/Extract-based Caching
**Context**
In the current Apache Superset implementation, any alteration to dashboard
filter values (e.g., column dropdowns, date range pickers) modifies the WHERE
clause of the underlying chart queries. This forces Superset to regenerate the
SQL, submit a completely new query to the target data source, and allocate a
fresh cache key in Redis.
While this ensures data accuracy, it creates massive performance and
infrastructure bottlenecks for large enterprise deployments utilizing engines
like Presto, Hive, or Trino.
**The Problem**
1. **Cache Key Explosion & Redundancy**: Because minor changes to a WHERE
clause generate unique SQL strings, Redis is flooded with duplicate/redundant
cache entries for data that is fundamentally unchanged.
2. **High Database Concurrency & Load**: In dashboards containing dozens of
charts, every single filter interaction triggers a wave of heavy queries to the
data source. This significantly degrades database performance and introduces
latency for end-users.
3. **Missed Optimization Opportunities**: If the baseline chart dataset is
already sitting in the cache, Superset cannot currently apply runtime dashboard
filters directly to that cached dataset; it must always round-trip back to the
database.
**Proposed Solution**
We propose implementing a Data Extract and Advanced Caching Mechanism in
Superset—conceptually similar to Tableau Data Extracts—to drastically improve
dashboard interactivity and reduce database footprint.
**Key Capabilities:**
- Precomputed Chart Extracts: Query the source database to cache a broader
dataset for the charts, serving subsequent filtered variations directly from
this high-performance cache layer.
- Asynchronous/Offline Cache Refresh: Utilize background/scheduled Celery jobs
to refresh these data extracts periodically or via data-change triggers.
- Cache-First Interactivity: Ensure charts dynamically render and filter based
on the user's dashboard selections using the precomputed/cached data structure.
- On-Demand Cache Bypass: Provide explicit dashboard controls allowing users to
fetch live data when real-time accuracy is required.
**Implementation Strategy & Technical Breakdown**
1. Cache Layer Integration (Redis)
- Introduce a specialized caching tier storing precomputed chart datasets.
- The cache can be keyed cleanly by Chart ID + Primary Dimensions, or
pre-warmed using the most common filter combinations (e.g., country=Korea).
2. Offline Cache Refresh Job
- Implement scheduled background workers to pre-warm and refresh the cache
during off-peak hours or upon ETL pipeline completion.
- Ensure the pre-warmed cache scope covers the most statistically frequent
filter combinations used by business teams.
3. Chart Rendering & Fallback Logic
- Upon dashboard load, the rendering engine checks for available cached
extracts.
- Cache Hit: Filter logic is applied to the cached slice, rendering the
chart instantly.
- Cache Miss: Fallback gracefully by returning an empty state or prompting
the user to query live data (unless an auto-fetch on miss policy is defined)
4. User Interface & Controls
To ensure data transparency, we propose adding the following UI components to
the dashboard level:
- "Use Cached Data" vs. "Force Live Query" Toggle: A clear interactive switch
for users.
- Cache TTL / Force Execution Dropdown: A control widget allowing power users
to explicitly adjust the query's time-to-live or bypass the cache entirely for
a session.
**Business & Performance Benefits**
- Drastic Reduction in DB Load: Eliminates thousands of repetitive, redundant
queries hitting analytical data warehouses.
- Sub-Second Dashboard Interactivity: Serving filter adjustments directly out
of an in-memory/Redis extract layer will drastically cut down dashboard
rendering times.
- Resource Scalability: Allows concurrent usage of heavy dashboards by hundreds
of business users without risking database timeouts or high cloud
data-warehousing costs..
GitHub link: https://github.com/apache/superset/discussions/41852
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
