How does PostgreSQL range partitioning by time work?

Declarative partitioning splits one logical table into several physical tables. You create a partitioned parent with PARTITION BY RANGE (occurred_at). The parent stores no rows itself. It defines the columns and the partitioning rule, and every query and insert goes through it.

Each child partition is an ordinary table attached with a bound such as FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'). The lower bound is inclusive and the upper bound is exclusive. Because of that, adjacent monthly partitions share boundary values without overlapping, and a row stamped exactly at midnight on the first of the month has exactly one home. PostgreSQL rejects any new partition whose range overlaps an existing one.

A minimal hand-written setup has three parts:

  • An events parent partitioned on occurred_at.
  • Two monthly children, events_2025_01 and events_2025_02.
  • Optionally, a DEFAULT partition.

When you insert into events, PostgreSQL routes each row to the child whose range contains its occurred_at value. If no child matches and there is no default partition, the insert fails. That failure is why someone, or something, has to create future partitions ahead of time.

The payoff comes at query time. When a WHERE clause constrains occurred_at, the planner compares the condition against each child's bounds and prunes partitions that cannot contain matching rows. If the value is a literal, pruning happens at plan time. If it is a parameter or comes from a join, pruning can also happen during execution. A query for last Tuesday's events touches one month's table instead of years of history.

Retention also becomes a metadata operation. Removing old data means detaching or dropping a whole partition rather than running a large DELETE that generates dead tuples and vacuum work.

Creating partitions, attaching them, and dropping expired ones on a schedule is exactly the repetitive work that pg_partman automates.

-- Parent: defines columns and the partitioning rule, stores no rows itself.
CREATE TABLE events (
    event_id    bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    event_type  text        NOT NULL,
    payload     jsonb,
    -- A primary key on a partitioned table must include the partition key.
    PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- Monthly children: lower bound inclusive, upper bound exclusive.
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');

-- Optional catch-all for rows that match no child range.
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Routed to events_2025_02: midnight on Feb 1 belongs only to February.
INSERT INTO events (occurred_at, event_type)
VALUES ('2025-02-01 00:00:00+00', 'signup');

-- Literal bounds on the partition key let the planner prune at plan time;
-- the plan should scan only events_2025_01.
EXPLAIN
SELECT count(*)
FROM events
WHERE occurred_at >= '2025-01-14'
  AND occurred_at <  '2025-01-15';

-- Retention as a metadata operation: detach, then drop the whole month.
ALTER TABLE events DETACH PARTITION events_2025_01;
DROP TABLE events_2025_01;

A hand-written monthly range-partitioned events table showing the parent, bounded children, a default partition, insert routing, plan-time pruning, and partition-level retention, which are the steps pg_partman later automates.

When should you partition an event table, and when should you avoid it?

Partitioning pays off when three things are true together: the table is large and append-heavy, most queries filter on the time column, and you have a retention policy that removes old data in whole time ranges. Event logs, audit trails and metrics tables usually fit. A table that is merely big, with random updates and lookups by user ID, usually does not.

The costs

  • Queries without a time predicate get slower. The planner cannot prune, so it scans every partition and probes every partition's index. A lookup by event_id alone becomes one index probe per partition instead of one.
  • Partition count has a price. Each partition is a separate relation with its own catalog entries, statistics and indexes. Thousands of small partitions increase planning time and lock overhead, and they make autovacuum and backups track more objects.
  • Constraints are restricted. A primary key or unique constraint must include the partition key, so global uniqueness on event_id alone cannot be enforced. Foreign keys that point at the table must target such a composite key.
  • Retention is coarse-grained. Dropping partitions removes whole intervals. Keeping selected rows longer, or deleting one customer's data, falls back to row-level DELETE.
Signal Partition Leave unpartitioned
Query shape Bounded time ranges Point lookups by non-time keys
Write pattern Append-mostly Frequent updates to old rows
Retention Drop data by age Keep everything, or delete by entity
Uniqueness Composite key with timestamp is acceptable Need a global unique ID constraint

Decision checklist

  1. Do most hot queries include a time range?
  2. Is the table large enough that index maintenance and vacuum are measurable problems?
  3. Does your retention policy expire data by age?
  4. Can your primary key include the timestamp?
  5. Will the chosen interval keep the partition count moderate over the retention window?

If you answer "no" to the first or fourth question, fix indexing or the schema before partitioning.

How do you choose the partition key, interval and primary key?

The partition key should be the timestamp your queries filter on and your retention policy ages out, usually the event's occurred_at rather than its ingest time. If those two differ, pick the one that appears in WHERE clauses. Pruning only works on the key column.

Choose the interval from three inputs: ingest volume, typical query range, and retention. A common query should touch one to a few partitions, and retention should drop whole partitions.

Illustrative estimate, with assumed inputs of 40 million events per day at about 250 bytes per row including indexes (roughly 10 GB/day), 90-day retention, and dashboards querying the last 1 to 7 days:

Interval Partitions over 90 days Approx. size each Typical 7-day query touches
Hourly 2,160 ~0.4 GB 168 partitions
Daily 90 ~10 GB 7 partitions
Monthly 3 ~300 GB 1–2 partitions

Daily fits this workload. Monthly partitions are large to vacuum and reindex, and they make retention coarse because you hold up to a month of extra data. Hourly is too fine. Each partition is a separate relation with its own catalog entries, locks, and relcache memory. Planning cost grows with partition count, especially when pruning can only happen at execution time, such as with generic plans for prepared statements.

Unique constraints must include the partition key. A primary key on event_id alone is rejected, so you need (event_id, occurred_at). Postgres then no longer guarantees that event_id is globally unique, only that each pair is unique. Generate IDs so that collisions are impossible, for example with a sequence or UUIDs. Lookups by ID alone scan every partition unless the caller also supplies the timestamp. Foreign keys that reference events must also carry both columns.

-- Partition key is the timestamp queries filter on and retention ages out.
CREATE TABLE public.events (
    event_id    bigint GENERATED ALWAYS AS IDENTITY,  -- one sequence for all partitions
    occurred_at timestamptz NOT NULL,
    event_type  text        NOT NULL,
    payload     jsonb,
    -- PRIMARY KEY (event_id) alone is rejected: unique constraints on a
    -- partitioned table must include all partitioning columns.
    PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- Daily interval, keeping 4 future partitions ready ahead of ingest
-- (pg_partman 5.x named-argument form; 4.x also requires p_type).
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'occurred_at',
    p_interval     => '1 day',
    p_premake      => 4
);

-- 90-day retention drops whole daily partitions instead of running DELETEs.
UPDATE partman.part_config
SET    retention = '90 days',
       retention_keep_table = false
WHERE  parent_table = 'public.events';

-- Lookup by ID alone checks every partition.
SELECT * FROM public.events WHERE event_id = 123456789;

-- Supplying the timestamp lets the planner prune to one partition.
SELECT * FROM public.events
WHERE  event_id = 123456789
  AND  occurred_at = '2026-11-08 14:03:12+00';

Creates a daily-partitioned events table whose primary key includes occurred_at, registers it with pg_partman with 90-day retention, and contrasts an ID-only lookup with one that can prune partitions.

How do you set up pg_partman for automatic partition creation and retention?

Install the extension into its own schema (conventionally partman) so its functions and the part_config table stay separate from application objects. Then register the parent table with create_parent, passing the partition key column and the interval. pg_partman creates the initial child partitions and adds a row to part_config, which holds the settings maintenance reads on every run.

The premake setting in part_config controls how many future partitions exist ahead of the current one. A few is usually enough. The buffer exists so that a missed maintenance run does not immediately push new rows into the default partition. A larger value gives you more time to notice a failure, at the cost of empty tables in the catalog.

Retention is also set in part_config. Set retention to an interval such as 90 days, and use retention_keep_table to choose whether expired partitions are detached (kept as standalone tables you can archive) or dropped. Either option is much cheaper than a large DELETE. A DELETE writes WAL for every row and leaves dead tuples that vacuum must clean up, while the table and its indexes stay bloated until then. Dropping a partition removes its files in one catalog operation and leaves nothing for vacuum to process.

Maintenance creates new partitions and enforces retention. You can run it in two ways:

  • Background worker: Add the worker to shared_preload_libraries and configure its interval and target database. Changing shared_preload_libraries requires a server restart.
  • Scheduler: Call run_maintenance (or the procedure variant) from cron or a job scheduler.

Managed providers may not offer the extension, or may offer it without the background worker. Check this before you design around it. Function signatures and parameter names have changed between major releases, so use the documentation for the version you have installed. Test extension upgrades on a copy of production before you apply them.

-- Assumes pg_partman 5.x. Parameter names and defaults differ in 4.x, so check your version's docs.
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

-- The parent must be natively range-partitioned on the time column.
-- The primary key has to include the partition key.
CREATE TABLE public.events (
    event_id    bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    payload     jsonb,
    PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- Register the parent. This creates the initial daily children and a part_config row.
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'occurred_at',
    p_interval     => '1 day'
);

-- Keep 4 future partitions so a missed maintenance run doesn't send rows to the default partition.
-- Partitions older than 90 days are dropped (keep_table = false), not deleted row by row.
UPDATE partman.part_config
SET premake              = 4,
    retention            = '90 days',
    retention_keep_table = false  -- true = detach and keep as a standalone table for archiving
WHERE parent_table = 'public.events';

-- Option A: run maintenance from cron or a job scheduler.
CALL partman.run_maintenance_proc();

-- Option B: background worker, configured in postgresql.conf (requires a restart):
--   shared_preload_libraries = 'pg_partman_bgw'
--   pg_partman_bgw.interval  = 3600        -- seconds between runs
--   pg_partman_bgw.dbname    = 'app_db'
--   pg_partman_bgw.role      = 'partman_maint'

Installs pg_partman in its own schema, registers a daily-partitioned events table, sets premake and drop-based 90-day retention in part_config, and runs maintenance either through a scheduled procedure call or the background worker.

What happens when maintenance fails or rows land in the default partition?

If the scheduled maintenance job stops running, nothing breaks right away. pg_partman creates partitions ahead of time, so the failure only shows up once inserts reach the last premade partition. After that, one of two things happens. Without a default partition, inserts fail with a "no partition found for row" error. With a default partition, inserts succeed, and rows quietly accumulate in the default.

The second outcome is harder to recover from. When you create a partition, PostgreSQL scans the default partition to make sure no rows belong in the new range. If matching rows exist, the creation fails. Maintenance then stays stuck until you move those rows out. pg_partman provides a procedure for this, which moves rows in small batches into correctly bounded child tables. Use the batch options. A single large move holds locks and produces a burst of WAL.

Monitoring checks

  • Partition lead time: compare the highest partition upper bound, read from the catalog, against now(). Alert when fewer than a few intervals remain.
  • Default partition row count: in steady state this should be zero. Alert on any rows.
  • Job health: record each maintenance run and alert when the last successful run is older than the interval.

Retention conflicts

Dropping partitions for retention removes an entire time range with no way to make exceptions. If some rows are under a legal hold, configure pg_partman to detach expired partitions instead of dropping them. You can then archive the detached tables or keep them separately.

Late corrections cause a similar problem. An update or insert that targets a range you have already dropped lands in the default partition, and this blocks future maintenance. Reject these writes at the application layer, or route them to a dedicated corrections table.

-- 1. Lead time: how far ahead do premade partitions reach?
--    show_partitions() excludes the default partition unless asked.
SELECT max(i.child_end_time)          AS last_upper_bound,
       max(i.child_end_time) - now()  AS lead_time
FROM partman.show_partitions('public.events', 'DESC') p
CROSS JOIN LATERAL partman.show_partition_info(
  p.partition_schemaname || '.' || p.partition_tablename
) i;
-- Alert if lead_time is shorter than a few partition intervals.

-- 2. Default partition: should be empty in steady state.
SELECT count(*) AS default_rows FROM public.events_default;

-- 3. See which time ranges the stray rows belong to.
SELECT date_trunc('day', created_at) AS day, count(*)
FROM public.events_default
GROUP BY 1
ORDER BY 1;

-- 4. Move rows out in small batches so locks stay short and
--    WAL is spread out. Each loop moves one interval of data
--    into a correctly bounded child, creating it if needed.
CALL partman.partition_data_proc(
  p_parent_table => 'public.events',
  p_interval     => '1 hour',
  p_loop_count   => 24,
  p_wait         => 2   -- seconds to pause between batches
);

-- 5. Confirm the default is empty, then let maintenance catch up.
SELECT count(*) FROM public.events_default;
CALL partman.run_maintenance_proc();

Checking partition lead time and default partition contents, then moving stray rows out in batches with partition_data_proc before rerunning maintenance.

How do you migrate an existing large table to a partitioned layout with minimal downtime?

Because PostgreSQL cannot convert an existing regular table into a partitioned table in place, migration means building a new partitioned table beside the old one and moving data across. Expect this to be the most expensive part of the project. Setting up pg_partman takes an afternoon. A safe migration can take weeks of planning and monitoring.

The migration sequence

  1. Create the partitioned table with the final key, primary key and indexes, and pre-create partitions that cover all historical data.
  2. Capture new writes. Either dual-write from the application or a trigger, or record a high-water mark (max id or timestamp) and plan a short catch-up window before cutover.
  3. Backfill in batches, oldest first. Small batches keep transactions short, which limits lock duration, WAL bursts and replication lag.
  4. Cut over inside one short transaction: run the final catch-up, then rename the old table aside and rename the new one into place. Keep the old table until you trust the new one, because it is your rollback path.

Illustrative estimate (assumed numbers, not a benchmark): backfill time = (rows ÷ batch size) × seconds per batch (2,000,000,000 ÷ 50,000) × 2 s = 80,000 s ≈ 22 hours, before any throttling you add to protect replicas.

Checks before cutover

  • Foreign keys: inbound references must include the partition key, because the primary key now includes it. Confirm your PostgreSQL version supports foreign keys that reference partitioned tables.
  • Sequences: reuse the old sequence, or set the new one past the current maximum value.
  • Queries: find statements that lack a time predicate, because they will scan every partition.
  • Rollback: decide in advance how writes made after cutover get back into the old table if you need to revert.

How do you confirm partition pruning with EXPLAIN?

Run EXPLAIN on the query and count the partitions under the Append (or Merge Append) node. If the predicate uses literal timestamps, the planner removes non-matching partitions at plan time, so only the relevant child tables appear. If the bound comes from a parameter or a stable function such as now(), the planner cannot prune at plan time. Pruning then happens at executor startup. EXPLAIN still lists the remaining children, and the plan shows a Subplans Removed: N line for the ones it skipped. With EXPLAIN ANALYZE, a partition that was pruned while the query ran shows (never executed).

Queries that scan every partition

There are two common causes:

  • No time predicate. A lookup by user_id or event_id alone has nothing to compare against partition bounds, so every partition is scanned, including the default partition. Each scan is an index probe, and the planning and locking cost grows with the partition count.
  • A function on the key. Predicates such as date_trunc('day', created_at) = '2024-05-01' or created_at::date = ... compare the output of an expression, not the column. Pruning matches only the raw partition key against the bounds, so the planner cannot use them.

Rewriting

Turn functions on the column into half-open ranges on the bare column: created_at >= '2024-05-01' AND created_at < '2024-05-02'. Apply the functions to constants instead.

For the application, add a time bound to every hot path. Callers that know an event ID often also know roughly when it happened. You can encode a timestamp in the ID, or keep a retention-length window such as "last 30 days" as the default. Then add a check to CI or code review that fails when a query against the event table has no created_at predicate.

What are the alternatives to pg_partman, and how do they affect engineering cost?

pg_partman is not the only way to handle event-table growth. The alternatives differ mainly in who writes and maintains the creation and retention logic.

Approach Initial setup Ongoing maintenance Operational risk Managed-service support
pg_partman Moderate: install, configure, schedule maintenance Low: monitor the job and the default partition Low if maintenance is alerted on Varies by provider; check its extension allowlist
Hand-rolled cron scripts Moderate: write DDL generation, locking, error handling Medium: you own the bugs and the edge cases Medium: silent failures are common Works anywhere you can connect and run DDL
Manual native partitions Low High: someone creates partitions ahead of time High: a forgotten partition sends rows to default or fails inserts Works anywhere
Single table, indexes, batched deletes Lowest Medium: tune batches, watch bloat and vacuum Medium: deletes cause WAL volume, bloat and replica lag Works anywhere
Archive to external storage High: export pipeline, formats, query path Medium: pipeline upkeep Medium: correctness of exports and restores Depends on export tooling

Archiving works alongside partitioning. You can detach an old partition, export it, then drop it.

The cost difference comes from recurring work. A dropped partition is a metadata operation. A batched delete has to scan rows, write WAL and leave dead tuples for vacuum to clean up. A manual process also needs a person every cycle.

Illustrative estimate (assumed numbers, not a benchmark): monthly hours = (tables × minutes per table per cycle × cycles per month) / 60. With 12 tables, 15 minutes each and 4 weekly cycles, that is (12 × 15 × 4) / 60 = 12 hours per month. Automating creation and retention reduces this to monitoring time. It also removes the failure mode where someone forgets a cycle.

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 postgres partitioning pg_partman, 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.
  • Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

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.
  • Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
  • 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.