Partitioning and sharding

Partitioning and sharding both split big data. They solve different problems.

• Partitioning: one Postgres instance, one logical table, many physical tables. Solves: manageable VACUUM, fast bulk delete, partition pruning for query speed.
• Sharding: many Postgres instances, data split by key. Solves: more storage, more CPU, more write throughput than one node can give.

Postgres partitioning

Three strategies:

• Range: by value ranges. created_at >= '2026-01-01' AND created_at < '2026-02-01'. Good for time series.
• List: by explicit list. country IN ('IN', 'US', 'GB') per partition. Good for tenant isolation.
• Hash: by hash of column. Good for even distribution when no natural key range exists.

CREATE TABLE events (id bigserial, created_at timestamptz, payload jsonb)
PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Why partition

• Drop old data fast: DROP TABLE events_2025_01 is O(1). DELETE WHERE created_at < ... is O(n) plus dead tuples.
• Smaller indexes per partition: B-tree depth lower, lookups faster.
• Parallel scans across partitions: Postgres can scan partitions in parallel.
• VACUUM per partition: each partition vacuumed independently. Less downtime.

Sharding

Postgres has no native sharding. Options:

• Citus: extension that turns Postgres into a distributed cluster. Coordinator routes queries.
• PostgreSQL Foreign Data Wrappers: manual sharding via postgres_fdw. Painful but works.
• App-level sharding: client routes by user_id % N. Cross-shard joins are your problem.
• CockroachDB / YugabyteDB: distributed SQL with Postgres wire protocol. Different DB.

The interview line

"Partitioning splits one table into many inside one Postgres instance. Range partitioning by time is the most common pattern, lets you drop old data in O(1) and keeps indexes small. Sharding splits data across multiple instances and Postgres does not do it natively. You use Citus, app-level routing, or move to CockroachDB. The trade is operational complexity for scale."