Database Selection and Trade Offs Questions
How to evaluate and choose data storage systems and architectures based on workload characteristics and business constraints. Coverage includes differences between relational and nonrelational families such as document stores, key value stores, wide column stores, graph databases, time series databases, and search engines; mapping query patterns and latency requirements to storage options; trade offs between strong consistency and eventual consistency and their impact on availability and complexity; partition key design, replication strategies, and high availability considerations; operational concerns including backups, monitoring, vendor and cost trade offs, migration or hybrid strategies, and when to adopt polyglot persistence. Senior level discussion includes selecting specific managed services and reasoning about expected load patterns, failure modes, and operational burden.
MediumSystem Design
57 practiced
Design the data storage architecture for a social feed service with 10M users and 1B posts. Requirements: personalized feeds with p95 read <100ms, write throughput 50k posts/min, support full-text search over posts, and eventual consistency acceptable for feed freshness. Map query patterns (fanout-on-write vs fanout-on-read) to storage options (wide-column, key-value cache, search engine) and justify replication, caching, and consistency trade-offs.
Sample Answer
Requirements clarification:- 10M users, 1B posts; writes ~50k posts/min (≈833 writes/s). p95 reads <100ms for personalized feeds; eventual consistency OK for freshness; full-text search over posts.High-level hybrid design:- Full-text search: Elasticsearch/OpenSearch cluster (inverted index) for search queries and discovery.- Primary durable store: Wide-column DB (Cassandra/Scylla) to store posts and per-user timeline fragments — good for high write throughput, predictable tail latencies, time-ordered reads.- Low-latency cache: Partitioned key-value cache (Redis Cluster) to serve precomputed personalized feeds (hot-path) with p95 <100ms.Query patterns → storage mapping and justification:1. Fanout-on-write (precompute timelines at write time) - When a user posts, background workers push reference entries (post_id, score, timestamp) into follower users’ timeline rows in Cassandra (wide rows keyed by user_id). Also write the canonical post into Cassandra. - Cache: After writer updates Cassandra, update Redis list for affected users (LPUSH / trim) so reads hit cache. - Use when writers have moderate fanout or for high-read, low-latency feeds (p95 SLA). Precomputing shifts cost to writes and background workers, ensuring reads are simple key lookups in Redis (sub-100ms). - Trade-offs: Higher write amplification and storage; manageable since eventual consistency acceptable and write rate (~833/s) is modest.2. Fanout-on-read (compute feed at read time) - For users with huge followings (celebrities) or very cold users, do not push to millions of followers. Instead store follower lists and retrieve latest posts from those followees from Cassandra and merge-sort by timestamp at read time; cache the merged feed briefly in Redis. - Use when write amplification would be prohibitive. - Trade-offs: Higher read CPU/IO and increased read tail latency; mitigate with caching and async precomputation for active users.3. Full-text search - Index canonical post content from Cassandra into Elasticsearch asynchronously (near real-time via change data capture or streaming pipeline). Use bulk/batched indexing to absorb write spikes. - Searches hit ES; search results include post_id then fetch latest metadata from Cassandra if needed.Replication, availability and consistency:- Cassandra: Multi-AZ replication with RF=3, quorum reads/writes for stronger consistency where needed; default to LOCAL_QUORUM for region-resilience. Because eventual consistency is acceptable for feeds, many operations can use ONE or LOCAL_ONE for lower latency; critical metadata (deletes, edits) use quorum.- Redis: Clustered, master-replica with replicas in different AZs. Use async replication for speed; promote replicas on failure. Keep caches warm with TTLs and write-through/aside strategies.- Elasticsearch: Multi-node, zone-aware shards and replicas (shard replica count >=1). Use near real-time indexing; accept slight indexing lag.Caching strategy and SLOs:- Primary read path: Redis serves top-N feed items. p95 <100ms achieved by keeping reads hit cache and limiting fetch size (e.g., top 50).- Cache warming: Precompute feeds for active users via fanout-on-write. For cold misses, fanout-on-read with on-demand cache population.- Eviction and TTL: short TTL (~1–5 min) for dynamic freshness; pinned items for promoted content.- Backpressure: If write worker backlog grows, temporarily switch heavy-followers to fanout-on-read to protect latency.Operational considerations (SRE-focused):- Monitoring: latency, error rates, replication lag, queue depths (Kafka/stream workers), index lag to ES. Alert thresholds on Redis miss rate, Cassandra pending compactions, ES merge times.- Capacity planning: writes 833/s → plan Cassandra write throughput with compaction headroom; ES indexing throughput via batching. Redis memory sizing for hot feeds (estimate avg feed size * active users).- Automation: auto-scaling workers for indexing & fanout; chaos testing for failover; playbooks for replica promotion and reindexing.- Data lifecycle: TTL/archival of old posts to cold storage (S3) and remove from hot indexes to control costs.Summary trade-offs:- Fanout-on-write + Redis = predictable, sub-100ms reads at cost of write amplification and storage; best for active users where read SLA matters.- Fanout-on-read + Cassandra = lower write cost, higher read latency and compute; used for high-fanout producers and cold users.- Elasticsearch handles search; eventual indexing lag accepted.- Replication tuned per operation: prefer lower-latency consistency for feeds (ONE/LOCAL_ONE) and stronger quorum for critical updates.This hybrid ensures the p95 read SLA while keeping write throughput, storage, and operational complexity balanced.
EasyTechnical
38 practiced
Describe workloads and trade-offs for key-value stores (Redis, DynamoDB) used as primary storage versus as a cache. Discuss persistence/durability options, eviction strategies, TTL usage, memory vs disk trade-offs, and scenarios where an in-memory KV store is acceptable as primary storage versus when persistence is required.
Sample Answer
Key-value stores (Redis, DynamoDB) can be used either as caches or primary storage; each choice has workload patterns and trade-offs SREs must weigh.Workloads & trade-offs- As cache: read-heavy, low-latency, ephemeral data (session tokens, rendered fragments). Benefits: sub-ms reads, reduced backend load. Risk: cache misses, stale data; acceptable to lose entries.- As primary store: source-of-truth data (leaderboards, user profiles if small), requires durability, backups, consistent reads/writes. Benefits: simplicity and speed; risks: data loss if durability weak, limited queryability.Persistence / durability- Redis: RDB snapshots (point-in-time, fast recovery but potential data loss between snapshots), AOF (append-only log, configurable fsync for better durability), combination (AOF + RDB) and Redis Cluster for HA. For strict durability use AOF with fsync=always or use disk-backed DB.- DynamoDB: fully managed, durable by default (replicated across AZs/regions depending on config), strong/ eventual consistency options, built-in backups and point-in-time recovery.Eviction strategies & TTL usage- Redis eviction policies: noeviction, allkeys-lru, volatile-lru, TTL-based (volatile-ttl), random. Use volatile policies when only some keys expire; allkeys-lru for global space limits.- TTLs: good for expiring sessions, caches; avoid relying on TTLs for critical business data. Use TTLs with leaky- bucket workloads to avoid thundering herd on expiry.Memory vs disk trade-offs- In-memory (Redis): fastest, but memory-constrained and expensive; requires eviction or sharding. Persistence adds IO overhead and may increase recovery time.- Disk-backed (DynamoDB or Redis on Flash): cheaper storage, higher latency, throughput limits. Choose based on SLOs (p99 latency vs cost).When in-memory KV is acceptable as primary- Acceptable: non-critical, easily recomputable data (caches warmable), low-RPO tolerance, applications requiring extreme low latency where occasional loss is tolerable (leaderboards, analytics counters with periodic reconciliation).- Not acceptable: single source of truth for financial transactions, order state, user data that cannot be reconstructed or tolerated to be lost; there use durable stores (DynamoDB with backups, RDBMS) and strong consistency.Operational recommendations (SRE)- Define SLOs for durability and latency; choose persistence settings accordingly.- Use replication, backups, and automated failover for primary usage.- Monitor memory pressure, eviction metrics, AOF rewrite times, and restore drills.- Combine: durable DB as primary + Redis as cache with cache-aside pattern and proper TTLs/eviction to balance cost and performance.
MediumTechnical
36 practiced
Explain how you would architect a highly available PostgreSQL deployment for a write-heavy OLTP workload (5k writes/s) with RPO <= 1 minute. Cover primary/standby topology, synchronous vs asynchronous replication trade-offs, failover automation tooling, monitoring alerts, and backup/point-in-time recovery strategy to meet RTO/RPO.
Sample Answer
Requirements & constraints:- Write-heavy OLTP ~5k writes/s (sustained), RPO ≤ 1 minute, high availability with automated failover and short RTO (minutes).- Priorities: durability (RPO), availability (fast failover), performance (IO/CPU), operational safety (no split-brain).High-level topology:- Single Primary (writer) + 2 synchronous-capable standbys in different AZs + 2+ asynchronous replicas. - Synchronous standbys (quorum = 1 or quorum=majority): at least one sync standby must ACK WAL to meet RPO ≤ 1 min. - Asynchronous replicas for read-scaling, backups, and analytics to avoid adding commit latency to writer.Replication choices & trade-offs:- Synchronous replication: - Pros: Lower RPO (commits acknowledged on standby), safer for durability. - Cons: Higher commit latency (depends on network/standby apply speed), risk of reduced throughput if standby slow. - Mitigation: Use synchronous_commit = on (or synchronous_commit = remote_apply for stronger guarantees if supported), place sync standbys on low-latency links, tune wal_sender / wal_receiver, use wait_for_wal to reduce stalls.- Asynchronous replication: - Pros: No commit latency impact; good for scaling reads and backups. - Cons: Potential WAL lag -> RPO gap; cannot guarantee <1-min without additional measures.- Recommended: Configure primary to require ACK from one synchronous standby for commit (synchronous_commit=on with primary_conninfo/synchronous_standby_names="FIRST 1 (<node1>,<node2>)"). Keep additional async replicas.Failure detection & automated failover:- Use Patroni (with etcd/consul/zookeeper) or repmgr + distributed consensus for leader election + fencing. Patroni is common for automated failover, leader election and reconfiguration.- Ensure automatic failover safety: - Use quorum-based election (etcd/consul) to avoid split-brain. - Configure fencing/maintenance mode for network partitions. - Use synchronous_standby_names to prioritize which node becomes primary after promotion.- Promote standby with transaction replay complete (remote_apply) to avoid data loss; use fast promotion but ensure WAL flush.Service routing & RTO:- Use a virtual IP managed by keepalived or an external TCP load balancer/HAProxy that points to the current primary; or update DNS with short TTL plus automation that updates records on failover.- Aim RTO: automated failover + load balancer switch < 1–2 minutes. Test regularly.Backup & PITR to meet RPO/RTO:- Continuous WAL archiving + periodic base backups: - Use pgbackrest or wal-g for reliable base backups and WAL shipping to S3 (or object store). - Configure archive_command to push WAL segments immediately; use compression.- PITR strategy: - Retain WAL segments sufficient to restore to any point within retention window (e.g., 7–30 days), but for RPO use replication for recent recovery. - To meet RPO ≤ 1 min, rely on sync standby for last-minute durability; WAL archive provides longer-term recovery and recovery from logical errors.- Regularly test restores and PITR; maintain scripts and runbooks.Configuration & tuning for 5k writes/s:- Storage/IO: provision NVMe or very fast disks with predictable IOPS. Monitor WAL write throughput and disk latency.- Postgres settings: - wal_level = replica - max_wal_senders >= number of replicas - wal_compression = on (if CPU allows) - checkpoint_timeout / max_wal_size tuned to avoid heavy checkpoint spikes; increase background_writer / checkpoint_completion_target to smooth IO. - synchronous_commit = on (or remote_apply) for durability - use replication_slots for async replicas but monitor slot lag to avoid disk growth.- Connection pooling (PgBouncer) to reduce backend connection pressure.Monitoring & alerts:- Key metrics and alert thresholds: - replication/WAL lag (bytes and time) — alert if > 30s, critical if > 60s. - commit latency (p99) — alert if increases beyond baseline. - disk latency (read/write ms) — alert on sustained high IOPS latency (>10–20ms depending on storage). - CPU, memory, swap usage. - checkpoint duration & frequency (frequent long checkpoints). - long-running transactions and open transactions > threshold. - WAL archive failures, backlog, and archive latency. - replication slots size growth. - failover/promotion events and node unreachable.- Tools: Prometheus + pg_exporter for metrics, Grafana dashboards, Alertmanager for dedup/notification. Integrate runbooks into alerts.Recovery & runbook:- Automated failover: Patroni promotes sync standby; load balancer updates endpoints. If automatic fails (partition, no quorum), manual intervention steps.- Post-failover checks: validate replication resume, verify replica apply lag 0, run smoke tests, check data integrity.- Restore from backup/PITR: use pgbackrest restore or base backup + restore WAL to chosen timestamp. For RTO targets longer than failover window, document expected restore times and test.Operational practices:- Regular failure drills (chaos testing), backup/restore drills, and capacity testing for 5k writes/s.- Maintain runbooks for failover, rollback, and emergency PITR.- Keep maintenance windows for major upgrades; use logical replication for zero-downtime schema changes where possible.Summary:- Use at least one synchronous standby in a separate AZ to meet RPO ≤ 1 minute, with async replicas for scale and backups.- Automate failover with Patroni + etcd and route traffic via VIP/load balancer.- Continuous WAL archiving + pgbackrest/wal-g for PITR; tune WAL/config for high write throughput.- Monitor WAL lag, commit latency, disk I/O, and replication health; run frequent drills to ensure RTO/RPO targets are met.
HardSystem Design
45 practiced
Architect read-write separation for a Postgres cluster serving both OLTP and analytical workloads. Requirements: 10k TPS for writes, complex analytical queries must not impact write tail latency, and eventual consistency for analytics is acceptable. Discuss replica lag, async replicas, query routing, materialized views, logical replication to analytical stores, and SLO maintenance.
Sample Answer
Requirements:- 10k TPS writes (OLTP), low write-tail latency SLO (e.g., p99 < 50ms)- Analytical queries must not increase write latency- Eventual consistency for analytics acceptable (minutes)- High availability and observable replica lagHigh-level design:- Single primary Postgres for OLTP writes; multiple async physical replicas for read-scaling; separate analytical pipeline using logical replication into a columnar store (e.g., ClickHouse, Snowflake) and dedicated read replicas for low-latency reads.- Query router (proxy like pgbouncer/pgpool or envoy + custom routing) directs latency-sensitive reads to primary/closely-caught-up replicas, heavy analytical queries to analytical store or isolated replicas.Components & responsibilities:1. Primary DB: handles 10k TPS, tuned (wal_level, checkpoint_segments, wal_compression, appropriate io subsystem/instance sizing).2. Async physical replicas: serve straightforward read traffic; labeled by replica_lsn/replication_lag.3. Close-catchup replicas (semi-sync optional): a small pool with minimal lag for critical reads and follower-fencing.4. Analytical pipeline: logical replication (pg_logical/pgoutput) -> CDC stream -> transform -> columnar DB; materialized views maintained in analytical store or via periodic refresh on scaled workers.5. Query Router: routing rules: - OLTP reads that require strong freshness -> primary or catchup replicas within lag threshold. - Non-critical reads/BI -> analytical store or long-lag replicas. - Long-running SQL -> send to analytical store; reject or queue if unknown.6. Materialized Views: created in analytical store or on dedicated replica; refreshes scheduled/incremental via CDC to avoid heavy scans on primary/replicas.Replica lag & async replicas:- Monitor replication lag via pg_stat_replication (write_lag, replay_lag, sync_priority) and application-level LSN watermarking.- Set routing thresholds (e.g., if lag > 500ms, route to alternative replica or analytical store).- Use semi-sync only if write SLO allows single-rep sync latencies; otherwise async to meet 10k TPS.SLO maintenance & observability:- Define SLOs: write p99 latency, tail latency budget, replication lag percentiles, availability.- Instrument: metrics for TPS, p50/p95/p99 write latency, WAL generation rate, replica lag histograms, CDC lag, query queue lengths.- Alerts: exceed lag thresholds, WAL backlog > capacity, slow checkpoints, high commit latency.- Automated mitigation: circuit-breaker routing when lag crosses thresholds, autoscale read replica pool, backpressure long analytical queries (query admission control).- Runbooks: failover steps, read-only promotion, analytical pipeline replay.Trade-offs:- Async replicas maximize write throughput but introduce lag; mitigated by routing policies and catch-up replicas.- Logical replication + columnar store increases operational complexity but isolates heavy analytics.- Semi-sync improves durability at cost of write latency—use only if SLO allows.Example flow:- Write -> Primary WAL -> Physical replicas (async) -> pgoutput -> CDC -> ClickHouse; BI query hits ClickHouse; freshness acceptable within configured lag window. If a user requests recent transaction view, router checks replica lag; if under threshold route to replica; else route to primary.This design isolates analytical workload from write tail, uses routing + observability to maintain SLOs, and leverages CDC/materialized views to provide eventual-consistent analytics without impacting OLTP performance.
HardTechnical
33 practiced
Explain trade-offs between normalized and denormalized data models for high-scale services. Provide examples where denormalization improves read performance at the cost of write complexity, and describe practical patterns (CDC, event sourcing, reconciliation jobs) to keep denormalized copies consistent in production.
Sample Answer
Normalized models (3NF) minimize redundancy and make updates simple and consistent: a single source of truth, small transactional writes, and easier referential integrity. But joins across many normalized tables are expensive at scale (latency, CPU, and cross-shard complexity), increasing read tail latency and load on DBs.Denormalized models trade storage and write complexity for read performance: precomputed joins, aggregated documents, or materialized views deliver single-key reads with low latency and predictable tail behavior (critical for SREs). Example: user profile + preferences + subscription state stored as one JSON document in a KV store—reads for serving requests become O(1) vs multiple joins on every request. E-commerce product pages often denormalize product, inventory, and pricing to serve pages under strict latency SLOs.Costs of denormalization:- Writes become more complex and more frequent (update many denormalized copies).- Risk of divergence / stale reads (eventual consistency).- Increased storage and complexity of schema evolution.Practical patterns to keep denormalized copies consistent:- Change Data Capture (CDC): stream DB binlogs into a message bus (Kafka) and apply transformations to update read stores. Advantages: reliable, low-latency propagation; requires idempotent consumers and schema-change handling.- Event Sourcing: write immutable events as the source of truth; materialize projections for reads. Good for auditability and replay, but increases operational complexity and requires careful event schema design and upcasting.- Reconciliation jobs: periodic full or incremental compares to detect and repair drift (useful safety net). Implement rate-limited repairs and backoff to avoid cascading load.Operational considerations (SRE focus):- Ensure idempotency, ordered processing (or causal guarantees), and retry semantics in consumers.- Monitor lag (consumer offsets), divergence metrics (staleness), and repair rates with alerts tied to SLOs.- Feature flags and graceful degradation: fall back to normalized reads for critical operations when projections are stale.- Plan schema migrations (upcasting, dual-write windows) and test via chaos/replay drills.Trade-offs: choose denormalization where read SLOs justify extra write complexity and operational cost; otherwise prefer normalization for simplicity and stronger consistency.
Unlock Full Question Bank
Get access to hundreds of Database Selection and Trade Offs interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.