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.

MediumTechnical

102 practiced

Describe replication topologies: single primary with read replicas, multi-primary (multi-master), and leaderless architectures (e.g., Cassandra). For each, explain consistency, conflict resolution, and typical operational trade-offs.

Sample Answer

Single-primary with read replicas- Consistency: Stronger for writes — a single primary serializes updates so writes are linearizable at the primary. Replicas are eventually consistent unless you synchronous-replicate. Reads from replicas may be stale (read-after-write not guaranteed).- Conflict resolution: Minimal — no concurrent writer conflicts because writes funnel through one node. Failover requires promotion of a replica to primary; careful ordering and fencing/timestamps avoid split-brain.- Operational trade-offs: Simple mental model and easier app logic. Good read scalability. Single point for write bottleneck and availability: primary failure requires failover (downtime or complexity with automated failover). Schema changes and backups are straightforward.

Multi-primary (multi-master)- Consistency: Can offer lower write latency (writes at any master) but weaker global consistency unless using synchronous replication across masters (which hurts latency). Often provides eventual consistency across masters.- Conflict resolution: Needed because concurrent conflicting updates can occur. Techniques: last-write-wins (timestamp), application-level conflict resolution, CRDTs, or merge procedures. Conflict detection/logging is important.- Operational trade-offs: Higher write availability and geographic write locality; more complex replication topology, conflict handling, and operational tooling. Risk of split-brain and complex schema migrations.

Leaderless (e.g., Cassandra)- Consistency: Tunable per-operation using consistency levels (QUORUM, ONE, ALL). Reads/writes go to multiple replicas; quorum rules can give strong consistency when configured (W + R > N).- Conflict resolution: Uses mechanisms like last-write-wins with timestamps, vector clocks in some systems, read-repair, and hinted handoff. Cassandra relies on timestamps and repair processes to converge.- Operational trade-offs: High availability and horizontal scalability, excellent for geo-distributed writes. Requires careful clock management (timestamp accuracy), periodic repairs to resolve divergence, and more complex operational procedures (anti-entropy, compaction). Debugging causal anomalies and ensuring correct consistency SLAs can be challenging.

Summary guidance- Choose single-primary when simple correctness and easy conflict semantics matter and writes are moderate.- Choose multi-primary for multi-region low-latency writes but only if you can accept/handle conflicts.- Choose leaderless for high availability and tunable consistency, but invest in repair/monitoring and understand timestamp/clock risks.

EasyTechnical

89 practiced

You're storing semi-structured product metadata. Discuss pros and cons of using a JSON column in PostgreSQL versus fully normalizing attributes into relational columns and tables. Address queryability, indexing, schema evolution, and storage.

Sample Answer

Situation: You need to store semi-structured product metadata. Two common choices are a JSON(B) column in PostgreSQL or fully normalizing attributes into relational columns/tables. Below are concise pros/cons across the requested dimensions and a practical recommendation.

Queryability- JSONB: Flexible — ad-hoc queries via ->, ->>, jsonb_path_query. Good for exploratory queries and when attributes vary by product.- Relational: Stronger, faster for complex joins and aggregations; SQL is clearer and safer (typed columns, constraints).

Indexing- JSONB: Supports GIN indexes (existence, containment) and expression indexes on specific keys. Good for common predicates but less efficient for range/index-only scans compared to btree.- Relational: B-tree and other native indexes per column provide predictable, fast point/range queries and index-only scans.

Schema evolution- JSONB: Very flexible; adding new attributes requires no DDL. Great for rapid iteration or plugin-like extensions.- Relational: Safer and explicit; altering schema requires migrations, which adds development overhead but enforces consistency and validation.

Storage and performance- JSONB: Compact binary storage but can store redundant keys per row; large documents increase IO and CPU for parsing. GIN indexes can be large.- Relational: More compact when normalized (no repeated key names), better for OLTP workloads; joins add CPU but predictable.

Trade-offs & recommendation- Use JSONB when attributes are sparse/highly variable or you need rapid schema evolution and mostly read-by-containment queries.- Use relational normalization when attributes are stable, frequently queried with filters/aggregations, or need strong typing and constraints.- Hybrid approach: keep core structured fields as columns (id, price, category, availability) and store optional/rare attributes in jsonb. Create expression indexes on hot JSON keys and maintain periodic data migrations to normalize hot attributes as they stabilize.

This balances developer velocity with query performance and maintainability.

MediumSystem Design

102 practiced

Design an analytics ingestion pipeline that streams application events into an OLAP store. Specify decisions about data schema (append-only vs upsert), partition keys, compaction, and how to allow both real-time and batch analytics.

Sample Answer

Requirements:- Ingest high-volume app events (millions/day), low-latency real-time analytics, and accurate batch reports. Support updates (user profile changes) and efficient storage for OLAP queries.

High-level architecture:- Producers → Kafka (topic per event type, partitioned) → Stream processors (Flink/Beam) → OLAP store (ClickHouse/Druid/Snowflake) + cold S3 lake- Stream processors emit two paths: real-time materialized views (served via realtime store or push to OLAP) and batched files (Parquet) to S3 for backfill.

Schema decisions:- Primary store in OLAP is append-only event table (immutable events) because append-only is simple, high-throughput, and preserves auditability.- For entity state (profiles/orders), maintain a separate upsert table (latest state) using a primary key and event-time watermark; stream processors convert event streams into upserts for that table.

Partitioning:- Partition append-only event table by event_date (date bucket) and shard by device_id or tenant_id to keep related events colocated and optimize time-range queries.- For upsert/state table partition by hash(user_id) and cluster by last_update_time to support point lookups and recent-state scans.

Compaction and retention:- Use periodic compaction jobs on OLAP to merge small files/segments (e.g., daily compaction) to reduce small-file overhead and improve query speed.- For append-only S3 lake, run compaction and create partitioned Parquet files and maintain Hive/Glue catalog.- Retention: hot recent partitions kept in the low-latency OLAP; older partitions moved to cheaper storage or summarized.

Real-time vs batch analytics:- Real-time: stream processors maintain materialized views and push to a real-time OLAP layer (Druid or ClickHouse with realtime ingestion) for sub-second dashboards.- Batch: nightly Spark/Flink jobs read S3 Parquet to rebuild aggregates, reconcile state (replay events to ensure correctness), and backfill corrections. Use watermarking and deduplication logic in streams to handle out-of-order events.- Ensure idempotency: include event_id and ingest_timestamp; deduplicate both in stream processor and during batch compaction.

Operational considerations & trade-offs:- Append-only simplifies ingestion and makes audit/lineage easy; upserts cost more (compaction, primary-key indexing) but required for current-state queries.- Partitioning by date + tenant balances query locality and manageable partition count. Too fine-grained partitions increase metadata overhead.- Choose OLAP with good realtime ingestion (Druid/ClickHouse) for low latency; use data lake + query engine for ad-hoc deep analytics.- Monitor lag, compaction success, and schema evolution; provide schema registry for event formats.

HardTechnical

88 practiced

As a Software Engineer, explain the trade-offs of polyglot persistence. Give an example architecture that uses a relational DB for transactions, a document store for flexible product models, and a columnar store for analytics. Explain data synchronization strategies between them.

Sample Answer

Trade-offs- Benefits: fit-for-purpose storage (ACID safety in RDBMS, flexible schemas in document stores, fast analytic queries in columnar stores); better performance and scalability for specialized workloads.- Costs: operational complexity (more services, backups, security), data consistency/coupling challenges, duplicated storage and ETL costs, need for cross-system schema/versioning and monitoring.- When to use: domain boundaries with different access patterns (transactions vs product catalog vs analytics). Avoid if team cannot operate multiple datastores.

Example architecture- OLTP: PostgreSQL (orders, payments, users) for ACID transactionality and FK integrity.- Catalog: MongoDB for flexible product models (variants, localized attributes) exposed to product-service via document APIs.- Analytics: ClickHouse for event/aggregate analytics and fast ad-hoc queries.- Integration backbone: Kafka as the event bus.

Data synchronization strategies1. Source-of-truth + CDC: Use PostgreSQL as source for transactional data; Debezium captures WAL changes and produces events to Kafka. Consumers: - Product service listens and updates MongoDB documents when product-related rows change. - ETL consumer streams cleansed events into ClickHouse (or writes to S3 partitioned files for bulk load). Advantages: low-latency, reliable, ordering preserved. Requires idempotent consumers and schema evolution strategy.2. Event-first (event sourcing): Application writes domain events to Kafka; services project those events into their local stores (Postgres projections, MongoDB views, ClickHouse aggregates). Advantages: single write path, easier replay; trade-off: more design work and eventual consistency.3. Batch ETL for heavy analytics: Periodic Airflow jobs export transactional snapshots to Parquet on S3 and bulk-load into ClickHouse/Redshift for cost-efficient large-scale aggregates.

Consistency & conflict handling- Accept eventual consistency; design APIs to surface freshness SLAs (e.g., “last updated” timestamps).- Ensure idempotency (upserts with natural keys), deduplication (event IDs), and schema migration plans.- For read-after-write strictness, route reads to the authoritative store or implement read-through caching.

Operational considerations- Observability: trace events (OpenTelemetry), monitor lag, consumer offsets, schema registry for Avro/JSON schema compatibility.- Backups & DR per datastore, unified access control.- Test: contract tests between producers/consumers, replay tests, and disaster recovery drills.

This design balances correctness for transactions, flexibility for products, and performance for analytics while managing complexity via CDC/event streaming, idempotent consumers, and clear ownership of source-of-truth.

HardSystem Design

104 practiced

Design a globally-distributed database architecture that stores user profiles with strong consistency and a social feed with eventual consistency. Specify region placement, replication model, latency expectations, and how you would route reads and writes.

Sample Answer

Requirements & constraints:- Strong consistency for user profiles (correctness for reads after writes, cross-region).- Eventual consistency for social feed (high write throughput, low read latency globally).- Global user base across multiple regions, target P99 read latency <50ms regional, cross-region writes acceptable up to ~200ms for profiles.- High availability (tolerate region failures).

High-level architecture:- Multi-master for feed (geo-partitioned log + fan-out), single-master-per-user-profile with synchronous cross-region replication using Paxos/Raft-based consensus or a strongly-consistent distributed datastore (e.g., Spanner / Cockroach / FoundationDB).- Regions: choose 3+ active regions per major continent (e.g., US-EAST, EU-WEST, AP-SE). Place a profile leader replica in the user's home region; maintain peers in 2+ other regions for failover.- Replication: - Profiles: semi-synchronous replication via consensus group spanning 3 replicas (local + two remote). Writes go to leader, commit requires majority (ensures strong consistency). Use per-user or per-shard groups to limit quorum size and latency. - Feed: writes are appended to a region-local distributed log and replicated async to other regions (log replication). Fan-out-on-write to followers optionally; or fan-out-on-read with caches for hot feeds.

Routing reads/writes:- Use geo-DNS + edge routing (GSLB) to send client to nearest region.- Profiles: - Reads: Route to local read follower that proxies to leader if needed; prefer local leader when user’s profile leader is in-region; otherwise perform linearizable read via leader or use lease-based reads from local replica if lease held. - Writes: Route to user's profile leader (usually home region). If client in other region, proxy write; commit waits for majority quorum before acknowledging.- Feed: - Writes: Route to nearest region; append locally and respond once durably stored locally (or optionally after local replication); async cross-region replication. - Reads: Always served from nearest region's cache/replica; eventually consistent ordering; for strong ordering per author, route to author's home-log when necessary.

Latency expectations:- Profile write P99 ≈ leader-local + quorum cross-region: 100–250ms depending on inter-region RTTs.- Profile read P99 (local leader or lease): <50ms; cross-region read if proxying to leader: +100–200ms.- Feed write/read P99: <50ms local; global propagation within seconds.

Scalability & optimizations:- Shard users by consistent hashing; place consensus groups to minimize cross-region quorum hops when majority in proximate regions.- Use per-author logical logs for feed to reduce cross-shard coordination.- Read path caches (CDN + regional caches) for hot feeds; watermark-based incremental replication to reduce bandwidth.- Failure handling: leader election within consensus groups; automatic failover to remote replica; for profiles, fail over with session stickiness to avoid split-brain.

Trade-offs:- Strong profiles cost higher write latency and complexity but guarantee correctness.- Eventual feed improves write throughput and read latency at cost of stale reads and weaker ordering—acceptable for social content.

Key metrics to monitor:- Write/read latencies, replication lag, quorum availability, cache hit rates, cross-region bandwidth, tail latencies.

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.