Partitioning and Sharding

TL;DR

When a table grows to hundreds of millions of rows, queries — even indexed ones — get slower. Maintenance (VACUUM, backups, index rebuilds) becomes painful. The fix is to split the table into smaller physical pieces:

• Partitioning — splits a table within a single database. The table still has one logical name; the engine routes reads/writes to the right partition. Postgres native partitioning since 10.
• Sharding — splits a table across multiple databases (servers). Application or middleware (Citus, Vitess) routes queries to the right shard.

Three partition strategies:

• Range — by a continuous key, usually time. “Each month is a partition.” Most common; great for time-series.
• List — by an explicit list of values per partition. “EU, US, APAC.”
• Hash — by hash(key) MOD N. Spreads writes evenly; doesn’t help range scans.

The win: queries that include the partition key in WHERE only touch the relevant partitions (partition pruning). Maintenance happens per partition — drop a year-old partition with one DDL instead of a giant DELETE.

Range partitioning — the typical setup

CREATE TABLE events (
    id      bigserial,
    user_id int,
    ts      timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, ts)              -- partition key must be in PK
) PARTITION BY RANGE (ts);

CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
-- ... one per month

Inserts land in the partition matching the row’s ts. Reads:

EXPLAIN SELECT * FROM events WHERE ts >= '2026-05-01' AND ts < '2026-06-01';

Append
  ->  Seq Scan on events_2026_05  (cost=...)
        Filter: ((ts >= '2026-05-01') AND (ts < '2026-06-01'))

Only events_2026_05 is touched — that’s partition pruning. The queries against the 2026-04 partition (and any older ones) are skipped entirely.

List partitioning

CREATE TABLE users (
    id int,
    region text NOT NULL,
    name text
) PARTITION BY LIST (region);

CREATE TABLE users_us  PARTITION OF users FOR VALUES IN ('US', 'CA');
CREATE TABLE users_eu  PARTITION OF users FOR VALUES IN ('UK', 'DE', 'FR');
CREATE TABLE users_apac PARTITION OF users FOR VALUES IN ('JP', 'IN', 'AU');
CREATE TABLE users_default PARTITION OF users DEFAULT;

Useful when you have a small fixed set of values and want geographic / tenant locality. WHERE region = 'US' only scans users_us.

Hash partitioning

CREATE TABLE orders (id int, user_id int, total numeric)
    PARTITION BY HASH (user_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Each user’s orders land in one specific partition (deterministically by user_id hash). Spreads writes evenly; helps WHERE user_id = 42 (one partition); doesn’t help WHERE user_id IN (...) over many users (likely all partitions).

When partitioning helps

• Time-series data with retention. Drop old partitions in O(1) instead of a multi-hour DELETE.
• Queries that filter on the partition key. Partition pruning skips most data.
• Per-partition operations. Indexes, VACUUM, statistics happen per partition — smaller and faster.
• Cold/hot data tiering. Move old partitions to cheaper storage (Postgres tablespaces, S3 via foreign tables).

When partitioning hurts

• Queries that don’t filter by the partition key. Every partition is scanned. You added complexity for nothing.
• High partition count. Postgres handles ~hundreds well, ~thousands with care, ~tens of thousands poorly. Each query has per-partition planning overhead.
• Cross-partition joins. Especially if the join key isn’t the partition key — turns into a many-way join.
• Unique constraints across partitions. Postgres can’t enforce a global unique constraint unless the unique key includes the partition key.

Maintenance tricks

-- Drop a year-old partition (instant, O(1))
DROP TABLE events_2025_01;

-- Detach (keep the data, remove from the parent table)
ALTER TABLE events DETACH PARTITION events_2025_01;

-- Attach a pre-built table as a partition (zero downtime if no overlap)
ALTER TABLE events ATTACH PARTITION events_2026_06
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

The “build new partition into a separate table, then attach” pattern is how you bulk-load without slowing live queries.

Sharding — when partitioning isn’t enough

When the dataset exceeds what a single machine can hold (tens of TB) or serve (write throughput), you shard across machines.

Two approaches:

Sharding by the wrong key is a permanent disaster — you can’t easily re-shard live data. Pick a key with:

• High cardinality (millions of distinct values).
• Even distribution (no hot key).
• Most queries can specify it (so you hit one shard, not all).

For SaaS multi-tenant: shard by tenant_id. For social: shard by user_id. For events: shard by (user_id, ts_bucket).

Common pitfalls

• Partition key not in queries. Partition pruning never fires; you read every partition. Defeats the purpose.
• Too many partitions. Each partition has overhead in the planner and pg_class. Keep it under ~1000 unless you’ve benchmarked.
• Cross-shard transactions. Distributed transactions are slow and can fail in weird ways. Design schemas so a transaction lives in one shard.
• Re-sharding live data. Painful. Pick the shard key right the first time, or use middleware (Citus, Vitess) that automates resharding.
• Hash partitioning with IN queries. WHERE user_id IN (1, 2, 3) hits multiple hash partitions; the planner runs N partition lookups in parallel but you’ve lost the single-partition win.

Production patterns for ML

1. Time-partitioned events table. Almost every event table at scale:

CREATE TABLE events (...) PARTITION BY RANGE (ts);
-- monthly partitions, auto-created by pg_partman or similar

Drop year-old partitions for retention. WHERE ts BETWEEN ... queries are fast. Each partition has its own indexes (small B-trees).

2. User-sharded feature store. When the warehouse outgrows a single node, shard the feature store by user_id:

-- Citus example
SELECT create_distributed_table('features', 'user_id');

All of a user’s features land on the same shard, so per-user lookups hit one node. Cross-user analytics (rare in serving paths) span shards.

3. Cold-data archival via partitioning. Hot last-30-days partitions on fast SSD; older partitions on slow/cheap storage (S3 via postgres_fdw). Queries against recent windows are fast; rare full-history scans are slower but cheap to operate.

Resources

• PostgreSQL — table partitioning — postgresql.org/docs
• pg_partman — automated partition management — github.com/pgpartman/pg_partman
• Citus — distributed Postgres — citusdata.com
• Designing Data-Intensive Applications, ch. 6 — dataintensive.net — the canonical sharding chapter.
• PostgreSQL documentation — postgresql.org/docs