Monitoring Retention Drops and Storage Reclamation

A retention policy can report a clean run while total disk usage stays flat, because dropping a chunk removes catalog rows and files but does not always hand free space back to the operating system — so the real question is not “did the job succeed?” but “did the oldest chunk advance past the horizon and did bytes actually get reclaimed?” This page separates those two signals: it profiles the retention job, tracks the oldest chunk’s range_start against the drop_after boundary to prove the sweep is advancing, and trends hypertable_size to catch the case where drops succeed but storage never falls.

Input Profiling: What to Gather First

Storage reclamation is a chain of three independent events, and any link can break silently. Profile all three before you trust a green dashboard:

The retention job itself — is it scheduled, enabled, and completing without error? Read this from timescaledb_information.jobs and timescaledb_information.job_stats.
The chunk boundary — is the oldest surviving chunk actually newer than the drop_after horizon? Read chunk ranges from timescaledb_information.chunks.
The bytes on disk — is total relation size trending down after drops, or is bloat holding it flat? Read this from hypertable_size and pg_total_relation_size.

Start by confirming the retention policy exists and is running. TimescaleDB registers retention as a background job whose configuration carries the drop_after interval:

sql

-- Retention jobs and their configured drop_after horizon.
SELECT
    j.job_id,
    j.hypertable_name,
    j.schedule_interval,
    j.config ->> 'drop_after'        AS drop_after,
    s.last_run_status,
    s.last_successful_finish,
    s.total_runs,
    s.total_failures
FROM timescaledb_information.jobs      AS j
JOIN timescaledb_information.job_stats AS s USING (job_id)
WHERE j.proc_name = 'policy_retention'
ORDER BY j.hypertable_name;

A last_run_status of Success with a rising total_runs tells you the job fires — but nothing about whether it dropped anything or whether disk fell. Those come next.

Proving the Boundary Advances

The load-bearing invariant of a healthy retention policy is simple: the oldest chunk’s range_start must never fall further behind the drop_after horizon than one chunk_time_interval. If the oldest chunk keeps aging past now() - drop_after, the sweep has stalled even when the job reports success (a common symptom when a chunk is pinned by an open transaction, a dependent continuous aggregate, or a lock timeout).

Define the horizon and the observed lag precisely. Let $t_{now}$ be the current time, $D$ the drop_after interval, and $r_{min}$ the range_start of the oldest chunk. The retention horizon is:

H = t_{now} - D

The policy is healthy when the oldest chunk begins after the horizon, i.e. $r_{min} \ge H$ . The observed overshoot — how far past the horizon the oldest data still stretches — is:

L_{drop} = H - r_{min} = (t_{now} - D) - r_{min}

A healthy sweep keeps $L_{drop} \le \Delta_{chunk}$ (one chunk interval, the unavoidable granularity of chunk-level drops). A growing $L_{drop}$ is the alarm signal. This query computes it directly:

sql

-- Oldest chunk vs the drop_after horizon, per hypertable.
WITH policy AS (
    SELECT
        j.hypertable_name,
        (j.config ->> 'drop_after')::interval AS drop_after
    FROM timescaledb_information.jobs AS j
    WHERE j.proc_name = 'policy_retention'
),
oldest AS (
    SELECT
        c.hypertable_name,
        min(c.range_start) AS oldest_range_start
    FROM timescaledb_information.chunks AS c
    GROUP BY c.hypertable_name
)
SELECT
    p.hypertable_name,
    p.drop_after,
    o.oldest_range_start,
    now() - p.drop_after                             AS horizon,
    o.oldest_range_start - (now() - p.drop_after)    AS margin_past_horizon
FROM policy AS p
JOIN oldest AS o USING (hypertable_name);

A positive margin_past_horizon means the oldest chunk starts after the horizon — the sweep is keeping up. A negative value equal to less than one chunk interval is normal (chunks drop only when their entire range clears the horizon). A negative value that grows run over run means drops are not happening.

Detecting “Drops Succeed but Size Stays Flat”

The subtler failure is the one the opening SVG illustrates: chunks are dropping, margin_past_horizon looks fine, yet hypertable_size refuses to fall. Dropping a chunk unlinks its underlying table and frees those files immediately, so the drop itself reclaims space. What stays flat is the space consumed by bloat in the surviving chunks — dead tuples from updates and late-arriving deletes, plus TOAST overhead — which only VACUUM returns and only VACUUM FULL or compression returns to the OS.

Trend total size on a fixed cadence and compare it to the drop activity:

sql

-- Total hypertable footprint, split into table, index, and toast bytes.
SELECT
    hypertable_name,
    pg_size_pretty(total_bytes) AS total,
    pg_size_pretty(table_bytes) AS heap,
    pg_size_pretty(index_bytes) AS indexes,
    pg_size_pretty(toast_bytes) AS toast,
    total_bytes
FROM hypertable_detailed_size('metrics') AS s
CROSS JOIN LATERAL (SELECT 'metrics'::text AS hypertable_name) n;

To make the flat-size case unambiguous, record total_bytes into a small tracking table each time the retention job runs, alongside the chunk count. When chunk count drops but total_bytes does not, bloat is the culprit:

sql

CREATE TABLE IF NOT EXISTS retention_audit (
    captured_at   timestamptz DEFAULT now(),
    hypertable    text,
    chunk_count   int,
    total_bytes   bigint
);

INSERT INTO retention_audit (hypertable, chunk_count, total_bytes)
SELECT
    'metrics',
    (SELECT count(*) FROM timescaledb_information.chunks
     WHERE hypertable_name = 'metrics'),
    (SELECT total_bytes FROM hypertable_detailed_size('metrics'));

-- The diagnostic: chunk count falling while bytes hold flat.
SELECT
    captured_at,
    chunk_count,
    chunk_count - lag(chunk_count) OVER w AS chunks_delta,
    pg_size_pretty(total_bytes)          AS total,
    total_bytes - lag(total_bytes) OVER w AS bytes_delta
FROM retention_audit
WHERE hypertable = 'metrics'
WINDOW w AS (ORDER BY captured_at)
ORDER BY captured_at DESC
LIMIT 10;

If successive rows show chunks_delta of -3 while bytes_delta hovers near zero, the drops are working and the reclamation is not. That is your cue to look at autovacuum settings, n_dead_tup on the hot chunks, or an overdue compression run.

Worked Example

A fleet hypertable metrics uses a 6-hour chunk_time_interval and a 30-day retention policy. On 2026-07-17 at 12:00 the horizon is $H = t_{now} - 30\text{d} =$ 2026-06-17 12:00. The oldest chunk’s range_start reads 2026-06-17 06:00.

L_{drop} = (t_{now} - D) - r_{min} = \text{12:00} - \text{06:00} = 6\text{ hours}

That 6-hour overshoot is exactly one chunk interval — the oldest chunk’s range is [06:00, 12:00), and it will drop the moment its whole range clears the horizon. The boundary is advancing correctly, so no alarm.

Now the storage side. Before the run, retention_audit shows 124 chunks at 41.2 GB. After the run: 121 chunks, but still 41.0 GB — three chunks gone, only 200 MB reclaimed. Each 6-hour chunk holds roughly 340 MB, so a clean drop of three should have returned near 1 GB. The missing ~800 MB is dead-tuple bloat in the recent chunks from a backfill job that rewrote rows. Running VACUUM (ANALYZE) on the active chunks, or letting the chunk compression scheduling automation convert them to the columnar format, returns the footprint to the expected curve. This is the exact split the SVG shows: the drops move the boundary, but the gauge only falls once bloat is cleared.

Edge Cases & When to Deviate

Compressed chunks and TOAST bloat — once a chunk is compressed, its size lives largely in a companion internal table and the compressed heap; a plain pg_relation_size on the user-facing chunk under-reports it. Always use pg_total_relation_size or hypertable_detailed_size, which fold in TOAST and the compressed relation, or a “reclaimed” number will look larger than reality.
Retention on continuous aggregates vs raw hypertables — a retention policy on a continuous aggregate drops the materialized chunks, not the raw ones. If you expect raw storage to fall but only added the policy to the aggregate (or vice versa), the boundary query will look healthy on one object while disk stays flat on the other. Run both the boundary and size queries against every object that carries data, keyed by hypertable_name.
drop_after not aligned to the chunk boundary — retention drops a chunk only when its entire range is older than the horizon. With a 6-hour interval and a 30-day drop_after, up to one full interval of data older than the nominal horizon survives until its chunk fully clears. This is expected; treat a residual $L_{drop}$ up to one chunk_time_interval as normal, and only alarm on growth beyond it. Aligning drop_after to an exact multiple of the interval — a technique covered in TTL policy mapping and enforcement — makes the sweep land on tidy boundaries.
Open long-running transactions — a transaction that started before the chunk aged out can block the drop entirely, so the job logs success on the chunks it could remove while the pinned chunk lingers. A stalled margin_past_horizon combined with a Success status points here; check pg_stat_activity for old xact_start values.

Verification

A retention policy is healthy only when both signals check out at once. Confirm them together:

sql

-- One-shot health check: boundary margin AND size trend.
WITH policy AS (
    SELECT (config ->> 'drop_after')::interval AS drop_after
    FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
      AND hypertable_name = 'metrics'
)
SELECT
    (SELECT min(range_start) FROM timescaledb_information.chunks
        WHERE hypertable_name = 'metrics')                       AS oldest_chunk,
    now() - (SELECT drop_after FROM policy)                      AS horizon,
    (SELECT min(range_start) FROM timescaledb_information.chunks
        WHERE hypertable_name = 'metrics')
        >= now() - (SELECT drop_after FROM policy)               AS boundary_ok,
    pg_size_pretty(
        (SELECT total_bytes FROM hypertable_detailed_size('metrics'))
    )                                                            AS current_size;

You are done when boundary_ok is true (oldest chunk age within one interval of drop_after) and retention_audit shows total_bytes trending downward across the last several runs rather than holding flat. If the boundary passes but size does not fall, the retention policy is not the problem — bloat or a missing compression pass is, and the fix lives in vacuum and compression, not in the drop schedule.

Compression & Retention Observability — the parent guide covering compression ratios, retention health, and lifecycle telemetry
Tracking Compression Ratio Trends Over Time — the sibling metric that explains where reclaimed bytes actually go
Chunk Compression Scheduling Automation — converting bloated chunks to columnar storage to return space to the OS
TTL Policy Mapping & Enforcement — aligning drop_after with chunk boundaries so the sweep lands cleanly

← Back to Compression & Retention Observability · Up to Monitoring, Observability & Alerting

Monitoring Retention Drops and Storage Reclamation

# Input Profiling: What to Gather First

# Proving the Boundary Advances

# Detecting “Drops Succeed but Size Stays Flat”

# Worked Example

# Edge Cases & When to Deviate

# Verification

# Related