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

33 practiced

You must migrate a monolithic PostgreSQL-backed application to microservices, each owning its own data store (potentially different technologies). Outline a migration plan that minimizes downtime and preserves data consistency during cutover. Include approaches like change-data-capture (CDC), dual writes, strangler pattern, and verification strategies.

HardTechnical

43 practiced

Propose monitoring, alerting, and SLOs for a full-stack application that uses multiple datastores (RDBMS for transactions, NoSQL for sessions, search for product discovery). What metrics should be collected per store, how do you convert them to user-facing SLOs (frontend latency and error budgets), and how would you structure runbooks for database incidents?

Sample Answer

**Approach (brief)** I’d define measurable datastore metrics, map them to user-facing SLOs (frontend p95/p99 latency and error budget), create tiered alerts, and ship concise runbooks for incident response and RCA.

**Metrics to collect (by store)** - RDBMS (transactions): query latency histogram, throughput (QPS), slow query count, connection pool usage, TX rollback/error rate, lock/wait times, replica lag, CPU/I/O, free connections. - NoSQL (sessions/cache): get/put latency, read/write hit ratio, TTL expirations, throttling/errors, node availability, memory pressure, compaction stats. - Search (product discovery): query latency percentiles, index/update latency, query error rate, index queue length, shard/unbalanced nodes, memory/GC.

**Convert to user-facing SLOs** - Define frontend SLOs (example): 99% of page loads <= 300ms backend time; error rate <= 0.5% per week. - Map datastore metrics to SLO impact: e.g., RDBMS p95 > 200ms correlates to frontend p95 breaches; replica lag > 5s increases errors for recent data reads. Use historical correlation (A/B or trace sampling) to set thresholds. - Define error budget = allowed error rate/time (e.g., 0.5% weekly). Use impact-weighted signals (latency spikes that cause degraded UX count against budget).

**Alerting strategy** - Fast alerts (page) for symptoms that immediately affect SLO: frontend error-rate spike, p95 latency breach, DB connection exhaustion, node down. - Warning alerts (email/Slack) for leading indicators: slow-query growth, replica lag rising, memory at 80%. - Use composite alerts: e.g., frontend latency + DB p95 both elevated -> escalate.

**Runbook structure for database incidents** 1. Title & severity, owner, pager. 2. Symptoms (what to look for) and quick checks (queries/metrics dashboards). 3. Immediate mitigations (step-by-step): - For RDBMS: identify top queries (slow query log), increase read replicas or failover, drop noncritical jobs, scale connection pool, restart problematic replica. - For NoSQL: increase capacity, evict large items, adjust TTLs, recycle node, re-route traffic to healthy nodes. - For Search: pause indexing, roll back recent index change, redistribute shards, increase replicas. 4. Safety checklist (backups, read-only switches, feature flags to disable heavy queries). 5. Communication template (status updates, customer-facing message). 6. Post-incident: RCA template, timeline, remediation plan, SLO impact, tests to prevent recurrence.

**Why this works** Collecting rich datastore metrics and mapping them via observed correlations to frontend SLOs focuses operations on user experience. Tiered alerts reduce pager noise, and runbooks ensure fast, consistent remediation and continuous improvement.

EasyTechnical

43 practiced

Describe the CAP theorem in your own words and give one concrete example of a system that favors availability over consistency, and one that favors consistency over availability. Explain why the trade-off is acceptable in each example.

MediumSystem Design

33 practiced

Design the storage layer for an e-commerce product catalog of 5 million items: heavy reads, infrequent writes, faceted search, and complex filtering. Choose primary datastore(s), indexing strategy, denormalization approach, and caching. Explain how you'd integrate a search engine and maintain sync.

MediumTechnical

42 practiced

You are building a social platform with features: authentication, user profiles, friend-graphs, posts, comments, and analytics. Propose a mapping of data stores (relational, document, graph, key-value) to each feature and justify your choices based on query patterns, transaction needs, and scalability. Mention where caching or search should fit.

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.