Distributed Database Architecture Questions
Covers principles and patterns for designing databases that span nodes and regions. Subjects include replication strategies synchronous and asynchronous, partitioning and sharding approaches, leader follower and multi leader architectures, consensus mechanisms and their trade offs, consistency models including eventual consistency and strong consistency, cross region failover and disaster recovery, indexing and query routing in partitioned systems, transactional semantics and distributed transactions, and operational concerns such as backup, schema evolution, and performance tuning for distributed data stores.
MediumTechnical
72 practiced
You have 1 TB of event data in Parquet files on S3 partitioned by dt=YYYY-MM-DD. Analysts frequently query last 7 days and sometimes filter by user_id. Explain how to optimize layout and query performance, covering partitioning strategy, file sizes, compaction, metadata/catalog choices, and caching layers.
Sample Answer
Situation summary: Analysts mostly query the last 7 days and sometimes filter by user_id. Goal: minimize S3 listing and scan cost, enable partition pruning and predicate pushdown, and reduce small-file overhead while keeping query latency low.Recommended design:Partitioning- Keep a top-level partition by dt=YYYY-MM-DD (daily) because queries target recent days. Daily partitions are small and align with ingestion cadence.- Avoid deep partitioning (no dt/user_id) — that would create too many tiny partitions.- For selective user_id filters, use clustering/bucketing or a table format that supports data clustering (see below) rather than extra folder partitions.File size and layout- Aim for 64–256 MB parquet files (commonly 128 MB). This balances parallelism and per-file overhead.- Within each daily partition, write files that are sorted/clustered by user_id (e.g., write outputs from Spark with repartitionByRange or bucketBy on user_id) to increase likelihood that user_id filters read fewer row groups.Compaction strategy- Run regular compaction jobs after ingestion (e.g., hourly micro-batches compacted to daily targets, or a daily compaction for the prior day). Use Spark jobs to merge small files into target sizes and to rewrite with sorting by user_id.- For streaming ingestion, perform periodic background compaction (time-windowed) to avoid many tiny files.Metadata / catalog choices- Use a table format with transactional metadata and fast partition listing: Apache Iceberg or Delta Lake recommended over raw Hive-style layout. Benefits: manifests, partition pruning without expensive S3 listings, atomic commits, easier compaction, and data clustering (Z-order or sort).- If using Glue/Hive metastore, enable partition projection or partition indexing to avoid costly S3 List operations for large numbers of partitions.- Store statistics (min/max) per data-file/row-group to allow predicate pushdown; both Iceberg/Delta and recent Hive/Parquet writers can capture these.Compression & Parquet tuning- Use Snappy compression and columnar encoding. Keep row-group size moderate (e.g., 128 MB) and enable dictionary encoding for high-cardinality columns as appropriate.- Ensure Parquet file contains min/max stats for the user_id column to enable skip-scanning.Caching layers and query engines- Use a query engine that benefits from the above layout: Presto/Trino, Spark SQL, Athena (if using Glue/Athena work with Iceberg), or Redshift Spectrum.- Add a metadata cache: for example, Hive metastore/Glue caches in EMR or Trino to reduce calls.- Use an in-memory cache where appropriate: Alluxio or EMRFS caching if you need sub-second interactive queries for last-7-days snapshots.- Use result caching at the BI layer or the query engine to serve repeated ad-hoc queries.Operational notes and monitoring- Automate compaction and vacuuming; track file counts per partition and average file size.- Monitor query scans (bytes read), S3 LIST latency, and number of opened files.- Provide analysts with views that limit scans (e.g., precomputed last_7_days materialized view or daily aggregated tables).Trade-offs- Iceberg/Delta introduce operational complexity (catalog, housekeeping) but pay off with faster metadata, atomic operations, and efficient reads.- Larger files reduce request overhead but may increase read latency for small, highly selective queries; clustering and row-group stats mitigate this.Example quick action plan1) Enable daily partitioning (if not already).2) Implement Spark compaction job to merge to ~128 MB and sort by user_id for daily partitions.3) Migrate to Iceberg/Delta or enable Glue partition projection and per-file stats.4) Add caching layer or materialized last_7_days view for interactive workloads.This combination reduces S3 listing/scan cost, improves predicate pushdown, and speeds selective user_id queries while keeping ingestion and operational complexity manageable.
MediumSystem Design
67 practiced
Design a cross-region failover strategy for a distributed SQL database. Requirements: RPO <= 1 minute for critical tenants, RTO <= 5 minutes, minimal data loss for noncritical tenants. Cover replication mode, failover orchestration, DNS/load-balancer considerations, and how to validate state after failover.
Sample Answer
Requirements clarification:- Critical tenants: RPO ≤1m, RTO ≤5m- Noncritical: tolerate minimal data loss- Assume distributed SQL (e.g., cloud-managed PostgreSQL/Spanner/CockroachDB) across two+ regions.High-level approach:- Use hybrid replication: synchronous (or semi-sync) replication for critical-tenant partitions; asynchronous for noncritical tenants.- Partition data by tenant (sharding/row-level routing) so replication mode can be per-tenant or per-shard.Replication mode:- Critical shards: semi-synchronous (write commits locally, replicate to remote replica(s) synchronously or with acknowledgement within <1m SLA). Prefer quorum writes across local+remote if latency allows; otherwise local commit + guaranteed WAL shipping with near-real-time streaming and accelerated commit acknowledgement.- Noncritical shards: async streaming replication (low overhead).Failover orchestration:- Automated multi-step runbook in orchestration engine (Kubernetes operator, Terraform + automation, or cloud provider failover APIs).1. Detect: health probes + WAL lag monitors + cross-region heartbeats. Alert if primary unreachable or lag beyond threshold.2. Promote: for critical shards, pre-designated warm standby replicas in secondary region are candidate primaries. Orchestrator pauses writes, verifies WAL caught up (within RPO), issues promotion, re-point downstream connectors.3. For noncritical shards: if async replica lag within acceptable loss, promote best-effort; if not, fallback to manual review.4. Ensure transactional safety: stop distributed transactions, drain connectors, apply any outstanding WAL.DNS / load balancer considerations:- Use global load balancer (Route53 + health checks, Cloud CDN LB) with low TTL (30s) and weighted routing. Maintain region-aware endpoints: app reads prefer local read-replicas; writes go to region primary.- On failover, update LB routing to promote secondary region endpoint. Use health checks + traffic shifting (gradual) to meet RTO while avoiding split-brain.- Use service discovery in app: support multi-endpoint failover and retry/backoff; avoid DNS-only reliance—use LB + client-side fallback.Validation after failover:- Automated post-promotion checks: - Consistency: run checksums on critical tables (row counts, hashes) comparing pre-failover snapshots and post-primary. - WAL offset verification: ensure applied LSN >= cutover LSN for critical tenants. - Smoke tests: transactional write-read tests for critical tenants. - Data pipeline validation: ensure CDC/ETL consumers resume without gaps (compare offsets).- Run integrity queries for sample noncritical tenants; alert on discrepancies for manual reconciliation.Operational practices:- Regular drills (chaos/failover tests) run quarterly.- Backups: continuous backups + point-in-time recovery for noncritical tenants.- Observability: per-shard latency, replication lag, promotion time metrics; enforce runbook SLAs.Trade-offs:- Synchronous replication increases write latency; limit to critical tenants only.- Lower TTL speeds failover but increases DNS churn—combine with LB and client retries.- Maintain warm standbys to meet RTO.This design balances RPO/RTO targets, limits user-visible latency impact, and provides validation and operational controls for safe cross-region failovers.
MediumTechnical
91 practiced
Compare two-phase commit (2PC) and the SAGA pattern for managing distributed transactions across services and sharded datastores. For each technique describe failure modes, performance characteristics, and when you would choose one over the other.
Sample Answer
Two-Phase Commit (2PC)- What it is: A coordinator-driven protocol that atomically commits or aborts a distributed transaction across participants (prepare → commit/abort).- Failure modes: - Coordinator crash after participants prepared → participants block waiting for decision (blocking). - Participant crash during prepare/commit → coordinator may retry; recovery requires logs. - Network partitions can cause long stalls and potential resource locks.- Performance: Synchronous, high latency (multiple round-trips), holds locks/resources during prepare → poor throughput at scale.- When to choose: Small set of tightly-coupled services/datastores where strong atomicity is critical (e.g., metadata updates across shards), transactions are short, and operational cost of coordinator/locking is acceptable.SAGA Pattern- What it is: A long-running choreography or orchestration of local transactions; each step has a compensating transaction to undo if later step fails.- Failure modes: - Partial completion requiring compensating actions — compensations may fail or be slow. - Ordering and concurrency issues across retries; requires idempotency and careful state tracking. - Eventually consistent state; temporary anomalies possible.- Performance: Asynchronous, high availability, much lower latency per step, scales well across many services/shards.- When to choose: Large-scale microservices or sharded datastores where availability and throughput matter more than immediate atomicity (e.g., event-driven ETL pipelines, user-facing workflows). Use when you can design compensating actions and tolerate eventual consistency.Practical considerations for a Data Engineer- Idempotency & deduplication are essential in SAGA (use idempotent APIs, durable saga log).- For 2PC across sharded databases, watch for distributed lock contention; prefer if you need strict consistency for metadata/coordinator state.- Hybrid: Use 2PC for short-living meta/coordination writes and SAGA for business data flows. Instrument retries, observability, and automated recovery in either approach.
HardTechnical
62 practiced
Design an indexing strategy to support complex text search and aggregations across millions of partitions. Compare per-shard inverted indices, a global search index, and federated search approaches. Cover freshness, storage cost, query fan-out, and ranking accuracy trade-offs.
Sample Answer
Requirements clarification:- Support complex full-text search + aggregations across millions of partitions (e.g., tenant/day/region).- Non-functional: low-latency queries, high freshness (near real-time for some use-cases), cost constraints, accurate ranking.Three approaches (summary + trade-offs)1) Per-shard (per-partition) inverted indices- What: build an inverted index inside each partition/shard; queries fan out to relevant shards and merge results.- Freshness: high — updates are localized, can refresh per-partition quickly.- Storage cost: higher overall due to index metadata duplicated across many shards; inefficiencies if many small shards.- Query fan-out & latency: high fan-out when search spans many partitions → increased CPU, network, and merge cost; requires efficient pruning (time/tenant filters, index-level Bloom filters).- Ranking accuracy: strong (local scoring + federated re-ranking), but global ranking requires careful normalization (length normalization, IDF computed globally or approximated).2) Global search index- What: ingest documents into a central distributed inverted index (e.g., ES/Opensearch cluster or global index in Lucene) covering all partitions.- Freshness: depends on ingestion pipeline; easier to provide consistent global IDF and ranking; could be near-real-time with streaming ingest but may lag under heavy volume.- Storage cost: lower duplication; compact global structures.- Query fan-out & latency: low fan-out—query hits index shards designed for balanced search; lower merge overhead.- Ranking accuracy: best for global relevance (accurate IDF, global term stats); easier to implement complex ranking.- Operational risk: single logical index becomes scaling bottleneck; reindexing/resharding at scale is complex.3) Federated search (hybrid)- What: maintain per-partition indices for freshness and a lightweight global index for popularity/IDF and aggregations. Query uses global index to get candidate ranking/term stats then selectively query partitions for final hits/aggregations.- Freshness: partitions deliver freshest content; global index may be slightly delayed but only used for statistics or caching hot partitions.- Storage cost: moderate—global index smaller (summary / top-k), per-partition indices retained.- Query fan-out & latency: reduced via candidate pruning (use global top-k or routing hints) and selective fan-out; still need robust routing logic.- Ranking accuracy: near-global quality by using global statistics for normalization and per-partition final scoring; can employ re-ranking on aggregated candidates.Operational considerations & best practices- Index routing and partition pruning: maintain routing metadata (Bloom filters, term partition maps, last-updated timestamps) to avoid full fan-out.- Global statistics: compute approximate global IDF via streaming sketches (Count-Min, HyperLogLog) to enable consistent scoring without full centralization.- Aggregations: maintain pre-aggregated rollups per partition and global rollups updated asynchronously to serve heavy aggregation queries cheaply.- Freshness SLA tiers: allow different pipelines—real-time per-partition index for search, asynchronous bulk for global index and analytics.- Autoscaling & resource isolation: isolate indexing and query workloads; use hot/warm architecture for index lifecycle.- Monitoring and backpressure: track fan-out, shard QPS, merge latency; implement circuit breakers and degrade to approximate results under overload.Recommendation for data engineer- Use a federated hybrid: per-partition indices for freshness + a compact global index of statistics and hot docs. Implement selective fan-out using routing hints and global sketches for IDF. This balances freshness and ranking accuracy while controlling storage and query fan-out.
EasyTechnical
49 practiced
Explain the CAP theorem and its practical implications for designing distributed database systems. In particular, discuss why partition-tolerance is usually non-negotiable in real deployments and how systems typically make trade-offs between consistency and availability during partitions.
Sample Answer
CAP theorem (Brewer): in a distributed system you can only simultaneously guarantee two of three properties: Consistency (every read sees the latest write), Availability (every request gets a non-error response), and Partition-tolerance (system continues to operate despite network partitions). Under a network partition you must choose between C and A.Why partition-tolerance is non‑negotiable in practice:- Real-world networks and multi-datacenter deployments will experience message loss, latency spikes, or node failures. You cannot prevent partitions at scale, so systems must tolerate them; the only practical decision is how to behave during them.How systems trade off C vs A during partitions:- CP systems (choose Consistency + Partition-tolerance): They sacrifice availability during partitions — some nodes reject requests or return errors to preserve strong consistency. Examples: HBase, MongoDB (configurable), many relational systems with leader-based replication. Good when correctness of reads/writes is critical (financial ledgers, metadata).- AP systems (choose Availability + Partition-tolerance): They remain available during partitions but allow divergent replicas (eventual consistency). Examples: Cassandra, DynamoDB (with eventual consistency modes). Good for high-throughput, low-latency needs where stale reads are tolerable.Practical considerations for data engineering:- Choose based on workload: analytical pipelines and data lakes often accept eventual consistency and use reconciliation jobs; transactional metadata services require stronger consistency.- Mitigations: use quorum writes/reads, vector clocks or CRDTs, idempotent writes, versioning, and compensating transactions.- Operational trade-offs: monitor replication lag, set appropriate consistency levels per API, and document SLAs so consumers know when data might be stale.In short: accept partition-tolerance, decide whether your application demands strict consistency (accept reduced availability under partitions) or prefers continuous availability with eventual convergence — then design replication, retry, and reconciliation accordingly.
Unlock Full Question Bank
Get access to hundreds of Distributed Database Architecture interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.