What is PostgreSQL table partitioning and how does range partitioning work?
PostgreSQL table partitioning splits one logical table into several physical tables called partitions. With declarative partitioning you declare the split once on a parent table and PostgreSQL handles routing. For time-series data, the usual choice is range partitioning: each partition owns a contiguous slice of a timestamp column.
You declare the parent with PARTITION BY RANGE (created_at). Each child is created with PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'). The lower bound is inclusive and the upper bound is exclusive. Because of that rule, adjacent partitions can share a boundary value without overlapping. A row stamped exactly 2025-02-01 00:00:00 belongs to February, not January, so you never have to write awkward upper bounds like 23:59:59.999999. PostgreSQL rejects any new partition whose range overlaps an existing one.
The parent is a routing and planning layer and has no storage of its own. Every row physically lives in exactly one child. On insert, PostgreSQL compares the partition key against each child's bounds and writes the row to the matching child. If no child matches, the insert fails with an error. You can avoid that error by attaching a DEFAULT partition, which catches rows outside every defined range.
INSERT INTO events (created_at = '2025-02-14 09:30')
│
▼
┌─────────────────────────────────┐
│ events (parent, no rows) │
│ PARTITION BY RANGE (created_at) │
└─────────────────────────────────┘
│ │ │
[2025-01-01, 2025-02-01) [2025-02-01, 2025-03-01) [2025-03-01, 2025-04-01)
│ │ │
events_2025_01 events_2025_02 ◄─ events_2025_03
(row lands here)
Queries go through the same parent. The planner can use those bounds to skip partitions that cannot contain matching rows.
-- Parent table: declares the partition key; it holds no rows itself.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
-- Monthly children: FROM is inclusive, TO is exclusive,
-- so adjacent partitions share a boundary without overlapping.
CREATE TABLE events_2025_01 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
-- Optional catch-all for rows outside every defined range.
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Exactly on the boundary: routed to February, not January.
INSERT INTO events (created_at, payload)
VALUES ('2025-02-01 00:00:00+00', '{"type": "login"}'),
('2025-02-14 09:30:00+00', '{"type": "click"}'),
('2026-07-01 12:00:00+00', '{"type": "late"}'); -- lands in DEFAULT
-- tableoid shows which physical partition stores each row.
SELECT tableoid::regclass AS partition, created_at
FROM events
ORDER BY created_at;
-- The parent itself stores nothing: ONLY skips the partitions.
SELECT count(*) FROM ONLY events; -- returns 0
-- Overlapping ranges are rejected with an error:
-- CREATE TABLE events_bad PARTITION OF events
-- FOR VALUES FROM ('2025-01-15') TO ('2025-02-15');
Creates a range-partitioned events table with monthly children and a DEFAULT partition, then uses tableoid and SELECT ... FROM ONLY to show that a boundary timestamp routes to the later partition and that the parent stores no rows.
Why partition time-series tables, and who actually benefits?
contain matching rows. That pruning is useful, but it is not the main reason to partition time-series data. The main payoff is that retention becomes a cheap metadata operation instead of a bulk rewrite, and routine maintenance stays predictable as the table grows.
Consider removing a month of old events. A DELETE with a time predicate visits every target row, writes WAL for each deletion, and leaves dead tuples in the heap and every index until vacuum reclaims them. Detaching that month's partition and dropping it, or archiving it first, removes the underlying files.
| Aspect | Bulk DELETE of old rows |
DETACH PARTITION then DROP TABLE or archive |
|---|---|---|
| WAL volume | Scales with rows deleted | Small and roughly constant |
| Vacuum work | Must scan and clean the deleted range | None for the removed data |
| Bloat | Heap and index bloat until space is reused | Files are removed and disk space is returned |
| Replica impact | Large WAL stream can cause lag | Minimal |
Detaching still has a locking cost. A plain detach takes a strong lock on the parent. Queries against the parent block while it waits, and it can queue behind long-running transactions. DETACH PARTITION ... CONCURRENTLY uses weaker locks and runs in stages, but it has restrictions. For example, it cannot run inside a transaction block. Check your version's documentation before relying on it.
This approach suits high-ingest, retention-driven workloads such as logs, metrics, audit trails, and event streams, where data ages out in time order. It also saves engineering time. Retention becomes a scheduled job that drops one partition. You no longer need to tune batched delete scripts, watch autovacuum fall behind, or respond to pages about disk growth. Vacuum effort concentrates on the small, active partition. Cold partitions need little further work once they have been vacuumed and frozen.
-- Contrast: bulk DELETE rewrites nothing but touches every row,
-- writing WAL per tuple and leaving dead tuples for vacuum.
-- DELETE FROM events WHERE created_at < '2026-02-01';
-- Partitioned alternative: remove a whole month as a metadata operation.
-- Assumes events is range-partitioned on created_at with monthly partitions.
-- Fail fast instead of queueing behind long transactions and
-- blocking other queries on the parent while waiting for the lock.
SET lock_timeout = '5s';
-- Plain detach: takes a strong lock on the parent; runs in a transaction.
BEGIN;
ALTER TABLE events DETACH PARTITION events_2026_01;
COMMIT;
-- Alternative (PostgreSQL 14+): weaker locks, runs in stages.
-- Cannot run inside a transaction block; check your version's docs
-- for other restrictions before relying on it.
-- ALTER TABLE events DETACH PARTITION events_2026_01 CONCURRENTLY;
-- The detached table is now standalone. Archive it first if needed,
-- e.g. from a shell: pg_dump -t events_2026_01 -Fc -f events_2026_01.dump mydb
-- Dropping removes the underlying files and returns disk space;
-- no dead tuples remain for vacuum to clean up.
DROP TABLE events_2026_01;
RESET lock_timeout;
Retention for a monthly range-partitioned table: detach the oldest partition with a lock timeout, optionally archive it, then drop it instead of running a bulk DELETE.
How do you choose the partition key and granularity?
Pick the column that nearly every query filters on and that determines when rows expire. For time-series data, that is almost always the event timestamp, not the ingestion time. Then size partitions from three inputs: retention policy, ingest rate and typical query window.
| Granularity | Fits when | Partitions per year | Trade-off |
|---|---|---|---|
| Daily | High ingest, short retention, queries over hours | ~365 | Many relations to plan and lock |
| Weekly | Moderate ingest, retention of months | ~52 | Retention is dropped a week at a time |
| Monthly | Low ingest, multi-year retention | 12 | Each partition can grow large |
Illustrative estimate. Assume 50 million rows per day, about 200 bytes per row on disk, and 90-day retention. That is roughly 10 GB per day before indexes, or about 900 GB retained. Daily partitions give about 90 tables of about 10 GB each. Monthly partitions give 3–4 tables of about 300 GB, which brings back slow vacuums, large index rebuilds and coarse retention drops. Weekly partitions, about 13 tables of about 70 GB, are a reasonable middle.
Too many partitions has costs:
- The planner has to consider every partition it cannot prune.
- Each backend caches metadata for every partition it touches, which increases memory use.
- A query that cannot be pruned at plan time takes locks on every partition, which puts pressure on the lock manager.
Keys and indexes
Primary keys and unique constraints on a partitioned table must include the partition key. This is because PostgreSQL enforces uniqueness only within each partition. An index created on the parent is created on every existing partition and on every partition attached later.
As a result, a bare id primary key is not possible. Use a composite key such as (id, created_at). Generating IDs from a single sequence keeps them unique in practice, but nothing enforces it. If you need enforced global uniqueness, for example on an external reference, keep a separate unpartitioned lookup table that holds that column with a unique constraint.
-- Partition key is the event timestamp, so the PK must include it.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
device_id bigint NOT NULL,
payload jsonb,
PRIMARY KEY (id, created_at) -- PRIMARY KEY (id) alone is rejected
) PARTITION BY RANGE (created_at);
-- Defined once on the parent. It is created on every existing
-- partition and on every partition created or attached later.
CREATE INDEX ON events (device_id, created_at);
-- Weekly partitions. Lower bound is inclusive, upper bound exclusive.
CREATE TABLE events_2026_11_02 PARTITION OF events
FOR VALUES FROM ('2026-11-02') TO ('2026-11-09');
CREATE TABLE events_2026_11_09 PARTITION OF events
FOR VALUES FROM ('2026-11-09') TO ('2026-11-16');
-- Optional: global uniqueness of id across all partitions.
-- This is an ordinary (non-partitioned) table, so PRIMARY KEY (id)
-- is enforced table-wide. It also maps an id to its partition key.
CREATE TABLE event_ids (
id bigint PRIMARY KEY,
created_at timestamptz NOT NULL
);
-- Write both rows in one transaction. A duplicate id fails on event_ids.
BEGIN;
WITH e AS (
INSERT INTO events (created_at, device_id, payload)
VALUES ('2026-11-06 10:15:00+00', 42, '{"temp": 21.5}')
RETURNING id, created_at
)
INSERT INTO event_ids (id, created_at) SELECT id, created_at FROM e;
COMMIT;
-- Lookup by id alone: fetch the timestamp first so the query can prune.
-- SELECT * FROM events WHERE id = $1 AND created_at = $2;
This example creates a range-partitioned events table with a composite (id, created_at) primary key, an index defined on the parent that propagates to each weekly partition, and an optional non-partitioned lookup table that enforces global id uniqueness.
How does partition pruning work, and how do you confirm it in EXPLAIN?
Partition pruning is how the planner turns your partitioning into a performance win. When a WHERE clause constrains the partition key with constants, the planner compares those values against each partition's bounds and drops partitions that cannot match. They never appear in the plan. Run EXPLAIN on a query for one day of a monthly-partitioned table, and you should see a single partition scan. If the same query lists every partition under an Append node, pruning failed.
Plan-time vs. execution-time pruning
Plan-time pruning needs values the planner can see. Sometimes it can't:
- Prepared statements with a generic plan. Parameters are unknown when the plan is built.
- Stable functions such as
now(). Their value isn't fixed until the query runs.
In these cases PostgreSQL prunes at executor startup instead. The plan still lists the Append, but EXPLAIN ANALYZE reports Subplans Removed: N. Pruning can also happen mid-execution, for example when a nested loop supplies key values from another table. Partitions skipped that way show as (never executed).
What defeats pruning
The planner reasons about the bare partition key column. Any of these forces a scan of every partition's index:
- Wrapping the key in a function, such as
date_trunc('day', created_at) = .... - Casting the key, such as
created_at::date = .... - Filtering only on other columns, such as
user_idorevent_id.
For functions and casts, the fix is to rewrite the predicate as a half-open range on the raw column. For filters on other columns there is no rewrite. Each partition gets probed separately, which can make the query slower than it was on the unpartitioned table.
Audit your access patterns first
Before you commit to a layout, pull your most frequent and most expensive queries. Check whether each one constrains the time column directly. If many don't, partitioning will help maintenance but hurt reads.
-- Monthly range partitions on created_at
CREATE TABLE events (
event_id bigint, user_id bigint,
created_at timestamptz NOT NULL, payload jsonb
) PARTITION BY RANGE (created_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');
CREATE TABLE events_2026_03 PARTITION OF events FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE INDEX ON events (created_at); -- created on every partition
CREATE INDEX ON events (user_id);
-- 1. Plan-time pruning: constants on the bare key.
-- Plan shows only events_2026_02; no Append over all partitions.
EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-02-10' AND created_at < '2026-02-11';
-- 2. Execution-time pruning: a generic plan has no parameter values.
-- Plan keeps the Append but reports "Subplans Removed: 2".
SET plan_cache_mode = force_generic_plan;
PREPARE day_q(timestamptz) AS
SELECT * FROM events WHERE created_at >= $1 AND created_at < $1 + interval '1 day';
EXPLAIN EXECUTE day_q('2026-02-10');
RESET plan_cache_mode;
-- 3. Defeated: cast on the key. Append lists all three partitions.
EXPLAIN SELECT * FROM events WHERE created_at::date = '2026-02-10';
-- 4. Defeated: function on the key. Same result, every partition scanned.
EXPLAIN SELECT * FROM events
WHERE date_trunc('day', created_at) = '2026-02-10';
-- 5. No key predicate at all: each partition's user_id index is probed.
EXPLAIN SELECT * FROM events WHERE user_id = 42;
-- Fix for 3 and 4: rewrite as a half-open range on the raw column (query 1).
Creates a monthly-partitioned table and runs EXPLAIN on five queries: plan-time pruning with constants, execution-time pruning through a generic prepared plan, and three predicates (a cast, a function and a non-key filter) that make every partition appear in the plan.
How do you implement and automate the partition lifecycle with pg_partman?
Partitions must exist before data arrives. If an insert has no matching range, PostgreSQL rejects the row with an error, and the write fails instead of degrading. A missed partition-creation job at midnight becomes an outage at 00:00:01.
A DEFAULT partition catches rows that match no range, so writes keep succeeding. The cost appears later. When you attach or create a new range, PostgreSQL has to scan the default partition to confirm that none of its rows belong in the new range, and it holds locks while it scans. An empty default makes this check trivial. A default holding millions of rows makes every new partition slow and disruptive. If any of those rows do fall in the new range, the attach fails until you move them out. Treat a non-empty default as an alert, not as storage.
pg_partman automates this lifecycle. It premakes a configured number of future partitions, applies retention by dropping or detaching old ones, and does both in a maintenance routine run by its background worker or by an external scheduler such as cron. The extension's documentation covers its configuration parameters.
You still own several things:
- Monitoring that maintenance actually ran. Alert on the newest partition's upper bound, not on job exit codes.
- Choosing the premake horizon. It needs to be long enough to survive a weekend of broken maintenance.
- Testing retention on a copy. Do this before you enable dropping in production.
- Handling late-arriving data. Rows timestamped in the past land in old partitions, or are lost if retention has already dropped that range.
The development cost is modest. Operationally, you are adding a scheduled job that becomes part of the critical write path.
How do you migrate an existing large table to a partitioned layout?
Two approaches work in practice, and they trade copy time against cutover complexity.
| Backfill into a new parent | Attach the existing table | |
|---|---|---|
| Data movement | Copies every row in batches | None; old table becomes one historical partition |
| Duration | Proportional to table size | Short if the constraint is pre-validated |
| Cutover | Dual writes or a brief write pause | Rename and attach inside one transaction |
| Main risk | Lost or duplicated rows at batch edges | One oversized partition that retention cannot drop in pieces |
Backfill. You copy closed time ranges in batches while new writes go to the new parent, through dual writes or a short write pause. Rows that change during the copy are the danger, because they can be missed or written twice. Before you switch reads, compare row counts and an aggregate checksum per range on both sides.
Attach. ATTACH PARTITION scans the table to prove every row fits the declared range. You can avoid that scan:
- Add a CHECK constraint that matches the bounds exactly, including
NOT NULLon the key, asNOT VALID. - Run
VALIDATE CONSTRAINT. This step scans the table but does not block normal reads and writes. - Attach the table. PostgreSQL sees that the validated constraint already proves the partition bounds and skips its own scan.
After rollout.
- Run
EXPLAINon the top production queries and confirm that pruning happens. - Track partition counts and sizes.
- Alert when no near-future partition exists.
- Check that autovacuum and
ANALYZEreach the active partitions.
Autovacuum analyzes each child partition separately, but it never processes the partitioned parent. The parent has no storage of its own, so the planner's whole-table statistics only update when you run a manual ANALYZE on the parent. Schedule that job.
When should you avoid partitioning, and what are the alternatives?
Partitioning trades query-time simplicity for lifecycle convenience, and that trade can go badly. Avoid it when:
- Hot queries don't filter on the partition key. A lookup by
device_idororder_idwith no time bound scans every partition. That is often slower than one index probe on a single table. - You need global uniqueness on non-key columns. Unique and primary key constraints on a partitioned table must include the partition key. Uniqueness of an
idalone across all partitions has to come from the application or a separate lookup table. - Foreign keys matter. Support for foreign keys that reference partitioned tables, and the cost of cascades that fan out across partitions, differ between major versions. Check the documentation for the version you run before you depend on them.
- The partition count gets large. Planning time, lock acquisition and memory all grow with the number of partitions a query touches. Very fine granularity over long retention adds overhead to every query.
- The team can't absorb the operational load. You take on premake jobs, retention jobs, alerts for when those jobs fail, and the manual
ANALYZEof the parent described above. Someone on call has to own all of it.
| Approach | Retention cost | Fits when |
|---|---|---|
Single table + B-tree + batched DELETE |
Dead tuples, vacuum load | Moderate volume, varied query shapes |
| Single table + BRIN on timestamp | Same as above; tiny index | Rows inserted in time order, range scans |
| Range partitioning | Near-free DROP TABLE |
Measured vacuum/bloat pain, time-bounded queries |
Decide from measurements. Partition when bulk deletes cause sustained vacuum lag or bloat that you have actually recorded, or when indexes no longer fit in memory, and when most expensive queries carry a time predicate. If neither holds, try the BRIN index or batched deletes first.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgresql table partitioning, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.