Course contentsShow
← System Design
Lesson 238 of 1,9197. Database Scaling: ShardingFree lesson

Query Limitations in Sharded Systems

Understanding which queries become expensive or impossible: cross-shard joins, aggregations, and transactions.

Query Limitations in Sharded Systems

What you'll learn: You'll understand which common database operations become expensive or even impossible after sharding your data.

The Sharding Trade-Off

When you distribute data across multiple shards (each holding a subset of your records), you gain scalability—but you lose some query flexibility. Operations that were simple in a single database can become complex or prohibitively expensive across shards.

Three Major Query Challenges

Cross-Shard Joins
Imagine your users table is sharded by user_id, and your orders table is sharded by order_id. A query joining users with their orders might require data from multiple shards on both sides. The database can't execute a single efficient join—it must gather data from multiple machines, ship it somewhere, and combine it. This is slow and resource-intensive.

Aggregations Across Shards
Simple counts, sums, or averages become multi-step operations. To count all orders, you must query each shard individually, then sum the results. Complex aggregations (like GROUP BY on non-shard-key columns) require gathering vast amounts of data from all shards for processing elsewhere.

Multi-Shard Transactions
Transactions touching data on multiple shards require distributed transaction protocols (like two-phase commit), which are complex, slow, and can fail in ways that impact availability. Many sharded systems simply don't support them.

Working Around Limitations

Smart shard key selection helps: if you shard orders by user_id instead of order_id, all one user's orders live together, making user-specific queries efficient. You might also denormalize data, duplicate information across shards, or handle aggregations at the application level.

Key Takeaway: Sharding trades query flexibility for scalability—cross-shard joins, aggregations, and transactions become expensive or impossible, requiring careful data modeling and application-level workarounds.