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.

MediumTechnical

56 practiced

SQL rewrite: Given table `events(user_id int, created_at timestamptz, type text)` and the query `SELECT * FROM events WHERE type = 'click' OR type = 'view' AND created_at > now() - interval '7 days'`, explain why this may avoid indexes and rewrite the query to ensure index usage and correct logical semantics. Use PostgreSQL syntax.

Sample Answer

**Problem — why indexes may be avoided**

Because AND has higher precedence than OR, the original condition parses as:type = 'click' OR (type = 'view' AND created_at > ...). That produces an OR across different predicates and columns, which often prevents PostgreSQL from using a single efficient index and can lead to a sequential scan.

**Correct, index-friendly rewrites**

1) If intention is “either type is click or view, and created_at within 7 days”:

sql

SELECT *
FROM events
WHERE (type = 'click' OR type = 'view')
  AND created_at > now() - interval '7 days';

Or more concisely:

sql

SELECT *
FROM events
WHERE type IN ('click','view')
  AND created_at > now() - interval '7 days';

2) Alternative using UNION ALL to allow separate index usage (useful if different indexes apply):

sql

SELECT * FROM events
WHERE type = 'click' AND created_at > now() - interval '7 days'
UNION ALL
SELECT * FROM events
WHERE type = 'view'  AND created_at > now() - interval '7 days';

**Index recommendations**

- Composite index to serve both predicates:

sql

CREATE INDEX ON events (type, created_at DESC);

- Or a partial index if these two types are hot:

sql

CREATE INDEX ON events (created_at DESC)
WHERE type IN ('click','view');

These ensure the planner can use an index for the date filter and/or type filter and avoid expensive seq scans.

MediumTechnical

55 practiced

Index maintenance and writes: Explain how having many indexes affects insert, update, and delete performance. Describe mitigation strategies (deferred index creation, partial indexes, batched writes, disabling indexes during bulk loads) and how to balance read optimization with acceptable write latency.

EasyTechnical

58 practiced

Explain the difference between primary keys, unique constraints, and indexes in relational databases. Include how each is implemented conceptually, how they affect performance for reads and writes, and when you would use a unique index versus a non-unique index versus a primary key.

EasyTechnical

43 practiced

Explain when and why to create composite (multi-column) indexes. Discuss index column order, left-prefix behavior, and provide an example where reversing column order changes whether an index is used for a given query.

Sample Answer

**When and why create composite (multi-column) indexes**

- Use composite indexes when queries filter/sort on multiple columns together frequently. They speed up multi-column WHERE, JOIN, ORDER BY, and GROUP BY patterns and avoid multiple single-column index lookups or expensive sorts.

**Index column order & left-prefix behavior**

- Composite indexes are ordered left-to-right. A composite index on (a, b, c) can be used efficiently for queries that filter the left prefix: (a), (a, b), or (a, b, c).- If a query filters only on b and c but not a, the index cannot use the left-prefix to seek (it might still be scanned but not seeked). Place the most selective / commonly-filtered columns first.

**Example where reversing order matters**

- Given table users(age, country, last_login), create index1 on (country, age) and index2 on (age, country).

Query A:

sql

SELECT * FROM users WHERE country = 'US' AND age = 30;

- Both indexes can be used, but (country, age) is ideal.

Query B:

sql

SELECT * FROM users WHERE age = 30;

- (age, country) will be used efficiently; (country, age) will not (unless planner picks it poorly).

Query C:

sql

SELECT * FROM users WHERE country = 'US' ORDER BY age;

- (country, age) can satisfy WHERE + ORDER BY without additional sort; (age, country) cannot.

**Practical tips**

- Analyze query patterns and selectivity; prefer covering indexes; keep index width small; test with EXPLAIN.

HardSystem Design

42 practiced

Design (hard): Architect a scalable metadata store for a SaaS product that will store billions of small metadata records (key, value, tenant_id, created_at) and must serve 100k QPS of read traffic for recent data and 10k QPS of writes. Describe database choice(s), schema, indexing, partitioning/sharding, caching layer, archival, and how you'd ensure efficient queries for tenant-scoped reads and point lookups.

Sample Answer

**High-level choice**- Use a distributed K/V optimized datastore (Amazon DynamoDB or Cassandra) as primary: single-digit ms point lookups, horizontal scale for billions of small items and tens of thousands QPS.- Use Redis cluster (hot cache) in front for recent data (reads) and S3 for cold archival snapshots.

**Schema (DynamoDB-style)**- Primary key: PartitionKey = tenant_id (string) + "#" + hash_prefix ; SortKey = key (string) or created_at for time queries- Attributes: value (blob), created_at (ISO), version/meta- Example item: - PK: tenant123#h12 - SK: config.feature_flag - value: {...} - created_at: 2026-02-01T12:00:00Z

**Indexing**- Use PK/SK for efficient point lookups: partition by tenant + small hash to spread hot tenants.- Global Secondary Index (GSI) for recent data queries: - GSI1: GSI_PK = tenant_id, GSI_SK = created_at (descending) — efficient tenant-scoped "recent" scans limited by time window and paginated.- Use sparse GSIs only when necessary to minimize cost.

**Partitioning / Sharding**- Logical shards: append a small consistent-hash suffix (N buckets per tenant) to tenant_id to avoid hot partitioning for heavy tenants.- Auto-scaling: DynamoDB On-Demand or provisioned with auto-scaling + adaptive capacity.- For Cassandra: use token-aware partitioning and a composite partition key (tenant_id, bucket).

**Caching layer**- Redis cluster (key = tenant_id:key). Cache recent items & most-read keys, with short TTLs; use lazy write-through for reads and write-behind / invalidation on updates.- Use Bloom filters locally to avoid unnecessary lookups for non-existent keys.

**Writes & Consistency**- Writes go to DB; for strong read-after-write for same client, use conditional writes or DynamoDB transactions or read-your-writes via cache invalidation + immediate write-through to cache.- Implement optimistic concurrency (version or etag) for updates.

**Archival**- Use TTL on items or a lifecycle job to move old items to S3/Parquet every day/week.- Use DynamoDB Streams (or Cassandra change data capture) -> Lambda/Kafka -> batch to S3 for cheaper long-term storage; keep an index to archived ranges if needed.

**Efficient tenant-scoped reads & point lookups**- Point lookup: compute shard suffix -> single PK+SK read (single-partition read).- Tenant recent reads: query GSI by tenant_id and created_at DESC limited N; use Redis to cache the most recent window (e.g., last 24h) for hot tenants.- Rate-limit heavy tenants and provide per-tenant capacity planning (quota buckets) to protect multi-tenant performance.

**Operational considerations**- Monitor per-partition throttles, cache hit ratio, and latency. Use metrics + alerts.- Backups, encryption-at-rest/in-transit, RBAC and per-tenant encryption keys if needed.- Trade-offs: DynamoDB simplifies ops + predictable latencies/costs; Cassandra gives more control but higher ops burden.

This design provides sub-ms–low-ms point lookups, handles 100k read QPS by caching recent data, supports 10k write QPS via horizontal scaling and shard buckets, and archives cold data to S3 for cost efficiency.

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.