Database Design and Query Optimization Questions

Principles of database schema design and performance optimization including relational and non relational trade offs, normalization and denormalization, indexing strategies and index types, clustered and non clustered indexes, query execution plans, common table expressions for readable complex queries, detecting missing or redundant indexes, sharding and partitioning strategies, and consistency and availability trade offs. Candidates should demonstrate knowledge of optimizing reads and writes, diagnosing slow queries, and selecting the appropriate database model for scale and consistency requirements.

HardTechnical

52 practiced

A complex SQL query runs fast on a small dataset but is very slow on production. Provide a step-by-step plan: how you would profile and benchmark, what indexes or rewrites you would try, and example SQL for a likely index to add and a rewritten query. Explain how you would measure improvement.

Sample Answer

Approach overview — profile first, then iterate with targeted indexing and query rewrites, measuring changes with reproducible benchmarks.

1) Profile & benchmark- Reproduce production-like conditions: copy a representative sample (or use production snapshot anonymized) with same cardinalities.- Capture baseline: run EXPLAIN (ANALYZE, BUFFERS) / STATEMENT on Postgres, or EXPLAIN for other engines; collect runtime, planner node times, I/O, CPU, and row estimates vs actuals.- Use pg_stat_statements / query profiler or cloud query logs to gather variance and percentiles (p50/p95/p99).- Save query plan and runtime as baseline.

2) Diagnose common problems- Look for sequential scans on large tables, large nested-loop join with many outer rows, massive temp file usage, or misestimates (rows vs actuals).- Check missing or wrong indexes, outdated statistics (ANALYZE), inappropriate data types, unnecessary DISTINCT/ORDER BY, or SELECT * returning wide rows.

3) Indexes and rewrites to try- Add covering index for join/filter columns used in WHERE, JOIN, ORDER BY.Example index (Postgres):

sql

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created_at
ON orders (customer_id, status, created_at DESC)
INCLUDE (total_amount);

- If queries filter on a date range and then aggregate, a composite index (customer_id, created_at) helps.- Consider partial indexes for common predicates:

sql

CREATE INDEX CONCURRENTLY idx_orders_active_recent
ON orders (customer_id, created_at)
WHERE status = 'active';

- Rewrite: replace correlated subquery with JOIN + aggregation or use window functions to avoid nested loops.Original slow pattern:

sql

SELECT c.id, c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_count
FROM customers c
WHERE c.region = 'EMEA';

Rewritten:

sql

SELECT c.id, c.name, COALESCE(o.orders_count,0) AS orders_count
FROM customers c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS orders_count
  FROM orders
  GROUP BY customer_id
) o ON o.customer_id = c.id
WHERE c.region = 'EMEA';

Or use window if you need per-row enrichment.

4) Validation & measurement- Re-run EXPLAIN (ANALYZE, BUFFERS) and compare: total runtime, planning vs execution time, actual vs estimated rows, heap read vs index read, temp file usage.- Use controlled benchmarks: run N cold runs (drop caches) and N warm runs; report median and p95. Measure CPU, I/O, and query duration.- Track business metrics: concurrency behavior under load (use load testing with pgbench or JMeter), and impact on downstream SLAs.

5) Additional considerations & trade-offs- Maintainability: too many indexes slow writes; prefer partial/covering indexes.- Stats: run ANALYZE after heavy changes.- Materialized views or pre-aggregation if realtime not required.

This systematic loop—profile, hypothesize (index/rewrite), implement, and measure with EXPLAIN + repeatable benchmarks—ensures improvements are real and safe for production.

EasyBehavioral

49 practiced

Tell me about a time when a database schema or design choice impacted the outcome of a project you worked on. Use the STAR format (Situation, Task, Action, Result) and emphasize how you analyzed the problem and what you would do differently.

MediumTechnical

79 practiced

Compare choosing PostgreSQL with a columnar extension (e.g., cstore_fdw), BigQuery, and Redshift for an analytics workload. Discuss criteria: concurrency, query latency, cost model, data freshness, maintenance, and when you'd prefer each option.

Sample Answer

High-level summary: All three can serve analytics workloads but trade off concurrency, latency, cost, freshness, and ops. Choice depends on data volumes, team skills, SLAs, and budget.

Concurrency- PostgreSQL + columnar FDW: limited by single-node Postgres and FDW implementation; fine for tens of concurrent analysts but will struggle under many concurrent heavy queries.- Redshift: moderate–high concurrency with Concurrency Scaling and WLM; good for hundreds of BI users if tuned.- BigQuery: excellent native concurrency (serverless), thousands of simultaneous short queries.

Query latency- Postgres + columnar: low-latency for moderately sized datasets (GBs–low TBs) if CPU/IO sufficient.- Redshift: low latency for complex, large joins if cluster sized/tuned; cold clusters/resize take time.- BigQuery: low latency for large analytical scans thanks to distributed execution; small ad-hoc queries sometimes have higher start-up overhead.

Cost model- Postgres + columnar: predictable infra costs (VM, storage); cheaper at small scale but you pay for provisioning and ops.- Redshift: cluster-based — pay for nodes or RA3 storage/compute separation; more predictable for sustained heavy usage.- BigQuery: consumption-based (bytes scanned) — excellent for variable workloads and no infra ops; can be cost-effective but unpredictable without governance and partitioning/clustering.

Data freshness- Postgres + columnar: near-real-time if using streaming ingestion, but background compaction/ETL may add lag.- Redshift: supports near-real-time with streaming (Kinesis/Firehose) but typically batch-oriented.- BigQuery: near-real-time with streaming inserts; best for high-frequency ingestion at scale.

Maintenance & ops- Postgres + columnar: highest ops burden (tuning, vacuuming, backups, FDW compatibility).- Redshift: managed but requires WLM tuning, vacuum/analyze optionally, node management.- BigQuery: minimal ops (serverless), no vacuuming, auto-scaling — lowest maintenance.

When to prefer each- Postgres + columnar: small-to-medium teams, <1–2 TB analytical data, need SQL familiarity, full control over environment, tight budget, or on-premise requirement.- Redshift: steady, heavy analytical workloads with predictable patterns, need low-latency complex joins and control over compute, and team comfortable with cluster tuning.- BigQuery: cloud-first, highly variable concurrency, large-scale analytics (multi-TB to PB), minimal ops, fast time-to-insight, and you can enforce cost controls (partitioning/clustering, slot commitments) for predictability.

Practical notes for a Data Scientist- For exploratory work on moderate data, Postgres columnar is fast and cheap.- For production BI and repeated heavy models, prefer Redshift or BigQuery; BigQuery reduces maintenance and scales best for ad-hoc user spikes.- Always combine partitioning, clustering, sample tables, and query cost controls; monitor query patterns and re-evaluate as data and user patterns grow.

MediumTechnical

47 practiced

Given these tables and a heavy analytic query joining orders and order_items:

orders(id BIGINT PRIMARY KEY, created_at TIMESTAMP, status TEXT, customer_id BIGINT)order_items(id BIGINT PRIMARY KEY, order_id BIGINT, product_id BIGINT, price NUMERIC)

The query filters recent orders and groups by order id to compute totals. Propose concrete indexes (CREATE INDEX statements) to speed up the JOIN and aggregation, and justify them.

Sample Answer

Approach: create indexes that (1) make the range filter on recent orders sargable, (2) support the join from order_items → orders, and (3) make the aggregation a covering scan where possible so the planner can use index scans and avoid heavy heap lookups.

Suggested concrete CREATE INDEX statements (Postgres syntax):

sql

-- 1) Index to accelerate filter on recent orders (range scan), and provide id for join.
CREATE INDEX idx_orders_created_at_id ON orders (created_at DESC, id);

-- Alternative: if query also filters by status frequently, use composite:
CREATE INDEX idx_orders_created_at_status_id ON orders (created_at DESC, status, id);

-- If you're only interested in very recent orders (e.g. last 30 days), use a partial index:
CREATE INDEX idx_orders_recent_complete ON orders (id, created_at)
  WHERE created_at >= now() - interval '30 days' AND status = 'complete';

-- 2) Covering index on order_items to speed join and GROUP BY/aggregation (group by order_id, sum(price))
CREATE INDEX idx_order_items_orderid_price ON order_items (order_id, price);

-- If using Postgres INCLUDE (to make truly covering):
CREATE INDEX idx_order_items_orderid_price_inc ON order_items (order_id) INCLUDE (price);

Why these help- idx_orders_created_at_id: the query filtering "created_at >= X" can perform an index range scan instead of full table scan; including id ensures the planner can quickly locate matching order ids for the join.- Composite with status: if queries commonly filter status, placing it in the index reduces rows scanned.- Partial index: if analytical queries focus on recent data, a partial index is smaller and much faster to scan/update.- idx_order_items_orderid_price: join from order_items to orders uses order_id; grouping by order_id and summing price can be satisfied from the index (index-only scan) if the index covers price and the MVCC visibility map is good — reducing heap I/O.

Trade-offs and notes- Indexes increase write cost and storage; prefer partial/composite tailored to frequent predicates.- ORDER BY created_at DESC benefits if you query latest rows.- Ensure autovacuum/vacuum keeps visibility map updated to enable index-only scans.- Test with EXPLAIN ANALYZE for your workload; adjust columns/order (most selective predicates first) and consider INCLUDE for non-key covering columns.

MediumTechnical

44 practiced

A product manager wants session data available with low latency while retaining sessions for 90 days. Evaluate storing active session state in Redis vs PostgreSQL. Consider cost, durability, consistency, eviction and TTL, hot-warm architecture, and fallback strategies.

Sample Answer

Situation: You need low-latency access to active session state (reads/writes in real time) while retaining 90 days of session history for analytics and modeling.

Evaluation summary:- Redis (in-memory, optional persistence) - Latency & throughput: Excellent (sub-ms), ideal for serving features to models and real-time personalization. - Cost: High if you keep 90 days fully in memory—RAM is expensive at scale. Can reduce cost with Redis-on-Flash or sharding but complexity rises. - Durability & consistency: By default ephemeral; AOF/RDB persistence helps but risks small data loss on failover. Strong consistency requires careful cluster config (replication, WAIT). - Eviction & TTL: Native TTLs are easy for per-session expiration. Eviction policies (LRU, LFU) can drop keys under memory pressure—dangerous for correctness unless acceptable.- PostgreSQL (disk-backed) - Latency & throughput: Higher latency (ms–tens-ms), still acceptable for non-critical reads; not ideal for high QPS real-time lookups. - Cost: Cheaper long-term storage for 90-day retention; simpler scaling with read replicas and partitioning. - Durability & consistency: Strong durability and ACID guarantees—best for analytics, backfills, auditable logs. - TTL/Eviction: No native eviction; implement partitioning and scheduled deletes for retention.

Recommended hybrid (hot-warm) architecture:- Hot layer: Redis holds only active or recently-updated sessions (e.g., last 1–3 days or top N by activity) to serve low-latency needs, using TTLs. Configure persistence (AOF) and replication for acceptable RPO/RTO; pick eviction policy that favors keeping recently used keys.- Warm/cold layer: Postgres (or data lake) stores full 90-day session history. Use efficient schema (time-partitioned tables), and materialized views/indices for common queries.- Write strategy: Write-through or write-behind. Prefer synchronous write to Postgres for durability on session creation/close, and update Redis for fast reads. If write latency to Postgres is a concern, use async actor/queue but accept delayed persistence.- Consistency & fallback: On Redis miss, read from Postgres and populate Redis (cache-aside). Implement a version or last-updated timestamp to detect stale cache; use optimistic concurrency (compare-and-set) for updates.- Failure modes & safeguards: - If Redis evicts or cluster fails, Postgres is source of truth—ensure tools to bulk-warm Redis or serve degraded features directly from Postgres. - Monitor Redis memory, eviction events, and hit-rate; auto-scale or increase TTL window for critical keys. - For analytics pipelines, stream session writes via Kafka to both Redis (for immediate use) and Postgres/warehouse (for retention and model training).

Recommendation: Use hybrid: Redis for low-latency active session access with strict monitoring and persistence tuned; Postgres for durable 90-day retention and analytics. As a data scientist, ensure downstream feature pipelines consume from the durable store (or the Kafka stream) to avoid training on ephemeral/inconsistent Redis state.

Unlock Full Question Bank

Get access to hundreds of Database Design and Query Optimization interview questions and detailed answers.

Join thousands of developers preparing for their dream job.