When a table like orders or logs grows into hundreds of millions of rows, indexes bloat, VACUUM drags on for hours, and "delete everything older than a year" becomes a DELETE that takes production down. Range partitioning fixes this: one logical table is physically split into chunks by a range of values, almost always a date.
PARTITION BY RANGE: the parent and its partitions
Declarative partitioning arrived in PostgreSQL 10 and is now the standard tool. You declare a parent table with a partition key, then attach child partitions, each owning its own FROM ... TO range.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY,
user_id bigint NOT NULL,
amount numeric(12,2) NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
Things that matter:
FROM is inclusive, TO is exclusive. Ranges must not overlap.- The partition key must be part of every
UNIQUE and PRIMARY KEY. That is why the primary key here is (id, created_at), not just id.
- Indexes created on the parent are automatically created on every partition.
Partition pruning: the planner reads only what it needs
The big speed win is pruning: when the WHERE clause constrains the partition key, the planner discards partitions that cannot possibly match the range.
EXPLAIN
SELECT sum(amount)
FROM orders
WHERE created_at >= '2025-03-01'
AND created_at < '2025-04-01';
The plan will reference only orders_2025 — the 2024 partition is never opened. Pruning happens both at plan time and at execution time (for parameters and JOINs). But if the query never mentions created_at — say, it filters only on user_id — Postgres has to scan every partition. That is the classic reason "partitioning didn't help."
Dropping old data in milliseconds
Retention is the killer feature. Deleting a year of rows with DELETE means hours of work and a bloated WAL. With partitions it is a metadata operation:
DROP TABLE orders_2024;
ALTER TABLE orders DETACH PARTITION orders_2024;
DROP TABLE orders_2024;
DETACH turns the partition back into an ordinary standalone table — you can archive it, move it to cheap storage, or just keep it around. The reverse, ATTACH PARTITION, plugs a ready-made table in as a new partition; use it for backfills or to bring historical data online.
ALTER TABLE orders ATTACH PARTITION orders_2026
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
The DEFAULT partition: a catch-all trap
If a row matches no range, the INSERT fails with an error. The safety net is a DEFAULT partition that absorbs everything else:
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
Gotcha: the DEFAULT partition is convenient but dangerous. If it already holds rows for 2026, then ATTACH-ing a new orders_2026 partition forces a full scan of orders_default to prove there is no overlap — under a lock. For time-series, pre-create future partitions (with pg_partman or a cron job) and keep DEFAULT empty as an alarm for missing ranges.
MySQL and ClickHouse: where they differ
- MySQL:
PARTITION BY RANGE (...) exists too, but the key must be an integer or wrapped in a function like TO_DAYS(created_at); there is no DETACH — you remove an old partition with ALTER TABLE ... DROP PARTITION.
- ClickHouse: partitions are defined with
PARTITION BY toYYYYMM(created_at) on top of the MergeTree engine; they exist mainly for DROP PARTITION and whole-part manipulation, not to speed up filters — that job belongs to the ORDER BY sort key.
Bottom line: design the partition key around how you filter and how you delete. For logs and orders that is almost always a date, sliced by month or year, with future partitions created ahead of time.
When a table like
ordersorlogsgrows into hundreds of millions of rows, indexes bloat,VACUUMdrags on for hours, and "delete everything older than a year" becomes aDELETEthat takes production down. Range partitioning fixes this: one logical table is physically split into chunks by a range of values, almost always a date.PARTITION BY RANGE: the parent and its partitions
Declarative partitioning arrived in PostgreSQL 10 and is now the standard tool. You declare a parent table with a partition key, then attach child partitions, each owning its own
FROM ... TOrange.CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY, user_id bigint NOT NULL, amount numeric(12,2) NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL ) PARTITION BY RANGE (created_at); CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');Things that matter:
FROMis inclusive,TOis exclusive. Ranges must not overlap.UNIQUEandPRIMARY KEY. That is why the primary key here is(id, created_at), not justid.Partition pruning: the planner reads only what it needs
The big speed win is pruning: when the
WHEREclause constrains the partition key, the planner discards partitions that cannot possibly match the range.EXPLAIN SELECT sum(amount) FROM orders WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';The plan will reference only
orders_2025— the 2024 partition is never opened. Pruning happens both at plan time and at execution time (for parameters andJOINs). But if the query never mentionscreated_at— say, it filters only onuser_id— Postgres has to scan every partition. That is the classic reason "partitioning didn't help."Dropping old data in milliseconds
Retention is the killer feature. Deleting a year of rows with
DELETEmeans hours of work and a bloated WAL. With partitions it is a metadata operation:-- Instant: drop a whole partition with its data and indexes DROP TABLE orders_2024; -- Cleaner: detach first so you can archive it ALTER TABLE orders DETACH PARTITION orders_2024; -- ... COPY orders_2024 TO '/archive/orders_2024.csv' ... DROP TABLE orders_2024;DETACHturns the partition back into an ordinary standalone table — you can archive it, move it to cheap storage, or just keep it around. The reverse,ATTACH PARTITION, plugs a ready-made table in as a new partition; use it for backfills or to bring historical data online.ALTER TABLE orders ATTACH PARTITION orders_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');The DEFAULT partition: a catch-all trap
If a row matches no range, the
INSERTfails with an error. The safety net is aDEFAULTpartition that absorbs everything else:CREATE TABLE orders_default PARTITION OF orders DEFAULT;MySQL and ClickHouse: where they differ
PARTITION BY RANGE (...)exists too, but the key must be an integer or wrapped in a function likeTO_DAYS(created_at); there is noDETACH— you remove an old partition withALTER TABLE ... DROP PARTITION.PARTITION BY toYYYYMM(created_at)on top of theMergeTreeengine; they exist mainly forDROP PARTITIONand whole-part manipulation, not to speed up filters — that job belongs to theORDER BYsort key.Bottom line: design the partition key around how you filter and how you delete. For logs and orders that is almost always a date, sliced by month or year, with future partitions created ahead of time.