When does event data need PostgreSQL table partitioning?
PostgreSQL table partitioning splits one logical table into child tables, each holding a slice of the rows defined by a partition key. For event data the key is almost always the event timestamp. The parent table holds no rows itself; the planner routes inserts and queries to the matching children.
Event tables suit this because they are append-heavy and usually read by time window. Partitioning pays off when three signals appear together:
- Time-bounded queries. Most reads filter on a recent range, so the planner can skip partitions outside it.
- Bulk retention by age. Dropping or detaching an old partition is a quick metadata operation. A
DELETEof millions of rows produces dead tuples that vacuum must then clean up. - Growing maintenance cost. Vacuum, reindexing and index bloat on one huge table get slower as it grows. Smaller partitions keep that work bounded, and old partitions that no longer change need little attention.
Avoid partitioning when many queries do not filter on the time key. A lookup by user_id or event_id alone must probe every partition's index. That can be slower than a single B-tree lookup on an unpartitioned table, and planning time also grows with the partition count.
| Workload signal | Recommended approach |
|---|---|
| Time-range scans, rows inserted in time order, no retention pressure | BRIN index on the timestamp |
| Slow queries filtering on several columns, modest table size | Better composite B-tree indexes |
| Occasional cleanup, low delete volume | Batched DELETE in small chunks |
| Time-bounded reads, age-based retention, vacuum falling behind | Range partitioning |
| Frequent lookups without the time key | Stay unpartitioned |
Who should use partitioning with pg_partman?
This approach fits teams that already run event, audit, log or metrics data in PostgreSQL and want to keep it there instead of adding a separate data store. The typical candidate has these traits:
- A large, append-mostly table keyed by time, where most reads cover a recent window and old data expires on a fixed schedule.
- A retention policy that is currently enforced with
DELETE, causing vacuum pressure, bloat or long-running cleanup jobs. - Someone on the team who owns the database and can install extensions, schedule a maintenance job and wire up alerts. pg_partman reduces the work but does not remove the need for monitoring.
- A PostgreSQL deployment where pg_partman is available. On self-managed servers this is a package install. On managed services, check whether the extension and its background worker are allowed.
It is a poor fit for small tables, for workloads dominated by lookups that do not include the timestamp, and for teams that need cross-partition uniqueness on a column other than the partition key. If you only need one or two partitions created once a year, hand-written DDL may be simpler than adding an extension.
How does declarative range partitioning work?
The parent table is declared with PARTITION BY RANGE (occurred_at) and stores no rows itself. Every insert is routed to the child whose bounds contain the row's occurred_at value. A monthly child is attached with FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'). The lower bound is inclusive and the upper bound is exclusive, so a row stamped exactly at midnight on February 1 belongs to the February partition. Because adjacent ranges share an endpoint, there are no gaps and no overlaps.
INSERT INTO events ──► events (parent, routes on occurred_at)
├─► events_2026_01 [2026-01-01, 2026-02-01)
├─► events_2026_02 [2026-02-01, 2026-03-01)
├─► events_2026_03 [2026-03-01, 2026-04-01)
└─► events_default (no matching range)
If no partition matches a row, the insert fails. A DEFAULT partition catches those rows instead, such as late-arriving or wrongly clocked events. Treat it as an alarm rather than as storage. When you later create a partition whose range covers rows already sitting in the default partition, PostgreSQL scans the default partition and rejects the new partition. Keep the default partition empty.
Keys must include the partition key
Unique indexes are enforced per partition, so any primary key or unique constraint must contain occurred_at. You cannot declare event_id alone as unique. Two partitions could each hold the same ID, and no index spans both.
The usual design is PRIMARY KEY (event_id, occurred_at). This deduplicates retries only if the producer assigns occurred_at once and resends the same value, so ON CONFLICT DO NOTHING matches the duplicate. If timestamps are assigned at insert time, deduplicate with an idempotency check in the application.
-- Parent table: holds no rows, routes inserts by occurred_at
CREATE TABLE events (
event_id uuid NOT NULL,
occurred_at timestamptz NOT NULL,
event_type text NOT NULL,
payload jsonb,
-- Must include the partition key; event_id alone is rejected
PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);
-- Monthly children: lower bound inclusive, upper bound exclusive
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00');
CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01 00:00:00+00') TO ('2026-03-01 00:00:00+00');
-- Safety net for rows outside every range; alert if it is ever non-empty
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Exactly midnight Feb 1 (UTC) lands in events_2026_02, not events_2026_01
INSERT INTO events (event_id, occurred_at, event_type)
VALUES ('6f1c2b9e-3a4d-4e5f-8a7b-1c2d3e4f5a6b', '2026-02-01 00:00:00+00', 'signup');
-- Producer retry with the same (event_id, occurred_at) is silently skipped
INSERT INTO events (event_id, occurred_at, event_type)
VALUES ('6f1c2b9e-3a4d-4e5f-8a7b-1c2d3e4f5a6b', '2026-02-01 00:00:00+00', 'signup')
ON CONFLICT (event_id, occurred_at) DO NOTHING;
-- Confirm routing and keep an eye on the default partition
SELECT tableoid::regclass AS partition, count(*)
FROM events
GROUP BY 1
ORDER BY 1;
Creates a range-partitioned events table with monthly children, a DEFAULT partition, and a composite primary key that includes occurred_at, then shows boundary routing and retry deduplication with ON CONFLICT DO NOTHING.
How do you choose partition granularity for event data?
Granularity comes down to three inputs: how fast data arrives, how long you keep it, and the time range a typical query covers. Partition boundaries should line up with how you delete data, and a common query should touch only a few partitions.
Total partition count matters in both directions. With too many small partitions, the planner does more work. Each query that cannot prune at plan time locks every partition, and the system catalogs grow with each table and index. With too few large partitions, a query for the last hour still scans a month of data. Retention also gets coarse: you can only drop data one whole partition at a time.
Illustrative estimate. Assume 50 million events per day at about 500 bytes per row, including index overhead, which is roughly 25 GB per day. Assume 90-day retention and dashboards that mostly query the last 24 hours.
| Granularity | Partitions for 90 days | Approx. size each | Fit for this workload |
|---|---|---|---|
| Daily | ~90 | ~25 GB | Dashboard queries hit one or two partitions. Retention drops one day at a time. |
| Weekly | ~13 | ~175 GB | Fewer partitions to manage, but a 24-hour query scans up to a week. |
| Monthly | ~3–4 | ~750 GB | Little pruning benefit, and retention removes a month at once. |
For this workload, daily partitions are the best fit. If ingest were a hundred times smaller, weekly or monthly partitions would keep each one reasonably sized without a long tail of tiny tables.
Treat the estimate as a starting point. Run EXPLAIN on your real queries before committing, as the next section shows.
How do you confirm partition pruning with EXPLAIN?
Pruning is the main payoff of partitioning, so verify that it happens. With plain EXPLAIN, a query filtered by constants on the partition key should list only the matching partitions. The planner discards the rest before execution, so they never appear in the plan.
When the bound is unknown at plan time, pruning moves to execution. This happens with a generic plan for a prepared statement, a value from a subquery, or a stable function such as now(). All partitions may appear in the plan, but the output shows a line like Subplans Removed: 27. Partitions pruned later during a join show up in EXPLAIN ANALYZE as (never executed). If you see neither and every partition reports rows or loops, pruning did not happen.
Most failures come from hiding the key from the planner:
date_trunc('day', created_at) = '2024-03-01'orcreated_at::date = '2024-03-01'cannot be matched against partition bounds. Rewrite both ascreated_at >= '2024-03-01' AND created_at < '2024-03-02'.WHERE user_id = 42with no time predicate scans every partition. Add a time window, even a generous one, so the planner can exclude old data.
An index created on the parent is automatically created on every existing partition and on partitions added later. Each partition gets its own small B-tree rather than one huge index. Queries against recent data touch only the recent partitions' indexes, and those indexes are small enough to stay in memory. Older indexes can drop out of cache without slowing the queries that matter.
-- Assumes events is range-partitioned on created_at with daily partitions.
-- Defeats pruning: the key is wrapped in a function or cast,
-- so the planner cannot compare it against partition bounds.
EXPLAIN SELECT count(*) FROM events
WHERE date_trunc('day', created_at) = '2024-03-01';
EXPLAIN SELECT count(*) FROM events
WHERE created_at::date = '2024-03-01';
-- Prunes at plan time: a bare range on the key with constants.
-- The plan should list only the 2024-03-01 partition.
EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02';
-- Defeats pruning: no predicate on the partition key at all.
EXPLAIN SELECT * FROM events WHERE user_id = 42;
-- Prunes: add a time window, even a generous one.
EXPLAIN SELECT * FROM events
WHERE user_id = 42 AND created_at >= now() - interval '7 days';
-- now() is stable, so pruning happens at execution time:
-- look for a 'Subplans Removed: N' line under the Append node.
-- Execution-time pruning with a prepared statement and a generic plan.
SET plan_cache_mode = force_generic_plan;
PREPARE recent(timestamptz) AS
SELECT count(*) FROM events WHERE created_at >= $1;
EXPLAIN (ANALYZE) EXECUTE recent('2024-03-01');
-- Expect 'Subplans Removed: N'. Partitions skipped later during
-- a join show up as '(never executed)'.
DEALLOCATE recent;
RESET plan_cache_mode;
-- An index on the parent cascades to every current and future partition.
CREATE INDEX events_user_id_created_at_idx ON events (user_id, created_at);
EXPLAIN queries contrasting predicates that defeat partition pruning with rewrites that prune at plan time or execution time, plus a parent index that cascades to every partition.
How does pg_partman work?
pg_partman does not change how PostgreSQL partitioning behaves. Routing, pruning and constraint enforcement are still handled by native declarative partitioning, as described above. The extension only automates the DDL around it.
It works in three parts:
- Configuration. When you register a parent table, pg_partman stores one row per partition set in its
part_configtable: the control column, the interval, how many partitions to create ahead of time (premake), and the retention policy. - Maintenance.
run_maintenancereads each configuration row, compares the newest existing partition with the current time, and issues ordinaryCREATE TABLE ... PARTITION OFstatements until the premake horizon is covered. It then finds partitions older than the retention interval and detaches or drops them. - Scheduling. Something has to call maintenance repeatedly. That is either pg_partman's optional background worker, which runs inside the PostgreSQL server on a configured interval, or an external scheduler that runs the call on a timer.
Because the output is plain DDL, the resulting tables are normal PostgreSQL partitions. You can inspect them in the catalog, query them directly, and keep them working even if you later stop using the extension.
How do you automate partitions and retention with pg_partman?
Creating those partitions by hand is where most teams start, and it is also where things break. pg_partman replaces that work with a configuration row and a maintenance call.
Register the parent and premake partitions
After installing the extension with CREATE EXTENSION, you register the partitioned parent through create_parent. You give it the control column (your event timestamp) and the interval. pg_partman records this in its part_config table. The premake setting tells it how many future partitions to keep ahead of the current time. Size premake so that a few missed maintenance runs still leave headroom. If inserts start landing in the DEFAULT partition, the system is already behind.
Schedule maintenance
run_maintenance creates upcoming partitions and applies retention. You can run it from pg_partman's background worker, which is loaded via shared_preload_libraries. You can also run it from cron or your job scheduler. Some managed hosts restrict which extensions you can install or block custom background workers, so confirm before you design around either option.
Retention
Set a retention interval and pg_partman detaches or drops expired partitions. Dropping a partition is a metadata operation. Deleting the same rows creates dead tuples, generates WAL and leaves vacuum with work to do afterwards. Keep detached tables if you need to archive them before you drop them.
Late-arriving events
An event whose partition is gone lands in DEFAULT or fails. Alert on rows in DEFAULT, and set retention longer than your maximum expected lateness.
Function signatures and configuration options differ between major pg_partman versions, so follow the documentation for the version you have installed. The payoff is that you no longer have to maintain DDL scripts, timezone edge cases or month-boundary bugs yourself.
-- Written for pg_partman 5.x; older major versions use a different create_parent signature.
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
-- Native range-partitioned parent keyed on the event timestamp.
CREATE TABLE public.events (
event_id bigint NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);
-- Daily partitions, kept 7 days ahead so missed maintenance runs
-- don't push new inserts into the DEFAULT partition.
SELECT partman.create_parent(
p_parent_table => 'public.events',
p_control => 'occurred_at',
p_interval => '1 day',
p_premake => 7
);
-- Retention: keep 30 days. Set this longer than your maximum expected
-- event lateness. retention_keep_table = true detaches expired partitions
-- (for archiving) instead of dropping them.
UPDATE partman.part_config
SET retention = '30 days',
retention_keep_table = true
WHERE parent_table = 'public.events';
-- Run this on a schedule (cron or a job runner) if you don't use the
-- pg_partman_bgw background worker. It creates future partitions and
-- applies retention.
CALL partman.run_maintenance_proc();
-- Alerting check: any rows here mean an insert arrived with no matching
-- child partition (maintenance is behind, or the event is very late).
SELECT * FROM partman.check_default();
Registering a time-partitioned events table with pg_partman 5.x, configuring premade partitions and 30-day detach-based retention, running maintenance, and checking the DEFAULT partition for stray rows.
What are the alternatives?
pg_partman is one way to manage time-based partitions. Depending on the workload, one of these may be a better fit:
- No partitioning, with better indexes. A BRIN index on a timestamp that increases with insert order is very small and works well for range scans. Composite B-tree indexes fix many slow-query problems without any schema change. Neither helps with retention cost.
- Batched
DELETEfor retention. Deleting in small chunks on a schedule keeps an unpartitioned table bounded in size. It is simple, but every deleted row still becomes a dead tuple that vacuum must process. That cost grows with ingest volume. - Hand-written partition DDL. A script or scheduled job that runs
CREATE TABLE ... PARTITION OFandDROP TABLEfor upcoming and expired ranges. This avoids an extension dependency, but you must handle boundary calculation, timezones, idempotency and failure alerting yourself. - Partition creation from the application. Some teams create the next partition from application code or a deployment migration. This works for coarse intervals, but it ties database maintenance to release schedules. A missed deploy means missing partitions.
- Legacy inheritance-based partitioning. Before declarative partitioning, partitions were built with table inheritance,
CHECKconstraints and routing triggers. It still works, but declarative partitioning handles routing and pruning natively and is the better choice for new designs. - Moving old events out of PostgreSQL. If old events are rarely queried, exporting them to cheaper storage and keeping only a short window in the database reduces the problem size. It also adds an export pipeline that must be operated and monitored.
How can pg_partman save engineering time?
Most of the time saved comes from removing recurring, error-prone work rather than from any single task:
- No partition DDL to write or review. Each new interval no longer needs someone to calculate bounds, check for overlaps and run a migration. The interval is declared once in configuration.
- Fewer boundary and timezone bugs. Month lengths, leap years and UTC offsets are handled in one tested code path instead of in each team's custom scripts.
- Retention without cleanup jobs. Dropping or detaching expired partitions replaces long-running
DELETEjobs, along with the vacuum tuning and bloat investigations that tend to follow them. - Smaller incidents. With premade partitions and a DEFAULT-partition alert, a failed maintenance run becomes a warning to fix during working hours, not an outage where inserts fail.
- A consistent pattern across tables. Once one event table is configured, adding another partition set is a single
create_parentcall. Each team does not have to invent its own approach.
The time does not drop to zero. Someone still has to choose the interval, plan the migration of existing tables, and own the monitoring described below. What disappears is the routine work that has to be repeated for every interval.
How do you migrate an existing large table to a partitioned layout?
Moving a live table is a copy, not an in-place conversion. Most of the work goes into keeping the copy correct.
Steps
- Create the partitioned table with the same columns, plus partitions that cover the full historical range and the near future. Run pg_partman against it so new partitions already exist at cutover.
- Backfill in batches by time range, such as one day per transaction. Short transactions keep locks brief, limit WAL bursts and replication lag, and let you resume after a failure.
- Keep new writes in sync. You can dual-write from the application or a trigger, or you can stop writes briefly while the last batch copies. A short freeze is simpler and avoids subtle ordering bugs if you can tolerate it.
- Verify before cutover. Compare row counts per time range rather than one global total, so a mismatch points to the batch that caused it.
- Swap the tables by renaming both in a single transaction. Set a
lock_timeoutso that if the rename waits behind long queries, it fails fast instead of queuing every other session behind it.
Space, rollback and integrations
During the migration you need disk space for a second full copy, including its indexes. To roll back, keep the old table under a new name and reverse the renames. Any writes made after cutover must be copied back first.
Test these integrations before rollout:
- Foreign keys pointing to or from the table, because partitioned tables have extra constraints here.
- Logical replication publications, which follow the old table rather than its name.
- Tooling that inspects the catalog, such as ORMs, backup scripts and migration frameworks.
Cost
The development cost is mostly the time spent on dual-write logic and on rehearsals against a production-sized copy. The ongoing operational cost is monitoring pg_partman's maintenance job.
-- 1. Backfill one day per transaction so locks stay short and a failure can resume from the last day.
BEGIN;
INSERT INTO events_new (id, created_at, event_type, payload)
SELECT id, created_at, event_type, payload
FROM events
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02';
COMMIT;
-- 2. Verify per day, not one global total, so a mismatch points at the batch that caused it.
SELECT coalesce(o.day, n.day) AS day, o.cnt AS old_rows, n.cnt AS new_rows
FROM (SELECT date_trunc('day', created_at) AS day, count(*) AS cnt
FROM events GROUP BY 1) o
FULL OUTER JOIN
(SELECT date_trunc('day', created_at) AS day, count(*) AS cnt
FROM events_new GROUP BY 1) n USING (day)
WHERE o.cnt IS DISTINCT FROM n.cnt
ORDER BY 1; -- an empty result means every day matches
-- 3. Swap both names atomically. If the renames wait behind a long query,
-- fail after 5s instead of queuing every other session behind the lock.
BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
COMMIT;
-- Rollback: reverse the renames in one transaction. Copy back any rows
-- written after cutover first, or they stay only in the partitioned table.
-- BEGIN;
-- SET LOCAL lock_timeout = '5s';
-- ALTER TABLE events RENAME TO events_new;
-- ALTER TABLE events_old RENAME TO events;
-- COMMIT;
Backfill one day per transaction, compare row counts for each day between the old and new tables, then swap the table names in a single transaction with a lock_timeout, keeping the reverse renames ready for rollback.
What should you monitor once partitioning is in production?
Three checks catch most production failures, and each one can be a query that returns a number for your alerting system to threshold.
Rows in the DEFAULT partition. A healthy DEFAULT partition is empty. Rows in it mean either a writer sent timestamps outside the expected range or maintenance fell behind. Alert on any non-zero count. pg_partman's check_default() function reports this, or you can run count(*) against the default table directly.
Future partition horizon. Find the highest upper bound among the parent's children by joining pg_inherits to pg_class and parsing pg_get_expr(relpartbound, oid). Alert when that bound is closer than your horizon, for example fewer than three intervals ahead. This check fires before data starts landing in DEFAULT, which gives you time to act.
Maintenance recency. Have the job that calls run_maintenance() write a timestamp to a heartbeat table when it succeeds. Alert when that timestamp is older than twice the schedule interval. A scheduler that has silently stopped looks healthy until the premade partitions run out.
Recovering from rows stuck in DEFAULT
PostgreSQL will not create a partition whose range covers rows that already sit in DEFAULT. The CREATE fails because the default partition's implicit constraint would be violated. Until those rows are moved, maintenance cannot create that partition.
To recover:
- Pause maintenance.
- In one transaction, copy the affected rows into a standalone table with the parent's structure.
- Delete those rows from DEFAULT.
- Attach the standalone table as the partition.
pg_partman's partition_data_proc automates these steps in batches. Run it during low traffic, because attaching a partition takes locks and scans DEFAULT to validate the constraint.
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.