Database Scalability and High Availability Questions

Architectural approaches and operational practices for scaling and maintaining database availability. Topics include vertical versus horizontal scaling trade offs; replication topologies, leader and follower roles, read replicas and replica lag; read write splitting and connection pooling; sharding and partitioning strategies including range based, hash based, and consistent hashing approaches; handling hot partitions and data skew; federation and multi database federation patterns; cache layers and cache invalidation; rebalancing and resharding strategies; distributed concurrency control and transactional guarantees across shards; multi region deployment strategies, cross region failover and disaster recovery; monitoring, capacity planning, automation for failover and backups, and cost optimization at scale. Candidates should be able to pick scaling approaches based on read and write patterns and explain operational complexity and trade offs introduced by distributed data.

MediumTechnical

38 practiced

Outline a strategy to change a frequently updated column type across a sharded database containing millions of rows. Include online migration techniques, backwards-compatible schema changes, dual-write approaches, verification, and rollback plans to minimize downtime and risk.

Sample Answer

Clarify constraints first: expected downtime SLA, whether app can be deployed in phases, DB type (MySQL/Postgres/Cassandra), shard key, and whether strong consistency is required. Then run a phased, backward-compatible online migration per shard with verification and fast rollback.

Plan (per-shard, rolling):1. Backwards-compatible schema: add a new column with the new type (nullable) rather than altering in-place. Keep the old column until cutover. Add indexes only after data backfill if needed.2. Dual-write strategy: deploy app change (behind a feature flag) to write both columns. Use: - Application-level dual-write for immediate correctness, or - CDC pipeline (Debezium/Kafka) to populate new column asynchronously for lower app risk. Ensure writes are idempotent and record write timestamps/versions for conflict resolution.3. Online backfill: perform per-shard streaming backfill (small batches, rate-limited) converting old->new. Use lightweight workers that: - Lock at row-level or use optimistic updates (WHERE new_col IS NULL) to avoid heavy locking. - Verify transforms are deterministic and idempotent.4. Verification: for each batch and shard compute checksums, row counts, sample comparisons, and end-to-end query equivalence tests. Run a shadow read path where a subset of traffic reads from new column and compare responses.5. Cutover: once all shards backfilled and verification passes, flip read path to prefer new column (feature flag). Monitor error rates, latency, and data divergence metrics for a defined period.6. Rollback plan: - If problems before cutover: disable dual-write feature flag and let CDC or backfill catch up; resume. - If problems after cutover: revert app to read old column (toggle feature flag), continue investigation; since old column preserved, no data loss. - For CDC failure, pause consumers, fix transformer, and resume with resume tokens; workers must be idempotent.7. Operational safeguards: - Run per-shard throttling, staging on an isolated shard, and automated health gates. - Maintain schema migration metadata table (state, last_processed_id, checksum). - Use transactions where possible; for eventual-consistent stores document the window and reconcile with compensating jobs.Trade-offs:- App dual-write increases code complexity but gives immediate correctness.- CDC/backfill reduces app risk but increases eventual consistency window.- Adding a new column uses extra storage temporarily.This approach minimizes downtime, allows safe rollbacks, and provides strong verification across millions of rows and shards.

HardSystem Design

44 practiced

Design a multi-master replication system across three regions allowing writes in any region that must deterministically resolve conflicting writes and converge. Include conflict-resolution policies (last-write-wins, CRDTs, application-level merge), metadata required (vector clocks), and operational considerations for partitions and eventual convergence.

Sample Answer

Requirements & constraints:- Three-region multi-master: allow reads/writes in any region, low write latency local-first, must deterministically resolve conflicts and converge (eventual consistency).- Non-functional: tolerate partitions, bounded storage for metadata, predictable conflict policy, auditability.

High-level architecture:- Each region runs a local storage node + replication agent. Regions exchange asynchronous change logs (append-only) over reliable transport (gRPC/Kafka/replication queues). Clients write locally; writes are applied and assigned local metadata.

Core components:1. Local store: accepts writes, keeps object state + metadata.2. Replication layer: bundles operations into change-sets, sends to other regions, applies incoming sets deterministically.3. Conflict resolver: pluggable policy module (LWW, CRDT, app-merge).4. Metadata store: vector clocks / per-region logical clocks + tombstones for deletes.5. Monitoring & repair: anti-entropy service to detect missed updates and reconcile.

Conflict-resolution policies (deterministic):- Last-Write-Wins (LWW): attach (region-id, wall-clock, logical counter). Use hybrid logical clocks (HLC) to avoid clock skew anomalies. Deterministic but may lose updates.- CRDTs: for data types amenable to CRDTs (G-Counter, OR-Set, LWW-Register with causal context). Converges automatically; best for counters, sets, maps with per-field CRDTs.- Application-level merge: include full causal context (vector clock + change-set). On conflict, call app-provided deterministic merge function that returns single resultant state and deterministic metadata (e.g., sorted merge). Use when semantics are domain-specific.

Metadata required:- Vector clocks sized by region count (three elements) per object to capture causality — small and sufficient for 3 regions.- HLC timestamps for LWW variants.- Operation IDs (UUID + origin region + seq) for idempotence and dedup.- Tombstones with vector clocks and TTL to ensure deletes propagate.- Optional per-field causal metadata for CRDT maps.

Data flow & application:- On write: increment local logical clock, create op with vector-clock delta, persist locally, return success immediately, publish op to other regions.- On receive: check vector clocks: - If incoming causally after local → apply. - If concurrent (neither dominates) → use configured resolver: CRDT merge or call app-merge or HLC compare.- Ensure operations are idempotent via op IDs.

Operational considerations:- Partitions: continue local writes; operations queue. Anti-entropy agents reconcile after partition. Use back-pressure and durable queues to avoid data loss.- Convergence: guarantee by using CRDTs or deterministic merge rules plus reliable at-least-once delivery and idempotent apply.- Clock skew: avoid pure wall-clock LWW; prefer HLC or logical clocks.- Metadata growth: vector clocks fixed-size (3) — ok. Tombstone TTL to reclaim storage. For high-churn objects consider compaction: create a reconciled snapshot with new vector-clock baseline.- Performance: batching and compression of replication, rate-limiting during recovery, read-your-writes via local session caching when required.- Observability & debugging: store provenance (op history) for auditing and replays; provide tools to simulate merges and inspect vector-clock states.- Security & compliance: encrypt replication channels, authenticate regions, and ensure data residency policies.

Trade-offs:- LWW: simple, low metadata, risk of lost updates.- CRDTs: stronger convergence guarantees, more complex design and potentially higher metadata/compute.- App-merge: best semantics but requires deterministic functions and more developer effort.

Conclusion:Choose CRDTs for heavy concurrent workloads where strong convergence without data loss is critical; use HLC+vector clocks with app-merge for complex domain logic; always provide deterministic, auditable merges, anti-entropy, tombstone management, and observability to ensure safe, convergent multi-master replication across three regions.

EasyTechnical

37 practiced

Describe leader-follower (primary-secondary) replication topology. What are the responsibilities of the leader versus followers, how is write and read traffic typically handled, and what considerations should be made for leader failover, promotion, and split-brain prevention?

Sample Answer

Leader-follower (primary-secondary) replication is a common topology where one node (leader/primary) accepts writes and propagates changes to one or more followers (replicas/secondaries) that typically serve reads. It's used to scale reads, provide redundancy, and enable backups.

Leader responsibilities:- Accept and serialize all writes/transactions- Generate and ship a replication stream (WAL/transaction log) to followers- Coordinate durability guarantees (fsync, commit acknowledgements)- Maintain authoritative state and schema changes

Follower responsibilities:- Apply replication stream to stay consistent with leader- Serve read traffic (possibly with read-after-write caveats)- Act as warm standby for failover or analytics/query offloading

Read/write handling:- Writes: routed exclusively to leader to ensure single source of truth and avoid conflicts.- Reads: routed to followers for scale; can also be served by leader for strongly consistent reads. Consider replication lag — followers are eventually consistent.

Failover & promotion considerations:- Detect leader failure via health checks and quorum-based consensus (avoid unilateral decisions).- Prefer automated failover with consensus (e.g., using ZooKeeper/etcd/Consul or a cluster manager) for fast recovery; allow manual promotion for critical systems.- During promotion, ensure follower has applied required WAL up to a safe point (no data loss) or accept bounded data loss based on RPO.

Split-brain prevention:- Use quorum/majority voting to elect a new leader; require fencing tokens/cluster locks to prevent two primaries.- Use external coordination (consensus service) or storage-level fencing (STONITH, volume locks).- Employ floating VIPs or DNS with short TTLs, and update clients/pools atomically to point to new leader.

Operational best practices:- Monitor replication lag, commit latency, and follower apply status.- Define RPO/RTO and choose synchronous vs asynchronous replication accordingly.- Automate failover playbooks, test promotion and rollback regularly, and document expected client behavior (e.g., retries, session stickiness).

EasyTechnical

41 practiced

Define sharding and partitioning in databases. Explain the differences and overlap between the terms, list common partitioning strategies (range, hash, list), and describe how each strategy affects query routing, joins, and operational complexity for a large transactional system.

Sample Answer

Sharding and partitioning both split data to improve scalability, but they’re used at different scopes and with different operational implications. Partitioning is the logical division of a table into segments (partitions) within a single database instance or cluster; sharding is horizontal partitioning across independent database instances (shards), typically with their own compute/storage and often in different machines or regions.

Key differences / overlap:- Overlap: Both are horizontal splits (rows divided by some key). Partitioning can be an implementation detail inside a shard.- Difference: Shards are autonomous servers (operationally independent, require routing), partitions are managed by the DB engine (less operational overhead).

Common strategies and effects:

1) Range partitioning- Definition: rows assigned based on ordered ranges of a key (e.g., date).- Query routing: good for range queries (targeted routing if router knows ranges).- Joins: problematic if join keys span ranges across partitions → may need scatter/gather.- Operational complexity: simpler to reason about; hotspots if data skews to recent ranges; easy to archive/drop whole partitions.

2) Hash partitioning- Definition: deterministic hash(key) mod N.- Query routing: excellent for point lookups (direct target); uniform distribution avoids hotspots.- Joins: ideal when both tables use same hash key — co-location enables local joins; otherwise requires cross-shard joins.- Operational complexity: adding/removing partitions requires rehashing or consistent-hash strategies; more complex rebalancing.

3) List partitioning- Definition: explicit lists of values map to partitions (e.g., country codes).- Query routing: direct when queries include the partition key; uneven distribution possible.- Joins: similar to range — co-locating related tables by same list key helps joins.- Operational complexity: manual mapping maintenance; good when business domains align with partition values.

Operational considerations for large transactional systems:- Routing layer: necessary for sharding (proxy/router or client-side hashing); increases latency/complexity.- Transactions & consistency: distributed transactions across shards are expensive; design to minimize cross-shard transactions (use single-shard tenant or aggregate keys).- Rebalancing: sharding requires careful resharding strategy (consistent hashing, resharding services).- Monitoring/backups: per-shard ops multiply complexity (alerts, backups, failover).Recommendation: design data model to maximize affinity (co-locate related data), choose hash for uniform scale, range/list for natural slices (time, tenant, region) and operational needs like archival.

HardTechnical

60 practiced

Propose a detailed strategy to mitigate hot-keys in a consistent-hashing partitioned cache or database. Include techniques such as key-splitting (logical sharding of a hot key), dynamic re-sharding, request routing to dedicated nodes, client-side batching, and rate limiting. Evaluate operational complexity and performance trade-offs.

Sample Answer

Situation & goal: Hot-keys (very high QPS to a single logical key) break uniform load in a consistent-hash partitioned cache/db. The goal is to limit per-node overload while preserving correctness and latency.

Strategy (multi-layered, progressive):1. Detection- Real-time metrics: per-key QPS, latency, error-rate; sliding windows and heavy-hitter sketches (Count-Min, Top-K).- Thresholds trigger mitigation policies.

2. Key-splitting (logical sharding)- Split hot-key K into N subkeys: K#0..K#N-1 (hashed by client or proxy).- Use deterministic or time-rotated suffix to keep reads/writes routable.- Consistency: maintain an aggregation layer or read-repair to materialize combined value if needed (e.g., counters).

3. Dynamic re-sharding / hotspot-aware placement- Temporarily increase replica factor for affected subkeys and place replicas on less-loaded nodes.- Adjust consistent-hash ring weights or assign virtual nodes for hot subkeys so traffic spreads.- Use control plane to move ownership without full global rehash—map hot subkeys to dedicated buckets.

4. Dedicated node routing- Route hot-key traffic to a small set of dedicated nodes (cache tier or write-back aggregator) via a routing table in proxy/load-balancer.- Ensures isolation; allow specialized tuning (larger memory, faster disks).

5. Client-side batching and caching- Batch writes/reads for idempotent operations (bulk-get, write-behind).- Use short-lived client-side caching or read-through TTLs to reduce QPS to backend.

6. Rate limiting & backpressure- Apply global or per-client rate limits; token-bucket implemented in edge proxies.- Return soft-fails or degrade to stale/partial data with clear SLAs.

Operational complexity vs performance trade-offs:- Key-splitting: Low code complexity if clients/proxies can hash; high benefit for throughput. Complexity rises if strong consistency/atomicity required—need merge/aggregation logic.- Dynamic re-sharding: Higher operational complexity (coordination, metadata updates), but minimal client changes; good for long-lived hotspots.- Dedicated nodes: Moderate complexity (routing + capacity planning); provides strongest isolation and predictable latency.- Client batching/caching: Low to moderate complexity; best for read-heavy or idempotent workloads; reduces latency and load but increases staleness.- Rate-limiting: Low complexity; protects cluster but degrades client UX.

Recommendation (Solutions Architect view):- Implement detection + automated policy engine.- Prefer key-splitting + increased replication for counters/aggregates when atomicity can be relaxed.- Use dedicated-node routing for business-critical hot-keys.- Combine with client batching and TTL caching before enforcing rate limits.- Automate re-shard/routing changes via a control plane and ensure observability, rollback, and playbooks to minimize ops risk.

Unlock Full Question Bank

Get access to hundreds of Database Scalability and High Availability interview questions and detailed answers.

Join thousands of developers preparing for their dream job.