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.

MediumSystem Design

57 practiced

Design a table schema for an event tracking system that supports high write throughput and efficient analytics queries for daily dashboards. Include primary keys, suggested indexes, partitioning strategy, and a short justification of choices. Example events: user_id, event_type, properties JSON, occurred_at.

Sample Answer

Requirements:- Very high write throughput (append-only events)- Fast daily/real-time dashboard queries (daily aggregates, funnels)- Flexible properties per event (JSON)- Easy retention (purge old data)

Schema (Postgres-oriented example):

events (append-only)- event_id UUID PRIMARY KEY DEFAULT gen_random_uuid()- user_id BIGINT NOT NULL- event_type TEXT NOT NULL- occurred_at TIMESTAMPTZ NOT NULL -- event time- event_date DATE NOT NULL GENERATED ALWAYS AS (occurred_at::date) STORED- properties JSONB

Suggested primary key / uniqueness:- event_id PK (UUID) for globally unique id and easy dedupe on ingest

Indexes:- BRIN on occurred_at (for very large tables if perfectly append-only) OR btree on occurred_at DESC for low-latency range scans- btree composite index (event_type, occurred_at) for filtering by event and time- btree (user_id, occurred_at) for user-session queries- GIN index on properties jsonb (properties jsonb_path_ops if supported) for analytical filters on properties

Partitioning:- RANGE partition on event_date (daily or weekly partitions depending on volume). Example: one partition per day for >10M events/day, or per week for lower volume.- Use declarative partitioning (Postgres) with automatic creation/retention scripts.- Local indexes per partition (avoids global index maintenance on heavy writes)

Ingest & scaling notes:- Bulk/stream ingestion via COPY or COPY FROM with batching; disable synchronous_commit for ingestion replicas if acceptable.- Consider a write-optimized landing table (unpartitioned) + background worker that routes/moves into partitioned table to reduce insertion overhead.- For very high scale, use a columnar/analytics DB (ClickHouse, BigQuery) as downstream for dashboards; stream events from OLTP into analytics store via Kafka.

Analytics optimization:- Maintain daily aggregated tables (materialized views or scheduled ETL) keyed by date/event_type/user_cohort to serve dashboard queries quickly.- Use CTAS or incremental materialized views refreshed nightly or near-real-time via streaming.

Justification:- UUID PK + append-only partitions supports deduplication and extremely fast writes (no global index hotspots).- Daily RANGE partitions make time-bounded queries and retention efficient.- GIN JSONB supports flexible property queries without rigid schema.- Aggregate tables reduce dashboard latency and cost.

HardTechnical

46 practiced

Your analytics workloads run on a cloud-managed database with autoscaling. Costs have spiked due to heavy ad-hoc queries. Propose concrete optimizations at schema, query, and operational levels to reduce cloud spending while maintaining acceptable performance for analysts. Include cost-vs-latency trade-offs.

Sample Answer

Situation: Costs spiked because analysts run heavy ad-hoc queries on a cloud-managed, autoscaling DB.

Approach: I’d tackle this at three levels — schema, query, and operational — balancing cost vs. latency and analyst productivity.

Schema-level optimizations- Add targeted partitioning (time/date) to limit scanned data and reduce IO costs.- Create selective clustering/sort keys or materialized views for high-cardinality joins/filters.- Denormalize hot-read tables (pre-join) for frequent analyst use to reduce expensive runtime joins.- Trade-off: more storage (and ETL complexity) for much lower query compute.

Query-level optimizations- Encourage predicate pushdown and LIMITs; avoid SELECT *.- Use sampled tables for exploratory analysis (e.g., TABLESAMPLE or CREATE TABLE AS SELECT ... WHERE rand() < 0.01).- Cache results of expensive ad-hoc queries into temp or result tables for reuse.- Example: materialize daily cohort aggregation:

sql

CREATE MATERIALIZED VIEW mv_daily_cohort AS
SELECT cohort, date_trunc('day', ts) day, count(*) users
FROM events
GROUP BY 1,2;

- Trade-off: slightly higher latency for fresher data if refresh interval is reduced vs. cost savings if refreshed nightly.

Operational-level optimizations & governance- Enforce query runtime caps and autosuspend idle sessions.- Provide curated analytics layer: a set of vetted views/materialized tables and example queries in a shared notebook.- Implement cost-awareness: tag queries by user/team, monitor top consumers, and allocate budgets/alerts.- Offer a self-serve sandbox with scaled-down compute for exploration and a production pool for scheduled/critical jobs.

Cost vs. latency guidance- For exploratory work: use sampling + smaller compute — low cost, higher latency/variance acceptable.- For dashboards: precompute materialized views or nightly aggregates — moderate cost, low latency.- For SLA-critical reports: reserve compute or use higher concurrency warehouses — higher cost, minimal latency.

Result: These changes reduce per-query scanned data, avoid repeated expensive computations, and shift heavy work into scheduled/materialized pipelines — cutting autoscaling spikes while keeping interactive analysis usable.

EasyTechnical

50 practiced

List the most common root causes of slow queries in analytic workloads and propose an ordered checklist of diagnostic steps you would run when a scheduled report suddenly starts taking much longer. Include quick checks you can run without admin privileges and deeper checks that require DBA access.

Sample Answer

Common root causes (analytic workloads)- Missing or stale statistics causing bad plans- Lack of appropriate indexes or wrong index usage- Large table scans due to growing data volumes or filters that can’t use indexes- Skewed data / hot partitions- Blocking/locks or long-running transactions- Resource contention (CPU, I/O, memory) or concurrency spikes- Suboptimal query plans (wrong joins / functions on columns)- Network latency or downstream system slowness (ETL / API)- Maintenance tasks (backups, vacuum, compaction) running concurrently

Ordered diagnostic checklist (quick → deeper)

Quick checks (no DBA privileges)1. Reproduce and note runtime: run query and capture elapsed time.2. Check result size and cardinality:

SELECT COUNT(*) FROM ... WHERE ...;

3. Run EXPLAIN/EXPLAIN ANALYZE to see plan and hotspots (look for seq scans, large sorts, nested loops):

EXPLAIN ANALYZE <your query>;

4. Look for missing filters or unintended cross joins in the SQL.5. Check for recent schema/data changes (new columns, big data loads) with team/ETL logs.6. Test simplified variants (limit, remove joins) to isolate slow component.

Deeper checks (DBA access or with DBA help)7. Review statistics/optimizer stats age; run ANALYZE if stale.8. Check indexes existence and usage; consider adding covering indexes or filtered indexes.9. Inspect running processes, locks, and blocking sessions.10. Monitor resource metrics (CPU, disk I/O, memory, swap) and concurrent query count.11. Examine query plan history / plan changes (plan drift) and consider forcing plan or hints temporarily.12. Check partition health and partition pruning effectiveness.13. Review maintenance schedule (vacuum/compaction) and recent heavy jobs.14. If cloud, check storage throughput limits / throttling.

Actions & priorities- First: run EXPLAIN ANALYZE and reduce query to narrow the expensive operator.- If plan shows scans/sorts: update stats, add relevant indexes, or rewrite query (use EXISTS, pre-aggregate).- If resource contention: schedule heavy workloads off-peak or request more resources.- Communicate findings with DBA/ETL owners and roll out fixes in stages (test, validate, deploy).

Typical fixes: update stats, rewrite joins/filters, add indexes or materialized views, partitioning, and rescheduling ETL/maintenance.

EasyTechnical

45 practiced

You suspect a database has missing useful indexes. As a data analyst, describe a step-by-step method to detect missing indexes using database metrics and query logs. What signals would you look for (e.g., long sequential scans, high CPU on certain queries), and how would you prioritize which missing indexes to create?

Sample Answer

Step-by-step method:

1. Gather baseline metrics- Pull overall slow-query and resource stats (Postgres example):

sql

SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 50;

- Check table-level scans/hit ratios:

sql

SELECT relname, seq_scan, idx_scan, n_tup_read, n_tup_ins
FROM pg_stat_user_tables;

2. Look for signals of missing indexes- High seq_scan counts on large tables (seq_scan >> idx_scan).- Queries with high total_time or mean_time and many calls.- EXPLAIN/EXPLAIN ANALYZE showing sequential scans, large estimated vs actual rows, or nested loop over large sets.- High CPU or I/O correlated to specific queries (monitoring tool or vmstat/iostat).- Frequent predicates on columns not covered by indexes (WHERE, JOIN, ORDER BY, GROUP BY).

3. Drill into candidate queries- For top offenders run EXPLAIN ANALYZE to confirm full table scan and estimate time saved.- Example: find predicate columns and cardinality; check whether an index can be selective.

4. Prioritize index creation- Rank by expected benefit = frequency * (current_time - estimated_index_time). Practically: - High frequency + high latency first. - Queries touching very large tables next. - High business impact queries (dashboards, SLAs) prioritized even if less frequent. - Favor indexes with low write overhead (on tables with fewer writes). - Prefer covering indexes that satisfy SELECT columns to avoid lookups.

5. Validate and monitor- Create index on staging or with CONCURRENTLY (Postgres) for production safety:

sql

CREATE INDEX CONCURRENTLY ON schema.table (col1, col2);

- Re-run pg_stat_statements and EXPLAIN ANALYZE; measure reductions in total_time, seq_scan, CPU, and dashboard latency.- Rollback if write performance regressions occur or benefits are marginal.

Key trade-offs: index improves reads but costs writes and storage. Use selective columns, composite indexes in query order, and consider partial or expression indexes for sparsity.

EasyTechnical

76 practiced

Explain database normalization and the first three normal forms (1NF, 2NF, 3NF). For each form: describe the rule, show a concise example of a violation in a customer-orders schema, and explain how you would correct it. As a data analyst, also explain situations where you would intentionally denormalize data for reporting and why.

Sample Answer

Database normalization is organizing tables to reduce redundancy and ensure data integrity. Below are the first three normal forms with concise customer-orders examples and fixes, plus when a data analyst might denormalize for reporting.

1NF (First Normal Form)- Rule: Each column holds atomic (indivisible) values and each row is unique.- Violation example: orders table has columns order_id, customer_id, product_ids where product_ids = "12,34,56".- Fix: Use a separate order_items table with one product per row: order_items(order_id, product_id, quantity).- Why: Enables reliable querying, aggregation, and indexing.

2NF (Second Normal Form)- Rule: Table is in 1NF and every non-key column is fully functionally dependent on the whole primary key (relevant for composite keys).- Violation example: order_items(order_id, product_id) PK and a column customer_name stored in order_items — customer_name depends only on customer_id, not the composite PK.- Fix: Move customer_name to customers(customer_id, customer_name) and keep only customer_id in orders/order_items.- Why: Prevents update anomalies and repeated storage of customer data.

3NF (Third Normal Form)- Rule: In 2NF and no transitive dependencies — non-key columns depend only on the primary key, not on other non-key columns.- Violation example: customers(customer_id, city, state, state_tax_rate). state_tax_rate depends on state, not customer_id.- Fix: Extract states(state, state_tax_rate) and reference state in customers.- Why: Avoids inconsistent updates and redundant storage of attributes derived from other non-key fields.

When to denormalize as a data analyst- For reporting/performance: create star-schema or materialized views/aggregated tables (e.g., orders_fact with customer_dim and product_dim flattened) to speed up read-heavy dashboards and simplify SQL for BI tools.- When to choose denormalization: large analytic queries with many joins causing high latency, requirement for precomputed aggregates (daily totals), or when BI tools expect flat tables.- Trade-offs: faster reads and simpler queries vs. increased storage and need for ETL to maintain consistency; prefer denormalized copies for reporting layers while keeping normalized OLTP for transactional integrity.

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.