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.

HardSystem Design

53 practiced

You must design the backend for a social feed (personalized timelines) with heavy reads, personalization, and eventual consistency for likes/comments. Compare using relational schemas, denormalized feed fan-out (push), fan-in (pull), stream processing, caching layers, and selection of primary database technologies. Make a recommendation and justify it for low-latency global reads.

Sample Answer

Requirements & constraints:- Personalized timelines, heavy read traffic, low-latency global reads (<100ms), large fanout from high-follower users, personalization ranking, eventual consistency acceptable for likes/comments, high availability and horizontal scale.

High-level recommendation (summary):Use a hybrid architecture: event-driven stream processing to maintain denormalized, materialized timelines (push/fan-out for normal users plus selective push for high-fanout "celebs"), backed by a globally-distributed NoSQL primary store (DynamoDB Global Tables or Apache Cassandra), with an edge caching layer (Redis Global/Elasticache + CDN) and a pull fallback for cold users. Use Kafka (or MSK) + stream processors (Flink/Beam) for personalization and incremental updates. Persist likes/comments in a separate service with async reconciliation to timelines.

Architecture & components:1. Writer services: accept posts, likes, comments. Writes append events to an event bus (Kafka).2. Event stream + stream processing: real-time jobs compute personalization scores, apply business rules, and produce timeline write tasks.3. Fan-out strategy: - Push (denormalized materialized view): for regular users push a post id into each follower’s timeline partition in the primary DB (precomputed ordered list) — enables very low read latency. - Selective push + pull: for high-fanout users (millions of followers) avoid full push; write to a “celebrity” bucket and on reads, merge celebrity recent posts by pull. Also maintain MV of hot followers only.4. Storage: - Primary store for timelines: DynamoDB with per-user timeline item lists or wide-row Cassandra with time-ordered partitions. Choose DynamoDB Global Tables for managed multi-region low-latency reads and serverless scale; Cassandra if you prefer self-managed, tunable consistency. - Write store for canonical posts/metadata: same DB or separate document DB (DynamoDB / CockroachDB for strong transactional needs). - Likes/comments: persistent store + materialized counters; event-driven updates to timeline view (eventual).5. Caching & CDN: - Per-user timeline cache in Redis (Global Datastore / Geo-replicated Redis). Cache top N items; cold reads hit DB. - Edge CDN for profile assets and possibly pre-rendered timeline fragments for anonymous/unauthenticated reads.6. Read path: - Read hits Redis cache -> if miss, read precomputed timeline from DynamoDB/Cassandra (low-latency), merge celebrity bucket if needed, apply lightweight personalization (server-side) and return.

Why this mix:- Denormalized fan-out (push) gives constant-time reads — ideal for heavy-read workloads.- Pure pull (fan-in) causes expensive join/merge per read and high tail latency for heavy reads; unacceptable for low-latency global reads unless combined with aggressive caching.- Selective push mitigates the expensive cost of pushing from extremely high-fanout users, avoiding huge write amplification.- Stream processing (Kafka + Flink/Beam) enables reliable, scalable, observable materialized view updates and incremental ranking, and supports replay/repair.- Global NoSQL (DynamoDB Global Tables) provides managed multi-region replication for single-digit ms reads, simplifies operational overhead; Cassandra is an alternative when you need multi-master write locality and control.- Redis as an edge cache reduces read latency further and can serve most requests from memory.

Trade-offs & considerations:- Storage cost vs read latency: full push increases storage and write amplification but optimizes read SLAs.- Complexity: hybrid strategy + stream processing increases operational complexity; requires robust monitoring, backpressure control, and repair jobs.- Consistency: likes/comments are eventually consistent; provide versioning/timestamps and reconcile background jobs to fix inconsistencies.- Hot partitions: shard timelines by user-id ranges or use time-based partitions to avoid hotspots.- Monitoring & replay: retain Kafka logs long enough to rebuild timelines if needed; provide idempotent processors.

Operational recommendations:- Start with DynamoDB Global Tables + Kafka (MSK) + Flink, Redis Global for caching to reach low-latency global reads quickly.- Implement rate-limited, batched fan-out workers and per-user fanout policies.- Build tools for repair, metrics (tail latency, cache hit rate), and backfill pipelines.

Conclusion:For low-latency global reads at scale, a hybrid denormalized push (for most users) + selective pull (for extreme fanout) architecture, powered by stream processing and backed by a globally-distributed NoSQL store with a geo-replicated Redis cache, offers the best balance of read performance, scalability, and manageability.

EasyTechnical

57 practiced

Large OFFSET queries slow down pagination. As a Solutions Architect, describe efficient pagination approaches for APIs with large result sets (e.g., millions of rows). Include examples of keyset (cursor) pagination and strategies when sorting by multiple columns is required.

Sample Answer

Problem: OFFSET N scans/reads rows before the page and gets slower as N grows. For large result sets use keyset (cursor) pagination (seek method) which avoids scanning skipped rows.

Keyset (cursor) pagination — single-column example:- Use a deterministic sort (e.g., created_at DESC, id DESC as tie-breaker).- Return an opaque cursor with last-row values.

sql

-- Page 1
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) <= ('2025-12-01T00:00:00Z', 12345) -- cursor for next page
ORDER BY created_at DESC, id DESC
LIMIT 50;

Server encodes (created_at, id) into the cursor token. For next page, client sends that token; DB uses the WHERE clause to "seek" directly using indexes — very fast.

Multi-column sorting / tie-breakers:- Include all sort columns in the index and in the cursor.- Translate lexicographic comparison into SQL predicates. Example for sort by priority ASC, created_at DESC, id DESC:

sql

WHERE (priority > :p)
   OR (priority = :p AND (created_at < :t OR (created_at = :t AND id < :id)))
ORDER BY priority ASC, created_at DESC, id DESC
LIMIT 50;

Implementation notes & trade-offs:- Build composite indexes matching ORDER BY to make seeks index-only.- Use a stable unique tie-breaker (PK) to ensure consistent pages under concurrent writes.- Opaque cursors: base64-encode JSON of key values to avoid leaking schema.- Backward paging: either keep reverse order queries or materialize cursors (store last-page pointers).- Jumping to arbitrary page numbers still needs offsets or precomputed/estimated offsets — avoid for very large datasets; instead provide search/filter or “go to time” UX.- Provide reasonable page size limits, caching, and include no-total-count option to avoid expensive COUNT(*) on huge tables.

This approach gives predictable latency, reduces DB work, and scales to millions of rows.

MediumTechnical

56 practiced

Write a PostgreSQL SQL query using a CTE and window functions to return the top 3 highest spending users per month from transactions(transaction_id, user_id, amount, occurred_at). Return columns: month, user_id, month_total, rank. Explain performance considerations for large datasets.

Sample Answer

First, aggregate monthly totals per user in a CTE, then use a window function to rank users per month and filter top 3:

sql

WITH monthly_user_totals AS (
  SELECT
    date_trunc('month', occurred_at)::date AS month,
    user_id,
    SUM(amount) AS month_total
  FROM transactions
  GROUP BY 1, 2
)
SELECT
  month,
  user_id,
  month_total,
  rk AS rank
FROM (
  SELECT
    month,
    user_id,
    month_total,
    ROW_NUMBER() OVER (PARTITION BY month ORDER BY month_total DESC, user_id) AS rk
  FROM monthly_user_totals
) t
WHERE rk <= 3
ORDER BY month, rank;

Performance considerations for large datasets:- Indexes: create a btree index on (occurred_at) or a composite index (date_trunc cannot use function directly) — instead add a persisted/month column or functional index: CREATE INDEX ON transactions (date_trunc('month', occurred_at));- Partitioning: range-partition transactions by month/year to limit scanned partitions for historic queries.- Pre-aggregation: maintain a daily or monthly summary table (or materialized view) refreshed incrementally for faster top-N.- Parallelism & memory: tune work_mem for windowing/aggregation and enable parallel query execution.- Filtering: add WHERE clauses (date range) to reduce scanned rows.- Trade-offs: real-time accuracy vs. query speed — summaries/materialized views reduce latency for near-real-time needs but require refresh logic.

MediumTechnical

50 practiced

You are given this schema: orders(order_id, customer_id, created_at, total), order_items(order_item_id, order_id, product_id, qty, price). A slow query joining orders and order_items is running. Describe how you would use an execution plan (EXPLAIN) to diagnose the bottleneck and list specific index changes or query rewrites that commonly fix join performance issues.

Sample Answer

Start by running EXPLAIN (or EXPLAIN ANALYZE) on the slow statement and inspect these fields: access method (seq scan / index scan), possible_keys/key/key_len, rows, cost, and Extra (Using where, Using temporary, Using filesort). For joins, note the join algorithm (nested loop, hash, merge) and which table is driving the join.

Diagnosis checklist (what to look for)- Sequential scan on a large table where an index could be used (key = NULL / possible_keys shows nothing).- Nested loop joining a large outer with large inner and high estimated rows → likely many probes.- “Using temporary; Using filesort” → sorts or GROUP BY causing disk work.- Big difference between estimated rows and actual rows → stale stats.- No covering index → engine must read full rows causing IO.

Concrete fixes (indexes)- Ensure PKs exist: orders(order_id) and order_items(order_item_id).- Add a foreign-key index to make join efficient: CREATE INDEX idx_order_items_order_id ON order_items(order_id);- Create covering indexes for common queries to avoid lookups: CREATE INDEX idx_oi_orderid_price_qty ON order_items(order_id, product_id, price); or for queries filtered by date: CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at);- For queries filtering by created_at and aggregating totals, a composite index (created_at, order_id, total) helps.

Query rewrites and other remedies- Push filters before joins (apply WHERE early or use subquery that pre-filters).- Replace SELECT * with only needed columns so a covering index can be used.- Change JOIN order or use semi-joins (EXISTS) to avoid materializing large result sets.- Use derived tables to aggregate before joining (reduce rows early).- FORCE INDEX / optimizer hints only as last resort.- Run ANALYZE/ANALYZE TABLE to refresh stats; increase work_mem / sort_buffer for hash/merge joins if needed.- Consider partitioning orders by date if volume is huge, or summarizing data in a materialized view.

Validation- After each change re-run EXPLAIN ANALYZE and compare actual time, I/O, and rows. Aim to replace expensive seq scans or nested-loop probes with index scans or hash/merge joins that scale better.

MediumTechnical

79 practiced

A production database shows slow transactional throughput. Describe the methodical steps you would take to determine whether the cause is CPU, disk IO, network, lock contention, or poor query plans. List specific metrics, system views, and tools you would use and what thresholds or signals you would look for.

Sample Answer

Start with a hypothesis-driven triage and gather a baseline (RPS, latency P50/P95/P99, concurrency). Correlate database metrics with OS and app layer to narrow CPU vs IO vs network vs locks vs plan issues.

1) Baseline & correlate- Metrics: DB TPS, transactions/sec, avg/95/99 response time, active connections.- Tools: Grafana/Datadog/CloudWatch / APM traces to correlate app latency spikes with DB metrics.- Signal: latency spikes aligned with increased TPS → resource saturation; spikes without TPS change → contention or bad plans.

2) CPU- OS: top/htop, mpstat; Cloud: EC2 CPUUtilization.- DB: Postgres pg_stat_activity CPU usage per PID (via pg_stat_statements joins), SQL Server sys.dm_os_waiting_tasks.- Look for sustained CPU >70–80%, user/system high, runqueue > number of cores.- If CPU bound: high system/user % and low IO wait.

3) Disk IO- OS: iostat -x, iotop, vmstat, sar. Key: util% (should not be near 100%), await (avg latency), svctm, await >10–20ms is suspicious; queue length high.- DB: PostgreSQL pg_stat_io extensions or check checkpoint/writeback rates; SQL Server: sys.dm_io_virtual_file_stats (latency >20ms per file problematic).- Signal: high iowait, high disk util, long write latencies, slow checkpoints.

4) Network- Tools: ifstat, ss, netstat, tcpdump for packet loss; Cloud: ENI metrics.- Signal: high retransmits, packet loss, latency between app and DB >5–10ms affecting RPC-heavy workloads.

5) Lock contention- DB views: Postgres pg_locks + pg_stat_activity (blocked_by), pg_blocking_pids(); SQL Server sys.dm_tran_locks, sys.dm_os_waiting_tasks, blocking_session_id.- Metrics: number and duration of blocking chains, longest blocker, % transactions waiting on locks, lock wait times rising before latency increases.- Signal: many sessions in waiting state, long-running transactions holding locks.

6) Poor query plans- DB: EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements (top time/rows), pt-query-digest (MySQL), sys.dm_exec_query_stats + sys.dm_exec_sql_text + sys.dm_exec_query_plan (SQL Server), AWR/ASH (Oracle).- Look for: heavy full table scans, high logical reads, high CPU per execution, plan changes vs baseline, parameter sniffing symptoms.- Action: capture slow query samples, run EXPLAIN ANALYZE, check missing indexes, stats staleness, and plan stability.

7) Remediation workflow- Reproduce on staging and capture profiler/traces (pg_stat_statements, Extended Events, slow_query_log).- Short-term mitigations: increase resources, add read replicas, add indexes, kill runaway queries, tune MVCC/auto-vacuum/checkpoint.- Long-term: query tuning, indexing strategy, schema changes, partitioning, caching, capacity planning.

Thresholds summary:- CPU sustained >70–80%; runqueue > cores.- Disk await/latency >10–20ms; device %util ~90%+.- Network packet loss >0.1–1% or RTT increases above app tolerance.- Lock wait times causing queueing and many blocked sessions.- Queries with >10x expected cost, excessive logical reads, or high p99 duration.

Document findings, timeline, and proposed fixes; validate with post-change metrics.

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.