Designing a Retention Policy for High-Frequency Telemetry #

A retention policy that exists only as a DELETE statement in a cron job is not a policy — it is a liability with a schedule. The questions it must answer are not technical: which data supports a published figure, how long that figure must remain defensible, what a regulator or a funder requires, and what happens to the evidence when a sensor’s readings are excluded from a report. This guide turns those questions into a policy document and an enforcement job with an interlock, as part of time-series storage partitioning and retention.

Classify by What the Data Supports #

Retention decisions get much easier once data is grouped by the claim it underwrites rather than by its table.

Data behind a published figure. If an annual report cites a site mean, the readings behind it have to survive as long as that report is something anyone might question. This is usually the longest requirement in the policy and the one most often overlooked, because the connection between a number in a PDF and a partition in a database is not recorded anywhere.

Data that could change a published figure. Raw readings within the window where reprocessing is plausible — after a calibration correction, a decoder fix, or a registry error. Once you are no longer willing to restate, this class can move to archive.

Diagnostic data. Rejected readings, out-of-range values, hardware-failure periods. Valuable for fault diagnosis and for justifying exclusions, worthless analytically, and highly compressible.

Derived data. Aggregates, quality scores, interpolated surfaces. Reproducible from raw data plus code plus registry — but only while all three survive. Once raw data is archived, an aggregate is effectively primary.

That last observation is the one that changes designs: the moment you delete raw readings, your hourly aggregates stop being a cache and become the record.

Production-Ready Implementation #

Express the policy as data, not as code, so it can be reviewed by people who do not read SQL:

# python 3.11 · psycopg[binary]==3.1.18
from __future__ import annotations

from dataclasses import dataclass


@dataclass(frozen=True)
class RetentionRule:
    """One retention rule, with the basis that justifies it.

    `basis` is not documentation — it is the field an auditor reads, and a rule
    without one is a number somebody guessed.
    """

    data_class: str
    hot_days: int                 # in the queryable row store
    archive_years: int | None     # in columnar object storage; None = delete, no archive
    basis: str


POLICY = (
    RetentionRule("raw_readings", hot_days=548, archive_years=10,
                  basis="reprocessing window (18 months) + long-term reanalysis"),
    RetentionRule("rejected_readings", hot_days=365, archive_years=2,
                  basis="fault diagnosis and justification of exclusions"),
    RetentionRule("hourly_aggregates", hot_days=36_500, archive_years=None,
                  basis="primary analytical record once raw data is archived"),
    RetentionRule("daily_aggregates", hot_days=36_500, archive_years=None,
                  basis="annual and trend reporting"),
    RetentionRule("registry_history", hot_days=36_500, archive_years=None,
                  basis="without it no historical reading is interpretable"),
    RetentionRule("interpolated_surfaces", hot_days=90, archive_years=None,
                  basis="reproducible from aggregates plus method"),
)

Enforcement needs an interlock: nothing is deleted that has not been archived and verified.

import psycopg


def enforce(conn: psycopg.Connection, rules=POLICY, *, dry_run: bool = True) -> list[dict]:
    """Apply retention. Returns what was (or would be) dropped, always logging it.

    The archive interlock is the safety property: a partition is dropped only if
    archive_log records a VERIFIED export. A date-only condition will eventually
    delete a partition whose archive failed weeks earlier.
    """
    actions = []
    for rule in rules:
        if rule.hot_days >= 36_500:
            continue                       # kept indefinitely
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT partition_name, row_count, archive_ref
                FROM   archive_log
                WHERE  data_class = %s
                  AND  partition_end < now() - make_interval(days => %s)
                  AND  (%s::bool IS FALSE OR verified_at IS NOT NULL)
                ORDER  BY partition_end
                """,
                (rule.data_class, rule.hot_days, rule.archive_years is not None),
            )
            for name, rows, ref in cur.fetchall():
                actions.append({"rule": rule.data_class, "partition": name,
                                "rows": rows, "archive": ref, "basis": rule.basis})
                if not dry_run:
                    cur.execute(f'ALTER TABLE reading DETACH PARTITION "{name}"')
                    cur.execute(f'DROP TABLE "{name}"')
                    cur.execute(
                        "INSERT INTO retention_log (partition_name, data_class, rows_dropped, "
                        "archive_ref, basis, dropped_at) VALUES (%s, %s, %s, %s, %s, now())",
                        (name, rule.data_class, rows, ref, rule.basis),
                    )
        if not dry_run:
            conn.commit()
    return actions

Running with dry_run=True on a schedule and mailing the result is worth more than any test: a human sees what is about to disappear, monthly, before it does.

Parameter Tuning Guide #

The right-hand column is the point of the table. None of these numbers is a pipeline decision, and a policy that does not name an owner per row is a policy the platform team invented.

Verification and Testing #

# python 3.11 · pytest==8.2.0
def test_nothing_is_dropped_without_a_verified_archive(conn):
    """The interlock: an unverified archive must block deletion."""
    seed_partition(conn, name="reading_2024_01", data_class="raw_readings",
                   archived=True, verified=False)
    actions = enforce(conn, dry_run=True)
    assert not any(a["partition"] == "reading_2024_01" for a in actions)


def test_indefinite_classes_are_never_selected(conn):
    seed_partition(conn, name="hourly_2019", data_class="hourly_aggregates",
                   archived=False, verified=False, age_days=3000)
    assert not any(a["rule"] == "hourly_aggregates" for a in enforce(conn, dry_run=True))


def test_every_deletion_is_logged_with_its_basis(conn):
    seed_partition(conn, name="reading_2023_01", data_class="raw_readings",
                   archived=True, verified=True, age_days=900)
    enforce(conn, dry_run=False)
    with conn.cursor() as cur:
        cur.execute("SELECT basis, archive_ref FROM retention_log WHERE partition_name = %s",
                    ("reading_2023_01",))
        basis, ref = cur.fetchone()
    assert basis and ref

Run a quarterly restore drill as well: pick an archived partition at random, restore it to a scratch table, and compare a checksum against the retention log’s recorded row count. An archive nobody has ever read is a hypothesis.

Gotchas #

Deleting on a date with no archive interlock. The failure is silent and total: the archive job failed three weeks ago, the retention job did not know, and the data is gone.

A policy with no owner per rule. When someone asks why raw data is kept for eighteen months, “it seemed reasonable” is not an answer that survives an audit. Name the obligation and the person.

Forgetting that aggregates become primary. Once raw data is archived to object storage, the hourly aggregate is what everyone actually queries. It needs the same backup treatment as any primary record, not the treatment of a cache.

Retention that ignores the registry. Deleting registry history to save space makes every archived reading uninterpretable — you will have kept the terabytes and thrown away the kilobytes that explain them.

FAQ #

Related #

• Time-Series Storage Partitioning and Retention — the storage stage this policy governs
• Writing Hive-Partitioned Parquet for Sensor Archives — the archive that must exist before anything is deleted
• Downsampling with TimescaleDB Continuous Aggregates — the rollups that outlive the raw data