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
60 practiced
List common index types (B-tree, Hash, GIN, GiST, BRIN) and explain typical use cases, query patterns they accelerate, their impact on write performance, and storage trade-offs. For each index type indicate when a data scientist should recommend it.
Sample Answer
B-tree- Use cases: default index for equality and range queries on scalar types (ints, dates, text).- Accelerates: ORDER BY, MIN/MAX, =, <, <=, BETWEEN.- Write impact: moderate — maintained on INSERT/UPDATE/DELETE, can slow high-write workloads.- Storage: compact for typical keys; balanced tree overhead.- Recommend when: filtering/sorting on columns with selective predicates or joining on PK/FK.Hash- Use cases: equality lookups only.- Accelerates: = on columns with many exact-match queries.- Write impact: similar to B-tree but historically less flexible (Postgres improved).- Storage: simple but limited functionality (no range/ordering).- Recommend when: extremely high-frequency exact-match lookups and you need marginal speed for equality (rare).GIN (Generalized Inverted Index)- Use cases: full-text search, arrays, JSONB containment, multi-valued fields.- Accelerates: CONTAINS (@>), full-text ts_vector @@, array element searches.- Write impact: higher — inverted lists are larger and costly to maintain.- Storage: larger; stores posting lists per key.- Recommend when: querying tokens/terms, tag arrays, or JSONB fields where lookups are frequent.GiST (Generalized Search Tree)- Use cases: spatial (PostGIS), range types, nearest-neighbor, similarity searches.- Accelerates: geometric queries, overlap, distance, similarity.- Write impact: moderate to high depending on complexity.- Storage: variable; supports custom indexing strategies.- Recommend when: spatial or range queries, or custom operators (e.g., indexing embeddings with approximate methods).BRIN (Block Range INdex)- Use cases: very large, naturally ordered tables (time-series, append-only logs).- Accelerates: very selective range scans on clustered data (e.g., recent time window).- Write impact: minimal — compact summary per block.- Storage: tiny compared to other indexes.- Recommend when: huge tables where full indexes are too large and data is correlated with physical order.Summary guidance for data scientists:- Use B-tree for general-purpose filtering/sorting.- Use GIN for text/array/JSONB heavy workloads.- Use GiST for spatial/similarity problems.- Use BRIN for massive, ordered datasets where storage matters.- Consider write volume: heavier indexes (GIN/GiST) cost more on writes; BRIN is lightest.
HardTechnical
63 practiced
You plan to add a new index to a hot table. Describe the instrumentation and metrics to collect (latency percentiles, TPS, write amplification, IO, CPU), design a canary or shadow-traffic rollout to validate the index under production load, and outline rollback criteria and steps.
Sample Answer
Situation & goal: We need to add a new index to a hot, production table without degrading latency or write throughput. I’d treat this like a low-risk experiment with careful instrumentation, a phased canary/shadow rollout, and clear automated rollback triggers.Instrumentation & metrics to collect (continuous, both baseline and during experiment):- Latency percentiles for key operations (p50, p95, p99, p999) separated by read vs write and by query type; measure end-to-end application latency and DB-side query execution time.- TPS / QPS: reads/sec and writes/sec, and peak bursts.- Write amplification: bytes written to storage and WAL/redo per unit time, and ratio vs logical bytes (track WAL growth rate).- IO: IOPS, throughput (MB/s), IO latency (avg & p95), queue depth per shard/disk.- CPU: system and user CPU, CPU steal, per-core saturation.- Locking/contention: row/table lock wait times, number of waits, lock timeouts.- Cache: buffer cache hit ratio, index cache hit ratio.- Replication/consumer effects: replication lag, change-data-capture lag.- Background maintenance: compaction/merge times, GC pauses, index build progress.- Business KPIs: error rates, request timeouts, tail-latency user-facing metrics.Canary / shadow-traffic rollout design:1. Prepare: build the index in a non-blocking way (e.g., CREATE INDEX CONCURRENTLY / online index build) on a replica or separate read replica first to avoid locks.2. Shadow writes (phase 0–1): replicate write traffic to a shadow table or dev cluster where the index exists (no traffic served from it). Collect metrics and run analytic queries and explain plans to ensure index is used and not causing unexpected I/O or amplification.3. Small-sample canary (phase 2): route 1–5% of real reads to a canary instance with the index live, or enable index usage via optimizer hints for a small set of requests. Compare latencies, CPU, IO against control using A/B testing; require statistically significant equivalence for p95/p99.4. Ramp (phase 3): 5→25→50→100% ramp with monitoring gates at each step; hold for N minutes/hours depending on workload. During ramp, run synthetic load tests for stress corners (burst writes, large transactions).5. Observability: create dashboards showing delta from baseline and automatic anomaly detection for rolling-window increases in p95/p99, write amplification, IO saturation, CPU > threshold, replication lag.Rollback criteria (automated and manual):- Automated thresholds (any one triggers immediate rollback): sustained increase >20% in p99 read latency OR >15% in write latency for >5 minutes; write amplification or WAL growth >2x baseline sustained 10 minutes; sustained IOPS or IO latency causing queue depth > threshold; replication lag exceeding SLA (e.g., >30s) OR error rate increase >0.5% absolute; CPU saturation causing OOMs or unacceptable retries.- Manual triggers: unexpected compaction/backpressure, customer-facing errors, stakeholder request.Rollback steps (pre-defined runbook):1. Pause ramp and divert new canary traffic back to control.2. If index build was concurrent and fully created, drop index on affected node(s) with DROP INDEX to remove extra write cost. If index creation is in-progress and blocking, abort safely per DB docs (cancel build, or if impossible, isolate node).3. If shadow replica used, stop replicating traffic and promote control replica back to traffic path.4. Revert any optimizer hints or query-plan pins.5. Monitor for recovery of metrics to baseline for at least two RTTs of the observed issue window.6. Postmortem: collect diagnostics (EXPLAIN ANALYZE logs, iostat, vmstat, WAL size, lock traces), document root cause, and decide next steps (index redesign, partial index, filtered/index on smaller column set, or different indexing strategy).Why this approach: it minimizes customer impact by testing in read-only environments first, uses small-sample statistically-significant canaries, monitors both system and business KPIs, and defines deterministic automated rollback to reduce time-to-recovery.
HardSystem Design
52 practiced
Design an online resharding strategy to split 4 heavy shards into 8 for a live user-data database without downtime. Describe routing changes, migration method (background copy, dual-write, proxy-based routing), backfill/consistency checks, cutover, and rollback scenarios. Explain how you'll validate correctness after resharding.
Sample Answer
Requirements and constraints:- Zero downtime, live traffic, eventually consistent acceptable during migration but final state must be consistent.- Split 4 heavy shards → 8 shards (deterministic shard map).- Minimal write amplification and bounded replication lag.- Ability to rollback to 4-shard state.High-level approach:- Use background copy + dual-write + proxy-based routing. Background copy seeds new shards, dual-write ensures new data present, proxy routes reads/writes based on migration phase.Architecture components:1. Migration coordinator (orchestration service) to track phases, per-shard progress and cutover.2. Proxy/router (sidecar or gateway) that consults migration map and coordinator to decide destination(s).3. Background copy workers performing snapshot + incremental replication.4. Consistency checker and analytics pipeline for validations.Phases and routing:1. Pre-migration: compute new shard map function H2(user_id) -> new_shard (8). Proxy uses existing map H1->old_shard.2. Seed copy (background): For each old shard O, spin copy worker to stream full data to corresponding new shards N1,N2 (based on H2). Use consistent snapshot (e.g., MVCC, WAL-based) plus change stream since snapshot start. - Proxy: read/write unchanged to old shards only.3. Dual-write start (per-shard, controlled): For a specific old shard O whose initial copy completed and its change stream caught up, enable dual-write for keys mapping from O→{N_a,N_b}. Proxy writes synchronously to old shard and asynchronously (or synchronously if strong consistency needed) to new shards. Reads still go to old by default. - Use write-idempotency (timestamps, version vectors) to avoid duplicates.4. Backfill of missed deltas: background worker replays change stream to new shards until lag < threshold.5. Read validation window: enable read-after-write from new shards for a sample or percentage (canary reads). Proxy can route reads for selected user IDs to new shards and compare responses.6. Cutover: When lag < threshold and consistency checks pass, update proxy to route all reads/writes to new shards for that old shard. Mark shard migration complete.7. Cleanup: Stop dual-write, retire old shard, garbage-collect data after retention.Migration mechanics and consistency:- Use change capture (WAL/CDC) to stream writes since snapshot start. Ensure ordering by sequence numbers or commit timestamps.- Dual-write with causal metadata: every write includes monotonic commit_ts to allow last-writer-wins deterministic resolution.- Idempotency keys so replays and retries do not duplicate.- If strong consistency required, perform synchronous dual-write and use quorum or two-phase commit for the cutover window (costlier).Validation and metrics (Data Scientist focus):- Row-level checksum: compute hash(user_id, payload, last_update_ts) per record on old vs new. Run diff job for entire migrated keyspace incrementally; report mismatches.- Aggregation tests: compare aggregate metrics (counts, sums, unique counts) per shard and globally before and after. Use sampling and stratified checks by activity level.- Time-series validation: verify write-consumption rates and latency distributions pre/post migration.- Probabilistic data validation: use Bloom filters or Merkle trees to detect missing keys quickly.- Canaries: route small % of real traffic reads to new shards and compare application-level behavior.- SLA monitoring: error rate, latency, tail latencies, replication lag.Cutover safety and rollback:- Safe cutover only when: replication lag < threshold, checksum mismatch = 0 (or within acceptable), canary reads pass.- Rollback scenario: if issues after cutover, proxy can revert routing to old shards per-shard quickly. Since dual-write continued during migration, old shard contains latest writes. Rollback steps: - Immediately update proxy to route to old shards. - Stop writes to new shards. - Investigate discrepancies using logged diffs and CDC streams. - Optionally re-run migration after fixes.- If dual-write was asynchronous and new shard has writes missing on old shard (should not happen), use coordinator to replay missing writes to old shard before rollback.Edge cases and trade-offs:- Long-running transactions: ensure CDC captures transactional boundaries; consider pausing schema changes.- Hot keys: split carefully—use consistent hashing with explicit routing for hot users to avoid double-hotting.- Latency vs safety: synchronous dual-write increases latency but simplifies rollback; choose based on SLA.Operational runbook highlights:- Migrate per-old-shard sequentially or in small parallel batches.- Pre-flight: dry-run validations on a copy cluster.- Automate alarms for lag, checksum mismatches, canary failure.- Post-migration audit report with metrics and retention plan.This strategy ensures online reshard with controlled consistency, measurable validations, safe cutover, and quick rollback capability while giving a Data Scientist tools (checksums, aggregates, canaries) to validate correctness.
MediumTechnical
94 practiced
A nightly ETL reads from production OLTP and is slowing down the application. Walk through a diagnostic checklist for identifying root causes (slow queries, locks, IO, missing indexes), propose immediate mitigations to reduce impact, and recommend long-term architecture changes to isolate ETL from production.
Sample Answer
Framework: triage → immediate mitigations → long-term fixes.Diagnostic checklist (what I’d check first, with how and why)- Confirm timeline & impact: correlate ETL window with app latency/alerts (APM traces, DB slow query logs).- Identify slow queries: enable/inspect slow_query_log (MySQL) or pg_stat_statements; capture top N by total_time and by calls.- Check locks/contention: SHOW ENGINE INNODB STATUS / pg_locks + pg_stat_activity to find blocking sessions and wait types.- IO/CPU/Memory pressure: OS metrics (iostat, vmstat, top), cloud metrics (Disk IOPS, queue depth), storage latency.- Missing/inefficient indexes: EXPLAIN plans for heavy queries; look for sequential scans, high row estimates.- Transaction patterns: long-running transactions, frequent full-table scans inside transactions, autocommit off.- ETL behavior: batch size, parallelism, use of SELECT FOR UPDATE, whether CDC or full extracts.Immediate mitigations (quick, low-risk)- Throttle ETL: reduce concurrency, increase sleep between batches, lower batch size.- Switch to read-consistent snapshot modes: in Postgres use REPEATABLE READ snapshot or pg_dump-style snapshot; in MySQL use consistent read (InnoDB MVCC) and avoid LOCK TABLES.- Run ETL at lower-load window; add backoff on detection of locks.- Use read replicas for reads if available; point ETL to replica immediately if lag acceptable.- Add short-term indexes for the few offending queries (after verifying selectivity) but monitor write overhead.- Kill or reprioritize low-priority ETL queries via resource governance (cgroups, pgbouncer pool sizing, nice/ionice).Long-term architecture recommendations- Replication/CDC: adopt async logical replication or Debezium to stream changes to a separate analytics DB (near real-time, no load on primary).- Dedicated reporting cluster: keep OLAP store (Redshift/Snowflake/BigQuery or Postgres replica tuned for analytics) and run ETL there.- Use snapshot isolation and incremental extracts: avoid full-table scans by tracking high-water marks / change columns.- Rate-limiting & orchestration: implement job scheduler with backpressure (Airflow sensors, custom throttling) and circuit breakers on resource thresholds.- Schema/query improvements: add appropriate indexes, materialized views, pre-aggregations; rewrite heavy queries to use covering indexes.- Monitor & alerting: expose DB resource and replication lag metrics; add automated mitigation (pause ETL if lag > threshold).- Test in staging: run ETL load tests against representative replicas to validate changes.Example quick SQL to find blocking in Postgres:\`\`\`sqlSELECT pid, state, wait_event_type, wait_event, query, age(clock_timestamp(), query_start) AS durationFROM pg_stat_activityWHERE state <> 'idle' ORDER BY duration DESC;\`\`\`Why this approach: first identify the specific bottleneck (queries, locks, IO) to apply the least disruptive immediate fix (throttle or use replica). Long-term, isolate analytics workload from OLTP via replication/CDC and a dedicated analytics store to eliminate contention and allow scalable ETL.
MediumTechnical
73 practiced
Compare row-oriented vs column-oriented storage for analytics and for ML feature extraction. For a high-cardinality, wide table used in model training, discuss IO patterns, compression behavior, query performance, and implications for feature pipelines.
Sample Answer
Definition and high-level tradeoff:- Row-oriented stores (e.g., MySQL row format) store entire records contiguously. Good for transactional, point-lookups, and when you frequently need most columns for a single key.- Column-oriented stores (e.g., Parquet, ORC, ClickHouse) store each column contiguously. Good for analytics and bulk scans that touch a subset of columns, because readers can prune irrelevant columns and compress similar values well.IO patterns- Row-store: typically many small sequential reads if you scan whole rows, or random I/O for point-reads. For wide tables where training reads many columns of many rows, a full-table scan reads contiguous row blocks (good sequential throughput) but reads unused data if you only need a subset of features.- Column-store: sequential scan per column; if you need only a subset of features, IO is dramatically lower (column pruning). For training that uses most features, column-store still performs well because each column can be read sequentially and in parallel; however reading hundreds of columns can increase overhead from many file streams or seeks if poorly implemented.Compression behavior- Columnar compression is generally stronger: contiguous similar-typed values allow run-length, delta, dictionary, bitpacking, and SIMD-friendly codecs. This reduces IO and memory for numeric features and low-cardinality categorical features.- High cardinality hurts some column encodings (RLE, low-bitpacking) but dictionary encoding still helps for repeated values; entropy codecs (Zstd) work well for numeric deltas.- Row stores compress per-row or per-page; compression is weaker because different columns interleave types and distributions, lowering compression ratio.Query / training performance- Analytics (aggregations, group-bys, filters on a few columns): columnar wins—fewer bytes read, vectorized compute, faster aggregations.- ML feature extraction: - Batch training: columnar is usually superior because pipelines often select a subset of columns and perform vectorized transforms. Column pruning + predicate/zone/bitmap filtering speeds materialization. - If model requires nearly all columns (very wide feature set), row-store sequential reads can be competitive because it avoids opening many column streams; but modern columnar formats optimized for vectorized IO (multi-threaded column reads) still perform well. - Online feature lookups / feature store serving: row-oriented or key-value stores (or precomputed features) are better for low-latency single-key retrievals.Implications for feature pipelines- Columnar advantages: smaller IO footprint for sparse selection of features, better caching of hot columns, lower memory pressure due to better compression, benefit from predicate and projection pushdown, enables vectorized transformations directly from disk.- Challenges with high-cardinality wide tables: dictionary build cost, memory for decoding many columns, and overhead when materializing many columns into dense training matrix. Also some encodings are less effective for high-cardinality categoricals—consider hashed embeddings or frequency-based bucketing before storage.- Operational suggestions: - Use columnar (Parquet/ORC) for batch training and analytics; partition by time/user to reduce scan. - Precompute frequently used or expensive features into a feature store to serve online or speed training. - Use columnar codecs tuned for numeric deltas and Zstd for heavy cardinality; enable vectorized readers and parallel IO. - If training requires almost every column and low-latency reads per-row, consider row-oriented storage or export compact binary format (NumPy arrays, TFRecords) for training to eliminate read-time decoding cost.Recommendation summary- For analytics and batch ML feature extraction where you read subsets of columns: columnar is the default choice (better compression, IO, and vectorized performance).- For high-cardinality, very wide tables used in training: still prefer columnar but combine partitioning, appropriate encodings, and materialization of dense training artifacts; use row-store or KV stores for online/point feature lookups.
Unlock Full Question Bank
Get access to hundreds of Database Performance Tuning and Scaling interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.