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

75 practiced

Write SQL to create a covering index for the query below and explain why it's covering. Query: SELECT user_id, COUNT(*) AS cnt FROM events WHERE event_type = 'click' AND created_at >= '2025-01-01' GROUP BY user_id ORDER BY cnt DESC LIMIT 50; Table: events(id, user_id, event_type, created_at, metadata JSONB).

Sample Answer

Approach: create a multicolumn b-tree index that covers all columns used by the WHERE, GROUP BY and SELECT so Postgres can do an index-only scan (no heap fetch) and avoid reading the JSONB metadata.

SQL:

sql

-- multicolumn index: filter columns first, then group-by/select column
CREATE INDEX idx_events_eventtype_created_user
  ON events (event_type, created_at, user_id);

Why this is covering:- The query references only event_type, created_at, and user_id (plus COUNT(*) which uses tuple visibility).- The index contains event_type and created_at (used in the WHERE) and user_id (used in SELECT and GROUP BY), so Postgres can satisfy the query from the index pages without reading table rows — this is an index-only scan when the visibility map is up-to-date.- Because all referenced columns are in the index, the engine doesn't need to fetch metadata (JSONB) from the heap.

Notes and caveats:- COUNT(*) still needs tuple visibility checks; index-only scans are possible only when the visibility map marks tuples as visible.- The index doesn't directly provide the ORDER BY cnt DESC; aggregation to compute counts and sorting by cnt still happens (you'll still need to aggregate and sort the results). The index speeds up filtering and grouping (faster grouping by scanning only matching index entries).- If event_type has low cardinality (e.g., few event types), consider a partial index to reduce size:

sql

CREATE INDEX idx_events_click_created_user_partial
  ON events (created_at, user_id)
  WHERE event_type = 'click';

This partial index is smaller and more efficient for this specific query.

HardTechnical

57 practiced

Hard operational: Create a runbook outline for a database performance regression detected by a CI benchmark (e.g., 20% increase in median query latency). Include steps to triage, rollback, validate a fix, and communicate to stakeholders. Mention tooling and logs to consult.

Sample Answer

Situation: CI benchmark alerted a 20% median query latency regression vs baseline.

Runbook outline — Database Performance Regression (20%+ median latency)

1. Immediate Triage (first 15 mins)- Acknowledge alert in incident channel (Slack/MS Teams) and tag on-call DB/data engineer.- Mark CI build as flaky and pause dependent deploys.- Snapshot failing CI run, baseline metrics, and commit ID(s).

2. Quick-impact assessment (15–45 mins)- Confirm scope: Which queries/pipelines, environments (staging vs prod), and datasets.- Check CI benchmark logs/artifacts (benchmark run output, query IDs).- Consult monitoring dashboards: Grafana (DB latency, p99/p50), Prometheus metrics, APM traces (NewRelic/Datadog).- Check DB-specific: slow query log, pg_stat_statements (Postgres), sys.dm_exec_query_stats (SQL Server), MySQL slow_log.

3. Triage deeper (45–120 mins)- Re-run failing benchmark against baseline commit and current commit in an isolated environment.- Capture explain plans: EXPLAIN (ANALYZE, BUFFERS) for suspect queries.- Check recent schema migrations, indices, config changes (workload, planner flags, connection pooler, memory).- Inspect infra: CPU/memory/io on DB hosts (CloudWatch/GCP Monitoring), disk latency, network, k8s node events.- Look for contention: locks (pg_locks), long transactions, autovacuum backlog.- Review application/ETL changes: query shape, parameterization, batch sizes.

4. Mitigation / Rollback (if impacting prod or clear culprit) - If change is commit-based and reproducible, roll back application/ETL deploy via CI/CD (e.g., kubectl rollout undo, Cloud Run rollback, Helm rollback) and rerun benchmark.- If DB config/schema caused regression, reverse migration or restore index, using safe migration tools and run during low-traffic window; if emergency, promote pre-change replica.- If infrastructure (I/O/CPU) spike, scale vertically/horizontally (read replicas, increased IOPS) as temporary relief.

5. Validate Fix- Re-run CI benchmark against the rolled-back commit/config and compare median/p99 to baseline.- Run smoke tests for pipelines and sample queries.- Monitor for 1–2 hours post-fix: Grafana dashboards, APM, error rates, and downstream job runtimes.

6. Communication- Initial incident note: summary, scope, ETA, owner in incident channel and to stakeholders (Data Consumers, SRE, Product) — use template: "What happened / Impact / Who's on it / Next update in 30m".- Post-fix update: actions taken, verification evidence (benchmarks before/after, graphs), rollback if any, mitigation steps.- Postmortem promised timeline (48–72 hrs).

7. Postmortem & Preventative Actions- Root cause analysis: commit diff, EXPLAIN changes, infra metrics.- Implement CI gate: add regression threshold, require perf review on DB-impacting PRs.- Add automated regression tests to CI with historical baselines.- Create runbook notes for future similar incidents.

Tooling & logs to consult- CI artifacts (Jenkins/Github Actions/Gitlab): benchmark outputs- Monitoring: Grafana, Prometheus, CloudWatch/GCP Monitoring, Datadog- APM/tracing: NewRelic/Datadog/Jaeger- DB logs: slow_query_log, error logs, pg_stat_statements, pg_locks, sys.dm_exec_query_stats- Infra logs: Cloud provider metrics, iostat, netstat, kubectl logs/events

Key decision criteria for rollback vs mitigation- Reproducibility, scope of impact, risk of rollback to data integrity, time-to-fix, stakeholder business impact.

Use this outline as a checklist during incidents; tailor commands and exact dashboards to your stack.

HardSystem Design

69 practiced

Hard design/problem: You need to implement a global leaderboard with frequent writes and reads with top-K queries per region and globally. Propose data models and storage choices (in-memory, Redis, relational, specialized stores), explain how to keep global top-K consistent, and how to scale both read and write paths.

Sample Answer

Requirements & constraints (clarify): frequent writes (many score updates), low-latency reads for top-K per-region and global, high availability, near-real-time consistency (strong per-user, eventual global OK?), bounded memory.

High-level approach:- Treat Redis Sorted Sets (ZSET) as the low-latency read/serve layer; use a durable authoritative store (OLTP DB or analytical store) as the source of truth and Kafka for write ingestion/streaming. Use stream processors (Spark Streaming / Flink / Kafka Streams) to aggregate and materialize leaderboards.

Components & data model:1. Ingestion: client -> API gateway -> Kafka topic "score-events" (partitioned by user-id or region).2. Stream processing: Flink job consumes events, applies idempotent updates, enforces latest score per user, and writes: - Regional leaderboards: update Redis ZSET per region (key: leaderboard:region:{region_id}) - Global interim ranking: update Redis ZSET: leaderboard:global - Persist events & latest per-user score to authoritative DB (Postgres/CockroachDB) or OLAP (BigQuery) for analytics.3. Read path: serve top-K directly from Redis ZSET via ZREVRANGE; for large K or cold data fall back to DB.

Keeping global top-K consistent:- Two options: A) Centralized aggregation: stream processor maintains a single global ZSET by merging regional updates deterministically (use last-write-wins with version/timestamp). This yields near-real-time global top-K in Redis. B) Hierarchical merge: maintain per-region ZSETs; compute global top-K by k-way merging top-M from each region in the stream job and writing resulting top-K to global ZSET. This reduces work (only top M per region needed) and bounds memory/compute.- Ensure idempotency: events include user_id, new_score, seq/version. Processor keeps latest seq per user to avoid double-counting.

Scaling writes & reads:- Writes: - Partition Kafka by user-id to parallelize processing. - Multiple stream consumers; use consistent hashing for Redis key ownership if using clustered Redis (Redis Cluster). Use pipelining and batched ZADD for throughput. - For extreme write volume, route hot-user writes through per-user rate limits or accumulate updates and apply only delta/top changes (write-behind).- Reads: - Redis read replicas and Redis Cluster shards per region keyspace. - Use CDN or API caching for very frequent identical queries (e.g., top-10). - Use materialized caches per region; for global heavy reads, precompute top-K refresh intervals (e.g., every second).

Durability, recovery & trade-offs:- Persist all score-events in Kafka (retention) and authoritative DB for replay.- Rebuild leaderboards by replaying streams if Redis loses state.- Trade-offs: strong global linearizability expensive — hierarchical merge gives eventual consistency with low latency. If strict global consistency required, route writes through a single serializing service (bottleneck).

Operational concerns:- Monitor Redis memory, eviction, and latency; set eviction policies and TTLs for obsolete users.- Handle ties deterministically (score, timestamp, user-id).- Provide backpressure and explainable SLA for staleness (e.g., top-K accurate within 1s).

This design balances low-latency reads (Redis), durable source-of-truth & replayability (Kafka + DB), scalable writes (partitioned stream processing), and an efficient global merge strategy to keep global top-K consistent and scalable.

MediumTechnical

52 practiced

You maintain a Postgres OLTP database with heavy write traffic. An index rebuild is required but must not impact latency for peak hours. Design an index maintenance plan that minimizes downtime and write amplification. Include scheduling, concurrent options, and rollback considerations.

Sample Answer

Situation: You need to rebuild an index on a high-write OLTP Postgres cluster without impacting peak-hour latency or causing excessive write amplification.

Plan (high-level):- Prefer non-blocking/concurrent operations or offload work to a replica.- Prepare by tuning maintenance params and monitoring WAL and I/O.- Build a replacement index concurrently, verify, then atomically swap names and drop the old index.- Have rollbacks and threshold-based aborts ready.

Step-by-step operational runbook:1. Prep (hours/days before)- Increase maintenance_work_mem on the instance running the build (temporary).- Ensure autovacuum is healthy; check pg_stat_activity, I/O, replication lag.- Identify peak and safe windows; target low-write hours but not necessarily full maintenance window (e.g., 2–4am local).- Alerting: set metrics on p99 latency, write throughput, replication lag and WAL volume.

2. Preferred approach — build on a standby replica then swap- Create the index on a replica (read-only) to avoid impacting primary: - Connect to replica and run:

sql

CREATE INDEX CONCURRENTLY idx_mytable_col_new ON mytable (col);

- Wait for the replica to catch up. Once built, you must also create it on primary or do fast swap (see below). Creating on primary will re-run build; so best if you can take brief lock to swap names (atomic and cheap).- If using logical replication or PgBouncer routing, you can promote a replica after index created, or use a fast rename strategy (next).

3. Direct primary approach (when replica route unavailable)- On primary, build replacement index concurrently with a temporary name:

sql

CREATE INDEX CONCURRENTLY idx_mytable_col_new ON mytable (col);

- This avoids exclusive locks for writes but increases WAL and requires two scans; monitor WAL generation and disk.- Validate query plans: run EXPLAIN on critical queries to ensure the new index is used or equivalent.- Swap names quickly (fast, takes brief access exclusive lock only on the index object, not the table):

sql

BEGIN;
  ALTER INDEX idx_mytable_col RENAME TO idx_mytable_col_old;
  ALTER INDEX idx_mytable_col_new RENAME TO idx_mytable_col;
  COMMIT;

- Drop the old index concurrently:

sql

DROP INDEX CONCURRENTLY idx_mytable_col_old;

Tuning & concurrent options:- Use CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY (Postgres 12+) to avoid table locks.- Increase maintenance_work_mem to speed builds, but watch memory.- If WAL/disk I/O is the bottleneck, throttle by scheduling during lower I/O or build on replica.- If index includes expressions or requires table rewrite, concurrent option might not apply; plan for maintenance window.

Rollback and failure handling:- If CREATE INDEX CONCURRENTLY fails: the failed index is cleaned up automatically; re-run after investigating cause.- If after swap the new index causes regressions, quickly rename back:

sql

BEGIN;
  ALTER INDEX idx_mytable_col RENAME TO idx_mytable_col_newbad;
  ALTER INDEX idx_mytable_col_old RENAME TO idx_mytable_col;
  COMMIT;
  DROP INDEX CONCURRENTLY idx_mytable_col_newbad;

- If WAL or replication lag crosses thresholds during build, abort and drop new index, restore settings.- Always test the full procedure on staging with production-like load.

Monitoring & metrics:- Track p99 write latency, replication lag, WAL volume, IOPS, index build progress in pg_stat_progress_create_index (Postgres 12+), and application error rates.- Use automated runbook scripts that check thresholds and send alerts or cancel builds.

Trade-offs:- CONCURRENT builds avoid locks but double I/O/WAL cost; building on replica shifts cost off primary but adds operational complexity.- Renaming swap is minimal-impact; dropping old index concurrently avoids table locks.

This approach minimizes client-facing downtime and write amplification by preferring replica/off-peak builds, using CONCURRENTLY, performing a fast atomic swap, and providing clear rollback steps and monitoring.

HardTechnical

66 practiced

Design an experiment to measure the performance impact of adding a new index on a production table with 500M rows. Describe steps to run the experiment safely, metrics to collect, how to use a canary or replica to avoid risk, and how to roll back if the index increases write latency unexpectedly.

Sample Answer

Requirements & constraints:- Measure read improvement vs write impact for adding index on a 500M-row production table with minimal user risk and ability to rollback quickly.

Experiment design (steps):1. Prep: create test plan with success criteria (e.g., read latency P95 reduced by ≥20% and write latency P99 increase ≤5ms). Identify time windows of representative traffic (peak + off-peak).2. Replica canary: provision a writable replica cluster (or a scaled-down production-like environment) that receives a sampled subset of production traffic via DB-level replica or query router (shadow/canary).3. Index build strategy: build index on the replica first. If online indexing is supported (online/offline), use online build tools (native online index or pt-online-schema-change/gh-ost) to avoid long locks.4. Traffic split: route a small percentage (e.g., 1–5%) of live reads and writes to the canary replica using feature flags or a proxy (e.g., HAProxy, Envoy, query sharding), ensuring writes are either replicated back or simulated.5. Run & measure for an observation period long enough for statistical power (e.g., 24–72 hours covering peak cycles).6. Gradual ramp: if safe, increase traffic to 10–25% then to 50% before full rollout.

Metrics to collect:- Read metrics: query latency distribution (P50,P95,P99), throughput (QPS), cache hit ratio, CPU usage, IO read amplification.- Write metrics: insert/update/delete latency distribution, commit time, transaction throughput, lock wait times, replication lag.- System metrics: CPU, memory, disk IO, disk space used (index size), network, GC pauses.- Business metrics: end-to-end job runtimes for downstream pipelines, user-facing latencies, error rates.- Operational: index build time, blocking events.

Using a canary/replica to avoid risk:- Use asynchronous replica or a read/write canary cluster fed by change data capture (CDC) so production master not impacted.- Shadow writes: run writes against canary without affecting primary (replay CDC or mirror writes) to measure pure write cost.- Use traffic mirroring (e.g., DB proxy or application middleware) to avoid exposing users to early regressions.- Keep canary isolated with alerting and automatic cut-over back to primary routing if thresholds breached.

Roll back plan if writes degrade:- Predefine rollback triggers (e.g., write P99 increase > threshold, replication lag > X seconds, error rate spike).- Immediate actions: stop routing traffic to canary; divert to primary.- If index was built on primary and causes regressions, drop the index (DROP INDEX) during low-traffic window or use online drop. For large tables, have scripted safe drop with monitoring.- If using online schema-change tool, abort/cancel job if supported; otherwise throttle traffic and drop index.- Post-rollback: run root-cause analysis, consider alternate index (partial, covering, filtered), or change schema (denormalization) and test again in canary.

Analysis & significance:- Use A/B statistical tests on latency distributions (non-parametric tests like Mann–Whitney or bootstrap) and compute effect sizes and confidence intervals.- Validate downstream effects (ETL runtimes, replication lag) before full rollout.

Operational safeguards:- Run index creation and drops with automation and runbook, enforce change approval, use circuit-breaker alerts, and limit rollout to maintenance windows if risk high.- Document index size and storage cost, ensure backups and snapshots exist before changes.

This approach isolates risk, quantifies read/write trade-offs, and provides clear rollback and analysis steps so you can make a data-driven decision.

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.