Partitioning vs. Sharding

📋 Overview

While often used interchangeably, Partitioning and Sharding represent different levels of data distribution. Partitioning is the process of splitting a dataset into smaller, logical segments within a single database instance. Sharding is a specific type of horizontal partitioning where those segments are distributed across multiple independent database servers (nodes). Both are essential for scaling beyond the limits of a single machine.

---

🏗️ Core Principles & Characteristics

1. Partitioning (Local)

• Vertical Partitioning: Splitting a table by columns (e.g., putting large BLOBs in a separate table).
• Horizontal Partitioning: Splitting a table by rows (e.g., by date range).
• Logical View: The application typically interacts with a single logical table; the DB engine handles the routing to physical partitions.

2. Sharding (Distributed)

• Shard Key: The attribute (e.g., user_id) used to determine which shard a specific row belongs to.
• Shared-Nothing Architecture: Each shard is an independent database instance with its own CPU, RAM, and Disk.
• Shard Router: An intermediate layer (or client logic) that routes queries to the correct physical node.

---

⚖️ Trade-offs: Pros & Cons

Partitioning

• Pros: Improves query performance (Partition Pruning); easier maintenance (e.g., dropping an old month's partition instantly).
• Cons: Limited by the resources (CPU/RAM) of a single server; doesn't provide horizontal scalability.

Sharding

• Pros: Unlimited horizontal scaling; isolates failures (if one shard goes down, only a subset of users is affected).
• Cons: Extreme complexity; cross-shard joins are nearly impossible or very slow; "Hot Shard" problem if the shard key is poorly chosen.

---

🌍 Real-World Implementation

• PostgreSQL Declarative Partitioning: Used for managing multi-terabyte tables by splitting them by created_at ranges.
• Vitess (YouTube): A massive sharding layer built on top of MySQL to handle billions of users.
• MongoDB: Native support for sharding using a "Shard Key" and "Mongos" routers.
• Instagram: Famously sharded their Postgres database by mapping user_id to logical shards, which are then mapped to physical servers.

---

💡 Interview "Gotchas" & Tips

• The "Shard Key" is Everything: If you choose a key like timestamp, all new writes will hit the same shard (the "Hot Shard" problem). Hashed keys are usually better for write distribution.
• Re-sharding: Ask: "What happens when we need to add a 10th shard?" Moving data between shards (rebalancing) is a high-risk operation. Mention Consistent Hashing as a solution.
• Partition Pruning: In an interview, explain how the DB optimizer skips non-relevant partitions, significantly reducing I/O.

---

📐 Suggested Architecture Primitives

• Consistent Hashing: To minimize data movement during re-sharding.
• Shard Router / Proxy: A layer like ProxySQL or Citus for routing.
• Lookup Strategy: A metadata database that stores the mapping of ResourceID -> ShardID.
• Z-Order Curves: For advanced multi-dimensional partitioning (e.g., Geo + Time).