Detailed explanation
Partitioning divides a large logical table into smaller physical parts according to a partition key, while queries usually continue to address the table by its original name. In the common interview comparison, table partitioning happens within one database, whereas sharding distributes subsets of data across independently hosted database instances. Both make large datasets more manageable, but they target different bottlenecks: partitioning mainly improves data access and maintenance, while sharding enables distributed storage and write capacity. More broadly, sharding is a form of horizontal partitioning, so the terms are related rather than mutually exclusive.
Table partitioning commonly uses range, list, or hash rules. With range partitioning on an order date, the database routes each inserted row to the appropriate partition. When a query contains a suitable filter, the optimiser may use partition pruning to exclude partitions that cannot contain matching rows. This reduces the data considered, but does not eliminate the need for indexes within relevant partitions. Partitioning also makes operations such as archiving or removing old data easier through partition-level maintenance where supported. Queries without useful partition-key predicates may still access every partition.
The supplied CREATE TABLE example uses MySQL-style syntax and partitions orders by YEAR(order_date). The p2025 partition accepts non-NULL years below 2026, not just 2025; p2026 accepts years from 2026 up to, but excluding, 2027. Without another partition, an order dated in 2027 has no matching partition and its insertion fails. A MAXVALUE partition can catch later dates. Partitioning syntax, supported expressions, and constraints differ across database engines, so this example should not be presented as portable SQL.
Sharding adds a routing layer, implemented by the application, middleware, or database platform, to select a shard using a key such as customer_id. Each shard holds only part of the dataset and can serve requests independently. The trade-offs include uneven load, cross-shard joins, distributed transactions, and rebalancing when capacity changes. Interviewers look for an explanation of these costs, not just a definition. Partitioning and sharding can also coexist: each customer shard might partition its orders locally by date.
Key points to remember
- Table partitioning splits a logical table into physical parts; sharding distributes data across database instances.
- Partition pruning skips irrelevant partitions when predicates and partition rules allow it.
- Partitioning helps maintenance and selective access; it does not automatically distribute writes across servers.
- Sharding scales storage and write capacity but introduces routing, coordination, and rebalancing costs.
- A shard can contain partitioned tables, and sharding is broadly a form of horizontal partitioning.
Common mistakes candidates make
- Claiming partitioning always makes queries faster or replaces indexes.
- Ignoring shard-key skew and the cost of cross-shard operations.
- Assuming the example's p2025 partition contains only 2025 data or that future dates are automatically accommodated.
Likely follow-up questions
How do you choose a partition key versus a shard key?
Choose a partition key that aligns with common filters and maintenance boundaries, such as order_date for retention. Choose a shard key that spreads data and traffic evenly while keeping frequently related operations on the same shard.
Does partitioning remove the need for indexes?
No: pruning selects which partitions to access, while indexes help locate rows within the relevant data. A query may need both to avoid scanning large partitions.
Why are cross-shard joins and transactions harder?
A cross-shard join may require fetching and combining data from multiple servers, adding network and coordination overhead. Atomic transactions across shards require distributed coordination where supported, so designs often try to keep transactional work within one shard.
Interview tip: Define both techniques, contrast their scaling goals, and use the orders example to explain pruning versus shard routing.