Tracking Compression Ratio Trends Over Time

A single compression-ratio reading tells you nothing about whether compression is still working — a hypertable that squeezed telemetry to 8% of its raw size last month can silently drift to 15% after a schema tweak, and you only notice when the disk fills. This page turns the ratio into a monitored time series: profile the raw and compressed byte totals TimescaleDB already records, reduce them to one ratio per hypertable, append that ratio to a metrics hypertable on a schedule, and run a regression query that compares a recent window against a baseline. The technique extends the compression models for high-frequency telemetry with the observability layer that catches regressions before they become storage incidents.

# Input Profiling: The Byte Totals to Read

TimescaleDB already accounts for every byte before and after compression; you do not need to measure anything yourself. The two numbers that define the ratio come from the compression statistics views:

• before_compression_total_bytes — the uncompressed heap, index, and TOAST footprint of the chunks at the moment they were compressed.
• after_compression_total_bytes — the columnar footprint those same chunks occupy once compressed.
• Chunk age spread — the range_start/range_end of the chunks feeding the totals, so you know whether you are averaging fresh and stale chunks together.
• Compression settings fingerprint — the current segmentby and orderby columns, because a ratio change almost always traces back to a change here or in the shape of the incoming data.

Two views expose these totals at different granularities. chunk_compression_stats('hypertable') returns one row per compressed chunk, which is what you want when you need to see which chunks regressed. hypertable_compression_stats('hypertable') returns a single rolled-up row per hypertable — the right input for a trend series because it is stable and cheap to snapshot. Both live in the timescaledb_information-adjacent function namespace and read from the internal catalog, so they cost a catalog scan, not a table scan.

The critical property to internalize: these totals only cover already-compressed chunks. A hypertable whose recent chunks are still warm and uncompressed contributes nothing to the totals until the compression policy converts them. That is why a ratio can look pristine for days and then shift the moment a batch of freshly-configured chunks compresses.

# Calculating the Ratio and Its Trend

The compression ratio is the compressed size divided by the raw size — a smaller number is better, because it means the compressed form is a smaller fraction of the original:

$$r = \frac{B_{after}}{B_{before}}, \qquad \text{savings} = 1 - r$$

A ratio of $r = 0.08$ means compressed chunks occupy 8% of their uncompressed size, a 92% saving. When $r$ climbs to $0.15$, the saving falls to 85% — the same data now costs nearly twice the disk it did at the baseline. Because the metric is a fraction of raw, a rising line is a degradation, which is the opposite reflex from most dashboards; label the axis accordingly so on-call engineers read it correctly.

# Current ratio per hypertable

This query rolls every compressed chunk of every hypertable into one ratio, guarding the division against hypertables that have no compressed chunks yet:

SELECT
    h.hypertable_name,
    s.before_compression_total_bytes                                    AS raw_bytes,
    s.after_compression_total_bytes                                     AS compressed_bytes,
    round(
        s.after_compression_total_bytes::numeric
          / NULLIF(s.before_compression_total_bytes, 0),
        4
    )                                                                    AS ratio,
    round(
        1 - s.after_compression_total_bytes::numeric
          / NULLIF(s.before_compression_total_bytes, 0),
        4
    )                                                                    AS savings
FROM timescaledb_information.hypertables h
CROSS JOIN LATERAL hypertable_compression_stats(
    format('%I.%I', h.hypertable_schema, h.hypertable_name)
) s
WHERE s.before_compression_total_bytes IS NOT NULL
ORDER BY ratio DESC;

Ordering by ratio DESC floats the worst-compressing hypertables to the top — the ones worth watching. NULLIF(..., 0) turns a would-be divide-by-zero into NULL rather than erroring, so a hypertable mid-first-compression does not abort the whole report.

# Snapshotting the ratio into a metrics hypertable

A single query is a snapshot; a trend needs history. Store the ratio in its own hypertable and append to it on a schedule (cron, a systemd timer, or a TimescaleDB user-defined action). This psycopg v3 snapshotter reads the rolled-up stats and inserts one row per hypertable per run:

import psycopg
from psycopg.rows import dict_row

DDL = """
CREATE TABLE IF NOT EXISTS compression_ratio_metrics (
    ts               TIMESTAMPTZ NOT NULL DEFAULT now(),
    hypertable       TEXT        NOT NULL,
    raw_bytes        BIGINT      NOT NULL,
    compressed_bytes BIGINT      NOT NULL,
    ratio            DOUBLE PRECISION NOT NULL
);
SELECT create_hypertable(
    'compression_ratio_metrics', 'ts',
    chunk_time_interval => INTERVAL '30 days',
    if_not_exists       => TRUE
);
"""

SNAPSHOT = """
INSERT INTO compression_ratio_metrics (hypertable, raw_bytes, compressed_bytes, ratio)
SELECT
    h.hypertable_name,
    s.before_compression_total_bytes,
    s.after_compression_total_bytes,
    s.after_compression_total_bytes::float
      / NULLIF(s.before_compression_total_bytes, 0)
FROM timescaledb_information.hypertables h
CROSS JOIN LATERAL hypertable_compression_stats(
    format('%I.%I', h.hypertable_schema, h.hypertable_name)
) s
WHERE s.before_compression_total_bytes IS NOT NULL
  AND h.hypertable_name <> 'compression_ratio_metrics';
"""


def snapshot_ratios(conn_str: str) -> int:
    """Append one ratio row per compressed hypertable; returns rows written."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute(DDL)
            cur.execute(SNAPSHOT)
            written = cur.rowcount
        conn.commit()
    return written

Excluding compression_ratio_metrics from its own snapshot keeps the series from measuring itself. A 30-day chunk_time_interval is deliberately coarse — the metrics table gains only a handful of rows per run, so large chunks keep the catalog tidy.

# Comparing a recent window against a baseline

With history accumulating, a regression is a shift between two windows: a stable baseline and a recent one. This query computes both averages per hypertable and flags a meaningful climb:

WITH windows AS (
    SELECT
        hypertable,
        avg(ratio) FILTER (
            WHERE ts BETWEEN now() - INTERVAL '35 days'
                         AND now() - INTERVAL '7 days'
        ) AS baseline_ratio,
        avg(ratio) FILTER (
            WHERE ts >= now() - INTERVAL '7 days'
        ) AS recent_ratio
    FROM compression_ratio_metrics
    GROUP BY hypertable
)
SELECT
    hypertable,
    round(baseline_ratio::numeric, 4) AS baseline_ratio,
    round(recent_ratio::numeric, 4)   AS recent_ratio,
    round((recent_ratio - baseline_ratio)::numeric, 4) AS delta,
    round(
        (100 * (recent_ratio - baseline_ratio) / NULLIF(baseline_ratio, 0))::numeric,
        1
    ) AS pct_change
FROM windows
WHERE baseline_ratio IS NOT NULL
  AND recent_ratio  IS NOT NULL
  AND recent_ratio > baseline_ratio * 1.20   -- 20% worse than baseline
ORDER BY pct_change DESC;

The 1.20 multiplier is a relative gate: it fires only when the recent ratio is at least 20% higher than the baseline, so a hypertable that naturally compresses to 0.30 is not judged against one that reaches 0.05. Feed the same query to an alerting job and page when a row appears.

# Worked Example

A telemetry hypertable, sensor_metrics, held a rock-steady ratio of 0.08 through its first four weeks: chunks arrived, the compression policy converted them, and each compressed to 8% of raw. In week five an engineer altered the compression settings to add a high-cardinality device_firmware column to segmentby, intending to speed up a per-firmware query.

The next batch of chunks compressed at 0.13, and by week eight the rolling hypertable_compression_stats ratio had drifted to 0.15. The regression query surfaced it plainly:

The arithmetic is unforgiving: at $r = 0.08$ a terabyte of raw telemetry compressed to 80 GB; at $r = 0.15$ the same terabyte needs 150 GB — an 88% jump in storage cost for identical data. The pct_change column reported +87.5%, well past the 20% gate.

The cause was the segmentby change. Adding a high-cardinality column fragmented each compressed batch into far more, far smaller segments, and small segments compress poorly because run-length and delta encoding have fewer consecutive similar values to exploit. The fix — reverting to a low-cardinality segmentby and re-compressing the affected chunks — is exactly the trade-off examined in choosing segmentby and orderby for maximum compression. Without the trend series, the drift would have been invisible until the volume alerted.

# Edge Cases & When to Deviate

The rolled-up ratio is an average, and averages hide structure. Watch for these situations:

• Mixed chunk ages. hypertable_compression_stats blends every compressed chunk regardless of age. A single well-compressing month of history can mask a bad recent week. When the trend query flags a hypertable, drop to chunk_compression_stats and order by range_start DESC to see whether the newest chunks are the culprits.
• A settings change that only touches new chunks. ALTER TABLE ... SET (timescaledb.compress_segmentby = ...) applies only to chunks compressed after the change; already-compressed chunks keep their old layout until re-compressed. The rolled-up ratio therefore moves slowly, a weighted blend of old-good and new-bad chunks — precisely the gradual week-five-to-eight slope in the chart, not a cliff.
• Tiny samples. A hypertable with only one or two compressed chunks produces a jittery ratio; a single atypical chunk swings the average. Require a minimum row or chunk count before alerting, and prefer the relative 20% gate over an absolute threshold so small tables are not perpetually noisy.
• Recompression churn. If you run a job that decompresses, backfills, and recompresses chunks, the ratio legitimately wobbles during the operation. Snapshot on a cadence longer than the recompression window, or tag those runs so the regression query can exclude them.
• Genuinely changing data shape. Not every regression is a misconfiguration. If devices start emitting higher-entropy payloads — more distinct values, less repetition — the ratio worsens for real. The trend still earns its keep: it tells you when the shape changed so you can correlate it with a firmware or schema rollout.

# Verification

After wiring up the snapshotter, confirm the series is actually populating before you trust an alert built on top of it. First, check that rows are landing with sane ratios:

-- Confirm the trend series is being written and ratios are in range.
SELECT
    hypertable,
    count(*)                       AS samples,
    min(ts)                        AS first_sample,
    max(ts)                        AS last_sample,
    round(avg(ratio)::numeric, 4)  AS avg_ratio
FROM compression_ratio_metrics
GROUP BY hypertable
ORDER BY samples DESC;

A healthy series shows a samples count that grows by one per hypertable per run and a last_sample within one snapshot interval of now. If last_sample is stale, the scheduled job stopped firing — check its status the same way you would any background job across the monitoring guides. Second, spot-check that a stored ratio matches the live view, proving the snapshotter read the right totals:

-- Latest stored ratio vs. the live rolled-up stat for one hypertable.
SELECT ratio AS stored_ratio
FROM compression_ratio_metrics
WHERE hypertable = 'sensor_metrics'
ORDER BY ts DESC
LIMIT 1;

SELECT round(
    after_compression_total_bytes::numeric
      / NULLIF(before_compression_total_bytes, 0), 4) AS live_ratio
FROM hypertable_compression_stats('public.sensor_metrics');

The two numbers should agree to within a snapshot interval’s worth of newly-compressed chunks. If stored_ratio and live_ratio diverge sharply, the snapshot job is either lagging or reading a different schema than you expect — reconcile the format('%I.%I', ...) qualification before relying on the trend.

# Related

• Compression & Retention Observability — the parent guide on watching compression and retention health over time
• Monitoring Retention Drops & Storage Reclamation — the storage-reclamation half of the same lifecycle
• Compression Models for High-Frequency Telemetry — how columnar compression turns raw telemetry into the byte totals you trend here
• Choosing segmentby and orderby for Maximum Compression — the settings whose changes most often cause a ratio regression
• Monitoring, Observability & Alerting for TimescaleDB Automation — the wider observability program this metric feeds

← Back to Compression & Retention Observability