Database sharding vs partitioning: scaling past one server

When a database table grows past 500 million rows, single queries degrade. B-Tree indexes exceed available RAM, checkpoints create I/O freezes, and vacuuming locks tables for hours.

To scale beyond single-table limits, systems use partitioning and sharding. While frequently conflated, they solve completely different scaling bottlenecks.

Partitioning (Same Server)                Sharding (Distributed Cluster)
┌───────────────────────────────┐        ┌─────────────┐   ┌─────────────┐
│       Single Database Host    │        │   Node 1    │   │   Node 2    │
│  ┌───────────┐ ┌───────────┐  │        │ (Shard A-M) │   │ (Shard N-Z) │
│  │ users_2024│ │ users_2025│  │        └─────────────┘   └─────────────┘
│  └───────────┘ └───────────┘  │        ┌─────────────┐   ┌─────────────┐
└───────────────────────────────┘        │   Node 3    │   │   Node 4    │
                                         │ (Shard 0-4) │   │ (Shard 5-9) │
                                         └─────────────┘   └─────────────┘

---

1. Table Partitioning (Single Instance)

Partitioning splits one large logical table into smaller, self-contained physical tables stored on the same database server.

How It Works (PostgreSQL / MySQL)

-- Partitioning by creation timestamp range
CREATE TABLE orders (
    order_id UUID,
    customer_id UUID,
    created_at TIMESTAMPTZ NOT NULL,
    total_cents INT
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

• Partition Pruning: The query planner inspects the WHERE created_at > '2025-06-01' clause and scans only orders_2025, skipping orders_2024 entirely.
• Preserves ACID: Transactions, foreign keys, and complex JOIN queries run with full relational integrity because all data lives in the same storage engine.
• Limits: It does not solve disk I/O, CPU, or memory constraints on a saturated machine.

---

2. Database Sharding (Horizontal Partitioning Across Nodes)

Sharding takes partitions and places them across completely separate network nodes.

Routing Strategies

When your application writes or reads an order, a Sharding Key determines the destination node:

1. Hash-Based Sharding: shard_id = hash(user_id) % N. Distributes load evenly across nodes.
2. Range-Based Sharding: Shard 1 holds User IDs 1–10M, Shard 2 holds 10M–20M. Prone to write hot-spots on newly registered IDs.
3. Entity/Geo Sharding: European users route to db-eu-central, US users route to db-us-east.

---

The “Distributed Tax” of Sharding

Sharding provides near-infinite write scalability, but introduces architectural complexity:

• No Native Cross-Shard JOINs: Joining users on Node 1 with orders on Node 2 requires fetching records over the network into application memory or running expensive distributed query engines (e.g., Presto/Trino).
• Distributed Transactions: Single-node ACID transactions no longer work. You must adopt Two-Phase Commit (2PC), distributed sagas, or accept Eventual Consistency.
• Re-Sharding Costs: Adding new shard nodes requires re-hashing and migrating terabytes of data over live networks (mitigated by Consistent Hashing).

---

Architecture Summary

Characteristic	Partitioning	Sharding
Location	1 Physical/Virtual Server	Multiple Network Servers
Routing	Managed automatically by DB engine	Managed by app code or proxy (e.g., Vitess, Citus)
ACID & Foreign Keys	✅ Native & 100% supported	❌ Lost across shards
Write Scaling	Limited to 1 machine’s hardware	✅ Horizontally scalable
Maintenance	Low complexity	High operational overhead

Rule of Thumb: Exhaust query indexing, read replicas, vertical hardware upgrades, and native table partitioning first. Only introduce sharding when write throughput exhausts a single node’s I/O capacity.