Database Performance Tuning and Scaling Questions

Situation: As a BI analyst building dashboards for executives and operations teams, choosing where to cache influences freshness, latency, and cost.

1. Client / Browser cache

• Pros: Lowest latency, less server load, good for repetitive interactions (sorting/filtering) on the same dataset; works offline for short periods.
• Cons: Limited size, inconsistent between users, stale quickly, security concerns if sensitive.
• When to use: Executive dashboards updated hourly where users navigate the same view within a session (cache HTTP responses or localStorage for 5–60 minutes).
• Invalidation: Short TTLs (e.g., 30–60m) and cache-busting on published report version changes.

2. Application-level cache (Redis)

• Pros: Fast, central, supports TTLs, eviction policies, and complex keys (user+filters); reduces DB load and allows shared cache across instances.
• Cons: Extra infrastructure and complexity; stale data if not invalidated correctly.
• When to use: High-concurrency dashboards with expensive aggregations (daily/hourly reports) or precomputed KPIs reused across dashboards.
• Invalidation: Time-based TTLs aligned with update cadence (hourly: TTL ~55m), and event-driven invalidation on ETL completion or publish hooks.

3. Database query cache / Materialized views

• Pros: Offloads work from runtime queries, consistent across users, can pre-aggregate huge datasets; materialized views can be refreshed incrementally.
• Cons: Storage cost, longer refresh times, heavy writes/refresh impact.
• When to use: Complex aggregates over massive tables (sales rollups, cohort metrics); use materialized views refreshed hourly or incremental.
• Invalidation: For hourly dashboards, schedule full or incremental refresh right after ETL finishes. For near-real-time needs, use incremental/materialized views with streaming (CDC) or low-latency partial refresh + short-lived application cache for final routing.

General strategy:

• Use multi-tier caching: materialized views for heavy aggregation + Redis for session/filter-level caching + browser cache for UX snappiness.
• For hourly updates: coordinate ETL → refresh DB/materialized view → purge/update app cache → rely on browser TTLs.
• For near-real-time: prefer streaming/incremental DB updates + short TTLs (seconds–minutes) in Redis, and disable long browser TTLs; consider websocket/push to notify clients to refresh when critical.

Requirements & intent:

• Detect if nightly ETL introduces a meaningful regression in query latency for downstream dashboards/queries, notify owner with runbook and reduce alert noise so BI & infra can act quickly.

Metrics to collect:

• Per-query and per-dashboard: duration histogram (Prometheus histogram or OpenTelemetry), sample count, success/error counts.
• Aggregates per query_name / dataset / service / env / ETL_build_id.
• Key percentiles: p50, p95, p99; mean and median; tail counts > SLO (e.g., >2s).
• Throughput (QPS) and concurrency to normalize.
• ETL markers: etl_run_id, start/end timestamps, job status.

Baseline computation:

• Use rolling historical baseline with time-of-day/day-of-week awareness: maintain 28-day window of same-hour samples and compute median and percentiles (p95,p99) and their interquartile ranges (IQR).
• For immediate post-run comparison, compute a short-term window (last 30–60 minutes) and compare to baseline for the same hour-of-day and weekday pattern to avoid diurnal noise.

Anomaly-detection technique:

• Two-stage detection:
  1. Statistical significance: compare post-ETL short-term metric distributions to baseline using robust Z-score on log-latency (z = (log(current_p95)-log(baseline_p95))/std) OR bootstrap to compute p-value for percentile shift. Flag if p < 0.01.
  2. Practical impact test: require absolute relative increase threshold (e.g., current_p95 >= baseline_p95 * 1.25) AND absolute increase >= 200ms (configurable per query).
• Complement with EWMA on percentiles to catch gradual drifts and a count-based check (increase in queries exceeding SLO by >X%).

Alert thresholds & severity:

• WARNING: 15–25% increase in p95 vs baseline OR p95 breach of soft SLO for 2 consecutive ETL runs.
• CRITICAL: >=25% increase AND statistically significant OR absolute p99 breach of hard SLO OR user-facing error-rate spike.
• Suppress low-impact alerts by minimum-sample checks (require >=30 samples in comparison window) and by prioritizing high-value queries (top N by QPS or business criticality tag).

Automatic metadata on alerts:

• Attach labels: query_name, dashboard_id, dataset, etl_run_id, region, environment.
• Include runbook link, owner(s) (on-call BI engineer or data platform owner), suggested next steps (verify ETL job status, check indexes/materialized views, check upstream schema changes), and recent baseline metrics snapshot.
• Auto-create a ticket in tracking system with pre-filled title/body when critical.

Noise reduction & surfacing meaningful alerts:

• Group alerts by root cause tags (same etl_run_id, same dataset) to avoid per-query spam—send one grouped alert with top-affected items.
• Rate-limit repeat alerts per etl_run_id; escalate only if persisted across multiple runs.
• Only alert during a post-ETL comparison window (e.g., first 2 hours after ETL). For non-ETL regressions, use separate pipeline.
• Apply dynamic suppression: mute alerts for known maintenance windows or when ETL job is in failed state causing expected downstream impacts.
• Provide a Grafana dashboard panel showing baseline vs current distributions, top affected queries, and drill-down links—allow on-call to triage from alert quickly.

Implementation notes:

• Collect metrics via app instrumentation + exporter to Prometheus; use Prometheus recording rules for percentile baselines and Grafana for visualizations.
• Use a small service or orchestration (Airflow task) that, post-ETL, computes comparisons, runs bootstrap/z-tests, and pushes alerts to PagerDuty/Slack/Jira with metadata.
• Monitor alert quality and tune thresholds per-query by labeling importance and measuring false-positive rate over time.

This design balances statistical rigor, business impact, and practical heuristics to surface actionable ETL-induced latency regressions with minimal noise.

Requirements & constraints:

• Protect SLAs for most tenants while supporting occasional heavy analytics; low tail-latency for dashboards; cost conscious.
• Support ad-hoc cross-tenant analytics (audit, BI) without risking production isolation.

High-level strategy:

1. Sharding policy (primary): shard-by-tenant by default, with shard-by-hash for very small tenants.

• Shard-by-tenant: place each tenant’s data and query routing on a specific shard/compute pool — gives strong isolation, simple quotas, easier to move heavy tenants.
• Shard-by-hash: pack many small tenants into shards by consistent hashing to maximize utilization; apply per-tenant limits inside shard.

2. Resource isolation & quotas:

• Enforce CPU, memory, concurrent-query, and I/O quotas per tenant at gateway/proxy (query limiter) and at DB/container level (cgroups/Kubernetes resource limits).
• Token-bucket rate limiting for queries + priority classes (interactive dashboards get higher priority than long batch jobs).
• Query cost-estimation: pre-check planner estimates cost and reject or schedule heavy queries.

3. Heavy tenants → dedicated clusters:

• Monitor tenant metrics (qps, avg cost, 95/99 latency, storage). Threshold triggers move to dedicated cluster or dedicated node-pool. Use automated migration: snapshot/replica + DNS routing.
• Dedicated clusters can be autoscaled and have bespoke SLAs/billing.

4. Cross-tenant analytical queries:

• Disallow cross-tenant joins on production shards. Provide a controlled path:
  • Export data to an isolated analytics workspace (data lake/warehouse) via ETL with sanitized keys.
  • Run cross-tenant heavy analytics in separate compute (adhoc cluster) with its own quotas and cost accounting.
  • For safe light queries, use a federated query layer that executes per-tenant fragments and aggregates results, respecting per-tenant quotas.

Operational considerations:

• Observability: per-tenant tracing, cost attribution, alerting.
• Backpressure & graceful degradation: queue low-priority jobs, return meaningful errors when quotas hit.
• Trade-offs: shard-by-tenant increases spare capacity needs; shard-by-hash gains utilization but reduces strict isolation. Balanced hybrid approach minimizes cost while protecting SLAs.

Many-to-many joins and very large dimension joins hurt interactive dashboard performance because they multiply rows, increase intermediate shuffles, and force the query engine to do heavy aggregation and deduplication. This raises CPU, memory, and I/O, increases latency, and can break optimizers (causing broad hash joins or expensive sorts).

Practical mitigation strategies:

• Pre-aggregation: Materialize commonly used rollups (daily/user/product aggregates) in a table or materialized view so dashboards query tiny summarized tables instead of raw events. Example: compute daily active users per product nightly.

• Denormalization: Store frequently-required attributes together (copy customer attributes into events) to avoid repeated joins. Trade-off: storage and ETL complexity, but huge read-time savings.

• Star schema: Model facts (event metric table) with narrow, high-cardinality dimensions connected via foreign keys. This simplifies JOIN patterns and enables efficient star-join optimizations in many engines.

• Selective predicates: Push filters early (date range, product id, region). Encourage dashboard defaults (last 30 days) and parameterized queries to limit scanned data.

• Bloom filters / semi-joins: Use bloom-filtered joins or broadcast small dimension tables so the engine filters large fact tables before full join. In engines like Spark/BigQuery, enable broadcast joins when dimension size is small.

Operational tips: profile slow queries, add explain plans, create indexed partitioning (by date), and prioritize pre-aggregation for metrics that drive interactivity (KPIs, top-N). Combined, these approaches reduce scanned rows and intermediate work, converting multi-second queries into sub-second dashboard interactions.

Requirements & constraints:

• Dashboard freshness: <=10 minutes
• Input: ~1 TB/day raw events (~11.6k MB/s peak), late arrivals possible
• Low query latency for BI tools (Tableau/Looker/Power BI)

High-level design:

• Build near-real-time aggregated tables (materialized views) at multiple granularities: 10-min (primary), 1-hour, daily rollups.
• Ingest raw events via streaming (Kafka/Kinesis) or micro-batch landing into a staging zone (cloud object store or streaming table).
• Use a scheduler (Airflow/DB-native tasks) every 1–5 minutes to compute increments for the latest 10-min window(s) and write into partitioned/clustered aggregate tables.

Partitioning & partition-level refresh:

• Partition aggregates by event_date and event_10min_bucket (e.g., YYYYMMDD_HHMM rounded down to 10m). This enables replacing individual 10-min partitions without touching others.
• For throughput efficiency, group small recent partitions (last 1–6 windows) into a “hot” partitioning strategy if the DB penalizes many tiny partitions; otherwise keep strict 10-min partitions.
• Partition refresh: when new raw data arrives for window T, compute delta-aggregate for T and perform atomic upsert/replace of partition T (CTAS to temp then atomic swap).

Incremental refresh strategy:

• Use append-only ingestion + offsets (stream position or max(event_time)) to identify new data since last run.
• Compute incremental aggregates using windowed GROUP BY on only new or changed events; for late-arriving events run a reprocess for affected recent partitions (watermark and a “reprocess window” e.g., last 60 minutes).
• For correctness, maintain idempotent upsert semantics (use MERGE or partition replace) and store last-processed offsets/checkpoints.

Storage and trade-offs:

• Storage cost vs query latency:
  • Full pre-aggregation at many dimensions increases storage but gives fastest BI queries.
  • Keep sparse/necessary dimensional combinations and push ad-hoc joins to compute layer.
• Compression and columnar formats (Parquet/ORC) reduce size.
• Retention: keep high-resolution 10-min partitions for recent period (e.g., 30–90 days), roll older data into hourly/daily rollups to save space.
• If DB charges per partition/file, balance partition granularity accordingly.

BI tool caching interaction:

• Use BI caching to reduce load: set cache TTL slightly > processing interval (e.g., 10–12 minutes) or trigger cache invalidation after partition swap.
• Best pattern: dashboards connect to pre-aggregated tables (fast) with BI set to auto-refresh every 10 minutes or on-demand extracts scheduled slightly after materialized view refresh to avoid stale reads.
• For low-latency live dashboards, use direct/live connection to aggregates; for heavy ad-hoc users, provide extracts with scheduled refreshes and incremental extract logic.

Operational concerns:

• Monitor lag, processing failures, and reprocess queue for late data.
• Provide SLA: e.g., 95th percentile freshness <=10 minutes, with alerts when reprocessing backlog > N windows.
• Backfill tooling for schema changes or historical recompute.

Trade-offs summary:

• More pre-aggregation = faster dashboards, higher storage & ETL complexity.
• Finer partitions = precise incremental refresh but more metadata/management.
• BI caching reduces DB load but adds another freshness layer to coordinate—coordinate cache TTL/invalidation with ETL schedule.

This approach gives predictable 10-minute freshness, efficient incremental updates via partition swaps, controlled storage growth via rollups/retention, and clear coordination with BI caching for consistent end-user experience.