Partitioning vs Sharding

Partitioning: Splits a table's data into multiple physical partitions within the same database instance. The partitioning is transparent to queries — the planner determines which partition(s) to read. PostgreSQL table partitioning: range, list, or hash.

Range partitioning: orders_2024_q1, orders_2024_q2, etc. Queries with WHERE created_at BETWEEN ... touch only relevant partitions (partition pruning). Excellent for time-series data where old partitions are rarely queried and can be archived.

Hash partitioning: orders_part_0 through orders_part_N based on hash(id) % N. Distributes writes evenly, enables parallel queries across partitions.

Why partitioning: a single table with 10 billion rows has huge index sizes and slow vacuum. Partitioning breaks it into manageable pieces — each partition has its own index (smaller, faster), vacuum runs per-partition (faster), old partitions can be detached and archived instantly (no DELETE, just ALTER TABLE DETACH PARTITION).

Sharding: Splits data across multiple independent database instances (different servers). Each shard is a completely separate database, unaware of other shards. No transparent query routing — the application must know which shard holds which data.

Key difference: partitioning = one DB server, multiple tables/files. Sharding = multiple DB servers, each holding a subset of rows. Partitioning solves storage and query performance within one server. Sharding solves the limits of one server entirely (RAM, CPU, disk, network).

graph TD subgraph part["Partitioning — ONE server"] s1["Single PostgreSQL instance"] s1 --> p1["orders_2024_q1"] s1 --> p2["orders_2024_q2"] s1 --> p3["orders_2024_q3"] note1["Query planner routes transparently.<br/>App connects to one instance either way."] end subgraph shard["Sharding — MULTIPLE servers"] app["Application"] -->|"must know which shard"| db1["Server 1: shard A"] app -->|"must know which shard"| db2["Server 2: shard B"] app -->|"must know which shard"| db3["Server 3: shard C"] note2["No transparent routing —<br/>the app itself decides which server to hit."] end

When to partition vs shard: Partition first — it is simpler, and a single well-tuned PostgreSQL instance can handle hundreds of GB with partitioning. Shard when the primary cannot handle write throughput or storage even with vertical scaling. Most applications never need sharding.

Trade-offs of sharding:

  • Cross-shard joins: must scatter-gather (query all shards, merge in application)
  • Distributed transactions: 2PC or SAGA for atomicity across shards
  • Re-sharding: moving data when adding a new shard is complex (consistent hashing minimizes this)
  • Operational complexity: N times the databases to backup, monitor, patch

Common pitfall

Choosing a shard key without checking its access patterns first is the most expensive mistake to discover late — a shard key that doesn't match how data is actually queried (e.g. sharding by user_id when the dominant query pattern is "all orders in a date range across all users") turns every such query into a scatter-gather across every shard, which defeats much of the point of sharding in the first place. The shard key decision is effectively permanent once real traffic depends on it — re-sharding later means migrating live data across servers, not editing a config value.