What Sharding Takes Away: Joins, Transactions, Unique Constraints, and IDs
A single database gives you joins, transactions, unique constraints, and auto-increment IDs because one engine sees every row. Sharding replaces it with many engines that each see a slice. Here is exactly why each feature breaks, what replaces it, and the rule that saves most designs.
The sharding post lists four things that stop working across shards: joins, transactions, unique constraints, and auto-increment IDs. Most candidates can recite that list. Far fewer can say why, and the why is what lets you predict the fourth, fifth, and sixth thing that breaks without having memorized them.
The reason is one sentence. A single database gives you those features because one engine sees every row and controls every change: one query planner, one transaction log, one index per constraint, one counter. Sharding replaces that engine with many engines, each of which sees only its own slice and knows nothing about the others. Every feature that needed the whole picture is gone, and you rebuild it yourself, at a price.
Where This Shows Up
- “You sharded by user ID. How do you join orders to users now?”
- “A transfer moves money between two users on different shards. What happens if one shard fails mid-transfer?”
- “How do you keep emails unique?”
- “Where do your IDs come from?”
- “What do you lose by sharding?”
- “Why not use a distributed SQL database and get all of this back?”
What They Are Really Checking
- Do you know the mechanism, not just the list? “We lose joins” is a fact. “No single engine holds both rows, so the database cannot do the join” is an understanding, and it lets you reason about cases you have not seen.
- Can you keep the features by choosing keys? Most of the loss is avoidable. Shard related tables by the same key and their joins and transactions stay local. The interviewer wants to see that you reach for this before reaching for distributed machinery.
- Do you know the price of each replacement? Two-phase commit, sagas, global indexes, and ID schemes all work. Each costs latency, availability, or consistency, and the strong answer names which.
- Do you know which constraints survive? A unique constraint that includes the shard key still works. One that does not, does not. That one rule answers half the follow-ups.
The Gotchas
Gotcha 1: Reciting the list without the reason. “Sharding loses joins and transactions” invites “why?”, and a candidate without the one-engine explanation has nothing to say next. Lead with the mechanism; the list follows from it.
Gotcha 2: Sharding related tables by their own primary keys. users by user_id, orders by order_id. Every table gets split, so a user’s orders scatter across every shard and the most common join in the system now touches all of them. Shard orders by its user_id column and the join is local again.
Gotcha 3: Assuming per-shard transactions add up to a global one. Each shard commits atomically. Two shards committing atomically is not one atomic commit. The failure between them is where money disappears.
Gotcha 4: Assuming a unique constraint still holds. It holds per shard. Unless the constraint includes the shard key, two shards can each accept the same value. Most candidates do not notice until the duplicate account report arrives.
Gotcha 5: Reaching for two-phase commit by default. It works, but it holds locks across network round trips, and a coordinator failure can leave participants blocked. Co-locate first, use a saga for business processes, and keep two-phase commit for the rare case that needs it and a database that does it well.
Gotcha 6: Joining in application code one row at a time. Fetching 50 orders and then making 50 separate calls for their products is the N+1 query pattern across a network. Batch the second lookup into one multi-get per shard.
Gotcha 7: Per-shard auto-increment, or the offset trick. Separate counters collide. Giving shard 1 the odd numbers and shard 2 the even numbers works until you add a third shard, at which point the scheme has to be redesigned under load.
Gotcha 8: Forgetting that reads across shards are not a snapshot. A query that reads shard 1 and then shard 4 sees each at a slightly different moment. A report can show money that has left one account and not yet arrived in the other.
Gotcha 9: Treating distributed SQL as free. Spanner, CockroachDB, and YugabyteDB give all four features back across shards by doing the work in this post for you. A cross-shard transaction still costs tens of milliseconds instead of one. The database hides the mechanism, not the latency.
How to Answer
The picture
1
2
3
4
5
6
7
8
9
10
One database: Sharded:
┌───────────────────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
│ one engine │ │ engine 1 │ │ engine 2 │ │ engine 3 │
│ sees ALL rows │ │ users │ │ users │ │ users │
│ one query planner │ │ A to H │ │ I to P │ │ Q to Z │
│ one transaction log │ │ own log │ │ own log │ │ own log │
│ one index per UNIQUE │ │ own index│ │ own index│ │ own index│
│ one ID counter │ │ own ctr │ │ own ctr │ │ own ctr │
└───────────────────────┘ └──────────┘ └──────────┘ └──────────┘
nobody sees everything
Each of the four features depends on one item in the left box. Remove the single item, and the feature goes with it.
| Feature | Depends on | After sharding |
|---|---|---|
| Joins | One query planner that can read every table | Each planner reads only its own rows |
| Transactions | One log, where one commit record makes all changes happen or none | Each shard has its own log; nothing records both halves |
| Unique constraints | One index covering every row | Each shard’s index covers only its own rows |
| Auto-increment IDs | One counter | One counter per shard, all starting at 1 |
Joins
How they worked. SELECT ... FROM orders JOIN users ON orders.user_id = users.user_id runs inside one engine. The planner reads both tables from local memory or disk, matches rows with a hash or nested-loop join, and returns the result in microseconds per row.
Why they break. Every large table is split across the shards, so each shard holds a piece of users and a piece of orders. Which piece depends on each table’s shard key. Shard users by user_id and orders by order_id:
1
2
3
4
User 42 → hash(42) % 4 = shard 2
Order 9001 (user 42) → hash(9001) % 4 = shard 4
Order 9002 (user 42) → hash(9002) % 4 = shard 1
Order 9003 (user 42) → hash(9003) % 4 = shard 2
User 42’s orders landed wherever their own IDs hashed. No engine holds the user row and all the matching orders, and shard 2’s engine cannot read shard 4’s disk. The database cannot perform the join.
What replaces it.
| Fix | How | Cost |
|---|---|---|
| Co-location | Shard orders by its user_id column, so a user and all their orders hash to the same shard |
None for joins within one user; queries across users still scatter |
| Reference tables | Copy small, shared tables (countries, currencies, categories) whole to every shard | Writes to them go to every shard; fine because they rarely change |
| Denormalize | Copy the few fields you need, such as product_name onto the order row |
The copies must be updated when the source changes |
| Join in the application | Fetch from one shard, then batch-fetch the related rows from the others, and combine in code | Extra round trips; you write and maintain the join |
| Read model | Stream all shards into a warehouse or search index that can join everything | Eventually consistent; another system to run |
Co-location is the fix that matters. Choose one entity that most data belongs to (the user, the tenant, the account), give every table that belongs to it the same shard key, and the joins your product runs most stay local.
Transactions
How they worked. One engine has one write-ahead log (WAL) and one lock manager. “Debit A, credit B” writes both changes to the log, then one commit record. The commit record is the moment both changes become real. If the machine crashes before it, recovery discards both; after it, recovery keeps both. There is no state in which only one happened.
Why they break. Put A on shard 1 and B on shard 4. Each shard can commit its half atomically, but there are now two logs and two commit records, and nothing ties them together.
sequenceDiagram
participant App
participant S1 as Shard 1 (account A)
participant S4 as Shard 4 (account B)
App->>S1: BEGIN, debit A by 100, COMMIT
S1-->>App: committed
App->>S4: BEGIN, credit B by 100, COMMIT
Note over S4: crashes before commit
S4--xApp: no response
Note over S1,S4: A is 100 lower, B is unchanged.<br/>Each shard is internally consistent.<br/>Nothing records that the transfer is half done.
Isolation breaks too. A report that reads shard 1 and then shard 4 between the two commits sees the debit without the credit, because there is no single snapshot of both shards.
What replaces it.
| Fix | How | Cost |
|---|---|---|
| Co-location | Choose keys so operations that must be atomic touch one shard (both sides of a transfer belong to one account group, an order and its line items share the order’s key) | Constrains the data model; not always possible |
| Two-phase commit (2PC) | A coordinator asks every shard to prepare (write and lock, promise to commit), then tells all of them to commit only if all said yes | At least two round trips plus disk syncs per transaction; locks held throughout; a coordinator failure can leave shards blocked holding locks |
| Saga | Commit each step locally; if a later step fails, run compensating steps that undo the earlier ones | No atomicity or isolation: intermediate states are visible, and compensations must be designed and retried. See sagas and the outbox |
| Distributed SQL | The database runs 2PC over replicated groups for you (Spanner, CockroachDB) | It works; cross-shard transactions cost tens of milliseconds |
The order of preference is the order of the table. If the core operations of a system cannot be co-located by any key, that is a sign the data model needs rethinking before the transaction protocol does.
Unique constraints
How they worked. UNIQUE(email) is one B-tree index over every row. An insert checks the index under a lock and fails if the value exists. Two concurrent inserts of the same email serialize on that index entry, and exactly one wins.
Why they break. Shard users by user_id. Two people sign up with mike@example.com at the same moment and get user IDs that hash to shards 3 and 7. Shard 3 checks its own email index and finds nothing. Shard 7 checks its own and finds nothing. Both inserts succeed. Each constraint is correct; neither covers the other shard.
The rule. A unique constraint is still enforced after sharding if and only if it includes the shard key, because then every row that could conflict lives on the same shard.
| Constraint | Shard key | Enforced? | Why |
|---|---|---|---|
UNIQUE(short_code) |
short_code |
Yes | Equal codes hash to the same shard |
UNIQUE(user_id, device_id) |
user_id |
Yes | Conflicting rows share a user_id, so they share a shard |
UNIQUE(email) |
user_id |
No | Equal emails can belong to different user IDs on different shards |
UNIQUE(order_id) |
user_id |
No, unless IDs are globally unique by construction | Two shards could issue the same order ID |
What replaces it. Make the check local again by giving it its own key. A user_emails table sharded by email, with an ordinary unique constraint. Two inserts of the same email hash to the same shard, and that shard’s index picks the winner. On signup, reserve the email first and create the user second; if the second write fails, a cleanup job releases the reservation. The reverse order can leave a user who exists but owns no email. The URL shortener post names this as its biggest risk: the unique constraint on the code column is load-bearing, and sharding by anything else loses it.
Auto-increment IDs
How they worked. One engine holds one sequence. Each insert takes the next number under a tiny lock: 1, 2, 3. Never a repeat.
Why they break. Each shard has its own sequence, and every sequence starts at 1. Shard 1 issues order 1001 and shard 2 issues order 1001. Two different orders, one ID. Every reference to “order 1001” in an email, a log, or another service is now ambiguous.
What replaces it.
| Scheme | How | Good | Bad |
|---|---|---|---|
| Snowflake-style | 64 bits: about 41 of timestamp in milliseconds, 10 of machine number, 12 of per-machine sequence | No coordination; roughly time-ordered; fits in a BIGINT |
Machine numbers must be assigned uniquely; clock going backwards must be handled |
| Shard in the ID | Like Snowflake, with the logical shard number in the middle bits (Instagram’s scheme) | The shard can be read from the ID, so “find order by ID” routes without an index | The record can never move to another logical shard without changing its ID |
| UUID | 128 bits, random (version 4) or time-ordered (version 7) | No coordination at all | Twice the size of a BIGINT; random version 4 IDs scatter inserts across the index |
| Range allocation | A central service hands each shard a block (1 to 10,000, then 10,001 to 20,000) | Short, dense numbers | The allocator must be highly available; IDs are not time-ordered across shards |
| Odd and even offsets | Shard 1 takes odd numbers, shard 2 takes even | Trivial with two shards | Fixes the shard count; adding a shard means redesigning the scheme |
Shard in the ID is worth calling out in an interview, because it solves a second problem for free. If orders are sharded by user_id, a lookup by order ID alone does not know which shard to ask. An ID that carries its logical shard number routes itself.
The ones nobody lists
The same mechanism takes away more than four things. Naming one or two of these shows the reasoning, not the memorized list.
| Also lost | Why | What to do |
|---|---|---|
| Foreign keys | The referenced row may be on another shard, and no engine can check it | Enforce in the application, co-locate, or accept and clean up orphans |
Global ORDER BY ... LIMIT |
Each shard sorts only its own rows | Ask every shard for its top N and merge; deep pagination gets expensive, so page by cursor |
COUNT, SUM, GROUP BY over everything |
No engine sees all the rows | Scatter, compute partial aggregates per shard, combine; or precompute in a read model |
| Consistent backups and snapshots | Each shard is backed up at its own moment | Accept per-shard consistency, or use a database with a global clock or snapshot mechanism |
| One-step schema changes | Every shard runs its own migration | Migration tooling that applies to every shard and tolerates partial failure |
Saying it in an interview
“Sharding gives each shard its own engine, so anything that needed one engine to see everything is gone: joins across shards, atomic transactions across shards, unique constraints that do not include the shard key, and a single ID sequence. I would keep most of it by co-locating: every table that belongs to a user is sharded by
user_id, so the joins and transactions the product runs stay on one shard. Uniqueness on email gets its own table sharded by email. IDs are Snowflake-style with the logical shard number embedded, so an order ID routes itself. The one cross-user operation, transfers, runs as a saga with a compensating refund.”
Follow-Up Questions to Expect
- “Why not just use two-phase commit everywhere?” It holds locks for at least two round trips, so contended rows serialize at network speed rather than memory speed, and a coordinator failure between prepare and commit leaves participants blocked holding those locks. It also requires every participant to support the protocol, which most brokers and external APIs do not. Use it rarely, where the database implements it well.
- “How do you look up an order by ID if orders are sharded by user?” Embed the logical shard number in the order ID so it routes itself, or keep a global index from order ID to user ID. The first is free at read time; the second is an extra hop but works for IDs you did not generate.
- “What if a user needs to move to a different shard?” Move the whole logical shard, never one user, so the shard key and IDs stay valid. If a single tenant must move on its own, a directory that maps tenant to shard makes that possible at the cost of a lookup on every request.
- “Does distributed SQL make this go away?” It makes the features available, not free. Spanner and CockroachDB run consensus and two-phase commit underneath, so a cross-shard transaction costs tens of milliseconds. Co-location still decides whether your hot path pays that cost.
- “Can you have a foreign key from orders to products if they are sharded differently?” Not enforced by the database. Validate in the application at write time, and make the read path tolerate a missing product (show “no longer available”) rather than fail.
- “Which of the four is hardest to live without?” Usually transactions, because the failure is silent: every shard looks healthy while the system as a whole is wrong. Joins and IDs fail loudly; a missing unique constraint fails slowly, as duplicates.
Key Takeaways
- One engine seeing every row is what gives you joins, transactions, unique constraints, and auto-increment IDs. Sharding replaces it with engines that each see a slice.
- Every large table is split. Joins break when related tables are split by different keys; shard them by the same key and the join is local again.
- Per-shard atomic commits do not add up to a global one. Co-locate first, use a saga second, and two-phase commit last.
- A unique constraint survives sharding only if it includes the shard key. Otherwise give the unique value its own table, sharded by that value.
- Replace auto-increment with Snowflake-style IDs, UUIDs, or allocated ranges. Embedding the logical shard number in the ID makes lookups by ID route themselves.
- The same mechanism also takes away foreign keys, global sorting and aggregates, and consistent snapshots. Naming one shows you understand the cause.
- Distributed SQL gives the features back by doing this work for you. It hides the mechanism, not the latency.
Further Reading
- How Would You Shard This Database?, the companion post: whether to shard, choosing the key, routing, and resharding
- Sagas and the Outbox Pattern, for what replaces a transaction that cannot be co-located
- Transaction Isolation Levels, for why uniqueness belongs in a constraint and never in application code
- Martin Kleppmann, Designing Data-Intensive Applications, chapter 6 (Partitioning) and chapter 9 (Consistency and Consensus, including two-phase commit)
- Pat Helland, Life Beyond Distributed Transactions, on designing entities so that transactions never need to cross them
- Corbett et al., “Spanner: Google’s Globally-Distributed Database” (OSDI 2012), for how a database provides cross-shard transactions and what it costs
- Instagram Engineering, “Sharding and IDs at Instagram” (2012), for IDs with the logical shard number embedded
- Flickr Engineering, “Ticket Servers: Distributed Unique Primary Keys on the Cheap” (2010), for range-style ID allocation with two database servers