Database Design and Architecture Questions

Designing and architecting databases for production and cloud environments with attention to data modeling, schema design, and access pattern optimization. Topics include normalization and denormalization trade offs, schema versus query driven modeling, entity and relationship design for transactional and analytical workloads, indexing and query optimization techniques, partitioning and sharding design decisions, schema evolution and migration strategies, materialized views and caching strategies, selection of storage layers for different data shapes, and practical operational runbooks for provisioning, monitoring, alerting, backups, disaster recovery, and capacity planning. Candidates should justify schema and architecture choices with respect to latency, throughput, development and operational complexity, maintainability, and cost.

EasyTechnical

93 practiced

You are advising a client building an e-commerce platform. They need low-latency product search, strong transactional checkout, and hourly analytics. Propose which database technologies and patterns you would use for each requirement (OLTP, search, analytics) and justify your choices in terms of latency, throughput, maintainability, and cost.

Sample Answer

Recommendation (high level): separate concerns — OLTP for transactional checkout, a dedicated search engine for low-latency product discovery, and a data warehouse/analytics store for hourly analytics. Use event streaming/CDC to move data between systems (CQRS pattern).

OLTP (checkout)- Tech: PostgreSQL (managed e.g., Amazon Aurora) - Why: ACID transactions, strong consistency, mature tooling, row-level locking for payments/inventory. Aurora gives high throughput, automated backups, read replicas for scale.- Latency/Throughput: sub-10ms for simple ops, scales reads via replicas.- Maintainability/Cost: familiar SQL stack, moderate cost with managed service.

Search (product search)- Tech: Elasticsearch or OpenSearch + Redis for hot caching - Why: Inverted-index full-text search, faceting, relevancy tuning, geo and suggestions; Redis caches top queries/IDs for microsecond reads.- Latency/Throughput: low-latency (tens of ms) at high QPS with shards and replicas.- Maintainability/Cost: additional ops overhead for cluster management; managed offerings (Elastic Cloud, OpenSearch Service) lower ops cost.

Analytics (hourly)- Tech: Columnar warehouse (Snowflake / BigQuery / Amazon Redshift) or ClickHouse for fast hourly aggregations - Why: Optimized for OLAP, fast aggregations over large data. Use Kafka/Kinesis + CDC (Debezium) to stream events into a staging S3/Cloud Storage then ELT into warehouse hourly.- Latency/Throughput: hourly windows easily handled; interactive queries in seconds.- Maintainability/Cost: pay-for-storage/compute separation (Snowflake/BigQuery) simplifies ops; ClickHouse lower query cost but more ops.

Patterns/data flow- Use CDC/event stream (Debezium → Kafka/Kinesis) to push DB changes to search index and analytics.- CQRS: OLTP handles writes; search and analytics are read-optimized copies.- Materialized views / pre-aggregations for common metrics to meet SLA.- Idempotent consumers and backpressure handling to ensure consistency.

Trade-offs- Strong consistency vs search freshness: expect small replication lag (seconds). If inventory must be strictly consistent at sale moment, validate against OLTP during checkout.- Cost: managed services cost more but reduce ops risk/time-to-market; self-hosted reduces $ but increases staffing needs.

This architecture balances low-latency search, strong transactional guarantees for checkout, and efficient hourly analytics while remaining maintainable and scalable.

MediumTechnical

108 practiced

For an analytics-heavy application, explain how you would design indexing and storage layout when queries frequently aggregate across very large historical tables with complex joins. Compare columnar stores, materialized aggregates, and covering indexes, and describe when each is preferable.

Sample Answer

Start by clarifying requirements: query patterns (time ranges, dimensions, filters), SLA (latency vs freshness), data volume, ingest rate, and join complexity. With that, design combines storage layout, partitioning, and selective precomputation.

Columnar stores- Best for wide analytic scans and heavy aggregations across many rows but few columns (e.g., SUM of metric by country/day). Columnar compression and vectorized execution reduce I/O and CPU.- Use when queries read large historical ranges, need ad-hoc slicing, and joins are relatively simple or can be pushed down (e.g., dimension table lookups).- Implementation: partition by time (daily/monthly), sort within partitions by high-cardinality join keys or common filter columns to improve predicate pushdown and merge joins. Enable zone maps/bitmaps to skip blocks.

Materialized aggregates (materialized views / pre-aggregations)- Best when query shapes are stable and many users request the same rollups (e.g., daily/week/month totals by product, region). They deliver low-latency responses and reduce compute.- Trade-offs: storage and freshness. Use incremental refresh (CDC/streaming) where possible to keep views near-real-time.- Combine with columnar storage for space efficiency.

Covering indexes- Best in row-store or hybrid OLAP where queries filter and project small number of columns and need fast point/lookup joins or selective slices (highly selective predicates).- Use for star-schema fact tables to accelerate joins to dimensions by including dimension keys and frequently queried measures (covering index eliminates base-table lookup).- In very large historical tables, keep covering indexes narrow and aligned with partitioning to avoid scanning index entirely.

When to choose:- Columnar: default for broad historical scans and flexible ad-hoc analytics.- Materialized aggregates: add when known rollups dominate traffic or SLA demands sub-second responses.- Covering indexes: add for highly selective queries, point lookups, or when you must support transactional-like latency on some analytics.

Operational notes:- Combine approaches: columnar base storage + periodic or streaming materialized aggregates + a few targeted covering indexes on hot partitions.- Monitor query patterns, hit-rates and refresh cost; automate refresh and pruning. Use cost-based optimizer statistics, pre-warm caches for hot ranges, and plan for storage-growth and compaction.

EasyTechnical

73 practiced

SQL (PostgreSQL): Given table orders(order_id PK, user_id INT, status TEXT, total NUMERIC, created_at TIMESTAMP) write a query that finds users who placed duplicate identical orders within 5 minutes (same total and status). Provide efficient SQL using window functions or grouping and explain index recommendations to speed this query.

Sample Answer

Approach: use a window function to compare each order to the next order for the same user ordered by time; flag when status and total are identical and created_at within 5 minutes. This is single-pass, efficient when supported by an index on the partition keys.

SQL (window-function solution):

sql

WITH ordered AS (
  SELECT
    order_id,
    user_id,
    status,
    total,
    created_at,
    LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS next_created,
    LEAD(status) OVER (PARTITION BY user_id ORDER BY created_at) AS next_status,
    LEAD(total) OVER (PARTITION BY user_id ORDER BY created_at) AS next_total,
    LEAD(order_id) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_id
  FROM orders
)
SELECT DISTINCT user_id, order_id AS first_order_id, next_order_id AS duplicate_order_id, created_at, next_created
FROM ordered
WHERE next_status = status
  AND next_total = total
  AND next_created <= created_at + INTERVAL '5 minutes';

Why this works:- Partitioning by user_id and ordering by created_at ensures we only compare adjacent orders per user (O(n) scan).- LEAD avoids expensive self-joins.

Alternative (grouping/self-join) can detect non-adjacent duplicates but is heavier.

Index recommendations:- Create a btree composite index to support the PARTITION+ORDER scan: CREATE INDEX idx_orders_user_created ON orders (user_id, created_at); and optionally include columns for index-only scans: CREATE INDEX idx_orders_user_created_incl ON orders (user_id, created_at) INCLUDE (status, total);- For very large historical tables consider partitioning by range(created_at) and/or using BRIN on created_at for cheaper scans.

Notes/trade-offs:- The window approach detects adjacent duplicates (typical case). If you must catch duplicates separated by other differing orders within 5 minutes, use a self-join on user_id with time range (requires careful indexing and may be heavier).- Keep statistics updated (ANALYZE) and consider retention/partitioning for scale.

HardSystem Design

94 practiced

Design schema and indexing for a geospatial property search service that supports 1000+ possible filter attributes (high-cardinality), bounding-box and radius queries, and faceted counts for UI. Discuss data storage choices (RDBMS, Elasticsearch, PostGIS), index strategies (R-tree, geohash, inverted indexes), and how to keep facets fast.

Sample Answer

Requirements & constraints:- Support geospatial queries (bounding-box, radius) over millions of properties- 1000+ high-cardinality filter attributes (facets) with fast counts for UI- Low-latency queries (sub-second), realtime-ish updates, horizontal scale

High-level architecture:- Primary data store: PostgreSQL + PostGIS as source-of-truth (ACID, joins, transactional updates)- Search & geo-query engine: Elasticsearch (or OpenSearch) for serving queries and faceting- Sync: Change Data Capture (logical replication / Debezium) or near-real-time ETL to keep ES index in sync- Cache & pre-aggregations: Redis or materialized indices for hot facets/tiles

Indexing strategy:- Geo: store geometry in PostGIS for authoritative spatial ops; in ES index use geo_point with geohash precision for fast radius / bbox filtering. For large range queries prefer geo_shape in ES or use bounding-box + filter.- Spatial index types: PostGIS uses GiST/R-tree; ES uses BKD + geohash tiles internally. Use PostGIS for complex spatial joins; ES for interactive search.- Attributes: in ES, model 1000+ attributes as multi-field: many attributes as keyword fields (not analyzed) and use sparse doc values to reduce storage. Use nested fields for arrays/complex attrs.

Facets design & performance:- Build inverted indexes in ES for all facetable attributes (keyword + doc_values). But computing counts at query time across 1000 attrs is expensive.- Pre-compute facet counts by: - Incremental aggregations: maintain aggregate counters per attribute-value per geo-tile (e.g., geohash level 6) in a fast KV store (Redis/ClickHouse) to answer regional facet counts quickly. - Use ES composite aggregations + partitioning for large cardinality, and avoid deep cardinality by limiting UI to top-K + “more”. - Cache common facet responses (per bbox, zoom level, filter set) in Redis with TTL.- For highly dynamic counts, use streaming updates to aggregation store (Kafka -> stream processors) to keep counts eventually consistent.

Scalability & trade-offs:- ES gives low-latency faceting and geo-filtering but can be heavy for 1000 high-cardinality facets — mitigate via doc_values, sparse mapping, and sharding strategy keyed by geo/tenant.- PostGIS is better for complex spatial analytics, guaranteed correctness, and offline recalculation.- Pre-aggregation increases complexity and eventual consistency risk but is necessary to keep UI snappy.

Operational notes:- Provide reindex pipelines, backfills, and schema evolution strategy.- Monitor index size, shard count, and aggregation latency; use index lifecycle management.- Securely partition data by tenant/region for multitenancy.

Why this works:- PostGIS ensures authoritative spatial operations; ES serves interactive search and faceting with inverted indexes and geo-optimized structures; pre-aggregations and caching keep faceted counts fast for a high-cardinality domain.

HardTechnical

92 practiced

Given a normalized OLTP schema that causes 'join explosion' for common read paths, propose a migration path to a polyglot persistence architecture combining relational, document, and columnar stores. Include mapping strategies, synchronization patterns (CDC, eventing), and how you'd keep data consistent for critical operations.

Sample Answer

Requirements & constraints:- Preserve OLTP guarantees for critical transactions (ACID), reduce join latency for common reads, enable analytical queries, minimize migration risk, support incremental cutover, and observable data sync.

High-level migration path:1. Identify hot read paths (API calls, dashboards) and candidate aggregates for denormalization.2. Map data stores: - Relational (primary OLTP): keep normalized canonical writes and transactions requiring strong ACID. - Document DB (e.g., MongoDB): store denormalized read models/aggregates for low-latency APIs (user profile + addresses + preferences). - Columnar store (e.g., BigQuery/Snowflake): store event streams / wide tables for analytics and historical ad-hoc queries.3. Phased rollout: build read-models, switch read traffic, then migrate less-critical writes.

Mapping strategies:- Define canonical domain model in RDBMS. For each read path create a projection spec: keys, required joins, TTL, update frequency.- Document mapping: 1:1 or 1:many denormalized documents per aggregate root (embed frequently-read children; reference large/volatile collections).- Columnar mapping: append-only event tables or flattened snapshots per time window.

Synchronization patterns:- Use CDC from primary RDBMS (Debezium/Cloud CDC) to publish change events to a streaming backbone (Kafka/Kinesis).- Stream processor (Kafka Streams/Flink) applies transformations into: - Upserts to Document DB for read models (idempotent, schema-evolving). - Append to columnar warehouse via micro-batches (stream -> staging -> load).- For derived aggregates, use event-sourced processors that maintain state stores and emit compensating events when needed.

Keeping data consistent for critical operations:- Keep RDBMS as source-of-truth for writes; synchronous writes go to RDBMS within transactional boundaries.- For read-after-write consistency on critical flows, either: - Route reads to RDBMS for that transaction (strong consistency). - Or implement write-through pattern: upon successful transaction, synchronously update document store within the same service (two-phase commit avoided) using a transactional outbox pattern: write DB + outbox row in same transaction, CDC publishes outbox reliably; consumer applies to doc store. This guarantees no lost updates and eventual convergence.- Use idempotent consumers, versioned documents (optimistic concurrency with version numbers) and logical clocks to resolve ordering.- Monitor lag (consumer offsets vs source LSN), business-validity checks, and automated reconciliations (periodic full-sync jobs for critical aggregates).

Trade-offs & considerations:- Eventual consistency for most read paths reduces latency; critical flows use RDBMS or transactional outbox for strong guarantees.- Denormalization increases storage and complexity for updates—mitigate with clear projection specs and observability.- Start small: migrate a single read path end-to-end, validate metrics (latency, error rate, lag), then expand.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.