Table Partitioning Strategies

Introduction¶

Partitioning divides a table into smaller physical pieces by a partition key, while queries still see one logical table. It's most valuable for time-series data (drop old data by dropping a partition) and for letting the planner skip irrelevant chunks (partition pruning).

The strategies¶

Range¶

Partition by a continuous value, usually a date:

-- PostgreSQL declarative partitioning
CREATE TABLE events (
    id bigserial,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

MySQL 8:

CREATE TABLE events (
    id bigint NOT NULL,
    created_at datetime NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
    PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
    PARTITION pmax     VALUES LESS THAN MAXVALUE
);

List¶

Partition by a discrete set — region, tenant tier, status:

... PARTITION BY LIST (region);
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('DE','FR','ES','IT');
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US','CA');

Hash¶

Even distribution when there's no natural range/list — spread write hotspots or parallelise:

... PARTITION BY HASH (customer_id);
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... p1..p7

Partition pruning¶

The win: if a query's WHERE constrains the partition key, the planner reads only the relevant partitions.

EXPLAIN SELECT count(*) FROM events WHERE created_at >= '2026-09-10';
-- should show only events_2026_09 (and later), not a scan of every partition

If your queries don't filter on the partition key, pruning can't happen — every query hits every partition, and you've added overhead for nothing. The partition key must match your access pattern.

The local-index rule¶

In PostgreSQL declarative and MySQL, indexes are per-partition (local). A UNIQUE constraint must include the partition key — you cannot have a global unique index on just email if you partition by created_at. Plan the schema around this; it's the most common surprise.

Rolling retention (the killer use case)¶

Time-series with a retention window: each period, create next month's partition and drop the oldest.

-- monthly, via cron / pg_partman / a scheduled event
CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

DROP TABLE events_2026_03;        -- instant; no giant DELETE, no bloat, no vacuum

DROP TABLE / DROP PARTITION is metadata-only — vastly cheaper than DELETE FROM events WHERE created_at < ... (which writes dead tuples, bloats, and needs vacuum). Tools: pg_partman (Postgres), MySQL events / a script.

When NOT to partition¶

• The table is small (< ~10–50M rows) — indexes are enough.
• Queries don't filter on any single good partition key.
• You need global uniqueness on a column that isn't the partition key.
• You'd end up with thousands of partitions (planning overhead; keep it to dozens–low hundreds).

Migrating a big table¶

1. Create the new partitioned parent + partitions.
2. Backfill in batches (INSERT INTO new SELECT ... WHERE created_at BETWEEN ...).
3. Dual-write (trigger or app) during the migration.
4. Swap names in a transaction; verify; drop the old table later.

Postgres 10+ can also ATTACH PARTITION an existing table with a matching CHECK constraint, avoiding a full copy.

Verification and troubleshooting¶

-- Postgres: partitions and sizes
SELECT inhrelid::regclass AS partition,
       pg_size_pretty(pg_relation_size(inhrelid)) AS size
FROM pg_inherits WHERE inhparent = 'events'::regclass;

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- confirm pruning ("Partitions removed")

• Every query scans all partitions — the WHERE doesn't constrain the partition key, or it wraps the key in a function the planner can't prune through, or enable_partition_pruning is off (Postgres). Add/adjust the filter.
• no partition of relation ... found for row — inserting a value outside every partition's range. Add a default/MAXVALUE partition or pre-create future partitions.
• UNIQUE constraint rejected — it must include the partition key. Redesign or enforce uniqueness in the app / a separate lookup table.
• Slower than the unpartitioned table for point lookups — too many partitions inflate planning time; or a lookup by a non-partition-key column now probes N local indexes instead of one. Fewer, larger partitions.
• Cross-partition JOIN/ORDER BY slow — the engine merges per-partition results; sometimes an index on the parent's sort column across partitions helps, sometimes de-partitioning that query path is the answer.
• Retention job fell behind, one partition is huge — the "create ahead" step failed; new rows piled into MAXVALUE/default. Split it back out.

Related tools and reading¶

• On-site: SQL / JSON formatter, Timestamp converter.
• Related posts: PostgreSQL autovacuum tuning, Safe online schema changes in MySQL.