Database Fundamentals and Storage Engines Questions
Core principles and components of data storage and persistence systems. This includes storage engine architectures and how they affect query processing and performance; transactions and isolation including atomicity, consistency, isolation, and durability; concurrency control and isolation levels; indexing strategies and how indexes affect read and write amplification; physical versus logical storage and object, block, and file storage characteristics; caching layers and cache invalidation patterns; replication basics and how replication affects durability and read performance; backup and recovery techniques including snapshots and point in time recovery; trade offs captured by consistency, availability, and partition tolerance reasoning; compression, cost versus performance trade offs, data retention, archival, and compliance concerns. Candidates should be able to reason about durability, persistence guarantees, operational recovery, and storage choices that affect latency, throughput, and cost.
EasyTechnical
43 practiced
Explain the ACID properties (atomicity, consistency, isolation, durability) and the CAP theorem (consistency, availability, partition tolerance). For an SRE, map ACID guarantees to operational practices such as WAL configuration, fsync behavior, replication mode, and backups. Provide concrete examples where you would intentionally favor availability over consistency and vice versa, and explain the operational trade-offs and risks involved.
Sample Answer
Atomicity, Consistency, Isolation, Durability (ACID)- Atomicity: a transaction is all-or-nothing. Operationally: use WAL (write-ahead log) so a partially applied transaction can be rolled back on crash.- Consistency: DB moves between valid states (schema, constraints). Enforce via constraints, synchronous replication modes, and transactional checks.- Isolation: concurrent transactions don’t interfere. Controlled by isolation levels (SERIALIZABLE, REPEATABLE READ); implemented by locks or MVCC.- Durability: once committed, data persists despite crashes. Achieved by fsync’ing WAL to stable storage and having reliable backups and replication.CAP theorem (Consistency, Availability, Partition tolerance)- In presence of network partitions, you can choose C or A (can't guarantee both). Most distributed systems pick Partition tolerance (P) and trade between C and A.Mapping ACID to SRE practices- WAL config: enable WAL and tune checkpointing frequency to balance write latency vs recovery time (atomicity + durability).- fsync behavior: fsync-on-commit ensures durability; disabling or batching fsync reduces latency but risks data loss.- Replication mode: synchronous replication provides strong consistency (C) at the cost of higher write latency/availability; asynchronous replication favors availability (A) but risks data loss on primary failure.- Backups: regular snapshots and point-in-time recovery preserve durability and consistency across failures.Concrete examples and trade-offs- Favor Availability over Consistency: a geo-distributed cache or feature-flag service where stale reads are acceptable. Use asynchronous replication, eventual consistency, and quorum reads with lower write quorum. Trade-off: clients may read stale data; risk: user-visible inconsistency and harder debugging.- Favor Consistency over Availability: financial ledger or order payment system. Use synchronous replication, fsync-on-commit, and strict isolation (SERIALIZABLE). Trade-off: higher latency and potential unavailability during partitions; risk: slower user experience and reduced throughput.Operational recommendations- Define SLOs and choose C/A trade-offs per service criticality.- Use monitoring/alerts for replication lag, fsync latency, and WAL growth.- Test failure scenarios (partition, primary crash) and rehearse recovery/playbooks.- Combine strategies: critical writes use strong consistency paths; noncritical operations use eventual consistency to preserve availability.
HardTechnical
45 practiced
Discuss the trade-offs between synchronous and asynchronous replication in terms of the CAP theorem, write latency, and failure modes. For a partition-tolerant distributed system, how would you design replication to provide strong consistency where needed while keeping most reads low-latency? Provide concrete quorum-based architectures and the operational implications.
Sample Answer
Start with CAP and the basic trade-off:- In the presence of network partitions (P), you must choose between Consistency (C) and Availability (A). Synchronous replication (blocking until replicas acknowledge) biases toward C: on partition you either block writes (preserve C) or risk A. Asynchronous replication biases toward A: writes succeed locally and replicas catch up later, preserving availability but risking inconsistency/loss.Write latency and failure-mode summary:- Synchronous: write latency ≈ local commit + network RTTs × required acks. Tail latency grows with slow/failed replicas. Failure modes: blocked writes if quorum cannot be reached; higher operational complexity (timeouts, retries); safer durability/less divergence.- Asynchronous: write latency ≈ local commit only (low). Failure modes: replication lag, lost acknowledged writes if primary crashes before durable remote commit, stale reads, complex reconciliation and conflict resolution.Design for a partition-tolerant system that must provide strong consistency for some operations while keeping most reads low-latencyPrinciples:1. Use a consensus/leader-based mechanism for strong consistency (RAFT/Paxos) for critical writes. This provides linearizability for operations routed through the leader.2. Adopt quorum rules (N replicas, choose W write acks, R read quorum) with W + R > N to guarantee read-after-write visibility.3. Hybrid replication: synchronous (or majority) commit for critical writes; asynchronous replication or relaxed read routing for non-critical reads.Concrete quorum architectures- Strong-consistency primary path (CP for critical ops): - N = 3 or 5, use leader + majority synchronous commit. Example: N=3, W=2 (majority), R=2. Writes block until 2 acks (local + one follower). Reads routed to leader or use R=2 to guarantee latest. This bounds write latency to one RTT and preserves safety during partitions (may reduce availability if majority lost). - For very strong durability: W = N (all replicas) — highest latency, safest against leader crash but lowest availability.- Hybrid low-latency reads: - N = 5, choose W = 3, R = 1 and enforce that “fast reads” go to leader for strongly consistent reads, while replicas serve stale reads for high throughput. Because W + R = 4 > N? For N=5, W=3,R=1 => 4<=5 so not enough. Correct condition: W + R > N. So with N=5, W=3,R=3 gives strong guarantees but R=3 is slow. - Practical hybrid: Leader-centric: make leader the single source of truth. Critical reads and writes go to leader (strong consistency, low read latency if read from leader). Non-critical reads can go to nearest follower (cached/stale). To ensure read-your-writes for clients, use session affinity or a client token that forces leader reads until session stable.Alternative architectures:- Chain replication: strong linearizability for writes and low-latency reads at tail; useful when writes are common and you can tolerate read path at tail.- Consensus logs (RAFT) for critical data, asynchronous changefeeds for analytics and low-latency reads.Operational implications (SRE concerns)- SLOs: define separate SLOs for critical write latency, read freshness, and availability. Track percentiles (p50/p95/p99).- Monitoring: replication lag, commit latency, number of pending acks, leader election frequency, split-brain indicators.- Capacity and tail latency: synchronous acks increase load; need autoscaling and careful placement to reduce RTTs.- Failure handling: implement timeouts, retry/backoff, and deterministic conflict resolution for async paths; automated failover for leader but with lease-based leadership to avoid split-brain.- Backups and reconciliation: async replicas need repair jobs, anti-entropy, and tombstone handling for deletes.- Testing: chaos experiments for partitions, replica failure, and leader loss to validate SLOs and recovery procedures.- Operational knobs: tune W/R per deployment (e.g., strict CP clusters for payments, AP-style for caches/metrics), allow runtime feature flags to switch replication mode per keyspace.Short example recommendation- For metadata and payments: RAFT with N=3, majority synchronous commit (W=2,R=1), route reads to leader for consistency, use client session tokens.- For user-visible feed/cache: asynchronous replication with background reconciliation, read-from-nearest follower for low latency, and best-effort consistency; provide eventual-consistency SLOs.This mixed approach gives strong consistency where required (by using consensus/majority sync and leader reads) while keeping most reads low-latency via follower reads, caching, and session affinity—at the cost of extra operational complexity: stricter monitoring, tuned timeouts, and robust failover procedures.
HardTechnical
41 practiced
Production Postgres write latencies spike and fsync appears to be the bottleneck. Explain the possible root causes (fsync frequency, small writes, group commit misconfiguration, disk controllers, write barriers, virtualization), and propose a prioritized set of mitigations ranging from DB configuration changes (synchronous_commit, wal_buffers), OS tuning (I/O scheduler, noatime), to hardware fixes (NVMe, battery-backed write cache). Include how you'd measure the impact of each change safely.
Sample Answer
Situation: Production Postgres shows intermittent write-latency spikes and fsync is the hotspot. Root causes to consider:- fsync frequency: each commit forces WAL flush; high sync rate -> many fsyncs.- Small writes: many tiny WAL writes increase syscall overhead.- Group commit misconfiguration: Postgres not batching concurrent commits.- Disk controller/cache: write reordering, disabled battery-backed cache, or unhealthy firmware.- Write barriers/fsync semantics: kernel/driver honoring writes slowly.- Virtualization/storage abstraction: hypervisor or networked storage adds latency or drops write-cache guarantees.Prioritized mitigations (safe, measurable progression)1) Quick DB config (low risk, reversible)- synchronous_commit = off/remote_write/remote_apply for non-DB-critical transactions to reduce fsync frequency. Measure: baseline p99 commit latency, use short canary DB or route small percentage of traffic, compare p99/p95 and error rates.- wal_buffers increase (reduce flush frequency for many small transactions). Measure wal write rate and fsyncs via pg_stat_bgwriter + counters before/after.- commit_delay / commit_siblings tuning to allow group commit to aggregate. Measure group-commit hit rate from pg_stat_bgwriter (check "checkpoints_timed" and "buffers_checkpoint").2) OS tuning (medium risk, test first)- Mount options: noatime, nodiratime to reduce metadata writes.- I/O scheduler: switch to noop or mq-deadline for NVMe/SSD; set elevator accordingly. Measure with fio synthetic workload and iostat/blkstat latencies.- sysctl: vm.dirty_ratio / dirty_background_ratio and fsync related knobs (fsync behavior varies by fs); tune to reduce synchronous write pressure. Measure dirty pages and writeback activity (vmstat).3) Postgres runtime/architectural fixes- Batch writes at application layer, use COPY or bulk inserts, reduce frequent single-row commits.- Use prepared transactions or async commits where acceptable.- Move WAL to faster device (separate disk, use RAID1+BBU, NVMe). Measure by isolating WAL device with fio and replicating workload.4) Hardware fixes (higher cost, high impact)- Enable battery-backed/flash-backed write cache on controller or use NVMe drives with power-loss protection. Replace HDDs with SSD/NVMe.- Use dedicated storage, avoid oversubscribed virtualization storage. For cloud, choose provisioned IOPS volumes.Measure: run controlled pgbench workloads and production-like trace-replay; capture p50/p95/p99, IOPS, latency from iostat, blktrace, and Postgres stats.Measurement and rollout strategy:- Baseline: capture p50/p95/p99 commit latency, fsync counts (pg_stat_bgwriter, pg_stat_database), iostat, blktrace, vmstat, and application error/throughput.- Canary: apply change to a single replica or small percentage of traffic; monitor for regressions over multiple hours and peak windows.- Synthetic verification: use pgbench with same transaction mix; run before/after to quantify improvement.- Correlate metrics: correlate fsync spikes with kernel metrics (latencies, queue depth) and controller logs.- Rollback plan: automated revert for config or orchestration-based rollback for kernel/hardware changes.Trade-offs:- Lowering durability (synchronous_commit off) reduces latency but increases risk of data loss on crash—only for non-critical writes.- OS/device-level changes may mask underlying app inefficiencies; prefer fixing high-frequency small commits first.This approach isolates cause, uses safe incremental changes, and quantifies impact before wide rollout.
MediumSystem Design
45 practiced
Outline an automated leader failover system for a replicated SQL cluster. Requirements: detect primary failure fast, elect or promote a replica safely, ensure fencing to avoid split-brain, and automate DNS / load balancer updates. Describe safety checks, preconditions for promotion, and how to handle network partitions and rejoining of the old primary.
Sample Answer
Requirements & assumptions:- Async or semi-sync replication to 2+ replicas; quorum/consensus service available (etcd/consul/Zookeeper) for coordination and fencing.- Health checks, monitoring, and automated runbook executor (e.g., Ansible, Salt, or operator).High-level flow:1. Failure detection- Fast heartbeat + replication lag + query responsiveness checks (e.g., probe on port 5432 and simple SELECT 1).- Combine multiple signals with short time-window voting to avoid flapping.- Push severity to coordinator only after N failed probes or majority of monitors agree.2. Coordination & fencing- Use distributed lock in quorum service to elect a failover controller for this event.- Place a “primary-epoch” token in the quorum with timestamp and node id; only token holder can accept writes.- On failover, write a fencing key that old primary cannot clear without quorum.3. Promotion preconditions (must all pass)- Replica is up-to-date: replication_lsn >= primary_lsn at failure time or within acceptable lag threshold.- Replica is healthy: CPU/memory, disk space, no pending long-running transactions.- No conflicting writes detected (confirm WAL continuity).- Able to acquire quorum lease and update fencing token.4. Promotion steps- Controller acquires lock → writes new primary-epoch and fence value.- Ensure replica is read-only -> stop replica replication receiver, apply any pending WAL, run crash-safe checkpoint.- Promote replica to primary (DB-specific command, e.g., `pg_ctl promote`), enable write acceptance.- Update quorum with new primary id and epoch.5. DNS / LB automation- Update load balancer via API (or update DNS with low TTL) after promotion and after health probe of new primary passes.- Use weighted draining: remove old primary from write pool, add new primary, then reconfigure read replicas.6. Handling network partitions & old-primary rejoin- Old primary on rejoin must detect epoch mismatch: it sees newer epoch in quorum so must refuse to accept writes and demote to replica automatically.- Reconfigure it as replica: stop accepting client traffic, reset WAL position, start replication from current primary.- If old-primary has writes (split-brain risk), fencing token prevents it from continuing; logs require manual reconciliation if writes occurred.Safety checks & mitigations- Require quorum lease for any promotion to prevent split-brain.- Use persistent fencing (STONITH-equivalent) where possible: power-cycle or hypervisor isolation if node is unresponsive yet still writable.- Audit logs for every promotion, and alert on any auto-failover to require postmortem.- Rate-limit automated failovers and implement cooldown windows to avoid thrashing.Failure modes & tradeoffs- Faster detection risks false positives; mitigate via multi-signal voting and cooldown.- Strict lag precondition may delay failover under heavy write load—tunable thresholds per SLO.This design ensures fast detection, safe promotion via quorum-based fencing, automated LB/DNS updates, and deterministic rejoin behavior that avoids split-brain.
HardSystem Design
37 practiced
Design a multi-region replication strategy for a global application that requires RTO < 60 seconds and RPO <= 5 seconds for critical data but has strict cost constraints. Describe replication topology (e.g., primary-region writes, follower reads, geo-primary, quorum), network and storage considerations, data partitioning strategy, and how to limit cross-region latency impact while meeting RPO/RTO targets.
Sample Answer
Requirements & constraints:- RTO < 60s, RPO ≤ 5s for critical data, global coverage, strict cost limits → hybrid design (selective synchronous-ish replication for critical data, asynchronous for bulk/low-tier), automation and observability.Replication topology:- Multi-primary avoided (complex/expensive). Use single writable primary-region per partition (geo-primary per shard) with regional followers for reads.- For critical shards: semi-synchronous replication to 2 nearby follower regions (primary ➜ replicas A/B). Acks required from at least one replica within configured synchronous window (fast path) to meet RPO ≤5s; fallback async if no ack to avoid blocking writes for > timeout.- For non-critical data: async replication to reduce cost.Network & storage considerations:- Use dedicated inter-region links or cloud provider backbone to reduce latency and egress unpredictability. Compress and batch replication logs; use change-data-capture (CDC) with binary log shipping or log-structured storage (e.g., WAL shipping to object store + apply).- Store critical replicated WAL on SSD-backed volumes with write-through caching disabled to ensure durability.- Apply backpressure controls: limit outstanding replication windows, tune TCP keepalive, congestion control.Data partitioning:- Partition by geography-aware keys (user-region id, account home region) so most writes/write-reads stay local; critical global objects can be sharded separately.- Hot partitions identified and optionally pinned to capacity regions; use consistent hashing with virtual nodes to rebalance.Limiting cross-region latency impact:- Local reads served from followers (read replicas) with read-after-write consistency for clients routed to primary-region; for strong read-after-write across regions, use read-your-writes token attached to client routed to primary or fast cache invalidation.- For write path, keep synchronous window small (e.g., 50–200ms) and require only one replica ack for critical durability; this minimizes median latency while still bounding RPO.- Use write coalescing, batching, and delta replication to reduce bandwidth and apply latency.Failure & failover:- Automated leader election orchestrated by consensus (e.g., etcd/RAFT per shard) but keep election within small set of regions to reduce quorum latency. Cold standby in other regions.- Failover procedure: detect primary loss by SLO-based monitors, promote nearest replica with fresh WAL within RPO; automated DNS + health-check based routing updates with TTLs tuned for <60s cutover.- Rollback and split-brain prevention via fencing tokens and epoch-based leases.Cost trade-offs:- Only critical shards use semi-sync and extra replicas; non-critical data on cheaper async replication and lower IOPS storage. Use regional object-store for long-term WAL retention (cheaper than hot block storage).Observability & testing:- Instrument replication lag, commit latency, TCP metrics, and WAL queue depth. Alert on lag > 2s for critical data.- Chaos testing for region loss and regular DR drills verifying RTO <60s and RPO ≤5s.Summary:Selective semi-synchronous, geo-primary per shard, geography-aware partitioning, small sync windows and single-replica ack for critical data balance RPO/RTO targets with cost constraints; couple with robust automation, monitoring, and regular DR tests.
Unlock Full Question Bank
Get access to hundreds of Database Fundamentals and Storage Engines interview questions and detailed answers.
