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

45 practiced

Design a sharding strategy for a multi-tenant SaaS application with tenants ranging from 10 users to 10 million users. Requirements: online tenant onboarding, ability to move hot tenants off a shard, minimize cross-shard joins, and support global backups. Describe shard key choices, metadata management, tenant placement logic, and how you'd handle resharding for growth.

Sample Answer

Requirements (clarified):- Multi-tenant DB with tenants from 10 to 10M users- Online onboarding, move-hot-tenant support, minimize cross-shard joins, global backups, resharding/growth

High-level approach:- Hybrid tenant-sharding: tenant-isolation by default (one tenant -> one shard) for mid/large tenants; pack small tenants into shared shards (multi-tenant bins). Use logical tenant ID as primary routing key; physical shards = DB instances/clusters.

Shard key choices:- Primary: tenant_id (routes all tenant-scoped data to same shard → zero cross-shard joins for intra-tenant queries).- For extremely large tenants, use composite shard key: (tenant_id, user_id % N) to horizontally split tenant across multiple shards (N chosen per-tenant dynamically).

Metadata management:- Central Placement Service (metadata DB + cache like Redis): maps tenant_id → shard_id, shard capacity, hotness score, shard topology, and per-tenant split-info. Expose API for router/proxy and control-plane.

Tenant placement logic (onboarding & movement):- On onboarding: Placement service picks either (a) dedicated shard if expected > threshold or (b) a bin shard with space; update metadata atomically.- Hot tenant detection: monitor metrics (throughput, CPU, latency). If hot and sharing shard, trigger move: provision target shard (or split across dedicated shards), perform online data migration (change metadata to point traffic to target), use dual-write + change-data-capture (CDC) to sync, then cutover when sync lag small.- To move within-shard large tenant to multiple shards, compute per-user partitioning function and update routing metadata.

Resharding for growth:- Two patterns: 1. Bin shards: when bin fills, create new bin shard and rebalance tenants (move cold tenants first) using online migration (CDC + cutover). 2. Big-tenant split: add shards and change tenant-level mapping to (tenant_id, hash(user_id)%k). Migrate ranges/users incrementally.- Use consistent hashing for ephemeral rebalancing to minimize moves; use background rebalancer that respects capacity and SLA.

Minimize cross-shard joins:- Enforce data model: colocate tenant-scoped tables; avoid cross-tenant joins. For cross-tenant/global queries, maintain an aggregated global store (analytics warehouse) fed by CDC.

Global backups:- Shard-level snapshots/backups coordinated by backup service using metadata. For point-in-time consistency across shards, use signed timestamps from a global clock service; trigger consistent snapshot across all shards by recording LSN/timestamp and then backing up each shard to that LSN via WAL/CDC.

Operational considerations:- Automate provisioning with infra-as-code, runbook for migrations, rate-limit moves, and test migrations on staging.- Security: tenant encryption keys stored in KMS, rotate after moves.- Metrics + alerts drive autoscaling and placement policies.

Trade-offs:- Tenant-isolation reduces cross-shard joins but increases number of shards; bin-sharding saves resources but needs careful hot-tenant handling.- CDC-based online migration adds complexity but enables zero-downtime moves.

MediumSystem Design

48 practiced

Design an audit/logging schema to record schema changes (DDL) across multiple database instances for compliance. Requirements: who made change, before/after DDL, timestamp, affected objects, and ability to replay DDL in a dry-run environment. Discuss storage choices and how to protect against tampering.

Sample Answer

Requirements clarification:- Capture actor, timestamp, before/after DDL, affected objects, instance identifier, correlation id, and provenance.- Support replay (dry-run) and tamper-evidence for compliance.

High-level design:- Immutable event store that records DDL-change events per instance; supplemental blob store for large DDL/text.- Components: DDL-capture agent (hooks into DB audit/CDC or proxies), API/service that validates and writes events, secure storage, replay tool.

Schema (relational/event-store example):DDLChangeEvent ( id UUID, instance_id VARCHAR, timestamp UTC, actor_id VARCHAR, auth_method VARCHAR, change_type ENUM (CREATE/ALTER/DROP), affected_objects JSON, -- list of schemas/tables/columns ddl_before_blob_uri VARCHAR, -- pointer to encrypted blob (optional) ddl_after_blob_uri VARCHAR, diff_text VARCHAR, correlation_id UUID, signature VARCHAR, -- signature of event payload prev_hash VARCHAR, -- chained hash for tamper-evidence verified BOOL DEFAULT false)

Storage choices:- Primary store: append-only ledger DB (e.g., PostgreSQL with append-only table + WAL archiving, or an event store like Kafka/Redpanda, or a blockchain/ledger DB for high assurance).- Blobs: object storage with write-once policy (S3 Object Lock in Compliance mode), server-side encryption (KMS).- Indexing/search: ElasticSearch for fast queries over affected_objects and actors.

Tamper protection:- Append-only write path enforced by service + DB permissions; no direct DB writes allowed.- Cryptographic chaining: compute SHA256 over canonicalized event payload and prev_hash; store signature (HSM/KMS) and prev_hash to create an immutable chain.- Sign events with private key stored in HSM or cloud KMS; verify signatures during audits.- S3 Object Lock for DDL blobs + immutable retention policies.- Strict RBAC, MFA for write paths, and alerting on anomalous writes (SIEM).

Replay/dry-run:- Replay tool reads events in timestamp order, verifies signature and chain, streams ddl_after blobs to a sandbox DB using transaction wrappers and EXPLAIN/DRY-RUN mode where supported. Provide toggles: schema-only validation (parse + diff) or full execution inside disposable environments (containers/ephemeral instances).

Operational concerns:- Scale: partition event store by instance_id or region; use async ingestion with backpressure.- Retention/compliance: policy-driven retention; archive older events to cold storage with retained signatures.- Testing: fuzz DDL parsing, idempotency checks, and rollback strategies for failed replays.

This design gives searchable, replayable DDL history with layered defenses (WORM, cryptographic chaining, HSM signing, RBAC) suitable for compliance audits.

MediumTechnical

43 practiced

You are asked to compress cold archival tables to save storage and also keep them queryable for occasional reports. Compare row-store compression, columnar formats (Parquet/ORC), and DB-native compression. Describe ETL approach to convert, query implications, and how to keep small ad-hoc queries responsive.

Sample Answer

Row-store compression (page/row-level, e.g., zlib on DB pages)- Pros: Transparent to apps, simple to enable, good for OLTP-ish row access patterns, low conversion effort.- Cons: Less effective for wide tables with many repeated values; still stores full rows so storage savings limited.

Columnar formats (Parquet/ORC)- Pros: Much higher compression ratios for analytic data via dictionary encoding, run-length, and per-column encoding; ideal for scans and ad-hoc analytics; cheaper long-term storage (object store).- Cons: Not ideal for single-row OLTP; requires external engine or query layer that reads files (e.g., Presto, Trino, Spark, Athena).

DB-native compression (vendor-specific)- Pros: Combines convenience and performance (e.g., hybrid columnar in Oracle, Aurora, SQL Server page/column compression); can speed queries by reduced I/O and may preserve queryability inside DB.- Cons: Varies by vendor; might not match Parquet compression ratios; migration/lock-in risk.

ETL approach to convert1. Define retention/archival policy and schema mapping (keep partition keys, primary keys as columns).2. Export incremental snapshot (CDC) or bulk extract to staging.3. Transform: normalize datatypes, drop unused indexes, convert timestamps/timezones, add partition columns (date, tenant).4. Write to columnar files partitioned (daily/monthly) and compress with appropriate codecs (Parquet + Snappy/Zstd, ORC + Zstd).5. Verify checksums and row counts; update metadata/catalog (Hive/Glue/Athena).6. Optionally delete or replace source rows after safe retention window.

Query implications- Full-table or wide-scan reports: much faster and cheaper on columnar due to predicate and projection pushdown.- Point lookups and small transactions: slower unless you keep indexes or secondary fast store.- Schema evolution: plan for evolving columns (nullable/back-compat).

Keeping small ad-hoc queries responsive- Keep recent hot partitions in the DB (hot/cold tiering) or maintain a small materialized set in row-store.- Build lightweight ORC/Parquet bloom filters and partition pruning to avoid scanning.- Use a query engine that supports predicate pushdown, column pruning, and metadata caches (e.g., Glue/Athena, Trino with Hive metastore).- Precompute summary tables / materialized views for common ad-hoc patterns.- Serve point queries from a small fast cache (Redis) or keep primary key subset in DB.- Monitor query patterns and adapt partition granularity and compaction to balance latency vs storage.

Trade-offs summary- Use Parquet/ORC for max compression and analytics; keep DB-native compression if you need simpler operations and transactional queryability; combine approaches via tiering (DB for hot, columnar for cold) and ETL automation with careful partitioning to keep ad-hoc responsiveness.

MediumTechnical

55 practiced

You inherit an application using an ORM (e.g., Hibernate / ActiveRecord) that generates many N+1 queries and slow bulk operations. Provide a plan to find, measure, and fix N+1 problems. Include code-level fixes (eager loading, batch fetching), database-level fixes (indexes, temp tables), and regression testing strategies.

Sample Answer

Start with detection and measurement- Instrument: enable SQL logging (slow query log), use APM (NewRelic, Datadog) and DB stats to find endpoints with high query count / latency.- Measure baseline: record average requests/sec, p95 latency, and "queries per request" for suspect endpoints. Use EXPLAIN for slow statements.

Code-level fixes- Eager loading (Hibernate example):

java

// Hibernate HQL with fetch join to avoid N+1
String hql = "select o from Order o join fetch o.items where o.id = :id";
Order o = session.createQuery(hql, Order.class).setParameter("id", id).uniqueResult();

- ActiveRecord (Rails) example:

ruby

# Instead of Order.all.each { |o| o.items } (N+1)
orders = Order.includes(:items).where(status: 'open')

- Batch fetching / pagination:

java

// Hibernate batch fetch size
sessionFactory.getSessionFactoryOptions().setProperty("hibernate.default_batch_fetch_size","50");

- Replace per-row updates with set-based operations:

sql

UPDATE users SET status='active' WHERE last_login > '2025-01-01';

Database-level fixes- Add indexes on join/filter columns (use EXPLAIN to justify).- Use composite indexes for multi-column WHERE clauses.- For massive joins/updates, use temporary tables or staging tables and a single JOIN-based UPDATE/INSERT to reduce round trips.- Consider materialized views for expensive aggregations.

Regression testing and CI- Add automated tests that assert query counts: e.g., in Rails use ActiveSupport::Notifications to assert <= N queries; in Java use datasource proxy to count statements.- Add performance tests to CI: run representative scenarios and fail on regressions for queries-per-request or p95 latency.- Code review checklist: require scrutiny for lazy access patterns (entity.getChildren() inside loops).- Monitor post-deploy via APM and compare to baseline; roll back if regressions occur.

Key trade-offs: eager-loading reduces queries but increases memory and payload size—limit with projection, pagination, or selective fetch. Use metrics-driven changes and iterate.

HardTechnical

42 practiced

Write SQL to compute, per user, their most recent N activity timestamps without using window functions (assume DB lacks them). Table: activities(id PK, user_id, occurred_at TIMESTAMP). Provide an efficient approach that works for large datasets and explain trade-offs compared to window-based solutions.

Sample Answer

Approach summary:Use a correlated-count trick: for each activity row, count how many later activities the same user has. Keep rows where that count < N; these are the N most-recent timestamps per user. Ensure proper indexing and consider materialized/maintained tables for scale.

SQL (ANSI-style, works in MySQL/Postgres without window functions):

sql

-- N is the number of recent timestamps you want
-- returns up to N rows per user (most recent first)
SELECT a.user_id, a.id, a.occurred_at
FROM activities a
WHERE (
  SELECT COUNT(1)
  FROM activities b
  WHERE b.user_id = a.user_id
    AND b.occurred_at > a.occurred_at
) < 3  -- replace 3 with N
ORDER BY a.user_id, a.occurred_at DESC;

Key points / reasoning:- For a given row a, the subquery counts how many events for that user are strictly newer. If fewer than N are newer, a is among the top N most recent.- Use strict > to break ties by timestamp; if you want deterministic tie handling include id in comparison (e.g., (occurred_at, id) > (a.occurred_at, a.id)).- Add WHERE filters (e.g., restrict to active users) to reduce scanned rows.

Performance considerations and optimizations:- Index: create a covering index on (user_id, occurred_at DESC) or (user_id, occurred_at) to allow efficient per-user scans. CREATE INDEX idx_user_time ON activities(user_id, occurred_at);- The correlated subquery executes per row; with the index and DB optimizer doing index nested loops, cost roughly O(M * logK) where M = total rows considered, K = avg rows per user. Still can be heavy for skewed users with many events.- For very large data or hot users, alternatives: - Precompute top-N per user periodically into a materialized table (batch job) or maintain it incrementally on insert (trigger/stream). - Use a numbers table join when N is small: join activities to a derived numbers table 0..N-1 and select the N-th by offset per user (can be faster if DB optimizes joins). - If DB supports LATERAL/ORDER BY ... LIMIT in FROM (e.g., Postgres), use lateral join: SELECT u.user_id, t.* FROM (SELECT DISTINCT user_id FROM activities) u JOIN LATERAL ( SELECT id, occurred_at FROM activities a WHERE a.user_id = u.user_id ORDER BY occurred_at DESC LIMIT 3 ) t ON true;

Trade-offs vs window functions:- Window functions (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at DESC)) are simpler, usually faster and more scalable because they do a single scan + sort per partition and are optimized in modern engines.- Correlated-count approach can be more I/O heavy (per-row subqueries) and slower for large/skewed partitions.- If your DB lacks windows, prefer materialized top-N tables or LATERAL-based per-user LIMIT (if available) for better performance than naive correlated queries.

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.