Time-Series Retention and Downsampling Design

Scope and reader

This guide is for whoever has to answer "how long do we keep this?" for building, plant or IoT telemetry: data engineers, BMS integrators and the developers of CAFM or CMMS systems that read trend data. It assumes you already store readings in a time-partitioned table (see how to store IoT sensor data) and focuses on the design: which resolutions to keep, for how long, how to produce them, and how to delete safely.

Mechanisms for keeping the roll-ups current (materialized views, continuous aggregates, incremental tables) are compared in continuous aggregates vs materialized views; storage arithmetic is in sizing storage for one-second telemetry.

Decide the tiers from how the data is used

Start from questions, not from disk size. Each question needs a resolution and a look-back period, and the tiers fall out of that list.

Choosing your own retention periods

No official source sets these values for building data. Decide each tier's period from: the questions in the table and how far back each really needs to look; contractual, regulatory and audit obligations (which can require longer retention, or archiving instead of deletion); the longest data delay you observe, which sets the minimum raw retention after a roll-up; storage and backup cost (see sizing storage); the partition or chunk interval, which rounds every period up; and how expensive it would be to need raw data you no longer have.

What each tier stores

• sum and count, so averages can be recomputed at any coarser level and corrected when late data arrives.
• min and max, because peaks matter for plant (maximum demand, high CO2) and are lost by averaging.
• first and last value for counters such as energy meters, so consumption is last - first per bucket and meter resets can be detected.
• sample count against expected count, so reports can flag buckets with gaps instead of presenting them as complete.

Order of operations: roll up, verify, drop

1. Roll up each closed period into the next tier with an idempotent upsert that recomputes each bucket from the raw rows, so re-running it for a window gives the same result (run here in a single session only). For scheduling, the recommended plain PostgreSQL pattern (recompute a bounded recent window on every run, plus a periodic reconciliation) is set out in continuous aggregates vs materialized views.
2. Wait out the grace period. Gateways that buffer through network outages deliver old readings later; keep raw data at least as long past the roll-up as your worst expected delay, then re-run the roll-up for affected windows. See late-arriving and out-of-order events.
3. Verify coverage: the roll-up's sample count for the period should equal the raw row count.
4. Archive if required (export the partition to object storage or a warehouse) before deleting.
5. Drop whole partitions or chunks. The PostgreSQL documentation notes that dropping or detaching a partition is far faster than a bulk DELETE and avoids the VACUUM work a large delete creates.

The order matters most with automatic roll-ups. Tiger Data warns that if a continuous aggregate is refreshed over a range whose raw data has been deleted, the refresh deletes the aggregated rows as well. With TimescaleDB, keep raw retention longer than the aggregate's refresh window.

Worked example: zone CO2 in PostgreSQL

One zone CO2 sensor reports every minute: 400 ppm outside occupied hours and 800 ppm from 08:00 to 17:59. On 26 October the sensor was offline from 09:10 to 12:54. Raw data sits in daily partitions; a 15-minute tier stores sum, count, min and max. The script derives daily figures two ways, checks coverage, then drops the oldest raw partition.

SET TIME ZONE 'UTC';
CREATE TABLE zone_co2 (
    point_id integer NOT NULL,
    ts       timestamptz NOT NULL,
    ppm      double precision NOT NULL,
    PRIMARY KEY (point_id, ts)
) PARTITION BY RANGE (ts);
CREATE TABLE zone_co2_20261025 PARTITION OF zone_co2 FOR VALUES FROM ('2026-10-25') TO ('2026-10-26');
CREATE TABLE zone_co2_20261026 PARTITION OF zone_co2 FOR VALUES FROM ('2026-10-26') TO ('2026-10-27');
CREATE TABLE zone_co2_20261027 PARTITION OF zone_co2 FOR VALUES FROM ('2026-10-27') TO ('2026-10-28');

INSERT INTO zone_co2
SELECT 1, t, CASE WHEN extract(hour FROM t) BETWEEN 8 AND 17 THEN 800 ELSE 400 END
FROM generate_series(timestamptz '2026-10-25 00:00', timestamptz '2026-10-27 23:59', interval '1 minute') AS t
-- the sensor was offline from 09:10 to 12:54 on 26 October
WHERE NOT (t >= '2026-10-26 09:10' AND t < '2026-10-26 12:55');

-- Tier 1: 15-minute roll-up that keeps sum and count, not just the average
CREATE TABLE zone_co2_15m (
    point_id integer NOT NULL, bucket timestamptz NOT NULL,
    sum_ppm double precision NOT NULL, samples integer NOT NULL,
    min_ppm double precision NOT NULL, max_ppm double precision NOT NULL,
    PRIMARY KEY (point_id, bucket)
);
INSERT INTO zone_co2_15m
SELECT point_id, date_bin(interval '15 minutes', ts, timestamptz '2000-01-01'),
       sum(ppm), count(*), min(ppm), max(ppm)
FROM zone_co2
WHERE ts >= '2026-10-25' AND ts < '2026-10-28'
GROUP BY 1, 2
ON CONFLICT (point_id, bucket) DO UPDATE
SET sum_ppm = EXCLUDED.sum_ppm, samples = EXCLUDED.samples,
    min_ppm = EXCLUDED.min_ppm, max_ppm = EXCLUDED.max_ppm;

-- Tier 2: daily figures derived from the 15-minute tier, two ways
SELECT date_trunc('day', bucket) AS day,
       round((sum(sum_ppm) / sum(samples))::numeric, 1) AS true_avg,
       round(avg(sum_ppm / samples)::numeric, 1)         AS avg_of_avgs,
       sum(samples) AS samples, min(min_ppm) AS min_ppm, max(max_ppm) AS max_ppm
FROM zone_co2_15m GROUP BY 1 ORDER BY 1;

-- Check against the raw data
SELECT date_trunc('day', ts) AS day, round(avg(ppm)::numeric, 1) AS raw_avg, count(*) AS samples
FROM zone_co2 GROUP BY 1 ORDER BY 1;

-- Retention guard: only drop a raw partition whose day is fully covered by the roll-up
SELECT (SELECT count(*) FROM zone_co2_20261025) AS raw_rows,
       (SELECT sum(samples) FROM zone_co2_15m
         WHERE bucket >= '2026-10-25' AND bucket < '2026-10-26') AS rolled_up_rows;
ALTER TABLE zone_co2 DETACH PARTITION zone_co2_20261025;
DROP TABLE zone_co2_20261025;

SELECT tableoid::regclass AS partition, count(*) FROM zone_co2 GROUP BY 1 ORDER BY 1;
SELECT count(*) AS buckets_25_oct FROM zone_co2_15m
WHERE bucket >= '2026-10-25' AND bucket < '2026-10-26';

          day           | true_avg | avg_of_avgs | samples | min_ppm | max_ppm
------------------------+----------+-------------+---------+---------+---------
 2026-10-25 00:00:00+00 |    566.7 |       566.7 |    1440 |     400 |     800
 2026-10-26 00:00:00+00 |    523.5 |       526.8 |    1215 |     400 |     800
 2026-10-27 00:00:00+00 |    566.7 |       566.7 |    1440 |     400 |     800

          day           | raw_avg | samples
------------------------+---------+---------
 2026-10-25 00:00:00+00 |   566.7 |    1440
 2026-10-26 00:00:00+00 |   523.5 |    1215
 2026-10-27 00:00:00+00 |   566.7 |    1440

 raw_rows | rolled_up_rows
----------+----------------
     1440 |           1440

     partition     | count
-------------------+-------
 zone_co2_20261026 |  1215
 zone_co2_20261027 |  1440

 buckets_25_oct
----------------
             96

Reading the result

On complete days both methods agree. On 26 October the outage left two partial 15-minute buckets (10 and 5 samples); averaging the bucket averages gives each of them the same weight as a full bucket, and the daily figure moves from 523.5 to 526.8 ppm. Computing sum / count reproduces the raw average exactly. After the drop, the raw partition for 25 October is gone and all 96 of its 15-minute buckets remain.

Whether a daily average should be weighted by samples (as here) or by time is a separate decision. With a fixed one-minute interval the two agree except around gaps. With a sensor that reports on change of value, samples cluster where the value moves, so a sample-weighted average gives those periods more weight, while a time-weighted average weights each value by how long it was in force. Which one a report needs is a judgement for the people who use it; decide it once and store what that calculation needs (for time weighting, the value and its start and end times, or the integral per bucket).

The same retention step in MySQL

MySQL range partitioning gives the same drop-a-partition retention, with one difference to plan for: a row whose timestamp has no partition is rejected, so the job that drops old partitions should also add future ones.

CREATE TABLE meter_reading (
    point_id INT NOT NULL,
    ts       DATETIME NOT NULL,
    kwh      DOUBLE NOT NULL,
    PRIMARY KEY (point_id, ts)
)
PARTITION BY RANGE COLUMNS (ts) (
    PARTITION p20261025 VALUES LESS THAN ('2026-10-26'),
    PARTITION p20261026 VALUES LESS THAN ('2026-10-27'),
    PARTITION p20261027 VALUES LESS THAN ('2026-10-28')
);
INSERT INTO meter_reading VALUES
    (1, '2026-10-25 10:00:00', 12.5), (1, '2026-10-25 10:15:00', 12.9),
    (1, '2026-10-26 10:00:00', 13.1), (1, '2026-10-27 10:00:00', 12.7);

ALTER TABLE meter_reading DROP PARTITION p20261025;
SELECT DATE(ts) AS day, COUNT(*) AS readings FROM meter_reading GROUP BY DATE(ts) ORDER BY day;

INSERT INTO meter_reading VALUES (1, '2026-10-28 10:00:00', 12.2);
ALTER TABLE meter_reading ADD PARTITION (PARTITION p20261028 VALUES LESS THAN ('2026-10-29'));
INSERT INTO meter_reading VALUES (1, '2026-10-28 10:00:00', 12.2);

+------------+----------+
| day        | readings |
+------------+----------+
| 2026-10-26 |        1 |
| 2026-10-27 |        1 |
+------------+----------+

INSERT INTO meter_reading VALUES (1, '2026-10-28 10:00:00', 12.2);
ERROR 1526 (HY000): Table has no partition for value from column_list

ALTER TABLE ... ADD PARTITION ...   -- OK
INSERT INTO meter_reading VALUES (1, '2026-10-28 10:00:00', 12.2);   -- OK, 1 row affected

How each engine expires and downsamples data

The Prometheus row deserves a caveat: the storage page we checked (27 October 2026) covers retention and remote storage integrations but does not mention downsampling, so do not assume Prometheus itself reduces resolution as data ages. The same page says that with proper architecture years of data can be kept in local storage, and that remote storage integrations exist because local storage is limited to a single node; whether a remote system downsamples depends on that system, so check its own documentation. Our editorial view: for building telemetry that is not metrics-shaped, the SQL engines above are the more common home. Product overviews: TimescaleDB, InfluxDB, QuestDB, and InfluxDB vs Prometheus.

Licence note (TimescaleDB). Tiger Data's editions page says TimescaleDB Community Edition is "available under the terms of the Tiger Data License (TSL)", and its feature table lists add_retention_policy and the continuous aggregate functions for Community Edition only, not for the Apache 2 Edition. The licence text is linked from Tiger Data's licenses page and published as LICENSE-TIMESCALE. This page does not interpret those terms: this is not legal advice; read the licence and take advice for your situation.

The statements below are illustrative configuration, not run here. Every period in them is an illustrative value for the example, not a recommendation; the QuestDB table TTL applies to QuestDB Open Source (its TTL page says Enterprise uses storage policies for tables instead).

-- All periods below are illustrative values for the example, not recommendations.
-- TimescaleDB: raw chunks after 30 days; hourly aggregate refreshed over 7 days
-- (keep the refresh window inside the raw retention period)
SELECT add_retention_policy('zone_co2', INTERVAL '30 days');
SELECT add_continuous_aggregate_policy('zone_co2_hourly',
    start_offset => INTERVAL '7 days', end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- QuestDB: table and view with separate TTLs
ALTER TABLE zone_co2 SET TTL 30 DAYS;
ALTER MATERIALIZED VIEW zone_co2_hourly SET TTL 5 YEARS;

-- ClickHouse: roll rows up to one per point per day after 30 days
CREATE TABLE zone_co2 (
    ts DateTime, point_id UInt32, ppm Float64,
    sum_ppm Float64 DEFAULT ppm, samples UInt64 DEFAULT 1, max_ppm Float64 DEFAULT ppm
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
PRIMARY KEY (point_id, toStartOfDay(ts), ts)
TTL ts + INTERVAL 30 DAY GROUP BY point_id, toStartOfDay(ts)
    SET sum_ppm = sum(sum_ppm), samples = sum(samples), max_ppm = max(max_ppm);

# 90d, 1h and 6h are illustrative values; in Core the retention period cannot be changed later
influxdb3 create database --retention-period 90d bms_raw

influxdb3 create trigger \
  --database bms_raw \
  --path "gh:influxdata/downsampler/downsampler.py" \
  --trigger-spec "every:1h" \
  --trigger-arguments 'source_measurement=zone_co2,target_measurement=zone_co2_hourly,interval=1h,window=6h,calculations=avg' \
  zone_co2_hourly_downsample

Trade-offs and limitations

• Detail is gone for good. After raw data is dropped you cannot answer a new question at one-minute resolution. If that risk matters, archive partitions to cheap storage (Parquet in object storage, a warehouse) instead of deleting them.
• Partition-sized granularity. Retention happens in whole partitions or chunks. With monthly partitions and a rule that drops a partition only when all of it is older than 90 days, a month can be dropped only once its last day is 90 days old, and by then its first day is 90 days plus the length of the month old. So the oldest raw data kept ranges from 90 days (just after a drop) to 118 to 121 days (just before one, for a 28 to 31 day month), plus up to one interval between runs of the retention job. Choose the interval with retention in mind (see choosing partition intervals).
• Roll-ups can drift. Late data, corrected readings and changed point definitions require re-running roll-ups; keep the raw grace period and make roll-ups idempotent.
• Time zones and calendars. Daily and monthly tiers depend on whether a "day" is the building's local day or a UTC day; decide which and store it. In time zones with a one-hour daylight-saving shift, local days of 23 and 25 hours occur, so a complete local day does not always hold 1,440 one-minute readings; coverage checks must count the expected samples per local day rather than assume a constant (see the run below).
• Fixed settings. Some products make retention hard to change (InfluxDB 3 Core); plan it before the first write.

SET TIME ZONE 'Europe/London';
SELECT d::date AS local_day, (d + interval '1 day') - d AS day_length
FROM (VALUES (timestamptz '2026-03-29 00:00'), (timestamptz '2026-10-25 00:00'),
             (timestamptz '2026-10-26 00:00')) AS v(d);
SELECT count(*) AS one_minute_samples
FROM generate_series(timestamptz '2026-10-25 00:00',
                     timestamptz '2026-10-26 00:00' - interval '1 minute', interval '1 minute');

 local_day  |   day_length
------------+----------------
 2026-03-29 | 23:00:00
 2026-10-25 | 1 day 01:00:00
 2026-10-26 | 1 day

 one_minute_samples
--------------------
               1500

Operations, security and when not to downsample

Operations. Run retention as a scheduled job with logging, a coverage check before each drop, and an alert when the job fails or when the oldest partition is older than policy. Keep the job's role able to drop partitions but not to change the roll-up tables, and audit who can change retention settings. Back up the roll-up tiers as carefully as raw data: after retention they are the only copy (see backup and restore for time-series data).

When not to downsample. Do not downsample data that is evidence for a contract, a safety investigation or a billing dispute; archive it intact. Think twice before downsampling small data: 300 meters at 15-minute intervals produce about 10.5 million rows a year (300 x 96 x 365), and whether that justifies roll-up code and its risks depends on your hardware and queries, so measure the table before deciding. And do not downsample before you know the questions; keep raw data longer at first and tighten retention once usage is clear.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Table Partitioning
• MySQL 8.0 Reference Manual: Management of RANGE and LIST Partitions
• Tiger Data docs: Understand data retention
• Tiger Data docs: Data retention with continuous aggregates
• Tiger Data docs: Refresh continuous aggregates
• InfluxDB 3 Core docs: Create a database
• InfluxDB 3 Core docs: influxdb3 update database
• InfluxDB 3 Core docs: Downsampler plugin
• Prometheus docs: Storage
• QuestDB docs: Time To Live (TTL)
• ClickHouse docs: Manage data with TTL
• Tiger Data docs: Compare TimescaleDB editions
• Tiger Data: Licenses

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.