Relational Database Fundamentals and Design Questions

Core concepts of relational databases and schema design including tables, relationships such as one to one one to many and many to many, primary keys and foreign keys, data integrity constraints, and the properties of atomicity consistency isolation and durability and why they matter. Understand differences between relational systems using structured query language and nonrelational databases, indexing strategies, normalization and denormalization trade offs, simple query optimization techniques, and when to choose a normalized relational design versus a document or key value store. Candidates should be able to perform basic entity identification, produce simple schema diagrams, explain persistence and durability considerations, and reason about basic performance and scaling trade offs.

MediumTechnical

21 practiced

You have frequent queries that filter by combinations of columns a,b and order by c. Given queries:1) WHERE a = ? AND b = ? ORDER BY c DESC2) WHERE b = ? AND c = ?3) WHERE a = ? ORDER BY bExplain how to design composite indexes using the leftmost-prefix rule and INCLUDE columns (Postgres) to optimize these patterns. Describe trade-offs in index count and write cost.

Sample Answer

Situation: We need indexes that make these frequent queries fast while minimizing write overhead.

Approach & leftmost-prefix rule:- A b-tree composite index can be used only if the query predicates respect the leftmost-prefix of the key list: an index on (x,y,z) can be used for predicates on x, (x,y), or (x,y,z) — and for ordering when the index key order matches ORDER BY.

Recommended indexes (Postgres syntax + INCLUDE to create covering indexes without affecting sort order):

1) Query: WHERE a = ? AND b = ? ORDER BY c DESC- Create an index whose key order matches equality predicates then ordering column: CREATE INDEX idx_a_b_c_desc ON t(a, b, c DESC) INCLUDE (<other selected columns>);- Reason: keys (a,b) are leftmost so the equality filters use the index; c DESC provides ordered rows so no sort.

2) Query: WHERE b = ? AND c = ?- Create: CREATE INDEX idx_b_c ON t(b, c) INCLUDE (<other selected columns>);- Reason: leftmost b then c — both are equality predicates so index is perfect. Alternatively, if you already have idx_a_b_c_desc but a is not provided, that index cannot be used efficiently for WHERE b = ? AND c = ?.

3) Query: WHERE a = ? ORDER BY b- This is satisfied by idx_a_b_c_desc because (a,b) is the leftmost prefix: - The engine can do an index scan on (a,b,...) which returns rows ordered by b for a fixed a. If you want to avoid having c in keys you could instead create CREATE INDEX idx_a_b ON t(a, b) INCLUDE (<other selected columns>); - But idx_a_b_c_desc already supports this; adding c as a later key does not prevent ORDER BY b for a fixed a.

Trade-offs:- Index count vs write cost: each index increases INSERT/UPDATE/DELETE cost and storage. Two indexes above (idx_a_b_c_desc and idx_b_c) are likely sufficient; adding a separate idx_a_b is redundant if idx_a_b_c_desc exists — avoid duplicate prefixes to reduce write overhead.- INCLUDE columns: storing non-key columns in INCLUDE makes queries “covering” (index-only scans) without affecting sort/index order. INCLUDE has minimal extra b-tree cost but increases index size (hence write I/O).- Size vs selectivity: put highly selective/equality predicates earlier. If c were frequently used in ORDER BY with mixed direction, consider creating the index with the matching DESC/ASC.- Alternatives: partial indexes (WHERE <filter>) to reduce index size if predicates are narrow; expression indexes for collations; materialized views if queries are heavy and data is mostly static.

Summary:- Create idx_a_b_c_desc (a,b,c DESC) INCLUDE(...) and idx_b_c (b,c) INCLUDE(...). Avoid redundant prefixes to limit write amplification. Use INCLUDE to get index-only scans and reduce heap fetches. Consider partial indexes and monitor index size and write latency to balance read speed vs write cost.

EasyTechnical

23 practiced

Consider this query against large tables:

SELECT * FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.user_id = 123 ORDER BY o.placed_at DESC;

Describe simple, practical optimizations: rewrite to return only needed columns, appropriate indexes (exact columns and order), and how to make the ORDER BY efficient for pagination.

EasyTechnical

39 practiced

You must choose between a relational database, a document store, and a key-value store for two different systems: (A) a user profile store where fields change frequently and different users have different attributes; (B) a financial ledger tracking account balances and transactions. For each system, choose the best storage type and justify your decision with respect to consistency, query patterns, schema flexibility, and index needs.

HardSystem Design

37 practiced

Explain the difference between partitioning (horizontal partitioning within a single DB instance) and sharding (splitting data across multiple DB instances). For a user data workload, describe how you'd choose a shard key, route requests, handle cross-shard joins, and manage re-sharding as traffic patterns change.

Sample Answer

Requirements & constraints:- User data workload: low-latency reads for profile/auth, high write volume for events, per-user transactions must be consistent, expect growth from millions → tens of millions of users.- Non-functional: availability, scalability, reasonable cross-user queries (analytics), operational simplicity.

Partitioning vs Sharding (concise):- Horizontal partitioning (single DB instance): split a table into ranges/segments managed by same DB process (e.g., Postgres table partitioning). Benefits: simpler routing, single transaction semantics; limited by single-node CPU/IO and fault domain.- Sharding: data split across multiple DB instances (each a shard). Benefits: linear horizontal scale, smaller working set per instance; trade-offs: routing complexity, distributed transactions, cross-shard operations harder.

Choosing a shard key:- Prefer stable, uniformly distributed, frequently used in request routing. For users: user_id (hashed) is common.- Hash(user_id) → even distribution and avoids hot shards; avoid time-sequential keys which create hotspots.- If locality matters (geo), can combine: geo_region + hash(user_id) to support data residency and lower cross-region latency.

Request routing:- Stateless routing service (API layer or middleware) maps user_id → shard using consistent hashing or virtual nodes. Store mapping in a distributed config (etcd/consul) or use client libraries.- Cache mappings at app edge with TTL. For writes/reads include shard id in connection string and use connection pools per shard.

Cross-shard joins & transactions:- Avoid at OLTP: design denormalized user profile replicated to other services; keep transactional scope within a shard.- For necessary cross-shard reads: perform scatter-gather queries (fan-out), aggregate in application or use secondary systems (OLAP) for joins.- For multi-shard transactions: use Saga patterns or two-phase commit only if necessary; prefer compensation logic to maintain availability.

Re-sharding (scaling & operational strategy):- Use consistent hashing with virtual nodes to minimize movement; when adding capacity, remap hash ranges gradually.- Implement online re-sharding pipeline: - Create new shard(s) and start dual-writes (write to old and new) or use change-data-capture (CDC) to stream deltas. - Backfill historical data in parallel; verify checksums and monitor lag. - Switch routing once backfill caught up; retire old shard.- Automate with orchestration (Kubernetes, Terraform) and tooling (gh-ost/pt-online-schema-change style for MySQL; Debezium for CDC).- Monitor hotspots, QPS per shard, storage, tail latencies; use metrics to trigger resharding.

Trade-offs & consistency:- Hash sharding maximizes balance but loses natural range scans; range-sharding supports range queries but risks hotspots.- Always favor design that keeps transactions local; rely on OLAP/ETL pipelines for global analytics.- Plan for capacity, backups, and disaster recovery per shard.

MediumTechnical

23 practiced

Demonstrate how to implement an UPSERT pattern in PostgreSQL using INSERT ... ON CONFLICT DO UPDATE. Show sample SQL that upserts into an inventory table (sku, qty, last_seen). Discuss concurrency implications, what unique index you need for the conflict target, and how to avoid race conditions during high parallelism.

Sample Answer

Use a single INSERT ... ON CONFLICT DO UPDATE so the DB performs the insert-or-update atomically (no separate SELECT). Example: maintain inventory by SKU, incrementing qty and setting last_seen to the latest timestamp.

sql

-- Ensure unique index on sku for conflict target
CREATE TABLE inventory (
  sku text PRIMARY KEY,        -- unique index on sku
  qty bigint NOT NULL DEFAULT 0,
  last_seen timestamptz
);

-- Upsert: add incoming_qty, update qty atomically, and update last_seen only if newer
INSERT INTO inventory (sku, qty, last_seen)
VALUES ('ABC-123', 5, now())
ON CONFLICT (sku) DO UPDATE
SET
  qty = inventory.qty + EXCLUDED.qty,                                 -- atomic increment
  last_seen = GREATEST(inventory.last_seen, EXCLUDED.last_seen)       -- avoid overwriting newer timestamp
RETURNING *;

Why this works- Conflict target: you need a unique constraint or index on the column(s) you use in ON CONFLICT — here PRIMARY KEY or UNIQUE(sku).- The DB executes the INSERT and, if a unique violation occurs on sku, applies the DO UPDATE in the same statement and lock scope, avoiding classic check-then-insert races.

Concurrency implications & race conditions- ON CONFLICT DO UPDATE is atomic at statement level: it acquires the necessary row-level lock to avoid two sessions inserting the same sku from both succeeding inserts. This prevents the "lost insert" race you get when doing separate SELECT then INSERT.- However, concurrent ON CONFLICT updates can still contend on the same row (serialization, row locks). If two transactions concurrently run the above and both try to add qty, they will serialize their updates — one will wait, then apply, so the qty increments correctly if you use inventory.qty + EXCLUDED.qty as above.- If you have more complex logic or care about ordering of last_seen, use expressions like GREATEST(...) to avoid stale overwrites.

Further robustness under high parallelism- Use a single upsert statement rather than SELECT-then-INSERT.- Keep ON CONFLICT update logic idempotent and deterministic (no non-deterministic side-effects).- For workloads with heavy contention on the same key: - Consider batching updates by key to reduce contention. - Use application-side sharding of hot keys. - Use advisory locks for very strict serialization (pg_advisory_lock / pg_try_advisory_lock) around the key when necessary: - Acquire a consistent hash-based advisory lock, perform upsert, release — but be careful: locks add latency and reduce concurrency. - Alternatively, use higher isolation (SERIALIZABLE) and implement retry logic for serialization failures (catch SQLSTATE '40001').

Performance tips- Ensure the conflict target column(s) have a supporting unique index to avoid full-table locks.- Reduce work in DO UPDATE; only update columns when needed via a WHERE clause in DO UPDATE (Postgres supports WHERE in DO UPDATE), e.g.:

sql

ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty,
    last_seen = EXCLUDED.last_seen
WHERE EXCLUDED.last_seen > inventory.last_seen;

This prevents unnecessary WAL churn and triggers.

Summary- Create UNIQUE/PK on sku, use single INSERT ... ON CONFLICT (sku) DO UPDATE with atomic expressions, use GREATEST/WHERE to avoid stomping newer data, and for heavy contention add batching, sharding, advisory locks, or retry-on-serialization strategies.

Unlock Full Question Bank

Get access to hundreds of Relational Database Fundamentals and Design interview questions and detailed answers.

Join thousands of developers preparing for their dream job.