Database Sharding vs Partitioning

1. Introduction

Sharding distributes data across multiple independent database servers (shards), each holding a subset of the total data. Partitioning splits a table within a single database into smaller physical tables (partitions). While partitioning improves scan performance and maintenance on one server, sharding scales writes and storage horizontally across many servers. PostgreSQL supports partitioning natively but requires external tools (Citus, FDW, application logic) for sharding.

2. Why It Matters

When a single PostgreSQL instance can't handle write throughput (e.g., >100K TPS) or storage (e.g., >10 TB), you must scale horizontally. Partitioning helps with query performance and data lifecycle on a single node. Sharding distributes load across nodes but introduces complexity in cross-shard queries, distributed transactions, and rebalancing.

3. Real-World Analogy

Partitioning is like organizing a single library's books by floor — history on floor 1, science on floor 2. You still have one building. Sharding is like building separate libraries in different cities — each holds books for its region. You need a card catalog (router) to know which library to visit.

4. How It Works

• Partitioning (single node): Range, list, or hash partitioning within one PostgreSQL instance. Transparent to the application — queries against the parent table are automatically routed to the correct partition.
• Sharding (multi-node): Data is split by a shard key (e.g., user_id). Approaches include: application-level sharding (app picks the DB), Citus extension (transparent distributed queries), or Foreign Data Wrappers (FDW) for cross-shard joins.
• Shard key selection: Choose a high-cardinality key that distributes evenly and is present in most queries. Bad shard keys cause "hot spots" where one shard gets disproportionate traffic.

5. Internal Architecture

Citus transforms PostgreSQL into a distributed database: a coordinator node parses queries and distributes them to worker nodes (shards). It supports distributed joins (co-located tables on the same shard), reference tables (replicated to all shards), and rebalancing. Application-level sharding uses a routing layer that maps shard keys to database connections. Cross-shard queries require scatter-gather (query all shards, merge results) which adds latency.

6. Visual Explanation

The diagram contrasts partitioning (single node, table split into partitions) with sharding (multiple nodes, each holding a shard of the data), showing how queries are routed in each case.

7. Practical Example

8. Common Mistakes

• Sharding too early: Adds immense complexity. Exhaust vertical scaling, partitioning, and read replicas first.
• Choosing a poor shard key: Low-cardinality keys (e.g., boolean, status) create hot spots. Use high-cardinality keys like user_id or tenant_id.
• Cross-shard JOINs: Expensive scatter-gather operations. Co-locate related tables on the same shard key to avoid them.
• Distributed transactions: 2PC across shards is slow and failure-prone. Design for eventual consistency where possible.

9. Quick Quiz

10. Scenario-Based Challenge

A multi-tenant SaaS platform has 50,000 tenants with 500M rows total. Design a sharding strategy using tenant_id as the shard key. Ensure that all queries for a tenant hit the same shard, plan for rebalancing when adding new shards, and handle cross-tenant analytics queries without impacting production shards.

11. Debugging Exercise

12. Interview Questions

• Q: When should you shard vs just use partitioning?
  A: Partition first for query performance and data lifecycle on a single node. Shard when you exceed a single node's write capacity, storage limits, or need geographic data distribution.
• Q: What are the challenges of cross-shard transactions?
  A: They require distributed coordination (2PC), have higher latency, and are vulnerable to partial failures. Most sharded systems avoid them by designing operations to stay within a single shard.

13. Production Considerations

• Rebalancing: Citus provides automated rebalancing. Application-level sharding requires manual migration scripts.
• Global sequences: Auto-increment IDs across shards collide. Use UUIDs or snowflake IDs for globally unique identifiers.
• Schema migrations: Must be applied to all shards simultaneously. Use a migration tool that supports parallel execution.
• Monitoring per-shard: Track CPU, memory, disk, and replication lag independently for each shard.