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

42 practiced

Describe how to design partition keys for time-series data where queries are mostly: "get metrics for device X over last hour" and occasional cross-device aggregations by region. Propose partition key and clustering strategy to optimize for both access patterns and explain trade-offs.

Sample Answer

Requirements & constraints:- Primary query: “metrics for device X over last hour” — low-latency, single-device, recent time window.- Secondary: occasional cross-device aggregations by region (less frequent, can be slightly slower).- Data is append-only, retention via TTL, high cardinality of devices.

Partition key and clustering:- Primary table (optimized for device reads): Partition key: (device_id, time_bucket) where time_bucket = YYYYMMDDHH (or epoch hour). Clustering columns: (metric_timestamp DESC, metric_type) This yields rows ordered by time within the partition so reads for “last hour” hit one partition (device_id + current hour) or at most two (current and previous hour) if you use hourly buckets. Use DESC to return latest points quickly.

- Secondary table / index for region aggregations: Denormalized table keyed by (region, time_bucket) with clustering (device_id, metric_timestamp) or pre-aggregated rollups per device-per-minute. Alternatively maintain periodic rollups (per-minute, per-5min) stored by region to speed aggregation.

Why this works:- Device queries touch 1–2 partitions, avoiding scatter and minimizing latency.- Time bucketing bounds partition size to avoid huge partitions and enables TTL per bucket.- Clustering by timestamp supports efficient range scans for last N minutes.

Trade-offs and mitigations:- Hot partitions: very active devices may overload a single (device_id, hour) partition. Mitigate by smaller buckets (minute-level) or include hashed suffix in partition key (device_id, time_bucket, hash_shard) to split hot write load; read then fan-in across shards for single-device reads.- Cross-device queries: scanning region partitions is heavier. Denormalization or materialized rollups trades storage for query speed. If strong consistency or up-to-the-second accuracy isn’t required, use eventual rollups.- Storage vs complexity: more tables/rollups increase write amplification and complexity; use change-data pipelines to update aggregates asynchronously.- Backfills and out-of-order writes: allow for upserts by timestamp and use compaction/merge strategies; choose a DB that handles wide rows efficiently (Cassandra, Scylla, Bigtable).

Operational tips:- Choose bucket size based on metrics rate and partition size limits (target partitions <100MB).- Use TTLs per time_bucket to auto-expire old data.- Monitor partition sizes and read/write latencies; implement sharding only when needed.

Summary recommendation: use (device_id, hourly_bucket) partitioning with timestamp-desc clustering for fast recent-device reads, plus denormalized region-level rollups for cross-device aggregations; shard hot devices if needed.

MediumTechnical

64 practiced

Your team must decide between managing a self-hosted database vs using a managed cloud DB service. List operational responsibilities each option imposes, and describe how you would quantify 'operational burden' and compute a cost comparison beyond pure dollar cost (include time-to-recovery, maintenance windows, and staffing).

Sample Answer

Situation: Our team must choose between self-hosting a production relational DB vs using a managed cloud DB service. I'll break down responsibilities, propose measurable metrics for "operational burden," and show how to compute a broader cost comparison.

Operational responsibilities

- Self-hosted: - Provisioning hardware/VMs, networking, storage sizing and IOPS planning - Installation, patching, OS and DB upgrades - Backups, restore testing, snapshot retention policy - Replication, HA setup (failover automation), cluster orchestration - Capacity planning, scaling (vertical/horizontal) - Monitoring, alerting, runbooks, incident response - Security hardening (patches, network ACLs, encryption key management), compliance audits - Disaster recovery (DR) site management and drills - Billing, asset lifecycle, and after-hours on-call rotations

- Managed cloud DB: - Instances, backups, basic HA and patching delegated to provider (but still verify) - Configuration, performance tuning, schema migrations remain our responsibility - IAM, network config (VPC, peering), encryption keys (if customer-managed) - Monitoring, alerting, runbooks for application-level issues and provider SLA gaps - Testing restores and failovers; less infrastructure work but still validation - Cost optimization (right-sizing), multi-region configuration for DR

Quantifying "operational burden"Define a Burden Score as weighted sum of time, risk, and people factors:- MTTR (mean time to recovery) in hours — higher is worse- Maintenance Window Impact (hours/week lost to maintenance) — productivity cost- FTE-hours/week required for DB ops (provisioning, tuning, on-call)- Risk multiplier for human work (0.5 managed to 1.0 self-hosted)

Compute: BurdenScore = w1*Normalized(MTTR) + w2*Normalized(MaintWindow) + w3*Normalized(FTE-hours)*RiskMultiplierChoose weights by business priorities (e.g., availability weight higher for customer-facing).

Cost comparison beyond dollarsTotal Cost = Direct $cost (hosting/license) + OperationalCostOperationalCost = FTE-hours/week * FullyLoadedHourlyRate * 52 + PenaltyCostForMTTRPenaltyCostForMTTR = ExpectedDowntimeHoursPerYear * RevenueLossPerHour + CustomerChurnFactor

Example (illustrative):- Self-hosted: Direct $50k/yr; FTE 1.5 ops (including shared SRE) → 1.5*150k/yr ≈ $225k; MTTR 4 hrs -> downtime cost 4*X; maintenance windows 4 hrs/month.- Managed: Direct $80k/yr; FTE 0.5 (app team + periodic ops) → 0.5*150k = $75k; MTTR 1 hr (provider SLA + our runbook); maintenance 1 hr/month.Plug into Total Cost and BurdenScore; typically managed reduces OperationalCost and BurdenScore, even at higher direct $.

Decision factors / trade-offs- If compliance, custom extensions, or extreme tuning needed → self-hosted may be justified.- If team bandwidth is limited, rapid time-to-market required, or availability SLAs moderate → managed DB usually wins.- Run a 6–8 week proof-of-concept: measure real MTTR, FTE-hours, and performance; use those measured inputs to finalize the TCO and BurdenScore.

This method gives a repeatable, quantitative way to compare options incorporating time-to-recovery, maintenance impact, and staffing beyond pure dollar cost.

HardTechnical

34 practiced

You are designing a schema migration process for a high-traffic production database. Describe safe techniques to add/remove columns and change types with zero-downtime, including backward- and forward-compatibility tactics and how to coordinate application rollouts.

Sample Answer

Situation: We need zero-downtime schema changes on a high-traffic DB while keeping old and new app versions working.

Approach (patterns & steps)1) Add column (safe):- Step A — deploy DB change: ALTER TABLE ... ADD COLUMN new_col TYPE NULLABLE (no default) so it’s cheap and non-blocking.- Step B — deploy app code that writes new_col but still reads fallback to old column if absent (dual-read/write via feature flag).- Step C — backfill existing rows asynchronously in small chunks with idempotent jobs and rate-limit to avoid WAL/replication spikes.- Step D — after all clients write/read new_col and backfill done, remove old column in a later migration.

2) Change column type:- Pattern: “expand, migrate, swap” - Add new_col with target type. - Shadow-write both old_col and new_col in app. - Backfill new_col from old_col in chunks with safe casts; validate checksums. - Switch reads to new_col behind a feature flag; run canary traffic. - Once stable, remove old_col.

3) Remove column:- Ensure no code refers to it (search, compile-time checks), deploy code that ignores it, then DROP COLUMN in separate safe migration.

Tactics for zero-downtime & compatibility- Backward-compatible schema: add nullable columns, avoid NOT NULL or defaults that rewrite table in one step.- Forward-compatibility in code: tolerate missing columns/fields; use feature flags and fallbacks.- Dual writes and dual reads during transition; make writes idempotent and consistent.- Chunked/online backfills with pause/resume, low-priority transactions, and monitoring (replica lag, WAL, CPU).- Use DB-native online tools (pt-online-schema-change, gh-ost) for large InnoDB MySQL tables.- Use views or computed columns when feasible to avoid data copy.

Coordination & rollout- Two-phase deploy: migrate DB first (safe add), then app rollouts (canary → partial → full) that enable new behavior via feature flags.- Maintain migration scripts as idempotent and reversible; version-control them.- Observe metrics (latency, error rate, replication lag) and have rollback/playbooks ready.- Communicate timing to teams; schedule during low-traffic windows only for risky ops.- Automate validation: checksum tools, smoke tests, and integration tests that run against staged DB snapshots.

Example SQL snippets

sql

-- safe add
ALTER TABLE users ADD COLUMN phone_normalized VARCHAR(32) NULL;
-- backfill in chunks
UPDATE users SET phone_normalized = normalize(phone) WHERE phone_normalized IS NULL LIMIT 10000;

Key trade-offs- Extra storage + complexity for dual columns vs. availability risk of in-place conversion.- Slower rollout but safe, observable approach is best for high-traffic production systems.

This method ensures zero-downtime, maintains backward/forward compatibility, and gives controlled, observable rollout and rollback paths.

HardSystem Design

43 practiced

A business requires a search for analytics on semi-structured JSON event data. Propose a storage architecture that supports both fast analytical queries (aggregations over millions of events) and near-real-time search. Explain choices for primary store, indexing pipeline, and how to handle eventual consistency between them.

Sample Answer

Requirements & constraints clarification:- Functional: support ad-hoc aggregations over millions of JSON events (analytics) and near‑real‑time full-text / field search.- Non‑functional: low query latency for analytics (seconds for common rollups), sub‑second to few‑seconds search freshness, cost/operational tradeoffs.

High-level architecture:Event producers → Ingest (API/Gateway) → Kafka (stream buffer) → Dual pipeline: 1) Analytics path → Stream processor (Flink/KStream) → Parquet files on object store (S3) partitioned + optional OLAP store (ClickHouse / BigQuery / Snowflake) for fast aggregations and materialized views. 2) Search path → Stream processor (Kafka Connect / Flink) → Elasticsearch / OpenSearch for near‑real‑time search and lightweight aggregations.

Primary store choice:- Object store + columnar files (Parquet) as the authoritative primary: durable, cheap, supports schema‑on‑read and batch/point-in-time analytics.- Optionally provision ClickHouse or a cloud data warehouse for low-latency aggregations and BI dashboards (pre-aggregations, materialized views).

Indexing pipeline:- Use Kafka as the source of truth for change stream. Produce canonical event JSONs with monotonic offsets/IDs and tombstones for deletes.- Stream processors transform/normalize events (flatten JSON, extract typed fields, enrich) and write: - Batched Parquet via Hudi/Delta/Iceberg (supports upserts, ACID-ish semantics, efficient compaction). - Near‑real‑time sink to Elasticsearch via Kafka Connect with idempotent writes (use event ID as ES doc _id).- Keep indexing tasks small and parallelized by partition keys (user_id, day) for throughput.

Handling eventual consistency:- Authoritative source: object-store table (Hudi/Delta/Iceberg) — analytics queries should read from committed snapshot; search is best‑effort near‑real‑time.- Use ordered event IDs and offsets to make ES writes idempotent and in-order per key; include event_version or last_updated timestamp in ES doc.- Use write‑through semantics: only acknowledge ingestion once event is in Kafka; workers independently deliver to both sinks.- Monitor lag (Kafka consumer lag, ES refresh latency, compaction backlog). Expose health metrics and notify staleness to clients.- Reconciliation: periodic reconcile jobs that diff ES vs. warehouse for sensitive queries; reindex by snapshot or reprocess a window for corrective updates.- For strict analytics correctness, query the primary OLAP store or run hybrid queries: use ES for fast filtering, then exact aggregations against warehouse for final numbers.

Scalability & trade-offs:- S3 + columnar scales and is cost efficient for long‑term retention; ClickHouse/Warehouse improves query latency at higher cost.- ES provides powerful search and low latency but isn’t ideal as authoritative analytics store (costly for wide table scans and joins).- Hudi/Delta/Iceberg gives upsert support enabling deterministic reconciliation and incremental compaction.

Example tech stack:- Kafka, Flink/Kafka Streams, Delta Lake or Apache Hudi on S3, ClickHouse/BigQuery for OLAP, Elasticsearch/OpenSearch for search, Kafka Connect for sinks, Grafana/Prometheus for monitoring, Airflow for reconciliation jobs.

Why this design:- Separates concerns: durable authoritative storage optimized for analytics; search optimized for low‑latency queries.- Stream-first design ensures near‑real‑time visibility and consistent pipelines.- Built-in reconciliation & idempotency mitigate eventual consistency while keeping operational simplicity.

MediumTechnical

36 practiced

A service requires full-text search with typo-tolerance, faceted navigation, and multi-language support. Compare using an off-the-shelf search engine vs building these features in a relational DB using extensions. Discuss trade-offs in developer productivity, relevance tuning, and operational management.

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.