Database sharding interview questions and answers

"We'll shard by user ID," the candidate said, confidently and correctly. The interviewer nodded. "Now the product team wants a page showing all orders in the last hour, sorted by value, across the whole platform." And there it was — a query that used to be one line of SQL now needs a fan-out to sixteen shards, a merge, and a sort, and it is slower than it was before they scaled.

That tradeoff is the heart of database sharding interview questions. Sharding buys write throughput and storage capacity, and charges you in query flexibility and operational complexity. This guide covers shard key selection, the partitioning strategies, resharding, and the cross-shard problems interviewers use to separate people who have read about sharding from people who have done it.

What sharding interviews actually test

• When to shard at all — and the cheaper things you should do first.
• Shard key selection — the decision that determines everything downstream.
• Partitioning strategies — range, hash, directory, and consistent hashing.
• The hard problems — cross-shard queries, transactions, joins, resharding.
• Operations — hotspots, rebalancing, backups, schema changes.

Do the cheap things first

A strong answer opens by not sharding. Interviewers deliberately present a scaling problem to see whether you reach for the most complex tool immediately. The ladder, in order:

• Indexes and query tuning — a missing index has fixed more scaling problems than sharding ever has.
• Caching — most workloads are read-heavy, and a cache absorbs reads without touching the data model.
• Read replicas — scales reads horizontally and is operationally simple. Note the replication lag consequence: read-after-write may return stale data.
• Vertical scaling — unfashionable but real. Modern single instances handle far more than people assume, and it costs nothing in complexity.
• Vertical partitioning / service extraction — move a large table or module to its own database.
• Then shard — when a single node genuinely cannot hold the data or absorb the write volume.

State the reason plainly: sharding is largely irreversible and complicates every subsequent operation — joins, transactions, migrations, backups, analytics. It buys write and storage scale specifically, and you should be able to say that is the constraint you have hit.

Choosing a shard key

The most important decision, and the one interviewers probe hardest. A good shard key has three properties:

• High cardinality — enough distinct values to spread across shards. Sharding by country puts most Indian users on one shard.
• Even distribution — no value should dominate. Sharding a B2B product by tenant_id is natural and creates hotspots when one enterprise customer is a hundred times larger than the rest.
• Query alignment — most queries should be answerable from one shard. This is the property people forget, and it is what the intro's question exposed.

The classic mistakes to name: auto-increment IDs with range partitioning, which sends every new write to the last shard, making it a hotspot while others idle; timestamps as shard keys, with the same problem; and picking a key that matches writes but not reads.

A frequently-asked scenario: an e-commerce system with users and orders. Sharding both by user_id keeps a user's orders co-located with them, so the common query — this user's order history — hits one shard. The cost is that seller-side or global queries fan out. That tradeoff, stated explicitly, is the complete answer.

Partitioning strategies

• Range-based — shard by key ranges (A–F, G–M). Range queries are efficient and stay on one shard, but distribution is easily uneven and sequential keys create hotspots.
• Hash-based — hash the key and modulo by shard count. Excellent distribution, and range queries become impossible without a fan-out. The killer problem: hash(key) % N means changing N remaps almost every key, which is why adding a shard is catastrophic.
• Consistent hashing — the fix. Map both shards and keys onto a ring; a key belongs to the next shard clockwise. Adding or removing a shard only moves the keys between adjacent points rather than remapping everything. Virtual nodes (each physical shard appearing many times on the ring) smooth the distribution — mention them, because that is the detail that shows real understanding.
• Directory-based — a lookup service maps keys to shards. Maximum flexibility, easy rebalancing, and it introduces a lookup hop and a single point of failure to make highly available.
• Geographic — shard by region for latency and data residency, which is increasingly driven by regulation rather than performance.

import hashlib, bisect

class HashRing:
    """Consistent hashing with virtual nodes."""

    def __init__(self, shards, vnodes=150):
        self.ring = {}
        self.vnodes = vnodes
        for shard in shards:
            self.add(shard)

    def _hash(self, key):
        return int(hashlib.md5(key.encode()).hexdigest(), 16)

    def add(self, shard):
        # Many points per shard so load spreads evenly
        for i in range(self.vnodes):
            self.ring[self._hash(f"{shard}#{i}")] = shard
        self._sorted = sorted(self.ring)

    def get(self, key):
        # First point clockwise from the key owns it
        h = self._hash(key)
        idx = bisect.bisect(self._sorted, h) % len(self._sorted)
        return self.ring[self._sorted[idx]]

# Adding a shard moves ~1/N of keys, not almost all of them
ring = HashRing(["shard-a", "shard-b", "shard-c"])
print(ring.get("user:8814"))

Consistent hashing is asked by name constantly. Be able to explain the ring, why it limits data movement to roughly 1/N of keys, and what virtual nodes solve. Our distributed systems interview questions guide covers the surrounding theory.

The hard problems

This is where the round is decided.

• Cross-shard queries — scatter-gather: query every shard, merge, sort, paginate in the application. Latency becomes the slowest shard's latency, and pagination across shards is genuinely painful. The practical answer is usually to avoid them: maintain a separate read model, push analytics to a warehouse, or denormalise.
• Cross-shard joins — generally not supported by the database. Options are denormalising so the join is unnecessary, replicating small reference tables to every shard, or joining in the application.
• Cross-shard transactions — you lose single-node ACID. Options are two-phase commit (correct, slow, and it blocks if the coordinator fails), or the saga pattern with compensating actions and eventual consistency. The senior answer is that the best fix is a shard key choice that keeps transactional data together.
• Unique constraints and IDs — auto-increment breaks across shards. Use UUIDs, or Snowflake-style IDs combining timestamp, node ID and sequence, which stay roughly sortable. Naming Snowflake IDs is a strong signal.
• Resharding — the operation everyone underestimates. Doubling shard count with consistent hashing still means moving data while serving traffic, usually via dual writes, backfill, verification, then cutover. Say that it needs to be online and reversible.

Operations and hotspots

The realities interviewers reward you for anticipating: hotspots from an outlier tenant (fixed by splitting that tenant across shards with a composite key, or giving them a dedicated shard); schema migrations that must now run across every shard, ideally in an online, backward-compatible way; backups and restores that must be consistent across shards or explicitly accept per-shard consistency; and monitoring per shard, because an average across shards hides the one that is dying.

Also worth naming: many teams get sharding without building it, through a managed system — Vitess for MySQL, Citus for Postgres, or a natively sharded database like MongoDB, Cassandra or DynamoDB. Recommending the managed path over hand-rolling is usually the right engineering answer and reads as maturity.

DDIA, real systems, ChatGPT — where each fits

• Designing Data-Intensive Applications — the partitioning chapter is the single best preparation for this topic and is what most interview questions are drawn from.
• The Dynamo and Bigtable papers — consistent hashing and range partitioning respectively, straight from the source.
• Vitess and Citus documentation — how sharding is actually operated in production, including resharding workflows.
• Sharding a toy app yourself — highest yield. Split a table across two Postgres instances by hash, then try to write the "all orders in the last hour" query. The pain is instructive and permanent.
• Instagram, Notion and Figma engineering posts on sharding — real migrations with real numbers and real mistakes.
• ChatGPT — good for explaining strategies and comparing options. It will not spring the cross-shard reporting query on you at minute thirty.
• Greenroom — the spoken layer. Ari, the AI interviewer, runs design rounds out loud and adds the requirement that breaks your shard key. Fair tradeoff: Ari will not migrate your data.

How to prepare for a sharding interview

• Week 1: the ladder. Rehearse indexes, caching, replicas and vertical scaling as the things you try first, with reasons.
• Week 2: shard keys. For five real products, choose a key and name which queries become scatter-gather as a result.
• Week 3: strategies. Implement consistent hashing with virtual nodes yourself, and be able to explain the ring in ninety seconds.
• Final week: the hard problems — sagas, Snowflake IDs, online resharding — then two spoken mocks where the interviewer adds a cross-shard requirement.

Building the rest of the stack? The caching guide covers the layer that often removes the need to shard, the distributed systems guide covers consistency, the DBMS guide covers the relational fundamentals, and the system design guide covers assembling them.

Frequently asked questions

What are the most common database sharding interview questions?

The most common questions cover what you would try before sharding, how you choose a shard key and what makes a good one, the differences between range, hash, directory and consistent hashing strategies, why hash modulo shard count breaks when you add a shard, how you handle cross-shard queries joins and transactions, how you generate unique IDs across shards, and how you reshard without downtime.

How do you choose a shard key?

A good shard key has high cardinality so data spreads across shards, even distribution so no single value dominates and creates a hotspot, and query alignment so most queries can be answered from a single shard. The third property is the one candidates forget, and it determines which queries become expensive scatter-gather operations afterwards. Avoid auto-increment IDs and timestamps with range partitioning, since all new writes land on the last shard.

What is consistent hashing and why does it matter for sharding?

Consistent hashing maps both shards and keys onto a conceptual ring, with each key belonging to the next shard clockwise. It matters because naive hash modulo shard count remaps almost every key when the shard count changes, making it impossible to add capacity without moving nearly all data. With consistent hashing, adding or removing a shard only moves roughly one over N of the keys. Virtual nodes, where each physical shard appears at many ring positions, smooth uneven distribution.

How do you handle cross-shard queries?

The direct approach is scatter-gather: query every shard in parallel, then merge, sort and paginate in the application. This makes latency equal to the slowest shard and makes pagination genuinely difficult. In practice the better answer is avoidance — maintain a separate read model or search index for global queries, push analytics to a data warehouse, or denormalise so the query can be answered from one shard.

How do you generate unique IDs across shards?

Auto-increment breaks because each shard would generate colliding values. The common options are UUIDs, which are globally unique but large and not sortable, or Snowflake-style IDs which combine a timestamp, a node or shard identifier and a per-node sequence number into a 64-bit integer. Snowflake IDs are preferred when you want roughly time-sortable identifiers, which helps index locality and pagination.

What should you do before sharding a database?

Work through the cheaper options first, because sharding is largely irreversible and complicates joins, transactions, migrations, backups and analytics permanently. Add missing indexes and tune queries, introduce caching since most workloads are read-heavy, add read replicas, and consider vertical scaling since modern single instances handle far more than people assume. Also consider vertical partitioning by moving a large table or module to its own database.