Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
System Design · Question Set
Database Sharding & Partitioning interview questions for placements and exams.
Questions
15
Included in this set
Subject
System Design
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for System Design.
If the WHERE clause references the partition key with ranges or equality, the planner can skip irrelevant partitions. Design queries and partition schemes together to exploit pruning and reduce scan cost.
WHERE event_date BETWEEN '2025-10-01' AND '2025-10-07'
For complete preparation, combine this set with full subject-wise practice for System Design. You can also explore other subjects and sets from the links below.
Horizontal sharding distributes different rows of the same table to multiple physical nodes to scale write and read throughput. Vertical partitioning separates columns by concern, often isolating wide or cold fields to another table or service for performance and cost control.
users_shard_17 holds user_id hash 544..575; profile_wide moved to users_profile_ext
Dual write keeps both locations consistent during cutover. You can compare read results and switch the router after parity checks. Backfills handle historical data. Once confidence is high, stop writing to the old shard and decommission.
router: write→old+new; read→old until parity flag flips
A router computes the shard from the partition key, manages shard maps, and provides health-aware failover. It shields clients from topology changes and exposes metrics on hit rates, latency, and errors. Deploy it stateless behind a load balancer with config in a durable store. Roll out changes with feature flags and canaries. Keep a fast local cache of the shard map and a background refresher to avoid cold lookups.
route(key){ sid=hash(key)%N; return pool[sid].pickHealthyReplica(); }Hashing a high-cardinality identifier spreads load evenly and avoids concentrating traffic on a single shard. Pure time or geography keys can cause skew during spikes or regional events unless combined with hashing.
shardId = hash(user_id) % N
Range partitions let the engine skip entire partitions for queries like last 7 days or Q2 2025, reducing I/O. Hash partitions are better for even distribution of random writes but offer less pruning for temporal scans.
PARTITION BY RANGE (event_date) (p2025q1, p2025q2, ...)
Consistent hashing maps keys to a ring so only a fraction of keys remap on topology change, easing resharding. Evenness improves with virtual nodes, but perfect balance is not guaranteed without monitoring.
node slots: N virtual nodes per physical shard on the hash ring
Salting spreads hot writes across multiple shards or partitions by appending a small salt, then merging on read. This reduces contention while keeping query semantics intact with a controlled fan-in.
post:{id}:{salt in 0..9} -> read fan-in 10, merge by timeCo-locating related entities under the same shard key keeps most reads single-shard and cheap. For read patterns that still span shards, build denormalized views or a search index. Heavy multi-shard joins are brittle at scale; push them to batch pipelines or OLAP systems.
Order + OrderLines share shard key = order_id
Snowflake-like IDs embed time and shard bits. They are k-sortable per shard and avoid a global sequence bottleneck. Pure UUID v4 distributes well but is not ordered; global sequences create a single hot writer.
id = (timestamp<<22) | (shard<<12) | sequence
A single logical index may receive updates from many data shards, creating skew and coordination cost. Maintaining global order and atomicity is complex. Common mitigations: local per-shard indexes plus scatter-gather reads, or an async global index with eventual consistency.
email→user_id index shard must absorb writes from all user shards
Two-phase commit gives atomicity across shards but adds a coordinator, locks, and higher latency. It is suitable when you need strict all-or-nothing semantics on small, infrequent transactions. Sagas split the workflow into local transactions with compensations. They scale better and keep availability high, at the cost of transient inconsistency and more failure handling code. Use Sagas for high-throughput, user-facing operations where partial progress is acceptable with recovery.
Reserve A → Reserve B → Confirm; on failure: Cancel B, Cancel A
Track per-shard QPS, p95 latency, storage, and hot-key frequency. Alert on deviations from the median and on sustained hotspot ratios. Sample keys of top contributors to locate bad partition choices. To fix, add virtual nodes, change the hash function with a remap table, or split hot keys using salting. Run an online rebalancer that backfills and then flips routing gradually.
metrics: shard_qps, shard_p95_ms, top_keys{key,qps}Choose tenant_id as the primary partition key so tenant-scoped reads and writes stay on one shard. Within a tenant, derive entity keys from tenant_id to keep locality for joins. For global reports, build asynchronous aggregates or warehouse exports. If some tenants are huge, allow them multiple sub-shards with a tenant hash suffix to avoid hotspots while preserving tenant isolation.
key = tenant_id + ":" + user_id // shard by tenant_id
Introduce a shard-map v2 that maps old slots to new slots (e.g., power-of-two expansion). Start dual writes and begin backfilling data to new shards in batches with throttling. Validate parity by sampling reads from both targets. Switch reads per-tenant or per-bucket using flags. Monitor error rates and lag. Once stable, disable writes to old slots, run a final delta sync, and decommission. Keep rollback by retaining old data and routing map until confidence is high.
slot = hash(key) & 127 // from & 63 to & 127; router supports v1|v2