Sharding vs Partitioning: Core Concepts

Partitioning and sharding both segment data to manage scale, but they operate at fundamentally different scopes — and conflating them leads to costly architectural mistakes. This page, part of Database Partitioning Fundamentals & Architecture, draws the exact boundary between the two approaches so you can reason clearly about routing complexity, transaction guarantees, and maintenance workflows. See also Consistency Models in Distributed Databases and Scaling Limits and Cost Tradeoffs for the broader architectural context.

Problem Framing

Consider a events table that has grown to 4 TB on a single PostgreSQL node. Queries filtered by created_at still hit 60-second timeouts despite a B-tree index on the column, because the planner cannot eliminate billions of rows with a single index scan. Two paths exist:

1. Declarative partitioning — split the table into child segments (one per month) inside the same database process. The planner enforces partition pruning and eliminates non-matching children at plan time. No application changes needed.
2. Horizontal sharding — distribute rows across independent database instances (e.g. two separate PostgreSQL servers). Each instance owns a disjoint slice of the keyspace. The application or a proxy must resolve which instance owns each row before executing.

Both paths solve query performance, but the operational profiles diverge sharply. Choosing the wrong one — typically reaching for sharding when partitioning is sufficient — multiplies infrastructure cost and breaks ACID guarantees without a commensurate scaling benefit.

Architecture Overview

The diagram below maps the two data paths side-by-side: a single-instance partitioned table on the left and a sharded cluster on the right.

The key architectural difference: in partitioning, the query planner is the only routing actor, and it operates entirely within one process. In sharding, a separate routing layer (application code or a dedicated proxy) must run before any database connection is acquired.

Implementation Walkthrough

Step 1 — Set up declarative range partitioning (single instance)

PostgreSQL’s declarative partitioning requires no changes to the application connection string. The engine handles all routing internally.

-- Create the parent (partitioned) table
CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at  TIMESTAMPTZ NOT NULL,
  tenant_id   INT NOT NULL,
  payload     JSONB
) PARTITION BY RANGE (created_at);

-- Attach annual child partitions
CREATE TABLE events_2024 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

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

-- Local index per child (covers all queries in range)
CREATE INDEX ON events_2024 (tenant_id, created_at);
CREATE INDEX ON events_2025 (tenant_id, created_at);

Verify that the planner uses partition pruning — it should show Seq Scan on events_2025 with no mention of events_2024 when the filter falls in 2025:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2025-06-01'
  AND tenant_id = 42;

Step 2 — Configure a shard router (multi-instance)

Sharding requires a router configuration that maps shard keys to node endpoints. The example below uses a YAML configuration for a consistent-hash router:

# shard_router.yaml
router:
  algorithm: consistent_hash
  virtual_nodes: 150        # higher = more even distribution; 100-200 is typical
  replication_factor: 2     # synchronous replicas per shard
  shards:
    - id: shard-0
      primary: "pg-shard-0.internal:5432"
      replica: "pg-shard-0-replica.internal:5432"
    - id: shard-1
      primary: "pg-shard-1.internal:5432"
      replica: "pg-shard-1-replica.internal:5432"
    - id: shard-2
      primary: "pg-shard-2.internal:5432"
      replica: "pg-shard-2-replica.internal:5432"
  shard_key_column: "tenant_id"
  fallback: "local_cache"   # serve stale reads if shard is temporarily unreachable

Application code must resolve the shard before acquiring a connection:

import hashlib
import yaml

with open("shard_router.yaml") as f:
    config = yaml.safe_load(f)["router"]

def get_shard(tenant_id: int) -> dict:
    """Map a tenant_id to its shard using consistent hashing."""
    key = hashlib.md5(str(tenant_id).encode()).hexdigest()
    ring_position = int(key, 16) % (len(config["shards"]) * config["virtual_nodes"])
    shard_index = ring_position // config["virtual_nodes"]
    return config["shards"][shard_index % len(config["shards"])]

# Usage: resolve shard before opening a connection
shard = get_shard(tenant_id=42)
conn = psycopg2.connect(host=shard["primary"], dbname="events")

Consistent hash routing distributes keys more evenly than modulo arithmetic and moves fewer rows when nodes are added or removed.

Step 3 — Add a new partition (partitioning path)

Attach a pre-built child table with no downtime in PostgreSQL 12+:

-- Build the child table independently (no lock on parent)
CREATE TABLE events_2026 (LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE events_2026 ADD CONSTRAINT events_2026_check
  CHECK (created_at >= '2026-01-01' AND created_at < '2027-01-01');

CREATE INDEX ON events_2026 (tenant_id, created_at);

-- Attach — acquires only a brief ShareUpdateExclusiveLock
ALTER TABLE events ATTACH PARTITION events_2026
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

The lock duration is proportional to the CHECK CONSTRAINT validation scan on the child, which is why building and indexing the child first is mandatory for zero-downtime operations.

Step 4 — Add a shard (sharding path)

Expanding a sharded cluster requires a dual-write migration. The approach below minimises risk by incrementally shifting traffic:

#!/usr/bin/env bash
# dual_write_migration.sh — add shard-3 to the cluster

# 1. Provision the new instance and replica
psql -h pg-shard-3.internal -c "CREATE DATABASE events;"

# 2. Start dual-write: the router sends writes to both old and new shards
#    for tenant IDs in the range being migrated
export DUAL_WRITE_RANGE_START=7500
export DUAL_WRITE_RANGE_END=9999
./router_control.sh enable_dual_write --range "$DUAL_WRITE_RANGE_START-$DUAL_WRITE_RANGE_END" \
    --target shard-3

# 3. Backfill historical rows for that tenant range
psql -h pg-shard-2.internal \
  -c "COPY (SELECT * FROM events WHERE tenant_id BETWEEN $DUAL_WRITE_RANGE_START AND $DUAL_WRITE_RANGE_END) TO STDOUT" \
  | psql -h pg-shard-3.internal -c "COPY events FROM STDIN"

# 4. Validate checksum parity before cutting over reads
./checksum_validator.sh --source shard-2 --target shard-3 \
    --tenant-range "$DUAL_WRITE_RANGE_START-$DUAL_WRITE_RANGE_END"

# 5. Shift read traffic; remove dual-write for migrated range
./router_control.sh cutover_reads --range "$DUAL_WRITE_RANGE_START-$DUAL_WRITE_RANGE_END" \
    --target shard-3
./router_control.sh disable_dual_write --range "$DUAL_WRITE_RANGE_START-$DUAL_WRITE_RANGE_END"

Configuration Reference

Operational Contrast

Partitioning fits within the same operational model as a regular table. pg_dump, pg_restore, point-in-time recovery, and EXPLAIN ANALYZE all work without modification. The automated partition creation workflows available at the implementation layer handle routine housekeeping with a single scheduled job.

Sharding changes every operational workflow. Backups must be coordinated across all nodes to capture a consistent global snapshot. Restore procedures require resurrecting multiple independent instances and re-validating the shard map. Cross-shard aggregation queries — analytics that span the whole keyspace — must scatter requests to every shard and merge results in the application or a proxy routing layer, adding latency proportional to the slowest shard.

The operationally safe default is: start with partitioning; migrate to sharding only when a single node’s hardware ceiling cannot be raised further.

Failure Modes

Common Mistakes

• Using sharding as a direct replacement for partitioning. Sharding requires application-level routing code and introduces distributed-transaction complexity. Native partitioning is transparent to the application layer and should be exhausted first. The use-case mapping guide makes this decision explicit.
• Choosing a shard key with low or monotonically increasing values. Auto-increment IDs as shard keys route all new writes to the same shard; UUIDs distribute writes evenly but make range scans expensive. Select a key that balances write distribution against your read access pattern.
• Ignoring cross-segment query latency budgets. Scatter-gather execution fans out to every shard and waits for the slowest response. Without explicit timeout budgets and circuit breakers, one degraded shard stalls every global aggregation.
• Attaching new partitions without pre-validating the check constraint. Skipping the CHECK CONSTRAINT pre-build causes ATTACH PARTITION to scan the entire child table while holding a lock, blocking writes for the duration of the scan on large tables.

FAQ

Related

• Database Partitioning Fundamentals & Architecture — parent section covering the full decision landscape
• Consistency Models in Distributed Databases — how ACID vs eventual consistency maps to shard topologies
• Scaling Limits and Cost Tradeoffs — capacity planning before choosing between partitioning and sharding
• How to Choose Between Sharding and Partitioning for High-Traffic Apps — decision guide for specific workload profiles
• Hash Routing Algorithms — consistent hashing implementation detail for sharded architectures