Compression & Retention Observability

Point-in-time storage numbers lie by omission: a single pg_size_pretty reading tells you how big a hypertable is right now, but says nothing about whether compression is still earning its keep or whether retention is actually returning bytes to the operating system. The engineering problem this guide solves is turning those isolated readings into durable trends — a small, self-maintained history of compression ratios, chunk counts, and drop activity that surfaces the moment a segmentby change quietly halves your ratio, or a stalled retention job lets disk creep upward for a week before anyone notices. This page sits inside monitoring, observability and alerting for TimescaleDB automation and assumes you already run compression and retention policies against production hypertables.

Compression and retention are the two levers that keep a time-series database from growing without bound, and both are driven by background jobs whose effect is only visible in aggregate. A ratio that drifts from 12x to 6x over a month costs you real money but never throws an error. A retention job that silently fails leaves the disk climbing on a slope too gentle to notice day to day. The remedy is the same in both cases: sample the storage stats on a schedule, store the samples in a hypertable, and compute deltas so a human or an alert rule can reason about the direction of storage, not just its current value.

# Prerequisites

Building a trend history depends on the compression stats functions being populated, on the retention and compression jobs actually running, and on a place to store snapshots that itself does not become a maintenance burden. Confirm each item before wiring up the snapshot writer.

• TimescaleDB 2.10 or newer on PostgreSQL 14+ — the hypertable_compression_stats, chunk_compression_stats, and timescaledb_information.job_stats shapes used below assume this baseline.
• At least one hypertable has compression enabled and has compressed at least one chunk, so the stats functions return non-empty rows (they error or return nulls on hypertables where compression was never enabled).
• A chunk compression scheduling policy and a retention TTL policy are registered, so job_stats has policy_compression and policy_retention rows to read.
• The connecting role can SELECT from timescaledb_information views and execute the stats functions against the target hypertables.
• timescaledb.max_background_workers has headroom for one more lightweight job if you schedule the snapshot writer as a TimescaleDB user-defined action rather than an external cron.
• You have decided a snapshot cadence (hourly is ample; retention and compression move on the scale of chunks, not seconds) and a retention window for the history table itself.

Verify that the stats functions return data before you build anything on top of them:

-- If this returns a row with a non-null after_compression_total_bytes, the
-- stats surface is populated and safe to snapshot.
SELECT total_chunks, number_compressed_chunks,
       before_compression_total_bytes, after_compression_total_bytes
FROM hypertable_compression_stats('sensor_readings');

# Step-by-Step Implementation

The four steps below map to the diagram: you first read the height of the raw and compressed bands (step 1), then confirm the jobs that move chunks between bands are healthy (step 2), persist both readings into a history hypertable so the bands become a time series (step 3), and finally compute trends and flag regressions from that history (step 4).

# 1. Read current storage stats

Two functions give you the compression picture. hypertable_compression_stats rolls a whole hypertable into one row — total chunks, how many are compressed, and before/after byte totals across table, index, and TOAST. chunk_compression_stats returns the same broken out per chunk, which you need to spot a single badly-compressing chunk hiding inside a healthy average. Join timescaledb_information.chunks for the is_compressed flag and range bounds:

-- Hypertable-level snapshot: coverage and the raw-vs-compressed byte totals
-- that become the two bands in the diagram.
SELECT
    'sensor_readings'                                    AS hypertable_name,
    total_chunks,
    number_compressed_chunks,
    total_chunks - number_compressed_chunks              AS uncompressed_chunks,
    before_compression_total_bytes,
    after_compression_total_bytes,
    -- guard against divide-by-zero before any chunk compresses
    CASE WHEN after_compression_total_bytes > 0
         THEN round(before_compression_total_bytes::numeric
                    / after_compression_total_bytes, 2)
         ELSE NULL END                                   AS compression_ratio
FROM hypertable_compression_stats('sensor_readings');

The before_compression_total_bytes and after_compression_total_bytes columns only account for chunks that have actually been compressed — freshly ingested raw chunks contribute to neither. To measure the raw band (the tall indigo region), size the uncompressed chunks separately from the catalog:

-- Bytes still sitting in the row store: the raw band the policy has not
-- yet reached. Summed with after_compression_total_bytes this is live disk.
SELECT
    count(*)                                       AS uncompressed_chunks,
    coalesce(sum(pg_total_relation_size(
        format('%I.%I', chunk_schema, chunk_name)::regclass)), 0) AS uncompressed_bytes
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_readings'
  AND is_compressed = false;

# 2. Read compression and retention job health

Storage stats tell you the state; job_stats tells you whether the machinery that produces that state is alive. A ratio that stops improving and a retention run that stops reclaiming both trace back to a job whose last_run_status is not Success. Read the compression and retention jobs together so a single query covers both bands’ drivers:

-- Health of the two policies that move chunks between bands. proc_name
-- 'policy_compression' shrinks the raw band; 'policy_retention' drops the
-- oldest chunks at the retention boundary.
SELECT
    j.job_id,
    j.proc_name,
    j.hypertable_name,
    js.last_run_status,
    js.last_successful_finish,
    js.last_run_duration,
    js.total_runs,
    js.total_successes,
    js.total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js USING (job_id)
WHERE j.proc_name IN ('policy_compression', 'policy_retention')
  AND j.hypertable_name = 'sensor_readings'
ORDER BY j.proc_name;

A retention job with a climbing total_failures is the leading indicator of a disk-space alert that has not fired yet. Capturing total_failures and last_successful_finish in every snapshot lets you alert on staleness — “retention has not succeeded in 26 hours” — which catches a wedged job long before the disk graph bends.

# 3. Persist periodic snapshots into a history hypertable

The stats functions are ephemeral: they recompute on every call and keep no history. To get trends, write each reading into a purpose-built hypertable. Keep it narrow, make it a hypertable so its own old rows are cheap to drop, and give it a modest chunk_time_interval since it accrues only a handful of rows per hour:

CREATE TABLE IF NOT EXISTS compression_stats_history (
    snapshot_time        timestamptz NOT NULL DEFAULT now(),
    hypertable_name      text        NOT NULL,
    total_chunks         integer,
    compressed_chunks    integer,
    uncompressed_chunks  integer,
    before_bytes         bigint,     -- raw size of compressed chunks
    after_bytes          bigint,     -- compressed size of those chunks
    uncompressed_bytes   bigint,     -- raw band not yet compressed
    compression_ratio    numeric(6,2),
    retention_last_success timestamptz,
    retention_failures   bigint
);

SELECT create_hypertable('compression_stats_history', 'snapshot_time',
                         chunk_time_interval => INTERVAL '7 days',
                         if_not_exists => TRUE);

-- The history table needs its own retention so the observer does not outgrow
-- the observed. Ninety days of hourly snapshots is a few thousand rows.
SELECT add_retention_policy('compression_stats_history',
                           drop_after => INTERVAL '90 days',
                           if_not_exists => TRUE);

The snapshot writer runs each cadence, collects the step-1 and step-2 readings, and inserts one row per monitored hypertable. Using psycopg v3, gather the stats and the job health in one transaction so the snapshot is internally consistent:

import psycopg
from psycopg.rows import dict_row


def write_snapshot(conn_str: str, hypertable: str) -> dict:
    """Collect compression + retention stats and persist one history row."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            # Hypertable-level compression totals.
            cur.execute("""
                SELECT total_chunks,
                       number_compressed_chunks AS compressed_chunks,
                       before_compression_total_bytes AS before_bytes,
                       after_compression_total_bytes  AS after_bytes
                FROM hypertable_compression_stats(%s);
            """, (hypertable,))
            comp = cur.fetchone() or {}

            # Raw band: chunks not yet compressed.
            cur.execute("""
                SELECT count(*) AS uncompressed_chunks,
                       coalesce(sum(pg_total_relation_size(
                           format('%%I.%%I', chunk_schema, chunk_name)::regclass)), 0)
                           AS uncompressed_bytes
                FROM timescaledb_information.chunks
                WHERE hypertable_name = %s AND is_compressed = false;
            """, (hypertable,))
            raw = cur.fetchone()

            # Retention job health for staleness alerting.
            cur.execute("""
                SELECT js.last_successful_finish, js.total_failures
                FROM timescaledb_information.jobs j
                JOIN timescaledb_information.job_stats js USING (job_id)
                WHERE j.proc_name = 'policy_retention'
                  AND j.hypertable_name = %s;
            """, (hypertable,))
            ret = cur.fetchone() or {}

            before = comp.get("before_bytes") or 0
            after = comp.get("after_bytes") or 0
            ratio = round(before / after, 2) if after else None

            cur.execute("""
                INSERT INTO compression_stats_history (
                    hypertable_name, total_chunks, compressed_chunks,
                    uncompressed_chunks, before_bytes, after_bytes,
                    uncompressed_bytes, compression_ratio,
                    retention_last_success, retention_failures)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
            """, (hypertable, comp.get("total_chunks"),
                  comp.get("compressed_chunks"), raw["uncompressed_chunks"],
                  before, after, raw["uncompressed_bytes"], ratio,
                  ret.get("last_successful_finish"),
                  ret.get("total_failures")))
        conn.commit()
    return {"hypertable": hypertable, "ratio": ratio}

Note the doubled %%I inside the SQL string: psycopg treats % as a parameter marker, so the literal percent signs format() needs must be escaped when the statement also carries %s placeholders.

# 4. Compute trends and detect regressions

With history accumulating, trends fall out of ordinary window functions. Bucket snapshots by day, average the ratio, and compare each day to the one before to expose the direction of travel:

-- Daily compression-ratio trend with day-over-day delta. A negative delta
-- means the ratio worsened that day.
SELECT
    day,
    round(avg_ratio, 2)                                          AS ratio,
    round(avg_ratio - lag(avg_ratio) OVER (ORDER BY day), 2)     AS delta,
    pg_size_pretty(avg_live_bytes::bigint)                       AS live_storage
FROM (
    SELECT time_bucket('1 day', snapshot_time)          AS day,
           avg(compression_ratio)                       AS avg_ratio,
           avg(after_bytes + uncompressed_bytes)        AS avg_live_bytes
    FROM compression_stats_history
    WHERE hypertable_name = 'sensor_readings'
      AND snapshot_time > now() - INTERVAL '30 days'
    GROUP BY 1
) d
ORDER BY day;

A regression is a ratio that drops meaningfully below its own recent baseline. Rather than a fixed threshold, compare a short trailing window against a longer one so seasonal ingestion changes do not trip the alert. Define the current ratio $r_t$ as the mean over the last day and the baseline $\bar{r}$ as the mean over the prior 14 days; flag a regression when

$$r_t < \bar{r}\,(1 - \delta), \qquad \delta \approx 0.15$$

that is, when the fresh ratio falls more than a tolerance $\delta$ below the established baseline. The same shape works for storage climbing under a failing retention policy — swap the ratio for after_bytes + uncompressed_bytes and invert the comparison:

-- Regression detector: current-day ratio vs the trailing 14-day baseline.
WITH windows AS (
    SELECT
        avg(compression_ratio) FILTER (
            WHERE snapshot_time > now() - INTERVAL '1 day')  AS current_ratio,
        avg(compression_ratio) FILTER (
            WHERE snapshot_time BETWEEN now() - INTERVAL '15 days'
                                    AND now() - INTERVAL '1 day') AS baseline_ratio
    FROM compression_stats_history
    WHERE hypertable_name = 'sensor_readings'
)
SELECT current_ratio, baseline_ratio,
       current_ratio < baseline_ratio * 0.85 AS ratio_regressed
FROM windows;

# Configuration Parameters Reference

The columns below are the raw material of every trend. They come from the two stats functions and the job_stats view; the reference names the source so you know which surface to query when a value looks wrong.

The compression ratio itself is the single most-watched trend. Define it as the raw size divided by the compressed size of the chunks that have been compressed:

$$r = \frac{S_{\text{before}}}{S_{\text{after}}}$$

For a hypertable reporting before_compression_total_bytes of 900 GB and after_compression_total_bytes of 75 GB, $r = 900 / 75 = 12$ — a 12x reduction, or equivalently a saving of $1 - S_{\text{after}}/S_{\text{before}} = 91.7\%$ on the compressed portion. Recording $r$ hourly turns “we compress well” into a line you can watch bend.

# Integration with Adjacent Features

Observability is the feedback loop that closes over the lifecycle stages; it reads the same surfaces those stages write.

• Compression scheduling. The trends here are the report card for chunk compression scheduling and automation. A ratio regression almost always traces to a segmentby/orderby change on that policy, and a growing pool of uncompressed chunks means the compression job is falling behind its schedule_interval. Snapshotting number_compressed_chunks ÷ total_chunks makes that backlog visible before disk does.
• Retention enforcement. Retention runs are the drop-to-zero at the left of the diagram. Cross-reference storage trends with TTL policy mapping and enforcement: if retention_last_success goes stale while live bytes climb, the policy has wedged. The snapshot’s retention_failures column is the earliest signal.
• Compression models. When a ratio regression is real rather than a measurement artifact, the fix lives in the compression models for high-frequency telemetry — re-examining segment and order columns. Observability tells you that the ratio moved; the model page tells you why.
• Sibling monitoring guides. These snapshots are one input among several. The job stats and scheduler diagnostics guide covers the job_stats view in depth for every policy type, Prometheus and Grafana integration turns the history table into dashboards and alert rules, and background worker and lock monitoring explains why a compression job that never runs is often a starved worker pool rather than a bad policy.

# Performance Validation

The observer must not perturb the observed. Two properties matter: the snapshot query is cheap, and the history table stays small.

Confirm the stats collection is a metadata-only read, not a table scan. hypertable_compression_stats reads the catalog; the uncompressed-bytes query calls pg_total_relation_size per chunk, which is also catalog-cheap. Time a single snapshot pass:

-- Should complete in low tens of milliseconds even on hundreds of chunks;
-- if it does not, the uncompressed-chunk count is very high — a compression
-- backlog worth investigating in its own right.
EXPLAIN (ANALYZE, TIMING ON)
SELECT count(*)
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_readings' AND is_compressed = false;

Confirm the history hypertable is not itself becoming a storage problem. At hourly snapshots across a handful of hypertables it accrues a few thousand rows a month; its own retention policy caps it. Check its footprint against the data it monitors:

SELECT hypertable_name,
       pg_size_pretty(hypertable_size(format('%I', hypertable_name)::regclass)) AS size
FROM (VALUES ('compression_stats_history'), ('sensor_readings')) AS t(hypertable_name);

If the history table shows up as anything but a rounding error next to the monitored hypertable, lower the snapshot cadence or tighten its drop_after.

# Troubleshooting

The compression ratio looks worse than a manual spot-check suggests. hypertable_compression_stats averages over only the compressed chunks, so a hypertable that just compressed a batch of atypical chunks — a backfill, or a period of low-cardinality data — will report a ratio that does not match your headline expectation. Drill into chunk_compression_stats('sensor_readings') and sort by before_compression_table_bytes / after_compression_table_bytes to find the specific chunk dragging the average down before concluding the policy is misconfigured.

Retention runs successfully but disk usage does not drop. Whole-chunk retention drops each eligible chunk with a DROP TABLE, which returns its files to the operating system immediately — no VACUUM needed for the drop itself. If space does not fall, the reclaimed files are being pinned: look for an inactive replication slot holding WAL (SELECT slot_name, active FROM pg_replication_slots WHERE NOT active) or a long-running transaction (SELECT pid, xact_start FROM pg_stat_activity ORDER BY xact_start ) whose snapshot keeps the dropped chunk’s files alive. VACUUM only matters for the compressed-chunk path, where a row-store-to-columnar rewrite leaves reclaimable heap pages; retention drops do not go through it.

The stats function returns nothing for a hypertable. hypertable_compression_stats returns null-filled or empty results when compression was never enabled on the table, and chunk_compression_stats raises compression not enabled on hypertable outright. Guard the snapshot writer: check SELECT compression_enabled FROM timescaledb_information.hypertables WHERE hypertable_name = 'sensor_readings' and skip the compression columns (still recording chunk counts and retention health) for hypertables where it is false, so one uncompressed table does not break the whole snapshot pass.

Frequently Asked Questions

# Related & Navigation

← Back to Monitoring, Observability & Alerting for TimescaleDB Automation

• Tracking Compression Ratio Trends Over Time — the ratio trend query and regression detector taken to production depth.
• Monitoring Retention Drops & Storage Reclamation — verifying that dropped chunks actually returned bytes to the OS.
• Job Stats & Scheduler Diagnostics — the full job_stats surface behind the health checks in step 2.
• Prometheus & Grafana Integration — export the history hypertable into dashboards and alert rules.
• Background Worker & Lock Monitoring — diagnose the starved worker pool behind a compression job that never runs.