Database Sharding vs. Horizontal Partitioning: Scaling Past 100 Million Records

1. The Limits of Vertical Database Scaling

Vertical scaling—upgrading CPU cores, RAM, and NVMe IOPS—eventually hits a point of diminishing returns. When a single table swells beyond 100 million records, B-Tree index heights increase, autovacuum runs take hours, and buffer pool hit ratios degrade.

Engineering teams must choose between native horizontal table partitioning within a single database instance or distributed database sharding across independent physical clusters.

2. Native Horizontal Partitioning (PostgreSQL)

PostgreSQL supports native declarative partitioning (Range, List, and Hash). While the application interacts with a single logical table, the database engine transparently splits physical storage into independent child tables.

Partition pruning enables the query planner to bypass irrelevant partitions entirely during queries that filter on the partition key, keeping index scans fast and targeted.

-- Declarative Range Partitioning by Year
CREATE TABLE orders (
  id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  amount_cents BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Child tables partition physical storage on disk
CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2026-01-01 00:00:00+00');

CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2027-01-01 00:00:00+00');

-- Query planner automatically eliminates partitions not matching timestamp filter!

3. Application-Level Sharding & Consistent Hashing

When write IOPS surpass the capacity of a single physical server, true sharding distributes data across independent database nodes. Each shard holds a discrete slice of the total database.

Using a consistent hashing algorithm on the Shard Key (typically tenant_id or user_id), application routing layers deterministically map read/write queries to the appropriate database shard.

4. The Cross-Shard Join & Transaction Dilemma

Sharding introduces architectural trade-offs: cross-shard SQL joins are prohibitively expensive, and two-phase commit (2PC) distributed transactions degrade throughput.

Engineers resolve this by carefully choosing shard keys that keep related data together on the same physical shard, using event-driven Saga patterns to coordinate rare multi-shard operations asynchronously.

5. Modern Alternatives: Vitess, Citus, and CockroachDB

Rather than building custom sharding logic in application code, modern teams evaluate Distributed SQL engines. Citus transforms PostgreSQL into a distributed cluster; Vitess scales MySQL at massive web scale; CockroachDB delivers native distributed ACID consensus.

6. Frequently Asked Questions