Sharding easy
Why are standard SQL Joins between two large tables often difficult or impossible in a sharded database environment?
- A Sharded databases are generally not compliant with the SQL standard.
- B Joining data located on different physical servers requires moving massive datasets over the network to a central coordinator node, resulting in poor performance.
- C The use of Joins in a sharded system is a well-known security vulnerability that leads to data leaks.
- D Joins require significantly more storage space than the sharded disks can provide.
Answer: B. Joining data located on different physical servers requires moving massive datasets over the network to a central coordinator node, resulting in poor performance.
On a single machine, a Join happens in high-speed RAM. In a sharded system, if the 'Users' table is on Shard 1 and the 'Orders' table is on Shard 2, the database must pull data from both servers across the network to combine them. This 'Distributed Join' is a significant performance killer. To minimize this, engineers often 'denormalize' data (storing the user's name directly in the order record) or ensure related data is sharded using the same key so it always lands on the same machine.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In an E-Commerce platform, why might you choose to shard the `Orders` table by `user_id` rather than the more obvious `order_id`?
- In the context of database scaling, what is the fundamental difference between 'Vertical Partitioning' and 'Horizontal Partitioning' (Sharding)?
- If Shard A fails in a large cluster, and its incoming traffic is automatically re-routed to Shard B, what is the risk of a 'Cascading Failure'?
- In a standard 'Modulo Sharding' setup (`key % N`), what is the primary consequence of adding a new server to the cluster (increasing N from 10 to 11)?
- From an engineering perspective, when should you choose 'Vertical Scaling' (a bigger server) over 'Horizontal Scaling' (Sharding)?
- Which sharding strategy is mathematically optimized for efficient 'Range Scans' (e.g., retrieving all records created between January and March)?