Time-Series Storage Partitioning and Retention #

An environmental sensor archive is unusual among time-series workloads: it is append-only, it is queried both temporally and spatially, it is rarely enormous by industry standards, and it has a retention obligation attached to it that most workloads do not. Those four properties together point at a specific architecture — time-partitioned rows in PostGIS with precomputed aggregates and a columnar cold tier — and away from the distributed systems that dominate general time-series advice. This stage of geospatial data storage, interpolation and GIS export lays out that architecture and the retention policy that governs it.

The number that anchors every decision is smaller than people expect. Two hundred sensors, four metrics, one-minute cadence is 1.15 million rows per day. Even at seven years that is under three billion rows, which one well-partitioned PostgreSQL instance handles without complaint. Most of the complexity in this area comes from designs sized for a workload the network will never have.

---

Prerequisites #

• PostgreSQL 15+ with PostGIS 3.4 and, optionally, TimescaleDB 2.14 for continuous aggregates and native compression.
• Python 3.11 with # python 3.11 · psycopg[binary]==3.1.18 · pyarrow==15.0.2 · pandas==2.2.2.
• A natural key on every reading. Partitioning and archival both rely on (device_id, observed_at, metric) being unique, which the PostGIS storage stage establishes.
• UTC observation timestamps. Partition boundaries are computed in UTC; a local-time column produces partitions whose boundaries move twice a year.
• QC flags and quality scores present. Retention policy differs by data class, and the class is read from the flag — see uncertainty quantification and data quality scoring.

---

Step-by-Step Workflow #

Step 1 — Size the Archive Before Choosing a Layout #

# python 3.11 · psycopg[binary]==3.1.18 · pyarrow==15.0.2 · pandas==2.2.2
from dataclasses import dataclass


@dataclass(frozen=True)
class ArchiveSize:
    sensors: int
    metrics: int
    readings_per_day_per_metric: int
    retention_days: int
    bytes_per_row: int = 96          # row header + 5 columns + index share, measured

    @property
    def rows_per_day(self) -> int:
        return self.sensors * self.metrics * self.readings_per_day_per_metric

    @property
    def total_rows(self) -> int:
        return self.rows_per_day * self.retention_days

    @property
    def gigabytes(self) -> float:
        return self.total_rows * self.bytes_per_row / 1e9

    def layout(self) -> str:
        if self.total_rows < 50e6:
            return "single table with a BRIN index"
        if self.total_rows < 5e9:
            return "monthly range partitions + continuous aggregates"
        return "partitions + columnar cold tier"

Complexity: arithmetic. Running it is the point: a network that lands in the first branch should not be building the third, and the estimate takes a minute against the weeks a premature architecture costs.

Step 2 — Partition by Time #

Range partitions on observed_at give three properties at once: dropping old data becomes a DETACH/DROP rather than a DELETE of a hundred million rows, each partition’s indexes stay shallow, and the planner prunes whole partitions from time-bounded queries.

CREATE TABLE reading (
    device_id   text        NOT NULL,
    observed_at timestamptz NOT NULL,
    metric      text        NOT NULL,
    value       double precision NOT NULL,
    qc_flag     smallint    NOT NULL DEFAULT 1,
    uncertainty real,
    geom        geometry(Point, 4326),
    PRIMARY KEY (device_id, observed_at, metric)
) PARTITION BY RANGE (observed_at);

CREATE TABLE reading_2026_08 PARTITION OF reading
    FOR VALUES FROM ('2026-08-01Z') TO ('2026-09-01Z');

CREATE INDEX ON reading_2026_08 USING brin (observed_at) WITH (pages_per_range = 32);
CREATE INDEX ON reading_2026_08 USING gist (geom);

BRIN on the time column rather than B-tree is the deliberate choice: rows arrive in time order, so correlation is near 1.0, and the index is thousands of times smaller — the argument made in full in GiST vs BRIN indexes for sensor geometry.

Complexity: partition pruning turns a time-bounded query from O(all rows) to O(rows in the matching partitions).

Step 3 — Precompute the Aggregates People Query #

Almost every dashboard query asks for hourly or daily means, and computing them from raw readings every time is the single largest avoidable cost in the system.

CREATE MATERIALIZED VIEW reading_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
       metric,
       time_bucket('1 hour', observed_at) AS bucket,
       avg(value)                          AS mean_value,
       max(value)                          AS max_value,
       count(*)                            AS n_total,
       count(*) FILTER (WHERE qc_flag IN (1, 2, 3)) AS n_measured
FROM   reading
GROUP  BY device_id, metric, bucket;

Carrying n_measured separately from n_total is not optional — it is what lets a consumer apply a data-capture rule, per flagging imputed values through the pipeline.

Complexity: the aggregate is maintained incrementally, so a query that scanned 4.3 million rows scans 720 instead.

Step 4 — Tier Cold Partitions to Columnar Files #

import pathlib

import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq


def archive_partition(conn, partition_start: str, partition_end: str,
                      root: pathlib.Path) -> pathlib.Path:
    """Export one partition to Hive-partitioned Parquet, then verify before dropping.

    The verification is the point: an archive job that drops a partition it has
    not confirmed it wrote is one bad disk away from a permanent gap.
    """
    frame = pd.read_sql(
        "SELECT device_id, observed_at, metric, value, qc_flag, uncertainty "
        "FROM reading WHERE observed_at >= %s AND observed_at < %s",
        conn, params=(partition_start, partition_end),
    )
    frame["year"] = frame["observed_at"].dt.year
    frame["month"] = frame["observed_at"].dt.month

    out = root / "reading"
    pq.write_to_dataset(
        pa.Table.from_pandas(frame, preserve_index=False),
        root_path=str(out),
        partition_cols=["year", "month"],
        compression="zstd",
    )
    written = pq.ParquetDataset(str(out)).read().num_rows
    if written < len(frame):
        raise RuntimeError(f"archive incomplete: {written} of {len(frame)} rows")
    return out

Complexity: O(n) per partition, run once per partition per lifetime. Expect 8 to 15 times compression against the row store — a year that occupies 60 GB in PostgreSQL fits in 4 to 7 GB of zstd-compressed Parquet.

Step 5 — Enforce Retention as a Scheduled Job #

-- Drop a partition older than the raw-retention window, but only after the
-- archive job has recorded a verified export for it.
DO $$
DECLARE part record;
BEGIN
  FOR part IN
    SELECT c.relname, p.archived_at
    FROM   pg_class c
    JOIN   archive_log p ON p.partition_name = c.relname
    WHERE  c.relname LIKE 'reading_%'
      AND  p.archived_at IS NOT NULL
      AND  p.partition_end < now() - interval '18 months'
  LOOP
    EXECUTE format('ALTER TABLE reading DETACH PARTITION %I', part.relname);
    EXECUTE format('DROP TABLE %I', part.relname);
  END LOOP;
END $$;

The archive_log join is the safety interlock. Retention that deletes on a date alone will eventually delete a partition whose archive failed silently three weeks earlier.

---

Configuration and Tuning #

Layout by archive size #

Retention by data class #

The last row is the one people miss when planning storage. The registry is kilobytes and it is the only thing that makes the terabytes meaningful.

---

Validation #

• Partition pruning actually happens. EXPLAIN a one-day query and confirm the plan touches one partition. A query whose predicate wraps observed_at in a function defeats pruning and scans everything.
• Aggregate agreement. Recompute an hourly mean from raw readings for a sample of buckets and compare against the materialized view. A drift here means the aggregate’s refresh policy is missing late arrivals.
• Archive round-trip. Read an archived Parquet partition back and compare row counts and a checksum of values against the source before the partition is dropped. This is the only test that matters for the tiering path.
• Retention job dry-run. Run the retention job in a mode that logs what it would drop, and check the list by eye monthly. A retention bug is unrecoverable by definition.
• Restore time. Time a restore of one archived month into a scratch table. If it takes hours, the archive is a backup rather than a queryable tier, and the difference matters when someone asks for a reanalysis.

---

Failure Modes and Edge Cases #

Late data arriving after a partition is archived. Readings delayed by a store-and-forward link can arrive days late. Keep the raw partition attached for a grace period well beyond your worst observed arrival latency — the figure measured in choosing a watermark grace period.

A missing partition for the current month. If partition creation is a manual step, the first insert after month end fails. Automate creation several months ahead and alert if the newest partition is less than two months in the future.

Aggregates that were computed before a recalibration. Corrected raw data does not automatically correct a materialized aggregate. Any reprocessing must invalidate and rebuild the affected buckets.

Dropping raw data that a published figure depends on. If an annual report cites hourly means, the raw readings behind them may need to survive as long as the report is defensible — which can be longer than the raw retention window. Resolve this when writing the policy, not when asked.

Parquet files partitioned so finely that metadata dominates. Hive partitioning by day and by device produces millions of tiny files where a query spends its time listing rather than reading. Partition by year and month only, and let row groups do the rest.

Compression applied to a partition still receiving writes. TimescaleDB compression makes a chunk effectively read-only; late data then fails to insert. Compress only past the late-arrival grace period.

---

Integration #

Storage sits at the end of the pipeline and its layout constrains everything upstream and downstream. Ingest writes into the current partition, so its throughput depends on the index choice made here. Spatial interpolation reads hourly aggregates rather than raw readings, which is what makes a daily surface tractable. And GeoJSON and QGIS export consumes the same aggregates, so a change to the bucket definition changes every published product at once.

The three guides below cover the parts with the most operational risk: continuous aggregates, the retention policy, and the Parquet archive tier.

---

FAQ #

How large does a sensor archive actually get?

Multiply sensors by metrics by readings per day. Two hundred sensors reporting four metrics every minute is 1.15 million rows a day — 420 million a year, and roughly 60 GB in a row store with indexes. That is comfortably within one PostgreSQL instance, which is why most environmental networks never need anything more exotic than good partitioning.

Monthly or daily partitions?

Daily when you drop data daily or query single days; monthly otherwise. The constraint is partition count: PostgreSQL plans fine with a few hundred partitions and degrades noticeably past a couple of thousand, so seven years of daily partitions is a planning problem where monthly is not.

Do I still need raw readings after computing hourly aggregates?

Usually yes, for a bounded period. Reprocessing after a calibration correction needs the raw values, and so does any audit of how a published figure was derived. Keep raw data for the period in which reprocessing is plausible — one to two years is typical — then keep aggregates indefinitely.

Is a purpose-built time-series database worth it?

For most environmental networks, no. A TimescaleDB extension on the PostgreSQL you already run gives you partitioning, continuous aggregates and compression while keeping PostGIS in the same database, which matters because almost every query here is both temporal and spatial. Reach for a separate system only when the spatial half stops being needed.

What retention does a regulator actually require?

It varies by jurisdiction and by what the data supports, and it is a question for whoever owns the reporting obligation rather than for the pipeline. What the pipeline owes is a policy that names each data class, its minimum retention and its basis — and a job that enforces it, so retention is a property of the system rather than of whoever last remembered to run a delete.

---

Related #

• Geospatial Data Storage, Interpolation & GIS Export for Environmental IoT — the storage and export section this stage belongs to
• Downsampling with TimescaleDB Continuous Aggregates — precomputing the rollups that most queries actually read
• Designing a Retention Policy for High-Frequency Telemetry — deciding what to keep, for how long, and on what basis
• Writing Hive-Partitioned Parquet for Sensor Archives — the columnar tier that cold partitions move into
• Partitioning PostGIS Sensor Tables by Time — the partitioning mechanics inside PostGIS itself