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.

EasyTechnical

35 practiced

Explain polyglot persistence, including when it is valuable and common architecture patterns that combine multiple data stores. Describe the operational and developer pitfalls to avoid and sketch a minimal architecture that uses an RDBMS for transactions, a document store for flexible profiles, and a search engine for product lookup.

Sample Answer

Polyglot persistence is the pragmatic use of multiple, purpose-fit data stores in one system — choosing the best database type (relational, document, key-value, columnar, search index, graph, etc.) for different workloads rather than forcing everything into a single store.

When valuable:- Heterogeneous requirements: strong ACID transactions + flexible user profiles + full-text/product search.- Scale and cost optimization: cheaper stores for high-volume, low-complexity data.- Faster feature delivery: use a schema-less store for evolving domains.

Common architecture patterns:- Command Query Responsibility Segregation (CQRS): writes to transactional store, reads from optimized read models (document store, search).- Event sourcing + projections: events are the source; projections populate multiple stores.- Cache-aside + denormalized read stores: RDBMS canonical, document store/search for reads.

Operational & developer pitfalls:- Data consistency: eventual consistency surprises; design for stale reads and reconciliation.- Complexity: more operational overhead (backups, upgrades, monitoring, security) and increased onboarding cost.- Data modeling gaps: duplicated logic across stores, drift between schemas/models.- Latency and failure modes: replication/ETL pipelines can lag; plan idempotency and retries.- Cost & compliance: multiple SLAs, licensing, and data residency concerns.

Minimal architecture sketch:- RDBMS (primary) for transactions (orders, payments) — strong consistency, FK constraints.- Document store for flexible user profiles and preferences; store profile canonical ID referencing RDBMS user_id.- Search engine (Elasticsearch/OpenSearch) for product lookup with denormalized product documents (name, description, facets).- Integration: on transactional commits emit domain events (or use DB triggers/CDC) → lightweight event bus (Kafka) → consumers that update document store and index search engine. Reads: queries routed—transactional reads from RDBMS; profile reads from document store; product search from search engine.- Cross-cutting: implement data versioning, consumer-side reconciliation, monitoring/alerts for replication lag, and canonical ownership rules (RDBMS owns truth for transactional entities).This balances correctness for payments, flexibility for profiles, and fast search results while explicitly handling eventual consistency and operational complexity.

EasyTechnical

37 practiced

Explain differences between full backups, incremental backups, snapshots, and point-in-time recovery (PITR). For a transactional OLTP database with 99.95% SLA and RPO 15 minutes, recommend a backup and retention approach and explain operational tradeoffs and testing you would perform.

Sample Answer

Full backup: a complete copy of the database at a point in time. Good for long-term restore but slow and storage-heavy. Incremental backup: captures changes since the last full (or incremental) backup; saves storage and speeds regular backups but requires chaining to restore. Snapshot: fast, block-level copy of a disk or volume (often storage- or hypervisor-native). Very quick to create/restore but may not be crash-consistent unless coordinated with the DB (quiesce/WAL flush). Point-in-time recovery (PITR): uses base backups + a continuous stream of transaction logs (WAL/Journals) to restore the database to any specific timestamp or transaction. Essential for transactional systems with tight RPOs.

Recommendation for a transactional OLTP with 99.95% SLA and 15‑minute RPO:- Continuous WAL/transaction-log shipping (or cloud-native continuous backup) to a durable location (S3, Blob) with sub-minute delivery — this provides PITR to meet 15‑minute RPO and enables rolling forward to any timestamp.- Daily full backups (off-peak) plus hourly incremental block or file-level backups for faster base restore options.- Application-consistent snapshots for fast recovery tests and short-term rollback (ensure DB is quiesced or snapshotting integrates with DB agent).- Retention: keep WALs for at least 7–30 days (depending on compliance); keep daily full backups 30 days, weekly fulls 12 weeks, monthly fulls 1 year (adjust per policy).

Operational tradeoffs:- Performance: continuous WAL shipping and frequent incremental backups add IO and network overhead; mitigate with async shipping, offloading to replica, and throttling.- Storage cost: frequent logs and snapshot retention increase cost; compress/encrypt and lifecycle to cheaper tiers.- Complexity: PITR adds operational complexity (managing base backups + logs). Automation and monitoring are essential.- RTO vs cost: snapshots + warm standby replicas reduce RTO dramatically but cost more than restore-from-backup.

Testing and validation:- Regular restore drills (monthly): full restore from full+incrementals and PITR to random timestamps; measure RTO and verify data consistency.- Automated continuous verification: checksum/row-count comparisons between primary and restored instance.- Disaster runbooks and run-throughs: simulate region failure and perform failover to replica.- Chaos tests: inject failures during backup or log shipping to validate recovery paths.- Monitor metrics and alerts: backup success/failure, lag in log shipping, retention expiration.

This approach meets RPO 15 minutes (via WAL/PITR) and supports a low RTO if combined with snapshots or warm replicas; tradeoffs are primarily cost and operational complexity, which are mitigated by automation, offloading backups to replicas, and regular testing.

EasyTechnical

40 practiced

Describe typical graph database use cases and explain why graph databases outperform relational joins for deep traversals. Provide an example design for social recommendations using a graph DB and outline scaling concerns such as partitioning and cross-shard traversals.

Sample Answer

Typical graph-database use cases:- Highly-connected data: social networks, fraud detection, recommendation engines.- Relationship-heavy queries: knowledge graphs, network topologies, access-control lineage.- Real-time traversals: shortest-path, influence propagation, multi-hop recommendations.

Why graph DBs outperform relational joins for deep traversals:- Storage model: Graph DBs store nodes and adjacency (edges) as first-class records, enabling O(1) neighbor access. Relational joins require index lookups and repeated merge/join operations that touch many rows and cause random I/O.- Locality: Traversals follow pointers (in-memory or locality-optimized on disk), not repeated global scans; costs grow with visited nodes, not with total graph size.- Query semantics: Native traversal engines (BFS/DFS) and optimizations (index-free adjacency, path caching) reduce overhead of multi-hop queries vs. multi-join SQL plans that explode intermediate results.

Example design: social recommendation (friends-of-friends + interests)- Model: - Node types: User, Post, Topic, Group - Edge types: FRIEND(User→User, weight), LIKED(User→Post), INTERESTED_IN(User→Topic), MEMBER_OF(User→Group)- Recommendation traversal: - For user U: traverse FRIEND^(1..2) to collect 2nd-degree users, aggregate their LIKED posts and INTERESTED_IN topics, score items by weighted counts (decay by distance and recency), filter out items U already consumed.- Implementation notes: use graph DB (e.g., Neo4j, JanusGraph) with precomputed scores for heavy items; maintain time-decayed edge weights.

Scaling concerns and mitigations:- Partitioning (sharding): Graphs are hard to shard without cutting many edges. Strategies: - Community-based (modularity) partitioning to keep dense subgraphs together. - Hybrid: keep hot/active subgraphs on single shard; put cold data separately.- Cross-shard traversals: costly due to network hops and serialization. - Mitigations: async expansion with bounded frontier (limit hop depth), result caching, precompute k-hop neighborhoods or similarity indexes, use graph-aware routing to minimize cross-shard hops.- Consistency & transactions: multi-shard updates require distributed transactions—prefer eventual consistency for non-critical edges or use lightweight compensating workflows.- Operational: monitor hot nodes (high-degree hubs), apply degree-based treatment (store popular node’s adjacency as replicated read-only copies), use load-aware replica placement.

Trade-offs: deeper real-time traversals favor graph DBs; for analytic, enormous batch joins might be handled by distributed columnar/graph-processing frameworks. Choose a hybrid: graph DB for OLTP traversals + batch jobs (Spark/GraphX/Giraph) to recompute heavy aggregates.

MediumTechnical

44 practiced

Propose a monitoring and alerting strategy for a stack containing Postgres, Elasticsearch, Cassandra, and Redis. Define key metrics to collect for each store, example SLOs and alert thresholds, correlation signals between stores and application layers, and runbook triggers for common incidents like slow queries or node failures.

Sample Answer

Situation: Designing an operator-friendly monitoring and alerting strategy for a stack with Postgres, Elasticsearch, Cassandra, and Redis. Below is a practical, role-appropriate plan covering key metrics, example SLOs/thresholds, cross-store correlation signals, and runbook triggers.

Key metrics to collect- Postgres: active connections, query latency (P95/P99), slow queries/sec, replication lag, deadlocks, cache hit ratio, WAL volume, CPU/IO wait.- Elasticsearch: cluster health (green/yellow/red), shard allocation/unassigned shards, node disk usage, JVM heap usage/gc pause P95, query latency, indexing rate, refresh/merge times.- Cassandra: node up/down, read/write latency (P95/P99), hinted handoff queue, pending compactions, tombstone scans, coordinator timeout rate, disk usage.- Redis: evictions/sec, memory used vs max, keyspace hits/misses, command latency, replication lag, persistence RDB/AOF rewrite duration.

Example SLOs & alert thresholds- Availability SLO: 99.95% availability per store monthly.- Latency SLOs: Postgres P99 < 500ms; ES search P95 < 200ms; Cassandra P99 < 200ms; Redis P99 < 50ms.- Alerts (examples): - Critical: node down (any store) OR cluster status red (ES) OR replication lag > 30s (Postgres/Redis) — page on call. - High: sustained P99 latency above SLO for 5m — notify on-call. - Medium: disk usage > 75% (warn), > 90% (critical). - JVM heap > 85% for 5m (ES) — investigate GC.

Correlation signals between stores & app- Spike in application request latency + increased Postgres P99: likely slow queries or CPU/IO saturation.- ES search latency + increased application 5xxs for search endpoints: ES bottleneck.- Increased Redis misses + increased Postgres/ES read load: cache failure/falling back to backing stores.- Cassandra coordinator timeouts + uneven node disk usage: hotspot or failed node.- Cross-store write errors (e.g., app write -> succeed in Redis but fail in Postgres): data consistency risk — surface with distributed tracing.

Runbook triggers & steps (high-level)- Slow queries (Postgres P99 > SLO for 5m): 1. Check slow query log, current queries (pg_stat_activity), locks. 2. Identify offending queries; capture EXPLAIN ANALYZE; check indexes. 3. If lock contention: identify blocker, kill if safe, scale read replicas if under heavy read load. 4. Short term: add query timeout or rate limit; long term: optimize query/index or add read replica.- ES node failure / yellow/red: 1. Check cluster health, unassigned shards, node logs, disk/xpack events. 2. If node down: restart node or reallocate; increase replica allocation if safe. 3. If disk pressure: free space, increase disk, or add node. 4. Rebalance shards; monitor recovery rate.- Cassandra node down / hinted handoff backlog: 1. Check nodetool status, pending compactions, repair status. 2. Reboot/rejoin node; run repair; monitor hinted handoff drain. 3. If permanent failure: replace node, rebalance tokens.- Redis high evictions/memory pressure: 1. Check eviction policy, key TTLs, and large keys. 2. Scale memory, enable LRU tuning, or add clustering/sharding. 3. If replication lag: promote replica if primary fails.

Operational best practices- Centralized observability: Prometheus + Alertmanager + Grafana for metrics; ELK/EFK for logs; distributed tracing (OpenTelemetry) to correlate app traces with datastore calls.- Use synthetic transactions and SLO burn-down dashboards per service and per customer-critical flows.- Alert fatigue control: use multi-condition alerts (e.g., latency + increased errors), severity routing, and runbook links in alerts.- Automated remediation where safe (auto-restart on transient node OOM, auto-scale read replicas), with careful guardrails.

This strategy balances actionable alerts, cross-system correlation, and documented remediation steps suited for client architectures and sales proposals.

HardSystem Design

45 practiced

Design architecture for a multi-tenant OLAP analytics platform that must serve sub-minute ad hoc queries over tens of TBs per tenant. Cover storage format choices, compute scaling, partitioning per tenant, data isolation/quotas, pre-aggregation strategies, caching, concurrency control, and cold versus hot tiering.

Sample Answer

Requirements & constraints:- Multi-tenant OLAP, tens of TB per tenant, sub-minute ad-hoc queries, strong isolation/quotas, cost-efficiency, support pre-aggregations/caching, scale compute independently from storage.

High-level architecture:- Object store (S3/GCS) for raw/parquet data + columnar format; query engine (distributed MPP like Trino/Presto or commercial lakehouse compute) for ad-hoc; serving layer of purpose-built pre-aggregation service (materialized views), an in-memory cache (Redis/Memcached) and a scheduler for refreshes; metadata/catalog and tenant management service.

Storage format & layout:- Parquet/ORC with ZSTD, column pruning, dictionary encoding. Partition by (tenant_id, date, region) and cluster-sort hot columns (customer_id, campaign) to speed filters. Maintain small-file compaction background jobs.

Compute scaling:- Decouple compute from storage: autoscaling MPP clusters per tenant-class (hot/interactive pools and larger batch pools). Use resource pools/queues to route queries; scale down idle pools and burst to cloud spot instances for batch.

Partitioning & isolation:- Logical tenancy: tenant_id in partitions and row-level ACLs. Physically isolate heavy tenants by assigning dedicated compute pools or separate catalogs. Per-tenant quotas enforced in query gateway (concurrency, bytes scanned, CPU seconds).

Pre-aggregation & caching:- Two-tier MV strategy: incremental, query-driven pre-aggregations for frequent patterns (hourly/daily aggregates) stored in columnar files; on-demand ephemeral rollups for ad-hoc. Use Redis for result caching with TTL and cache keys including query fingerprint + tenant. Maintain freshness metadata and backfill pipeline.

Concurrency control:- Centralized query coordinator with admission control, per-tenant concurrency limits, cost-based admission (estimate bytes scanned via statistics), and queuing with SLA classes. Use optimistic concurrency on materialized view refreshes; lock minimal metadata with retry/backoff.

Cold vs hot tiering:- Hot: recent partitions (last 30 days) stored on fast object storage with cached warmed parquet, kept in SSD-backed warm cache or warmed compute nodes. Cold: older partitions moved to deep archive or compressed columnar files with slower retrieval and longer query latencies; transparent via catalog that rewrites partition locations.

Operational & trade-offs:- Monitor cost vs latency; heavy tenants get dedicated pools (cost higher but latency guaranteed). Use compaction and statistics collection to keep planner efficient. Security: per-tenant encryption keys and RBAC. This design balances sub-minute ad-hoc SLAs, multi-tenant isolation, and cost-effective large-scale storage.

Unlock Full Question Bank

Get access to hundreds of Database Selection and Trade Offs interview questions and detailed answers.

Join thousands of developers preparing for their dream job.