TimescaleDB Retention Policies vs pg_partman

Two teams solving the same problem — expiring old time-series data on PostgreSQL — reach for different tools: pg_partman’s retention runs on declarative partitions maintained by pg_cron, while TimescaleDB’s add_retention_policy drops chunks through its own background scheduler. This page compares the two mechanisms head to head so you can pick the one that fits your storage engine, your compression strategy, and the operational surface you are willing to run.

# Input Profiling: What to Weigh Before Choosing

The decision is rarely about retention alone — it is about which lifecycle stack the retention step plugs into. Profile these five dimensions against your current deployment before committing:

• Storage engine already in place — Are you running a TimescaleDB hypertable, or vanilla PostgreSQL declarative partitions on the plain PARTITION BY RANGE syntax? The engine you already ingest into usually decides the winner by itself.
• Native compression integration — Does the expiry step need to coordinate with columnar compression? TimescaleDB retention understands compressed chunks and drops them the same as uncompressed ones; pg_partman has no concept of TimescaleDB compression and manages only heap partitions.
• Chunk vs partition granularity — Chunks are sized by chunk_time_interval and can be sub-daily (30 minutes, 1 hour); pg_partman partitions are typically daily, weekly, or monthly. Finer granularity means retention reclaims space in smaller, more predictable increments.
• Scheduler surface — TimescaleDB ships a background job scheduler that runs policies with no external dependency. pg_partman’s maintenance function must be driven by something — almost always pg_cron — which is a second extension to install, secure, and monitor.
• Aggregate awareness — If retention must respect downstream rollups so a drop does not orphan a continuous aggregate, that logic lives in the TimescaleDB catalog. pg_partman has no visibility into materialized rollups.

Two of these — engine and compression integration — tend to be decisive. The rest shape how much operational machinery you end up running to keep expiry on schedule.

# Feature Comparison

Both tools ultimately expire time-partitioned data by dropping storage rather than issuing row-level DELETEs, but they differ on every dimension that matters operationally:

# Decision Matrix: When Each Wins

TimescaleDB retention wins when you are already on hypertables, when compressed columnar data must expire cleanly, when retention must interlock with a continuous aggregate downsampling tier, or when you want zero external scheduler dependencies. pg_partman wins when you are committed to vanilla PostgreSQL declarative partitioning, when the TimescaleDB extension is unavailable in your managed environment, when you already operate pg_cron for other maintenance, or when your partitions are keyed on something other than time (pg_partman also supports serial/ID partitioning that hypertables do not).

The dividing line is mostly your existing storage engine — the deeper analysis of that split lives in TimescaleDB chunk partitioning vs PostgreSQL table inheritance. If you are already ingesting into a hypertable, adding pg_partman on top is redundant; if you are on plain partitions and cannot adopt TimescaleDB, pg_partman is the mature answer.

# Equivalent SQL, Side by Side

On a hypertable, retention is a single policy call that the background scheduler then runs on its own interval:

-- TimescaleDB: assumes sensor_readings is already a hypertable.
-- Compression and retention are two independent policies on the same table.
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days',
                              if_not_exists => TRUE);

SELECT add_retention_policy('sensor_readings',
                            drop_after   => INTERVAL '90 days',
                            if_not_exists => TRUE);

-- Inspect the scheduled job the policy created.
SELECT job_id, application_name, schedule_interval, config
FROM   timescaledb_information.jobs
WHERE  proc_name = 'policy_retention';

On vanilla PostgreSQL, pg_partman first takes over the parent table, then a retention setting plus a scheduled maintenance job does the expiry:

-- pg_partman: sensor_readings is a declaratively partitioned parent
-- (CREATE TABLE ... PARTITION BY RANGE (time)) with no children yet.
SELECT partman.create_parent(
    p_parent_table => 'public.sensor_readings',
    p_control      => 'time',
    p_type         => 'range',
    p_interval     => '1 day',       -- one partition per day
    p_premake      => 4              -- keep four future partitions ready
);

-- Configure retention: expire partitions older than 90 days and DROP them.
UPDATE partman.part_config
SET    retention          = '90 days',
       retention_keep_table = false   -- false = DROP; true = only detach
WHERE  parent_table = 'public.sensor_readings';

-- Drive maintenance on a schedule (pg_cron). This both premakes future
-- partitions and applies retention on every run.
SELECT cron.schedule('partman-maintenance', '@hourly',
                     $$SELECT partman.run_maintenance('public.sensor_readings')$$);

The structural difference is visible in the code: TimescaleDB expiry is declarative and self-scheduled, while pg_partman couples partition pre-creation and retention into one run_maintenance call that you must keep on a cron schedule.

# Worked Example: An IoT Fleet Deciding

Consider a fleet of 40,000 devices reporting every 15 seconds — roughly 2,600 rows/sec, about 225 million rows per day. The platform must keep 90 days of raw telemetry queryable, compress anything older than 7 days, and feed a set of hourly and daily rollups for dashboards. Storage growth is the pressure point: uncompressed, 90 days is well over 20 billion rows.

Score the two options against this workload:

• Compression is mandatory, so the retention step must expire compressed chunks. pg_partman cannot see TimescaleDB compression, which immediately makes it a poor fit unless the team abandons columnar compression entirely — not an option at this row volume.
• Rollups must not be orphaned. Raw retention at 90 days feeds hourly and daily aggregates on longer horizons; keeping those consistent is far simpler when retention and the aggregates share the TimescaleDB catalog.
• Sub-daily granularity helps. At 2,600 rows/sec the team runs a 1-hour chunk_time_interval, so retention reclaims storage in hourly increments instead of waiting for a whole daily partition to age out.

The choice is TimescaleDB retention. The lifecycle collapses to three policy calls on one hypertable:

-- Ingest path is a hypertable with a 1-hour chunk interval.
SELECT create_hypertable('sensor_readings', 'time',
                         chunk_time_interval => INTERVAL '1 hour',
                         if_not_exists       => TRUE);

-- Compress cold chunks, then expire them at 90 days — retention drops
-- compressed chunks without any special handling.
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days',
                              if_not_exists => TRUE);
SELECT add_retention_policy('sensor_readings',
                            drop_after => INTERVAL '90 days',
                            if_not_exists => TRUE);

Had this fleet been running on a managed PostgreSQL offering without the TimescaleDB extension, the same 90-day expiry would fall to pg_partman with daily partitions and an hourly pg_cron maintenance job — functional, but without compression-aware drops and with a second extension to operate.

# Edge Cases & Migration Considerations

• Already on native partitioning — If you run plain PostgreSQL declarative partitions and cannot install TimescaleDB, pg_partman is the right tool; there is no benefit to bolting a hypertable-only feature onto heap partitions. Keep retention_keep_table explicit so you know whether expiry drops or merely detaches.
• Mixed environments — In a database instance that hosts both hypertables and plain partitioned tables, let each table use its native mechanism: add_retention_policy on hypertables, pg_partman on the rest. Do not point pg_partman at a hypertable — chunk management belongs to TimescaleDB, and the two will fight over the same child relations.
• Migrating from pg_partman to TimescaleDB — Convert the parent with create_hypertable(..., migrate_data => TRUE) on a maintenance window, then drop the pg_partman config row and unschedule the pg_cron maintenance job so it does not keep pre-creating partitions the hypertable will not use. Re-add retention as a native policy afterward.
• Retention window shorter than compression age — If drop_after is smaller than the compression policy’s interval, chunks may expire before they are ever compressed; that is fine mechanically, but it means you are paying to store hot data you immediately delete. Align the two so the horizons make sense, the same alignment principle covered in TTL policy mapping and enforcement.
• Interval alignment — Whichever tool you use, keep the retention window an exact multiple of the chunk or partition span. A drop_after that straddles a boundary leaves partial chunks in place until the whole chunk ages out — sizing that lives in time-based chunk partitioning strategies.

# Verification

The proof for either tool is the same question asked two ways: has old data actually been dropped? On TimescaleDB, confirm no chunk survives past the retention horizon and that the retention job succeeded on its last run:

-- No chunk should have a range_end older than the 90-day horizon.
SELECT chunk_name, range_start, range_end, is_compressed
FROM   timescaledb_information.chunks
WHERE  hypertable_name = 'sensor_readings'
  AND  range_end < NOW() - INTERVAL '90 days';   -- expect zero rows

-- The retention job ran cleanly on its last invocation.
SELECT job_id, last_run_status, last_successful_finish, total_successes
FROM   timescaledb_information.job_stats
WHERE  job_id IN (
        SELECT job_id FROM timescaledb_information.jobs
        WHERE proc_name = 'policy_retention');

On pg_partman, verify that no child partition older than the retention window is still attached, and that maintenance is actually running on schedule:

-- List surviving child partitions and their oldest bound; none should
-- predate the 90-day window if retention is working.
SELECT inhrelid::regclass AS partition,
       pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM   pg_inherits i
JOIN   pg_class c ON c.oid = i.inhrelid
WHERE  i.inhparent = 'public.sensor_readings'::regclass
ORDER  BY partition;

-- Confirm the pg_cron maintenance job succeeded recently.
SELECT jobname, status, start_time, end_time
FROM   cron.job_run_details
WHERE  jobname = 'partman-maintenance'
ORDER  BY start_time DESC
LIMIT  5;

If either query returns partitions or chunks older than the horizon, retention is not keeping up: on TimescaleDB check the job’s last_run_status; on pg_partman check that run_maintenance is scheduled and that future partitions were pre-created so maintenance is not stalling on a missing target.

# Related

• Chunk Compression Scheduling & Automation — how compression policies interlock with the retention step
• TTL Policy Mapping & Enforcement — aligning retention windows with downstream lifecycle rules
• Time-Based Chunk Partitioning Strategies — sizing the chunks that retention drops
• TimescaleDB Chunk Partitioning vs PostgreSQL Table Inheritance — the engine-level split behind this comparison
• Data Retention, Compression & Lifecycle Automation — the parent guide to expiring and tiering time-series data

← Back to Chunk Compression Scheduling & Automation