A table has grown to hundreds of millions of rows — how do you partition it?

#Why partition at all

Once a table crosses into the hundreds of millions of rows, several things degrade independently of how well-indexed it is:

• Index maintenance — VACUUM/autovacuum in Postgres?, or OPTIMIZE/index rebuilds elsewhere, take longer and compete for I/O with production traffic as the table (and its indexes) grow.
• Query planning and cache locality — even an indexed lookup pays more once the index itself no longer fits comfortably in memory; a full scan for an unindexed or poorly-selective query becomes brutally expensive.
• Bulk operations and lifecycle management — "delete everything older than 2 years" against an unpartitioned table means a slow, lock-contending DELETE scanning and removing rows one at a time; against a partitioned table it can mean dropping a partition, which is near-instant.
• Blast radius of maintenance — rebuilding one index on one partition doesn't lock or scan the other 99.

Partitioning addresses all four by physically splitting the table into smaller sub-tables (partitions) that are queried, indexed, and maintained largely independently, while still being addressable through one logical table name.

#The three strategies

• Range partitioning — partitions are defined by ranges of a column's values, most commonly a timestamp. Ideal for time-series/event/log-style data where queries and retention are naturally time-scoped ("give me last week's orders," "drop everything older than 90 days").
• Hash partitioning — a hash function maps each row to one of N partitions, distributing rows roughly evenly regardless of the key's actual distribution. Ideal when there's no natural range and the goal is purely spreading load/size evenly (e.g., partitioning by user_id when there's no time or category structure to exploit).
• List partitioning — partitions are defined by an explicit set of discrete values, typically a category, region, or tenant/status column. Ideal when the query pattern is "give me everything for region = 'EU'" and the value set is known and small.

#Range partitioning example (Postgres, time-series)

Wrap long lines
CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    occurred_at TIMESTAMPTZ NOT NULL,
    user_id     BIGINT NOT NULL,
    event_type  TEXT NOT NULL,
    payload     JSONB NOT NULL,
    PRIMARY KEY (id, occurred_at)   -- partition key must be part of any unique/PK constraint
) PARTITION BY RANGE (occurred_at);

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

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Each partition gets its own local index — the planner prunes to just the
-- partitions overlapping the query's date range before it ever touches an index.
CREATE INDEX idx_events_2026_01_user ON events_2026_01 (user_id);
CREATE INDEX idx_events_2026_02_user ON events_2026_02 (user_id);

-- Retention becomes a metadata operation instead of a row-by-row DELETE.
DROP TABLE events_2026_01;  -- or: ALTER TABLE events DETACH PARTITION events_2026_01;

#Hash partitioning example

CREATE TABLE sessions (
    id      UUID NOT NULL,
    user_id BIGINT NOT NULL,
    data    JSONB NOT NULL,
    PRIMARY KEY (id)
) PARTITION BY HASH (id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

There's no meaningful range or category in a session ID, so hashing spreads rows (and their storage/index size) evenly across four partitions purely to keep any one partition from becoming its own hot, oversized table.

#List partitioning example

CREATE TABLE shipments (
    id      BIGINT GENERATED ALWAYS AS IDENTITY,
    region  TEXT NOT NULL,
    status  TEXT NOT NULL,
    weight_kg NUMERIC NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE shipments_eu PARTITION OF shipments FOR VALUES IN ('EU');
CREATE TABLE shipments_us PARTITION OF shipments FOR VALUES IN ('US');
CREATE TABLE shipments_apac PARTITION OF shipments FOR VALUES IN ('APAC');

This is also how a data-residency requirement gets satisfied physically: the shipments_eu partition can live on EU-based storage while the logical table stays one name the application queries normally.

#Choosing the partition key: the entire game

The partition key must match the table's dominant query and lifecycle pattern, or partitioning actively hurts:

• If most queries filter by occurred_at but you partitioned by user_id hash, every query still has to scan every partition (no pruning), and you've added overhead with zero benefit.
• Every unique constraint and primary key must include the partition key (visible above: PRIMARY KEY (id, occurred_at)) — this is a real design constraint, not a formality, and it sometimes forces surfacing a column into the PK that wasn't there before, breaking assumptions elsewhere.
• Cross-partition queries (a query that can't be pruned to one or a few partitions) don't disappear — they just now touch N sub-tables' indexes instead of one, which can be slower than an unpartitioned table for certain access patterns.
• Too many small partitions has its own cost: query planning time grows with partition count, and each partition carries fixed overhead (its own file, its own indexes, its own autovacuum bookkeeping) — a common failure mode is partitioning by day on a table that only gets a few hundred rows/day, producing thousands of tiny partitions for no benefit over a monthly grain.

#Migrating an existing giant table into partitions (without a multi-hour outage)

1. Create the new partitioned table (same columns, PARTITION BY ...) alongside the existing one.
2. Backfill historical data in batches (e.g., by month, by ID range), off-peak, monitoring replication lag and lock contention as you go.
3. Add a mechanism to keep the two in sync for the gap between backfill start and cutover — dual writes from the application, a trigger, or logical replication/CDC from the old table into the new one.
4. Once caught up, do a brief cutover: stop writes momentarily, catch up the last delta, rename tables (events → events_old, events_new → events) inside a transaction.
5. Keep the old table around, untouched, for a rollback window before dropping it.