Cross-Shard Queries: The Hard Problem
Understand why cross-shard joins and transactions are the hardest problem in distributed databases, and the patterns that minimise them.
Cross-Shard Queries: The Hard Problem is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Sharding Tax
Sharding scales writes and capacity but makes queries that span shards painful: every cross-shard query is a fan-out.
Single-Shard Queries Are Easy
If your shard key is in the WHERE clause, the router sends one query to one shard:
-- shard_id = hash(user_id) % N
SELECT * FROM orders WHERE user_id = 42;
-- Router computes shard, sends one query, gets one result.Fan-Out Queries
Without the shard key, the router queries every shard and merges:
-- WHERE status = 'paid' — no user_id
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 100;
-- Must query all N shards, merge results, sort, take top 100.Fan-Out Aggregations
For COUNT/SUM/AVG: get partial results from each shard, then combine in the router or app:
-- On each shard:
SELECT COUNT(*), SUM(total) FROM orders;
-- Aggregator:
final_count = SUM(counts), final_sum = SUM(sums)
-- AVG is trickier — need SUM and COUNT, can't average averages.Cross-Shard JOINs
If both sides shard by the same key (collocated), the JOIN is shard-local. Otherwise it's essentially impossible at scale.
Reference Tables (Replicated Everywhere)
Small "lookup" tables are replicated to every shard, so JOINs to them stay local. Citus calls them "reference tables".
Avoiding Cross-Shard Patterns
Schema design tricks:
- Denormalise — duplicate parent data into the shard with the child
- Use the shard key everywhere — even when "unnecessary"
- Pre-compute reports in a separate analytics DB
Pagination Across Shards
OFFSET 1000 LIMIT 10 over many shards is awful — every shard must produce 1010 rows. Use keyset pagination instead.
Distributed Transactions
Two-phase commit (2PC) coordinates atomic commits across shards. Slow, fragile under partition. In practice, design for "saga" patterns:
// Saga pattern (conceptual):
// 1. Local TX on shard A: mark as pending, log
// 2. RPC to shard B: do its part
// 3. Local TX on shard A: mark as committed
// 4. On failure: compensating transactionsHot Shards
A celebrity user, a viral product — concentrated traffic on one shard kills your scaling story. Detect and split (sub-sharding) or move.
Cross-Shard Foreign Keys
RDBMS FKs don't span shards. You enforce referential integrity in the app or accept eventual consistency for cross-shard relationships.
Recap
Sharding shifts the difficulty from "scale writes" to "shape queries to stay shard-local".
- Single-shard queries are fast
- Fan-out is slow
- Denormalise to keep work local
- Reference tables for shared dims
- Sagas instead of 2PC
Quick Check
Why is a SELECT without the shard key in WHERE expensive on a sharded database?
Frequently asked questions
Is the “Cross-Shard Queries: The Hard Problem” lesson free?
Yes — the full text of “Cross-Shard Queries: The Hard Problem” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Cross-Shard Queries: The Hard Problem”?
Understand why cross-shard joins and transactions are the hardest problem in distributed databases, and the patterns that minimise them. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Cross-Shard Queries: The Hard Problem” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Sharding Strategies: Range, Hash, Directory
- Cross-Shard Queries: The Hard Problem
- Citus and Distributed Postgres
- When NOT to Shard