Database Sharding: Horizontal Scaling

The Register That Ran Out of Room

Picture the admission register in a school office: one thick book, one clerk, entries written in order by admission number. For 300 students this works perfectly. Now imagine the same idea scaled up to a company running an online mock-test platform used by 12,00,000 CBSE students across India. Every student's login details, test scores, and attempted questions live as rows in one giant table, and that table sits on one database server. What breaks first?

Two different walls appear. The first is storage: a single machine has a fixed amount of disk space, and at some point 12,00,000 rows (each with scores across dozens of subjects and years) simply will not fit. The second, more common wall is speed: even before storage runs out, every single query — a student logging in, a teacher pulling up a mark sheet, a parent checking a rank — has to be handled by the same one machine. During a result-announcement rush, thousands of students might query that server in the same second. One machine, however powerful, processes requests through a limited number of CPU cores and a limited number of disk operations per second. As the table and the traffic both grow, response time creeps from milliseconds toward seconds, then toward "please wait" spinners. The database has not lost any data — it is simply one clerk trying to serve a queue that has become too long.

This is the core problem that database sharding exists to solve: what do you do when one server, no matter how well-tuned, cannot keep up with the size or the traffic of your data?

Two Ways to Grow: Scaling Up vs Scaling Out

There are exactly two directions to solve this problem, and it is worth being precise about the difference, because CBSE Computer Science and general systems literature both use these terms constantly.

Vertical scaling ("scaling up") means replacing your one server with a bigger one — more RAM, a faster processor, a larger and faster disk. It is the equivalent of giving the school clerk a faster pen, a bigger desk, and a photographic memory. This is often the first fix people reach for, and it genuinely helps for a while. But it has two hard limits. First, there is a ceiling: at any point in time, there exists some maximum amount of RAM and CPU power you can buy in a single machine, and that ceiling is expensive to approach. Second, and more importantly, a single machine — however powerful — remains a single point of failure. If that one server crashes, the entire system goes down, because there is nothing else holding any of the data.

Horizontal scaling ("scaling out") takes the opposite approach: instead of one giant machine, use several ordinary machines working together, with each one responsible for only a slice of the total data. This is the equivalent of hiring three clerks and giving each one a separate register covering a different range of admission numbers, so they can all write entries in parallel instead of queuing behind a single book. Sharding is exactly this idea applied to a database: you take one very large table and split its rows across multiple independent database servers, called shards, so that each shard stores a different, non-overlapping subset of the rows. No single shard holds the whole table — together, all the shards hold it.

It is worth separating this from the general word "partitioning." A single server can partition its own data internally (for example, storing this year's records separately from last year's, still on the same machine, purely to organize storage). Sharding specifically means the partitions live on separate servers, each capable of answering queries independently and in parallel — that is what actually relieves the load on any one machine.

The Central Decision: The Shard Key

You already know, from working with any database table, that a table has columns, and usually one column — the primary key — uniquely identifies each row (for instance, a student_id column where no two students share a value). To shard a table, you must choose one column and decide: for every row, we will look at the value in this column and use it to decide which shard that row lives on. This chosen column is called the shard key.

Once a shard key is chosen, you need a rule — a shard function — that takes any shard key value and always outputs the same shard number for it. This "always the same" requirement is not optional: if the same student_id could map to Shard 0 today and Shard 2 tomorrow, the system would lose track of where that student's row actually lives. Formally, if there are N shards numbered 0 to N−1, we need a deterministic function:

shard_number = f(shard_key_value)

There are three widely used ways to design this function f, and each one makes a different trade-off. We will work through each with real numbers.

Strategy 1: Range-Based Sharding

The simplest idea: divide the possible range of shard key values into contiguous blocks, and give each block its own shard. Suppose our mock-test platform assigns student_id values from 100000 upward, and we split them across 3 shards as follows:

• Shard 0 holds IDs 100000 – 399999
• Shard 1 holds IDs 400000 – 699999
• Shard 2 holds IDs 700000 – 999999

As code, the routing rule looks like this:

def range_shard(student_id):
    if student_id <= 399999:
        return 0
    elif student_id <= 699999:
        return 1
    else:
        return 2

Trace it by hand for student_id = 550210: it is not ≤ 399999, so we check the next condition; it is ≤ 699999, so the function returns 1. This student's row lives on Shard 1.

Range sharding has a genuine strength: queries that ask for a range of keys — "list all students with IDs between 400000 and 420000" — can be answered by contacting only Shard 1, since the router can tell from the range alone which single shard could possibly contain those rows.

But it has a serious weakness when the shard key grows sequentially, which is exactly how IDs usually work (each new student gets the next available number). Every newly registered student — 1000001, 1000002, 1000003, and so on — will have an ID greater than 999999, and under this scheme every single one of them lands on whichever shard covers the highest range. That one shard absorbs 100% of new write traffic while the other shards, holding only old, rarely-changing rows, sit comparatively idle. This is called the hot shard problem: the whole point of sharding was to spread load evenly, and sequential range sharding defeats that purpose for write-heavy, ever-growing tables.

Strategy 2: Hash-Based Sharding

To avoid clustering new rows on one shard, hash-based sharding scatters keys pseudo-randomly (but deterministically) across all shards, regardless of whether the keys arrive in sequence. The simplest possible hash function for a numeric key is the remainder after division — the modulo operator:

def hash_shard(student_id, num_shards):
    return student_id % num_shards

Trace this for three consecutive IDs with num_shards = 3:

• 100001 % 3: 3 × 33333 = 99999, remainder 2 → Shard 2
• 100002 % 3: 3 × 33334 = 100002, remainder 0 → Shard 0
• 100003 % 3: remainder 1 → Shard 1

Notice what just happened: three IDs that are numerically right next to each other landed on three different shards. That is precisely what solves the hot-shard problem — brand-new, sequential registrations get spread evenly across all shards instead of piling onto one.

Now let's route a real query. A request comes in for student_id = 482195. We compute 482195 % 3: since 3 × 160731 = 482193, the remainder is 482195 − 482193 = 2. So this student's row lives on Shard 2. Keep this exact number in mind — it appears again in the diagram below.

One caution about correctness: real production systems do not take the modulo of the raw key directly, especially when the shard key is not a clean, evenly-spread number — a string like an email address or a mobile number would not behave the same way under plain modulo. Instead, they first pass the key through a proper hash function (such as MD5 or SHA-256) to turn it into a large, well-mixed number, and then take the modulo of that hash. This also guards against subtle numeric patterns in real IDs (for instance, IDs that are always even, or always multiples of a school code) accidentally skewing the distribution. Python's own built-in hash() function is intentionally randomized between runs for security reasons, so it is unsuitable here — a sharding hash must be stable forever, not just within one program run.

The Resharding Problem

Hash sharding solves the hot-shard issue, but it creates a different one: what happens when you need to add a shard? Suppose the platform grows and a 4th shard is added, so num_shards changes from 3 to 4. Take the ID we already traced, 100001. We know 100001 % 3 = 2. Now compute 100001 % 4: 4 × 25000 = 100000, remainder 1. The shard assignment flips from Shard 2 to Shard 1 — for this ID, and, if you check others, for almost every ID in the table, because changing the divisor in a modulo operation changes nearly all remainders. The practical consequence is severe: adding one more machine to a plain modulo-sharded cluster forces you to physically move almost the entire dataset between servers, just to accommodate one new machine. For a live system serving students during exam season, that kind of mass migration is disruptive and risky.

The standard fix is a technique called consistent hashing. Instead of directly using "key mod number-of-shards," imagine placing both the shards and the keys as points on a large numbered circle (using a hash function to decide each point's position). Each key is then owned by whichever shard is the next one going clockwise from it on that circle. When you add a new shard, it inserts itself at one point on the circle and takes over only the small arc of keys immediately before it — every other shard keeps almost all of the keys it already had. You do not need the full mathematics of consistent hashing at this stage; the important idea to remember is why it exists: it minimizes how much data has to move when the number of shards changes, which plain modulo hashing does not.

Strategy 3: Directory-Based Sharding

The third approach sidesteps formulas entirely. A separate, small lookup service — the directory — keeps an explicit table mapping shard key values (or ranges of them) to shard numbers. Before running any query, the application first asks the directory, "which shard holds student_id = 482195?" The directory answers, say, "Shard 2," and only then is the actual query sent there.

The advantage is flexibility: since the mapping is just data in a lookup table, an administrator can move any individual student's row to any shard at any time — useful for balancing load manually or isolating a very active school onto its own dedicated shard — without being constrained by a rigid range or hash formula. The disadvantage is twofold: every single query now needs an extra network round-trip to consult the directory before it can even begin, adding latency, and the directory itself becomes a new critical component — if it goes down or falls behind, the whole system cannot find any data, so it typically needs its own careful replication to avoid becoming a single point of failure.

A Common Misconception: Sharding Is Not Replication

Students very often use "sharding," "replication," and "backup" as if they were interchangeable, and this mixes up two solutions to two different problems. Replication means copying the entire dataset onto multiple servers, so that every replica holds all the data — this protects against a server failing (another replica still has everything) and helps spread out heavy read traffic (different users can be served by different replica copies). Sharding means the opposite kind of split: each shard holds only its own unique, non-overlapping slice of the data, and no single shard has the full picture on its own.

These techniques are not alternatives — large real systems normally use both together. Data is first sharded across many servers to handle its total size and write volume, and then each individual shard is separately replicated (commonly with two or three copies) so that losing any one machine does not lose any data. Sharding answers "our data and write traffic are too big for one machine"; replication answers "what happens if one machine fails, and how do we serve heavy read traffic." Confusing the two leads to a common exam mistake: describing a system with three shards as "having three backups of the data," when in fact each of those three shards holds a different third of the data, not three copies of the same thing.

What Sharding Makes Harder: Cross-Shard Queries

Sharding is not free. Consider the query "count all students who scored above 90% in Mathematics." On one unsharded table, this is a single scan. On a sharded cluster, no single shard has the whole picture, so the router must send the same query to every shard, wait for each one to compute its own partial count, and then add those partial counts together at the end. This pattern is called scatter-gather, and it is inherently slower and more complex than a single-server query, because the total time is limited by whichever shard is slowest to respond, and the results must be correctly merged afterward.

Joining two tables causes a sharper problem. Suppose a Students table is sharded by student_id, and a separate Fee_Payments table is sharded by school_id. A query joining a student's record with their fee payments now has no guarantee that both related rows live on the same shard — the database cannot perform an ordinary SQL join across two different physical servers. This is exactly why the choice of shard key matters so much beyond just load-balancing: if both tables were instead sharded using the same key (student_id), each student's record and their fee payments would always land on the same shard together, and the join would stay local to one machine, cheap and fast.

Seeing It Together

Choosing a Good Shard Key

Not every column makes a workable shard key. Four checks matter in practice:

• High cardinality. The column needs many possible distinct values. Sharding by a column like gender, which has only two or three possible values, caps you at two or three shards forever, and one of those shards would end up holding roughly half of everyone — the opposite of balance.
• Even access pattern, not just even row count. A key can split rows evenly while still concentrating write traffic unevenly — this is exactly the sequential-ID problem seen in range sharding above, where row counts per shard were fine on day one, but all future writes piled onto one shard.
• Matches your most common query filter. If almost every query filters by student_id, sharding by student_id lets the router go straight to one shard. Sharding by a column your queries rarely filter on forces scatter-gather queries across every shard, every time.
• Stability over time. If a row's shard-key value can change (for example, a "current class/section" column, where every student's value updates once a year during promotions), that row would need to physically move between shards whenever it changes — an expensive operation you want to avoid needing regularly.

Quick Recap

• Vertical scaling = one bigger machine; horizontal scaling = many ordinary machines sharing the load.
• Sharding = splitting one table's rows across multiple independent servers (shards), each holding a distinct, non-overlapping slice.
• The shard key is the column used to decide a row's shard; the shard function deterministically maps a key value to a shard number.
• Range sharding supports fast range queries but risks a hot shard under sequential writes.
• Hash sharding (e.g. key % num_shards) spreads sequential writes evenly, but changing the shard count remaps almost every key — the resharding problem, addressed in real systems by consistent hashing.
• Directory-based sharding trades an extra lookup hop for full manual flexibility.
• Sharding ≠ replication: sharding splits data across servers; replication copies the same data onto multiple servers. Real systems typically use both.
• Sharding makes cross-shard queries and joins more expensive — mitigated by picking a shard key shared across related tables.

Test Yourself

1. A platform uses 4 shards with student_id % 4. Which shard holds student_id = 271829? Show the division.
2. Using hash sharding with 5 shards, which shard holds student_id = 930014? (Hint: for mod 5, only the last digit of the number matters.)
3. A range-sharded system has Shard 0 for IDs 0–499999 and Shard 1 for IDs 500000–999999. New students are currently being assigned IDs starting at 500000 and counting up. Which shard will receive essentially all new-student writes for the next several years, and why is that a problem?
4. A Students table is sharded by student_id, and a separate Attendance table is sharded by school_id. Will a query joining a student to their attendance records generally stay on one shard? Explain why or why not.
5. A classmate says, "Our database has 3 shards, so we have 3 backups of all our data." Explain what is wrong with this statement and rewrite it correctly.
6. A system currently has 3 shards using id % 3. It expands to 4 shards using id % 4. For id = 100001, compute the shard under both schemes. Did the row have to move? What does this reveal about plain modulo hashing?

Answer key — (1) 271829 ÷ 4 = 67957 remainder 1 → Shard 1. (2) last digit 4 → 930014 % 5 = 4 → Shard 4. (3) Shard 1, because sequential growth keeps every new ID ≥ 500000; Shard 0 stays static while Shard 1 becomes a write hot spot. (4) No — the two tables use different shard keys, so a given student's attendance rows are not guaranteed to sit on the same shard as their student row, forcing a cross-shard join. (5) Three shards means three different, non-overlapping slices of data, not three copies of the same data; correct version: "our data is split across 3 shards, and each shard should additionally be replicated for safety." (6) 100001 % 3 = 2 (old shard); 100001 % 4 = 1 (new shard) — the row must move, showing that adding a shard under plain modulo hashing forces large-scale data migration, which consistent hashing is designed to avoid.