Database Performance Tuning and Scaling Questions

Addresses database system level performance and scaling strategies and how they interact with query design. Candidates should describe approaches for identifying and resolving database level bottlenecks including slow query diagnosis using logs and profiling instrumenting metrics and establishing baselines and targets for latency and throughput. Topics include caching strategies materialized views partitioning and sharding replication and read replica architectures connection management and improving cache utilization as well as trade offs when denormalizing schema or adopting alternative data models. Candidates should be able to propose step by step remediation plans measure the impact of changes and reason about operational concerns such as index maintenance windows monitoring and capacity planning.

EasyTechnical

72 practiced

An executive expects dashboard tiles to load under 2 seconds (99th percentile). Describe the metrics and instrumentation you would collect to establish a baseline and set measurable SLAs for database-backed dashboards. Include what to measure at client, BI-tool, application, and database layers and how to summarize the distribution (p50/p95/p99).

HardSystem Design

67 practiced

You operate a multi-tenant BI platform where tenant workloads vary wildly. Design a sharding and resource-isolation strategy to prevent one 'hot' tenant from degrading performance for others. Discuss shard-by-tenant vs shard-by-hash, per-tenant resource quotas, moving heavy tenants to dedicated clusters, and how to handle cross-tenant analytical queries.

Sample Answer

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.

HardSystem Design

72 practiced

Design a read-scale architecture for an analytics platform that must serve 10,000 dashboard renders per minute, support 1TB/day ingestion, and provide interactive queries (median <2s, p99 <10s). Describe how you would combine replication, sharding/segmentation, materialized/pre-aggregated tables, query caching, and multi-region deployment to meet latency and freshness targets. Include operational concerns (schema migration, backups, cross-region consistency).

Sample Answer

Requirements (clarify): 10,000 dashboard renders/min (~167 RPS), 1 TB/day ingestion, interactive queries median <2s p99 <10s, near-real-time freshness (assume sub-1–5 min SLAs), multi-region availability.

High-level architecture:- Ingest layer: Kafka / Kinesis for durable, partitioned streaming; stream processors (Flink/Beam/ksqlDB) for transformations and incremental aggregates.- Storage: - Hot store: distributed columnar OLAP (ClickHouse, Druid, Pinot) for low-latency analytical queries. - Cold store: S3/Parquet + query engine (Trino) for long-term historical queries.- Serving layer: API + query gateway that routes dashboard queries to pre-aggregations, cache, or ad-hoc engine.

Sharding / Segmentation:- Time + tenant (or customer) based partitioning on ingestion (Kafka partitions aligned with time windows).- Physical table partitioning by date + business shard key in OLAP. This ensures scans are limited and ingestion parallelizes.

Replication:- Within-region replication for high availability (native replication + consensus where applicable).- Cross-region async replication of compressed segments/partitions to meet DR and reduce cold-read latency; for critical dashboards replicate recent hot partitions synchronously or with low-latency async.

Materialized / Pre-aggregated tables:- Maintain incremental materialized views for common dashboard slices (hourly/day aggregates, user cohorts) computed in stream processors and persisted in hot store.- Design pre-agg hierarchy: high-cardinality dimensions kept raw, common dimensions pre-aggregated. Use cubes or rollups for multi-granularity reads.- Provide TTLs and real-time update windows (e.g., last 15 min real-time, older data batched hourly).

Query caching:- Multi-layer cache: - CDN-level caching for fully static dashboards. - API-level cache (Redis/Memcached) for query-result caching keyed by query fingerprint + freshness TTL. - Result-set materialized cache for repeated queries; invalidate on new materialized updates or time-window expiry.- Use approximate query caches for percentiles or count-distinct (HyperLogLog) to speed p99 paths.

Query routing & freshness trade-offs:- Gateway evaluates query: if matches pre-agg or cached result and freshness requirement satisfied → return quickly.- Otherwise route to hot OLAP for real-time reads; fall back to cold Trino for deep historical queries (with warning in UI).- Provide dashboard settings: “real-time” (last 1–5 min) vs “eventual” to let users pick freshness-latency.

Scaling to targets:- 167 RPS dashboard renders often translate to many aggregated queries; pre-agg + cache should satisfy >90% of renders. Size hot cluster for peak QPS with replication factor 3 and auto-scaling nodes.- Ingest 1 TB/day → ~11.6 MB/s sustained; partition across many Kafka partitions and parallel stream tasks; bulk load to OLAP in micro-batches if needed.

Multi-region deployment:- Active-passive or active-active read replicas: - Active regions serve local reads from replicated hot partitions for low latency. - Writes/ingest centralized or sharded by region with cross-region replication; use conflict-free strategies (append-only events).- Cross-region routing via global API gateway; prefer regional reads to meet latency.

Operational concerns:- Schema migrations: use backward-compatible changes (additive columns), deploy transformation jobs to populate new columns, migrate consumers with feature flags, run dual-write or read-path compatibility tests, and use online schema change tools for OLAP (rebuild partitions asynchronously).- Backups: nightly snapshots of OLAP segments + S3 retention; keep Kafka topic retention and maintain archived raw events in immutable S3.- Cross-region consistency: accept eventual consistency for non-critical aggregates; for critical KPIs, synchronously replicate or run cross-region reconciliation jobs and surface “last-updated” timestamps in dashboards.- Monitoring & SLOs: instrument ingestion lag, query latency (median, p95, p99), cache hit rate, replication lag, and error rates. Alerts for ingestion backlog, high p99 latency, or replication lag.- Testing & runbooks: chaos tests for region failover, restore drills from backups, and automated schema migration dry-runs.

Trade-offs:- Strong freshness vs. latency: favor pre-aggregations + short TTLs to meet latency while keeping near-real-time. Synchronous cross-region replication increases latency/cost; prefer async with reconciliation unless strict consistency required.- Storage cost vs. query speed: keep hot limited to recent window (e.g., 30–90 days) and push older data to cheaper cold store.

This design combines sharding for parallelism, replication for availability, materialized/pre-aggregated tables and multi-layer caching for latency, and multi-region replicas for locality — plus operational practices (safe migrations, backups, monitoring) to meet the stated latency and freshness targets.

EasyTechnical

75 practiced

JOINs can dramatically affect dashboard performance. As a BI analyst, explain the effects of many-to-many joins and large dimension joins on query runtime and give practical mitigation strategies (pre-aggregation, denormalization, star schema, bloom filters, selective predicates) that reduce runtime for interactive dashboards.

MediumTechnical

62 practiced

A product manager asks you to denormalize part of the schema to speed up dashboard queries. Describe a decision framework you would use to evaluate denormalization: which metrics to measure, expected improvement, maintenance burden, storage overhead, correctness/consistency implications, and rollback plan.

Sample Answer

I would evaluate denormalization with a structured decision framework covering business value, technical impact, and risk/operational costs.

1) Clarify objective and success criteria- Which dashboard(s) and queries are slow? Target SLA (e.g., 95th percentile < 2s).- Business impact: users affected, frequency, revenue/decision latency cost.

2) Measure baseline- Query performance: p50/p95/p99 latencies, average rows scanned, execution plan, I/O, CPU.- Usage metrics: dashboard loads per day, concurrency, peak times.- Current storage and table sizes, and downstream ETL times.

3) Estimate expected improvement- Simulate with explain/analyze on denormalized query or build a small materialized sample.- Estimate reduction in joins, scanned rows, and network hops → project latency and resource savings (e.g., expect 3x faster or X% less I/O).

4) Assess maintenance burden & storage overhead- Extra storage: size of denormalized table(s) and growth rate; cost estimate.- ETL complexity: additional pipeline steps, incremental vs full refresh, latency requirements.- Operational tasks: monitoring, backfills, schema changes propagation.

5) Correctness and consistency implications- Staleness window: acceptable freshness for stakeholders; choose real-time vs batched updates.- Conflict risk: who owns canonical data? Define single source of truth and reconciliation procedures.- Data quality tests: add row counts, checksums, and alerting for drift.

6) Rollback and deployment plan- Start with read-only materialized view or indexed cache, run in parallel (A/B).- Validate results with row-level comparisons and user acceptance.- Rollback: switch dashboards back to normalized source; keep denormalized copy until confirmed safe; well-documented migration scripts.

Decision criteria: proceed if business value (reduced latency or cost) outweighs storage and maintenance costs, freshness meets requirements, and testing/rollback paths limit data correctness risk. If not, consider alternatives: query tuning, appropriate indices, caching layers, or pushing aggregations to ETL.

Unlock Full Question Bank

Get access to hundreds of Database Performance Tuning and Scaling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.