Chapter 7: Sharding — Splitting the Database

Curriculum: Distributed Systems from First Principles Chapter: 7 of 24 Part: 3 — Partitioning Prerequisites: Chapter 2 (pages, B-Trees, storage internals), Chapter 4 (replication motivation), Chapter 6 (consistency spectrum) Estimated reading time: 80–90 minutes

Opening

Replication gave you fault tolerance and the ability to scale reads by adding replicas. It left one problem completely unsolved: write scale. No matter how many read replicas you add, every write still goes to one machine — the primary. That machine has a ceiling. It has finite disk. It has finite RAM to buffer writes. It has finite network bandwidth to receive client connections. It has a finite number of fsync operations it can perform per second before its storage becomes the bottleneck. When your write throughput approaches that ceiling, adding another replica doesn't help at all. The only solution is to spread the writes across multiple machines — to split the data. This is sharding, and it is the most architecturally consequential decision you will ever make about a database. The shard key you choose on day one will constrain your data model, your query patterns, and your operational complexity for the entire lifetime of the system. Sharding is not hard in the way that consensus algorithms are hard — it's hard in the way that choosing the wrong door is hard. The decision is simple; the consequences of getting it wrong are years of pain. This chapter gives you the framework to get it right.

Part 1: The Wall One Machine Eventually Hits

Before sharding becomes the right answer, something specific must be true: you must have exhausted the alternatives. This matters because sharding adds complexity that compounds — every operation that was simple on a single machine becomes harder, more expensive, or impossible across shards. Let's first understand exactly what limits you're hitting, so you know when sharding is genuinely the answer.

A single well-configured Postgres instance on modern hardware can handle roughly:

• 10,000–50,000 simple writes per second (INSERTs with one or two indexes, depending on row size and fsync behaviour)
• 100,000+ reads per second from buffer pool (if the working set fits in RAM)
• Several terabytes of data before operational complexity (vacuum, backup, restore times) becomes severe
• Hundreds of concurrent connections before connection overhead becomes significant (use a connection pooler like PgBouncer beyond ~100 connections)

These numbers drop significantly under heavier workloads: large rows, many indexes, complex queries, cross-table joins on large datasets.

The critical insight from Chapter 1: writes are bottlenecked by disk I/O. Every committed write requires an fsync — a guarantee that the WAL record is durably on disk. A modern NVMe SSD can handle roughly 100,000–500,000 fsyncs per second, but each fsync call has overhead, and Postgres groups multiple writes into batch fsyncs (WAL group commit). In practice, at high concurrency, the bottleneck becomes the serialisation of writes through the WAL — all writers contend for the WAL write lock. Adding more CPU or RAM doesn't help at this point. The machine is I/O-bound at the WAL.

Before concluding you need to shard, verify that the problem isn't one of these:

Query problem, not a scale problem. A single slow query dragging down the whole database is not a sharding problem. It's an index problem (Chapter 3). Run EXPLAIN ANALYZE. Find the missing index. A properly-indexed table with hundreds of millions of rows serves queries in milliseconds.

Read problem, not a write problem. If your primary's CPU is high because it's processing thousands of read queries, you have a read scale problem — solved by adding read replicas (Chapter 5) and caching (Chapter 9). Sharding your writes doesn't help reads if all queries scatter across all shards.

Connection problem. Thousands of application connections to Postgres is a connection overhead problem, solved by PgBouncer (connection pooler), not sharding.

Table bloat. A massively-bloated table (from unfixed autovacuum issues, Chapter 2) performs like a much larger table. Fix vacuum before assuming you need to shard.

If after exhausting these the bottleneck is genuinely: more writes per second than one machine can commit to WAL, or more data than one machine can store, then sharding is the right tool.

Part 2: What Sharding Is — The Physical Picture

Sharding means splitting a table's rows across multiple independent database servers, each holding a subset of the rows. Each server is called a shard. Every row exists on exactly one shard. Together, the shards hold the complete dataset.

Each shard is a full, independent database server — it has its own heap files, its own indexes, its own buffer pool, its own WAL. It can itself have replicas for fault tolerance (replication within a shard). The shards don't know about each other; a routing layer in front of them determines which shard a given request goes to.

The routing decision — "which shard does this row belong to?" — is determined by the shard key: a column (or combination of columns) whose value, when passed through a partitioning function, maps to a specific shard. Every read and write must include the shard key, or the router doesn't know where to send it.

The two fundamental partitioning functions are range partitioning and hash partitioning. They make opposite trade-offs, and understanding those trade-offs is most of what you need to know about sharding.

Part 3: Range Partitioning — Order Preserved, Hot Spots Likely

In range partitioning, the shard key's value space is divided into contiguous ranges, and each range is assigned to a shard.

Shard on: user_id (integer, values 1 to 100,000,000)

Shard 1: user_id   1  –  25,000,000
Shard 2: user_id   25,000,001  –  50,000,000
Shard 3: user_id   50,000,001  –  75,000,000
Shard 4: user_id   75,000,001  –  100,000,000

To find which shard holds user 42,000,000: it falls in the range 25,000,001–50,000,000 → Shard 2. Simple comparison, no hash computation needed.

The strength of range partitioning: range queries stay local

If your application commonly queries ranges of the shard key — "give me all events from January", "give me all users with ID between 1,000 and 2,000" — range partitioning is ideal. The router knows exactly which shard(s) contain the relevant range, and the query touches only those shards. A query for user IDs 26,000,000–27,000,000 goes to Shard 2 and nowhere else.

This also enables a powerful operational feature: partition-based data lifecycle management. Time-series data (logs, events, metrics) partitioned by time range can be purged by simply dropping old shards. Instead of a slow DELETE FROM events WHERE timestamp < X that processes millions of rows, you DROP a shard — an O(1) filesystem operation. This is why almost all logging and analytics systems partition by time.

The fatal weakness: sequential inserts create hot shards

Here is the trap that range partitioning sets for you.

If your shard key is a sequential integer (auto-increment primary key, which most databases default to) or a timestamp (events happen in time order), then new inserts almost always have the highest key value. That means new inserts almost always go to the last shard — the one covering the highest range.

Auto-increment inserts: 1, 2, 3, ..., 25,000,001, 25,000,002, ...

Shard 1: receives user 1, 2, 3, ..., 25,000,000  (filled. done.)
Shard 2: receives user 25,000,001, 25,000,002, ...  (ALL new writes)
Shard 3: empty
Shard 4: empty

Shard 1 is cold. Shards 3 and 4 are empty. All writes hammer Shard 2. You've bought yourself no write scale improvement whatsoever — you have 4 machines but only 1 is doing any work. This is the sequential insert hot spot, and it makes range partitioning on sequential keys nearly useless for write throughput improvement.

The time-series version is identical. All new events have "now" as their timestamp. The shard covering the most recent time range receives 100% of new writes. The historical shards are read-only graveyards.

Range partitioning is excellent for data lifecycle (drop old partitions) and range queries (scan a time window). It is poor for write distribution unless your writes are naturally spread across the key range (e.g., you're inserting records for historical dates in random order, or your primary key is a random UUID).

Part 4: Hash Partitioning — Uniform Distribution, Range Queries Destroyed

Hash partitioning applies a hash function to the shard key and uses the result to determine the shard.

shard = hash(user_id) % 4

user_id = 1:        hash(1) % 4 = 1   → Shard 1
user_id = 2:        hash(2) % 4 = 2   → Shard 2
user_id = 3:        hash(3) % 4 = 3   → Shard 3
user_id = 4:        hash(4) % 4 = 0   → Shard 0
user_id = 5:        hash(5) % 4 = 1   → Shard 1
user_id = 25000001: hash(25000001) % 4 = ?  → distributed uniformly

A good hash function distributes its outputs uniformly. So sequential inserts (1, 2, 3, 4, 5...) are distributed across all shards roughly evenly. No sequential hot spot.

The strength: uniform write distribution

Hash partitioning eliminates the sequential insert problem. It doesn't matter whether your keys are sequential, random, or clustered — the hash function scatters them uniformly across shards. Write throughput scales linearly with the number of shards (roughly): double the shards, roughly double the write capacity.

The weakness: range queries are scatter-gather

The hash function destroys the ordering of keys. user_id values 25,000,001–27,000,000 are not concentrated on one shard — they're scattered across all shards based on their hash. A query WHERE user_id BETWEEN 25000001 AND 27000000 has no choice but to query all shards simultaneously and merge the results. This is called a scatter-gather query, and it has several unpleasant properties:

Its latency is determined by the slowest shard. If you query 4 shards in parallel, you wait for the last one to respond. If one shard is slow (GC pause, I/O spike), the entire query is slow. As you add shards, you add more tail latency.

It multiplies load on every shard. A scatter-gather query that arrives at a rate of 100/second creates 400 queries/second across 4 shards. This load grows linearly with shard count, which partially negates the scale benefit.

It requires a merge step. The router must collect results from all shards, merge and sort them, apply LIMIT after merging, etc. This merge computation lives somewhere — the router, the application, a separate aggregation layer.

The practical consequence: in a hash-partitioned system, your query patterns must be shard-key-centric. Queries that filter on the shard key go to exactly one shard — fast. Queries that don't filter on the shard key go to all shards — expensive and scaling badly.

This constraint shapes your data model more than almost any other architectural decision. Once you've chosen to hash-shard on user_id, queries like "give me all users from India" become scatter-gather queries touching every shard. If that's a common query pattern, you either change the shard key or build a secondary index layer (Part 7).

Part 5: The Hot Spot Problem

Even with perfect hash distribution of writes, hot spots can emerge at the application level — when a specific key receives disproportionate traffic compared to average.

The celebrity problem

Your social media platform has 500 million users. You've hash-partitioned your posts table on post_id. Hash distribution is perfect — each shard receives roughly equal inserts.

A celebrity with 100 million followers posts a photo. That post lands on, say, Shard 3. Over the next hour, 100 million users open their feeds and fetch that post. Shard 3 receives 100 million read requests. Shards 1, 2, 4 receive roughly 100 million / 4 = 25 million read requests each. Shard 3 is taking 4× the read load of every other shard.

Hash partitioning distributed writes uniformly. It couldn't distribute reads uniformly because reads are not uniformly distributed — they're skewed by the popularity of the content. The hash function has no knowledge of the access frequency of the data it's distributing.

The solution for reads: caching (Chapter 9). The celebrity's post, once cached at the application tier or in a CDN, stops hitting Shard 3 at all. The shard only receives the first request per cache instance. This is why caching is the correct tool for hot read keys — sharding cannot solve a hot read problem without degrading into "shard the shard."

The solution for writes: if a single key is receiving enormous write load (e.g., a real-time leaderboard for a viral game event), you can add artificial entropy to the key — post_id + random_suffix(1..100) — distributing one logical key across 100 physical keys and 100 shard locations. Reads must then query all 100 keys and aggregate. This is the "write spreading" pattern used by systems like Cassandra's counter implementation.

The sequential key problem and UUID as solution

If your shard key is assigned sequentially — auto-increment integers, or anything with temporal monotonicity — hash partitioning distributes them uniformly (hash(1), hash(2), ... are all different), so sequential keys are fine for hash partitioning.

But sequential keys cause a different problem you should know: index hot spots at the B-Tree leaf level. In Chapter 2 you learned that B-Tree leaf nodes are sorted by key. Sequential inserts always go to the rightmost leaf node. That leaf node becomes a contention point — multiple concurrent writers try to insert into the same leaf page simultaneously, causing lock contention at the page level even within a single shard.

The solution is UUIDs (random 128-bit identifiers) or snowflake-style IDs (timestamp + machine ID + random bits, providing rough time-ordering with sufficient entropy to avoid leaf hotspots). Random UUIDs distribute inserts across B-Tree leaf pages uniformly, eliminating the right-side hotspot. The cost: slightly worse page utilisation (random inserts can't pack pages as densely as sequential ones) and slightly worse cache locality for range scans. For most high-write systems, this cost is worth paying.

Before moving on, break it yourself. Each tab asks you to predict first — the celebrity post, the viral counter, and the timestamp key are the three hot spots this chapter just described.

Part 6: Cross-Shard Operations — What You Sign Up For

When you shard a database, you're trading the simplicity of a single machine for the complexity of distributed operations. Every operation that was simple on one machine becomes harder, more expensive, or impossible across shards. These are not edge cases — they are the central operational burden of a sharded system.

Cross-shard joins

On a single machine, joining two tables is a matter of the query planner reading pages from both tables and finding matching rows — one machine, one buffer pool, no network.

After sharding, consider a system where orders is sharded on order_id (hash) and users is sharded on user_id (hash). A query that needs both order data and user data — say, "fetch order 12345 with the customer's name and email" — must:

1. Route to the orders shard where order 12345 lives: fetch order data including user_id = 42.
2. Route to the users shard where user 42 lives: fetch user data.
3. Merge in the application.

That's two network round trips where there used to be zero. At scale — thousands of such queries per second — those round trips add up.

The mitigation: co-location. If you shard both orders and users on user_id (rather than their respective primary keys), then user 42's record and all of user 42's orders are on the same shard — determined by hash(42) % N. The join is local. The application sends one request to the shard for user_id = 42 and gets back both the user data and the orders in one round trip.

Co-location is a deliberate design choice that shapes your shard key selection. The question to ask: which entities are most commonly queried together? Shard them on the same key so they end up on the same shard. For a multi-tenant SaaS product, the answer is often tenant_id or account_id — shard everything on the tenant, so all of one customer's data is co-located.

Cross-shard transactions

Suppose your checkout flow must atomically: decrement inventory (inventory shard), create the order (orders shard), and create a payment record (payments shard). These three operations span three shards. You need all-or-nothing semantics — if payment fails, the order should not be created and inventory should not be decremented.

On a single machine, this is a database transaction. Across shards, it requires a distributed transaction — a protocol where multiple independent databases coordinate to either all commit or all abort. The standard protocol is two-phase commit (2PC), which we'll examine in Chapter 13. The short version: 2PC is correct but slow and brittle — the coordinator can fail, leaving participants in a blocked state waiting for a decision that never comes.

The alternative is the Saga pattern: break the transaction into a sequence of local transactions with compensating actions. If the payment step fails, the order creation is compensated (cancelled) and the inventory decrement is compensated (reversed). Each step is a local transaction — no distributed coordination. The downside: your system can observe intermediate states (the order briefly exists without a payment), and compensating actions must be idempotent. We'll cover this in Chapter 13.

The practical implication for shard key design: minimise the need for cross-shard transactions by ensuring the operations that most need atomicity are co-located. If you're building an e-commerce platform, keeping orders and inventory on the same shard (by product category, by warehouse region) might be worth more than optimal write distribution.

Scatter-gather queries

You already know these from hash partitioning. The additional point worth making: in production, scatter-gather queries are often invisible until they cause an incident. A query that was fast on a 4-shard system degrades gracefully as you add shards — until suddenly you have 32 shards and what used to be a 20 ms query now takes 200 ms because the merge phase is collecting and sorting 32 partial result sets, and the tail latency of 32 parallel requests is much worse than 4.

Instrument scatter-gather queries explicitly. Count them, measure their latency separately from single-shard queries. Make them visible. They tend to be the first things that break when you scale the shard count.

Part 7: Secondary Indexes Across Shards

Chapter 3 covered secondary indexes on a single machine — a B-Tree on a non-primary-key column enabling efficient lookup. On a sharded database, secondary indexes become a distributed systems problem.

Suppose your users table is sharded on user_id (hash). A user can look up their account by email. email is not the shard key — you can't hash on email to find the shard, because you'd need to know the user_id to compute the shard. So how do you find the user by email?

Local secondary indexes (scatter-gather approach)

Each shard maintains its own B-Tree index on email for the users that live on that shard. A query WHERE email = 'alice@example.com' must query all shards simultaneously and take the result from whichever shard returns a row.

This is a scatter-gather query. It queries all shards, waits for all to respond, and takes the non-empty result. For a table with 8 shards, it's 8 parallel index reads instead of 1. The latency is bounded by the slowest shard, not the average.

At small shard counts (4–8 shards), this is often acceptable. At large shard counts (100+ shards), querying all shards for a single email lookup is prohibitively expensive.

Global secondary indexes (index-then-fetch approach)

An alternative: maintain a separate, dedicated index database that maps non-shard-key values to shard locations.

Global index database:
email → (shard_id, user_id)
'alice@example.com' → (3, 42)
'bob@example.com'   → (1, 17)
...

A query WHERE email = 'alice@example.com':

1. Query the global index: returns (shard_id=3, user_id=42).
2. Query Shard 3 for user_id=42: returns the full row.

Two hops instead of N hops. Much better at large shard counts.

The cost: the global index is a bottleneck. Every write to the users table must also update the global index (one more database write, one more network hop). The global index must be highly available — if it goes down, email-based lookups fail entirely. And the global index must be kept consistent with the shards: if a write to the shard succeeds but the global index update fails, the index is stale.

DynamoDB's Global Secondary Indexes work approximately this way: a GSI is a separate partition of the data with a different partition key and sort key. Writes to the base table trigger asynchronous updates to the GSI. The GSI is eventually consistent — a write that just landed on the base table may not yet appear in the GSI. This is the consistency vs latency trade-off from Chapter 6 manifesting at the index level.

The practical decision for a SaaS product: if you have a handful of secondary lookup patterns (login by email, lookup by phone number, lookup by external ID), build a separate lookup table or global index for each. Keep it simple — a small Postgres table mapping email → user_id is often sufficient, even if the main users table is sharded. Only invest in a full global index infrastructure when the lookup volume requires it.

Part 8: Rebalancing — The Pain of Changing the Shard Count

Your system starts with 4 shards. Traffic grows. You need 8 shards. What happens to the existing data?

With naive hash partitioning (shard = hash(key) % N): changing N from 4 to 8 changes the mapping for roughly half of all keys. Every key where hash(key) % 4 ≠ hash(key) % 8 (which is most of them) now maps to a different shard.

Before (N=4):  hash(user_42) % 4 = 2  → Shard 2
After  (N=8):  hash(user_42) % 8 = 6  → Shard 6

User 42's record must move from Shard 2 to Shard 6. And so must roughly half of all other records. For a database with 10 TB of data across 4 shards (2.5 TB per shard), adding 4 new shards means moving roughly 5 TB of data around the network. At 1 Gbps inter-shard bandwidth, that's over 11 hours of data migration — during which your routing layer must serve the dual state: some data has moved, some hasn't yet.

This is the rebalancing problem, and it's the reason consistent hashing exists. Chapter 8 is entirely about consistent hashing: an algorithm that allows nodes to be added or removed while moving only a minimal fraction of keys (~1/N of total keys when adding a node), instead of half. The algorithm is used in DynamoDB, Cassandra, Riak, and most distributed cache systems. It's the answer to the question this chapter raises but deliberately leaves open.

For now: understand that naive hash % N makes rebalancing catastrophically expensive, and that real distributed systems use smarter approaches to avoid it. If you're designing a system today and you need to shard, use a database that handles rebalancing for you (CockroachDB, Spanner, Vitess) rather than implementing it yourself.

Part 9: Choosing a Shard Key — The Most Important Decision

The shard key is the most consequential schema decision you will make. It is effectively permanent — changing the shard key after deployment requires re-sharding the entire dataset, which is a major migration. Choose with care.

A good shard key satisfies as many of these properties as possible:

High cardinality. The key must have many distinct values — at minimum, significantly more than the number of shards. A boolean column has 2 values; it can serve at most 2 shards. A user_id column with millions of values can serve any number of shards.

Uniform distribution. Values should be evenly distributed, not skewed. If 60% of your users are in the US and you shard by country, the US shard will be 6× larger than average. user_id or a UUID are much more uniformly distributed.

Query alignment. Your most common queries should include a filter on the shard key. If 80% of queries say WHERE user_id = X, shard on user_id. These queries go to exactly one shard. If the shard key is rarely in your WHERE clauses, most queries become scatter-gather.

Co-location of related data. Entities that are always queried together or that need atomic operations together should share the same shard key value so they end up on the same shard. In a multi-tenant SaaS product, sharding on tenant_id ensures all of one customer's data is co-located, enabling single-shard transactions for all intra-tenant operations.

Low write skew. The key should not cause some shards to receive far more writes than others. Sequential keys with range partitioning cause write skew (all new writes go to the last shard). Random or well-distributed keys with hash partitioning avoid it.

Classic shard key patterns

User ID (or tenant ID): The most common choice for user-facing applications. All data for a given user (or tenant) co-locates on one shard. Queries filtered by user go to one shard. Cross-user operations scatter, but most interactive user-facing queries are single-user. Multi-tenant SaaS products almost always shard on tenant ID.

Geographic region: Data for users in a specific region is co-located. Useful when legal data-residency requirements mandate that EU user data stays in EU infrastructure. The risk: uneven growth across regions causes shard imbalance.

Time range (for time-series/logging systems): All events in a time window go to one shard. Enables fast purging of old data (drop the old-time shard). The write hot spot (all new events go to the current-time shard) is usually acceptable for logging systems because the old shards serve reads while the current shard handles writes — and you can have dedicated write capacity for the current shard.

Hash of the primary entity (DynamoDB style): Hash the primary entity identifier. DynamoDB calls this the "partition key." All items with the same partition key value are co-located (on the same partition). Within a partition, items are sorted by the "sort key" — enabling range queries within a partition. This gives you the best of both: write distribution across partitions, range queries within a partition.

What to avoid

Low-cardinality columns. Boolean, status enum (pending/completed/failed), country. Fine as secondary filters but disastrous as shard keys.

Sequential integers (with range partitioning). All new inserts to the last shard. Useless for write scale.

Columns that change. If the shard key value changes after insert, the row must move to a different shard. This is a re-sharding operation in miniature, repeated for every such update. Avoid shard keys on mutable fields.

Columns not in your queries. If you shard on a column that doesn't appear in your WHERE clauses, you've made every query a scatter-gather. The shard key must be in your queries.

Part 10: Postgres Table Partitioning vs Sharding — Don't Confuse Them

Postgres 10 introduced native table partitioning with CREATE TABLE ... PARTITION BY RANGE/LIST/HASH. This syntax looks similar to sharding but is architecturally different and solves a different problem.

Postgres table partitioning splits a table's rows into multiple physical files (partition tables) on the same server. It does not distribute data across multiple machines. A partitioned table on one Postgres instance with 8 partitions is still one Postgres instance with one WAL, one buffer pool, one connection limit, one write bottleneck.

What Postgres partitioning does well:

• Partition pruning: a query WHERE created_at > '2024-01-01' on a table partitioned by created_at only scans the relevant partitions. Effectively the same as an index for range predicates on the partition key.
• Fast partition drops: DROP TABLE orders_2022 deletes all 2022 orders in milliseconds. No slow DELETE + VACUUM.
• Parallel query across partitions: Postgres can query multiple partitions in parallel using multiple workers.
• Partition-level operations: vacuum, index creation, and backup can target individual partitions.

Postgres partitioning is the right tool for: large time-series tables where old data is periodically dropped, tables where partition pruning eliminates most scans, any table that benefits from partition-level maintenance.

It is not the right tool for: exceeding a single machine's write throughput, storing more data than a single machine can hold, providing fault isolation between portions of the dataset.

The mental model: Postgres partitioning is a performance and maintenance feature. Sharding is a scale and architecture feature. Use Postgres partitioning before considering sharding — it gives you many of the query performance benefits with none of the distributed systems complexity.

-- Postgres native partitioning by time range
CREATE TABLE events (
    id          bigint,
    user_id     bigint,
    event_type  text,
    created_at  timestamptz
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Drop old data instantly:
DROP TABLE events_2022_q1;

Vocabulary Map

Practice Problems

Problem 1 — Failure Analysis

Your team shards the messages table across 8 shards using hash(conversation_id) % 8. Six months later, you add 8 more shards for a total of 16. Calculate: what percentage of existing rows need to move to a different shard? What is the minimum amount of data that must migrate if the table is 4 TB? At an inter-shard transfer rate of 500 MB/s, how long does the migration take? During the migration, how do you serve queries — rows might be on their old shard or their new shard? Describe the dual-routing logic required and the consistency guarantees it provides.

Problem 2 — Design from Scratch

You are designing the database sharding strategy for a multi-tenant project management SaaS (like Linear or Asana). The product has these entities: tenants, projects, tasks, comments, users, attachments. The most common queries are:

• Load all tasks for a project: WHERE project_id = X
• Load a user's assigned tasks across all projects: WHERE assignee_id = X
• Load all comments for a task: WHERE task_id = X
• Admin report: count of tasks per tenant in the last 30 days

Choose a shard key for this system. Justify it by tracing each of the four query patterns above through your sharding scheme: does it go to one shard or scatter? Which queries are now scatter-gather and why is that acceptable? Which entity co-location decisions follow from your shard key choice? What is the one query that your shard key design makes most painful, and how would you handle it?

Problem 3 — Synthesis

Connect range partitioning, the sequential key hot spot, and Chapter 5's WAL mechanics.

Your analytics event logging system receives 500,000 events per second and is range-partitioned by event_timestamp. The system runs on 10 shards. You notice that Shard 10 (the current time range) has 10× the write latency of Shards 1–9.

Trace the physical chain: what is happening at the WAL level on Shard 10 that isn't happening on the other shards? Why does write latency grow as WAL write rate increases (Chapter 5 covered WAL group commit — apply that here)? Then: propose two architectural solutions. One that changes only the partitioning strategy. One that keeps range partitioning but redesigns the write path to distribute load within the current time range.

Problem 4 — Go Deeper

The chapter described two secondary index approaches: local (scatter-gather reads) and global (two round trips, consistency complexity). There is a third approach used by some systems: index on the base table itself, denormalized.

Instead of a separate index database, you store the email → user_id mapping as an additional row in the same sharded users table, with a different shard key. DynamoDB calls this the "adjacency list pattern." For example: shard on lookup_key where for a user, lookup_key = user_id (the main record) or lookup_key = 'email:' + email (the lookup record). Both rows live in the same table but potentially on different shards.

From first principles: what are the advantages of this approach over a separate global index database? What consistency challenges does it introduce? Under what failure scenario does this design return incorrect results? How is this similar to and different from a hash index at the database level (Chapter 3)?

Problem 5 — Connect to Previous Chapters

Chapter 3 introduced composite indexes with the rule "equality before range, most selective column first." Chapter 6 introduced the concept that consistency is a spectrum.

Apply both to sharding: your orders table is sharded on (tenant_id, order_id) — a compound shard key where rows are co-located by tenant and then range-ordered by order_id within each tenant's partition.

First, trace the physical B-Tree structure on a single shard: the shard receives all orders for tenants 1–500 (range partition on tenant_id). Within the shard, describe how the compound key creates a multi-level sort order in the heap and indexes, and what query patterns this enables (connect Chapter 3's composite index rules to the compound shard key).

Second: a customer reports that after they create an order, their order history page sometimes doesn't show the new order. Using Chapter 6's consistency vocabulary, identify which consistency level this symptom indicates is being violated, and trace the specific distributed systems path — from the write on the primary through the routing layer to the replica — where the stale read is occurring.

What's Coming Next

You now understand the two fundamental partitioning schemes and their trade-offs. The rebalancing problem — naive hash(key)%N moves most data when nodes change — is left deliberately open. Chapter 8 closes it with consistent hashing: the algorithm that enables distributed systems to add and remove nodes while moving the minimum possible data. Consistent hashing is not just an academic curiosity; it is the core data placement algorithm in DynamoDB, Apache Cassandra, Riak, and most distributed cache systems. After Chapter 8, you'll understand why those systems can scale horizontally without the painful data migrations that naive hash partitioning requires.

Chapter 7 complete. Problem 2 is the most valuable for interview preparation — it's a complete system design decision with full justification. Work through it for a multi-tenant SaaS product you've actually built or used. Problem 1 makes the rebalancing pain concrete with real numbers — do the math before moving to Chapter 8, so consistent hashing feels like a relief rather than a complication.