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.

HardSystem Design

54 practiced

Design an analytical dashboard backend to support 100k concurrent users and fast lookups on 10B rows. Specify data storage (columnar vs relational), caching layers, pre-aggregation strategies, and how you would architect read replicas or query engines (e.g., BI accelerators). Include cost vs latency trade-offs.

Sample Answer

Requirements:- Serve 100k concurrent dashboard users with sub-second to low-second interactive queries over 10B rows.- Support ad-hoc exploration plus recurring dashboards, acceptable eventual consistency for analytics.

High-level architecture:- Ingest layer → Raw data lake (object store) + ETL → Columnar data warehouse + Pre-aggregation service → Query layer (BI accelerators / MPP) → Cache/CDN → Frontend.

Data storage:- Columnar analytic store (e.g., Snowflake, BigQuery, Redshift Spectrum, ClickHouse) for base 10B rows — optimized for scans, compression, vectorized execution. Use object store for raw/partitioned storage to lower cost.- Avoid transactional row RDBMS for primary analytics.

Pre-aggregation & modeling:- Build a curated semantic layer (dbt) that defines metrics and materialized aggregates.- Materialize multi-granularity cubes: - Near-real-time incrementally updated hourly aggregates for common dimensions. - Daily rollups for less-frequent slices.- Use rollup tables and summary tables keyed by high-cardinality dims (date, product, region).

Caching layers:- Query result cache at three levels: 1. CDN / edge caching for static dashboard tiles. 2. Application-level cache (Redis/Memcached) for frequently-requested query results and parameterized templates. 3. Warehouse query cache (built-in) to reuse compiled results.- TTL policies tuned per dashboard criticality.

Query engine / read scaling:- Use MPP or cloud-managed BI accelerators (e.g., Presto/Trino + Hive metastore, or BigQuery BI Engine) to serve concurrent ad-hoc queries.- Maintain a pool of read replicas or compute clusters sized for concurrency; autoscale compute separately from storage.- Route heavy queries to pre-agg stores; route ad-hoc to MPP with concurrency limits and queuing.

Cost vs latency trade-offs:- Lowest latency: keep more pre-aggregates and larger Redis cache — higher storage & memory cost.- Moderate cost: rely on fast columnar queries + BI accelerator caching — balanced.- Lowest cost: fewer materializations, rely on on-demand scanning — higher query latency and degraded interactive experience.- Recommendation: invest in pre-aggregations for top 20-30 dashboards + Redis caching; autoscale compute for spiky concurrency; monitor cost per QPS and adjust retention/rollup granularity.

Observability & governance:- Metrics: query latency, cache hit rate, compute cost, row scanned.- Versioned models, access controls, and explainable lineage (dbt) so analysts trust data.

EasyTechnical

75 practiced

You're investigating a slow SELECT on PostgreSQL. Explain what information EXPLAIN and EXPLAIN ANALYZE provide, how you interpret estimated vs actual rows, and which plan nodes (e.g., Seq Scan, Index Scan, Hash Join) indicate CPU-bound vs I/O-bound behavior. Describe the first three actions you would take after reading the plan.

MediumTechnical

62 practiced

For a high-cardinality dashboard that aggregates by many dimensions, compare using materialized views, pre-aggregated summary tables, and application-side caching. Provide a decision matrix showing latency, freshness, storage cost, and operational complexity for each option in a typical analytics stack.

Sample Answer

Clarify requirements: dashboard queries many high-cardinality dimensions, needs interactive latency (<1–5s) for users, near-real-time freshness (minutes–hours), and reasonable storage/ops effort.

Recommendation summary: choose a hybrid approach — pre-aggregated summary tables for common rollups, materialized views for moderate reuse with DB-managed freshness, and application-side caching for very hot queries / UI-level assets (e.g., top N filters). This balances latency, freshness, cost, and ops.

Decision matrix (typical analytics stack):- Materialized views - Latency: Low–Medium (fast reads, depends on idx) - Freshness: Medium (depends on refresh strategy: on commit, incremental) - Storage cost: Medium (stores results, usually smaller than raw) - Operational complexity: Medium (DB maintenance, refresh scheduling, versioning)- Pre-aggregated summary tables - Latency: Very Low (optimized for target queries) - Freshness: Low–Medium (batch updates; near-real-time possible with streaming) - Storage cost: High (many combinations -> explosion) - Operational complexity: High (ETL jobs, schema for many dimensions, maintenance)- Application-side caching - Latency: Very Low (in-memory) - Freshness: Low (cache TTLs, invalidation complexity) - Storage cost: Low–Medium (depends on cache tier) - Operational complexity: Medium (cache invalidation, consistency logic)

Implementation considerations:- Profile query patterns and build pre-aggregations only for top N combinations; use DRUID/ClickHouse rollups if supported.- Use materialized views for joins/filters that are expensive but stable; prefer incremental refresh.- Use app cache (Redis/CDN) for UI fragments, with short TTLs and event-driven invalidation for critical metrics.- Monitor hit rates, storage growth, and set SLOs (latency, freshness) to drive which layer to optimize.

This hybrid approach minimizes user latency while controlling storage and ops overhead; iterate based on real query telemetry.

EasyTechnical

65 practiced

Explain how connection pooling improves database stability for BI tools and web apps. For a BI tool that opens many short-lived connections and causes connection exhaustion, outline configuration changes (pool size, timeouts) and architectural approaches (proxy, connection pooler) you'd recommend.

EasyTechnical

69 practiced

Explain read replicas and asynchronous replication. For a reporting workload that performs read-heavy queries, describe how you'd introduce read replicas, how to route read traffic, and how you would handle replication lag and eventual consistency for dashboards.

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.