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.

EasyTechnical

41 practiced

Define a covering index and explain how it improves query performance. Given a table users(id int, email text, country text, created_at timestamptz) and the frequent query SELECT email FROM users WHERE country = 'US' AND created_at > '2024-01-01', propose a PostgreSQL index definition that would act as a covering index for this query and explain why it helps.

HardTechnical

56 practiced

Design a high-throughput rate-limiting system for API requests that enforces per-user quotas (for example, 100 requests/minute) with low latency and the ability to handle millions of requests per second. Compare implementing counters in the primary relational database versus Redis (in-memory) and discuss token-bucket algorithms, Lua scripts for atomic operations, TTL-based sliding windows, and consistency/accuracy trade-offs in a distributed environment.

Sample Answer

Requirements & constraints:- Enforce per-user quota e.g., 100 req/min with low (<ms) latency- Handle millions of req/sec, horizontally scalable, resilient- Prefer eventual consistency for performance but support strong for critical cases- Minimal operational complexity and cost

High-level architecture:- Edge/API gateways (NGINX, Envoy, cloud LB) receive requests and perform local checks.- Use Redis cluster as the global rate-limiter store (sharded by user-id) with local caches for burst decisions.- Async logging of hits to a durable store (Kafka → data warehouse) for auditing and analytics.

Why Redis vs relational DB:- Relational DB: durable, transactional, but high-latency and poor write throughput at millions/sec. Not suitable for per-request counters.- Redis (in-memory): sub-ms ops, high throughput, built-in TTL, clustering and sharding for scale. Best fit for hot-path enforcement.

Rate limit algorithm options:- Fixed window counter: simple, low-cost, but causes boundary spikes.- Sliding window with TTL (approx): store per-user timestamps or use two rolling counters to smooth edges — accurate enough and efficient.- Token Bucket (recommended for high-throughput + burst handling): tokens refilled at rate R, consumption per request; supports bursts and steady rates.

Implementation details:- Use Redis + Lua scripts for atomic operations: compute refill, consume tokens, return remaining tokens in one script to avoid race conditions across multiple app nodes.- Example steps inside Lua: read token_count + last_ts, compute tokens_to_add = (now - last_ts) * rate, cap to bucket_size, if tokens >= 1 then decrement and allow else reject; set TTL to garbage collect inactive keys.- Shard keys by user-id using Redis Cluster to scale horizontally.

Consistency/accuracy trade-offs:- Strong consistency via central Redis + atomic Lua ensures accurate counts but introduces a single dependency; mitigate with HA and clustering.- For extreme scale, employ client-side token caches or local leaky-bucket with periodic reconciliation to Redis to reduce network hop frequency — trades strict accuracy for lower latency and higher throughput.- Multi-datacenter: accept slight allowance (allow-list) or use synchronized clocks and per-region quotas with global reconciliation; otherwise cross-DC latencies make strict global limits impractical.

Operational considerations:- Monitor Redis latency, key count, memory; use eviction policies and TTLs.- Backpressure: reject early at gateway when Redis is unhealthy.- Audit trail: stream allowance/denial events into Kafka for downstream analytics and billing.

Recommendation:Use Redis token-bucket implemented with Lua for atomicity, TTLs for TTL-based cleanup, and local caching for performance. Use relational DB only for long-term metadata, not runtime counters.

MediumTechnical

74 practiced

A reporting query frequently scans a large fact table producing many temporary files and spilling during sorts/aggregations. Propose strategies at the database configuration level and schema/query level to reduce spills and temporary disk usage: consider work_mem, maintenance_work_mem, parallel_workers, indexes, pre-aggregation, and query rewrite approaches.

Sample Answer

Start by measuring: run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) and check pg_stat_statements / server logs for temp file usage and per-query temp_bytes. That informs how much memory/sorts/hashes are happening.

Database/config-level strategies- Increase work_mem (per sort/hash operation): work_mem controls memory for sorts and hash aggregates; raising it reduces spills. Tune gradually (e.g., from 4MB → 32–128MB) and test; remember it’s per operation per session — calculate worst-case concurrent usage before global changes.- Use role/session-level overrides: instead of raising global work_mem, set it for the reporting user or in the session/connection pool (ALTER ROLE reporting_user SET work_mem = '64MB').- maintenance_work_mem: increase for index builds/refreshes of materialized views to speed creation and reduce temp usage during maintenance tasks.- Parallelism: increase max_parallel_workers_per_gather and max_worker_processes carefully so large scans/aggregates can parallelize, which spreads memory & CPU but may increase temp files if workers spill; monitor.- temp_tablespaces: place temp_tablespaces on fast NVMe/SSD separate from data disks to reduce I/O latency for spills.- Enable/disable planner features: ensure enable_hashagg and enable_hashjoin are on so hash-based ops use memory; if hash agg is unavailable, sorts may produce more temp files.

Schema/query-level strategies- Pre-aggregate: create materialized views or summary tables (daily/hourly rollups) that reduce scanned rows. Prefer compact, partitioned MV with incremental refresh (REFRESH MATERIALIZED VIEW CONCURRENTLY or ETL-based incremental update).- Partitioning: range or time partition the fact table so queries prune partitions and scan less data.- Indexes & covering indexes: add btree indexes on GROUP BY keys and filtering predicates; create covering (include) indexes so queries can use index-only scans and avoid large sorts. For very large append-only tables consider BRIN indexes for low-cardinality columns.- Query rewrites: - Push filters early (WHERE before JOINs), push projections (SELECT only needed columns). - Replace heavy GROUP BY on raw table with GROUP BY on pre-aggregated subquery or materialized view. - Use streaming aggregates (GROUP BY with proper planner hints) or HASH_AGG hints if available. - Avoid ORDER BY when not needed; use LIMIT with appropriate index if you only need top-N. - Replace DISTINCT with GROUP BY or use approximate algorithms (hyperloglog) for distinct counts. - Use lateral joins / window functions carefully; sometimes rewriting as aggregated subqueries reduces intermediate rows.- Incremental processing: break big queries into staged steps that write cleaned/intermediate results to a temp table or staging schema, then aggregate—this gives you control over memory via indexes/maintenance_work_mem during the staging step.- Materialized temporary tables: create a temp table with appropriate indexes for the reporting run—this shifts work from repeated sorts to indexed scans.

Operational safeguards and monitoring- Calculate safe work_mem: (max_connections * avg_operations_per_conn * work_mem) should fit under RAM minus OS and DB buffers.- Use connection pooling for reporting workloads (PgBouncer) to limit concurrency spikes.- Monitor temp file growth (pg_stat_activity temp_files, system disk) and iterate: increase work_mem for the hotspot queries and introduce pre-aggregation/partitioning to reduce raw scanned rows.

Concrete example:- ALTER ROLE reporting_user SET work_mem = '64MB';- CREATE MATERIALIZED VIEW mv_orders_day AS SELECT date_trunc('day', ts) d, customer_id, sum(amount) FROM fact_orders GROUP BY 1,2;- CREATE INDEX ON mv_orders_day (d, customer_id);- Place temp_tablespaces on fast SSD: CREATE TABLESPACE fasttmp LOCATION '/nvme/tmp';

Trade-offs- Higher work_mem reduces disk I/O but risks OOM at high concurrency.- Materialized views reduce query cost but add refresh complexity/storage.- Parallelism speeds wall time but can increase aggregate memory use and temp files per worker.

Start by measuring, then apply targeted work_mem/session overrides for heavy reporting users, add partitioning and pre-aggregations, and rewrite queries to push filters/projections and use indexed scans or materialized summaries. Monitor and iterate.

MediumSystem Design

54 practiced

For a SaaS multi-tenant application, compare three multi-tenant schema approaches: (a) single shared table with tenant_id column, (b) separate schemas per tenant, and (c) separate databases per tenant. Discuss trade-offs for operational complexity, security/isolation, backup and restore, scaling, and query performance. Provide recommendations for small, medium, and large tenants and a migration path between models.

Sample Answer

Clarifying assumptions: transactional SaaS on cloud RDBMS (Postgres/MySQL) with data workloads from ETL pipelines and analytical queries. We compare three tenancy models on five dimensions and give recommendations and a migration path.

Comparison (concise):

1) Single shared table (tenant_id column)- Operational complexity: Lowest — one schema, single deployment, simple migrations.- Security/isolation: Weakest logically; relies on app-level row-level security (RLS) and thorough testing.- Backup/restore: Coarse-grained; restoring a single tenant requires table-level filtering + complex export/import or logical dump.- Scaling: Easier horizontal scale at app layer; harder to allocate resources per-tenant; noisy neighbor risk.- Query performance: Best for small tenants with uniform indexing; large tables can suffer from size-related slowdown; require partitioning and proper indexes (e.g., tenant_id + timestamp).

2) Separate schemas per tenant- Operational complexity: Medium — one DB instance, migrations must run per-schema but can be automated.- Security/isolation: Better (DB-level privileges) and simpler to audit than shared tables.- Backup/restore: Easier to restore a schema-level tenant snapshot.- Scaling: Moderate — can move heavy tenants by provisioning separate DB instances; still limited by single-instance resources.- Query performance: Good; per-tenant indexes and vacuuming reduce contention. Admin ops per-schema increase with tenant count.

3) Separate databases per tenant- Operational complexity: Highest — provisioning, migrations, monitoring for each DB; but automatable via orchestration.- Security/isolation: Strongest (OS/instance-level if needed); minimal blast radius.- Backup/restore: Easiest and fastest per-tenant restore.- Scaling: Best — isolate hot tenants to own instances; allows vertical/horizontal scaling per tenant.- Query performance: Best for large tenants; no cross-tenant contention.- Cost: Highest due to many DB instances and idle capacity.

Recommendations:- Small tenants (many, low usage): Shared table with tenant_id + RLS + partitioning by tenant or hash. Use strict guardrails, row-level limits, and monitoring.- Medium tenants: Separate schemas per tenant to balance isolation and operational cost. Automate schema migrations, backups, and per-schema monitoring.- Large tenants (few, high resource): Separate databases (or dedicated clusters) to guarantee performance and per-tenant SLAs.

Migration path:1. Start shared-table for MVP. Implement strong multitenancy abstractions: tenant_id enforced at ORM/data layer, RLS where supported, comprehensive tests and monitoring.2. Introduce logical partitioning by tenant and background jobs to detect heavy tenants (metrics: QPS, rows, CPU).3. For medium/hot tenants, migrate to separate schema on same DB: CREATE schema, copy data (pg_dump/pg_restore or logical export), update app routing. Automate schema migrations with migration tooling.4. For very large tenants, provision dedicated DB/cluster, perform logical or physical export/import, switch connection routing (service discovery). Use replication to minimize cutover.5. Maintain tooling: tenant-aware backups, automated migrations, canary test for schema changes, and capacity orchestration to move tenants between tiers.

Operational best practices:- Telemetry per-tenant (latency, resource usage).- Quota and throttling to mitigate noisy neighbors.- Automated backup/restore and infra-as-code for provisioning.- Use row-level security + least-privilege DB users for shared model.- Plan schema evolution strategy (backwards-compatible migrations, feature flags).

This balances cost, isolation, and performance while enabling gradual moves from shared to isolated models as tenant needs grow.

HardTechnical

53 practiced

An EXPLAIN ANALYZE shows a Bitmap Index Scan followed by a Bitmap Heap Scan producing many random I/Os and slow performance on a frequently-run read query. The table is large but the query only selects two small columns. Describe how you would change schema, indexes, or table clustering to allow index-only scans and reduce random I/O. Include specific PostgreSQL commands (for example, CREATE INDEX ... INCLUDE, CLUSTER) and discuss trade-offs such as maintenance cost and bloat.

Sample Answer

Situation: A frequent read query on a large table shows Bitmap Index Scan → Bitmap Heap Scan with many random I/Os because the planner must visit heap tuples to fetch the two small columns selected.

Approach: Enable index-only scans so PostgreSQL can satisfy the query from the index without touching the heap. Three levers: build a covering index with INCLUDE columns, cluster the table on that index (or use BRIN for physical locality), and ensure visibility map is healthy (VACUUM).

Recommended steps and commands:

1) Create a covering B-tree index that contains the WHERE columns as key columns and the two selected columns as included payload:

sql

CREATE INDEX CONCURRENTLY idx_table_filter_cover
ON schema.table (filter_col1, filter_col2)
INCLUDE (small_col_a, small_col_b);

2) Force clustering (one-time) to lay out table rows by index order to make any remaining heap access more sequential:

sql

CLUSTER VERBOSE schema.table USING idx_table_filter_cover;
-- or for large tables:
ALTER TABLE schema.table SET (toast.toast_tuple_target = 2048);

3) Maintain visibility map: run VACUUM (autovacuum tuning if needed):

sql

VACUUM ANALYZE schema.table;

Alternative: If table is extremely large and queries touch contiguous time ranges, a BRIN index reduces random I/O:

sql

CREATE INDEX idx_table_brin ON schema.table USING brin(time_col) WITH (pages_per_range = 32);

Key reasoning:- INCLUDE stores non-key columns in the index leaf pages; index-only scans are possible if visibility map marks tuples as visible.- CLUSTER arranges heap in index order, reducing random heap reads but is a one-time blocking operation; it must be re-run after substantial writes.- VACUUM sets visibility map bits so index-only scans don’t consult heap.

Trade-offs:- Index bloat and extra storage for included columns; slower writes (INSERT/UPDATE/DELETE) and longer index maintenance time.- CLUSTER is heavy and requires downtime or careful scheduling; it does not stay effective under high churn and can cause fragmentation — re-cluster periodically.- Increased autovacuum load to maintain visibility map; consider autovacuum tuning (lower thresholds) for hot tables.- BRIN has tiny index size and low write cost but only helps when physical ordering correlates with query predicate.

Result: A covering index + healthy visibility map allows index-only scans, removes bitmap heap random I/O, and greatly speeds frequent reads; monitor index size, autovacuum, and re-cluster frequency to balance read performance vs. write/maintenance cost.

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.