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.

MediumTechnical

64 practiced

You're modeling many-to-many relationships (e.g., users and groups) for a backend that must support fast membership checks and group-mutation operations. Compare approaches using a relational join table, an embedded array in a document store, and a separate membership service with denormalized data. Describe trade-offs for each.

HardTechnical

62 practiced

A Cassandra cluster experiences a network partition and two halves accept writes independently (split-brain). Enumerate possible failure modes, data divergence scenarios, and a recovery plan to reconcile data while minimizing data loss and downtime.

Sample Answer

**Situation overview (short)** If a Cassandra cluster splits and both partitions accept writes, you can get divergent SSTables, tombstone mismatches, and metadata/schema drift. Reconciliation must balance data-loss risk, downtime, and operational safety.

**Possible failure modes** - Divergent rows because writes accepted on both sides (last-write-wins by timestamp may be inconsistent if clocks skew). - Tombstone conflicts: deletes written on one side may be lost if the other side later writes older timestamps (resurrection). - Hinted handoff/inflight hints lost or misapplied after partition heals. - Repair/anti-entropy windows missed, leading to permanent divergence if gc_grace_seconds expired. - Schema migration divergence if DDL applied only on one side. - Client confusion / split-brain on coordinator selection causing write amplification.

**Data divergence scenarios** - Conflicting updates same primary key with different timestamps → conflict resolved by highest timestamp (may be wrong if clocks unsynced). - Concurrent delete vs update → tombstone may be overwritten if timestamps wrong, causing resurrected rows. - Missing partition replicas where hinted-handoff never applied, creating gaps. - Partial schema changes (extra columns/types) that break reads.

**Recovery plan (step-by-step, minimize loss & downtime)**

1. Immediate containment - Quiesce writes if possible (redirect traffic to read-only or a single partition). If must keep writes, prefer the partition with majority/closest to full replica set or upstream clients. - Freeze schema changes and operator actions.

2. Assessment & snapshot - Take snapshots on all nodes (nodetool snapshot) and export commitlogs for both partitions. - Collect node logs, hint directories, system tables, commitlog segments, and schema definitions.

3. Choose reconciliation strategy - If one side received significantly more writes or has global quorum state, treat it as canonical. Otherwise prefer highest aggregate timestamps *only if* clocks are trusted (NTP verified). - If application-level write logs exist (change data capture, CDC), use them to deterministically replay or merge.

4. Repair & merge data - Ensure clocks are synchronized (NTP). Do NOT rely on timestamps if skew unknown; use CDC/commitlog replay instead. - Apply nodetool repair across the whole cluster once partition routes restored. Prefer full repairs (-full or use incremental carefully) ensuring all replicas converge. - For rows where automatic LWW is unsafe, run custom reconciliation: export SSTables (sstable2json), run a deterministic merge using application rules (e.g., merge by vector clock or authoritative event time), then load back via sstableloader.

5. Handle tombstones & gc_grace_seconds - If gc_grace_seconds has passed on either side, you may have permanent loss; restore from snapshots/backups for lost partitions. - Where tombstones risk resurrection, prefer applying deletes with higher timestamps or replay delete events from CDC.

6. Schema reconciliation - Reconcile schema manually: pick canonical schema, apply DDL uniformly, run nodetool describering and repair metadata.

7. Validation & cutover - Run nodetool verify, compaction, and consistency-check reads (read at QUORUM) to assert convergence. - Gradually re-enable writes; monitor error rates, read divergences, and application-level invariants.

8. Postmortem & hardening - Restore CDC/audit logs if not enabled; add monitoring for NTP, partition detection, and coordinator distribution. - Consider stronger consistency levels for critical writes (LOCAL_QUORUM or QUORUM) and enable hinted handoff/allow_remote_downgrades policy adjustments. - Document runbooks for split-brain: prefer pausing writes and using backups + deterministic merging over blind LWW.

**Why this approach** - Prioritizes safety: snapshots + backups protect against irreversible gc expiry. - Uses repairs and sstableloader for deterministic reconciliation when timestamps unreliable. - Balances downtime by allowing controlled reads/writes and staged cutover.

**Tools/commands to use** - nodetool snapshot/repair/verify/describecluster - sstabledump / sstable2json and sstableloader - CDC or commitlog export and application-level merge scripts - NTP check and restart

This plan minimizes data loss by protecting raw data first, preferring deterministic reconciliation when timestamps are unreliable, and using Cassandra’s anti-entropy tooling for final convergence.

EasyTechnical

39 practiced

Explain when you would use a search engine (e.g., Elasticsearch/OpenSearch) instead of querying the primary database for full-text search. Describe synchronization challenges and two strategies to keep search indices consistent with the primary data store.

EasyTechnical

45 practiced

List and explain five key metrics you would monitor to assess the health and performance of a production relational database. For each metric, describe one alert condition you might set and why.

MediumBehavioral

33 practiced

Behavioral: Tell me about a time you had to choose a new database technology for a project. Describe how you gathered requirements, compared options, involved stakeholders, and what trade-offs you documented. What was the final decision and what did you learn?

Sample Answer

**Situation & Task**At my last job we rebuilt an order-management service for a multi-tenant SaaS product. The existing MySQL setup struggled with schema changes and horizontal scaling. I was responsible for choosing a replacement DB that met performance, schema flexibility, and operational constraints.

**How I gathered requirements**- Met with PM to capture SLAs, expected QPS, data retention, and multi-tenancy needs.- Interviewed frontend and analytics teams to understand read/write patterns and query shapes.- Consulted SRE/security on backup, IAM, encryption, and cost/ops limits.- Documented non-functional requirements: transactional guarantees, indexing, migrations, latency <100ms p95.

**Options compared**- PostgreSQL (managed RDS/Aurora): strong ACID, rich SQL, migrations easy, vertical scaling + read replicas.- MongoDB: flexible schema, good for evolving documents and high write throughput, weaker multi-row transactions.- DynamoDB: fully managed, virtually unlimited scale, pay-per-use, eventual consistency by default.

I created a comparison matrix scoring: transaction support, query complexity, scaling model, operational overhead, cost, migration effort.

**Stakeholder involvement**Presented findings and trade-offs in a short deck to PM, SRE, and senior devs; ran a 2-week spike implementing critical flows in Postgres and DynamoDB to validate latency and effort.

**Trade-offs documented**- PostgreSQL: better for strong consistency and complex joins; higher ops for horizontal scaling.- DynamoDB: great scale and cost predictability, but required rethinking data model and added complexity for transactional workflows.- Migration risk and developer familiarity vs long-term ops costs.

**Decision & Result**Chose Amazon Aurora (Postgres-compatible) with partitioning and read replicas. We kept strong ACID where necessary, added a caching layer for high-read hot keys, and used a separate DynamoDB table for an append-only audit stream where scale mattered.

Outcome: 3x reduction in latency variance, easier schema migrations, and acceptable operational cost. Zero data loss during cutover.

**What I learned**Balance short-term developer productivity and long-term operational costs; validate assumptions with spikes; involve SRE early for scaling patterns; document trade-offs so future teams understand why choices were made.

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.