11 Data Retention and Downsampling

11.1 Start With the Deletion Request

This section: Start With the Deletion Request — Picture a cold store that keeps one temperature reading each minute.

Delete Only After the Replacement Can Answer

Picture a cold store that keeps one temperature reading each minute. Telemetry means readings and status sent from equipment for remote use. The storage owner wants to remove old raw data, while the duty team may still need to explain a short heat spike from last month.

Write what each user must still learn after deletion. Name the raw time range, summary fields, unit, source clock, gap mark, quality state, archive, hold rule, and owner. State which detail may be lost and which event must remain visible.

Test one normal day, one missing hour, one late reading, one sharp spike, and one reading with a bad quality mark. Restore a sample archive and repeat a real query. Compare the result with the raw record before any deletion job is allowed to run.

Pause deletion during an incident, dispute, warranty case, or legal hold. Keep the pause in the same release record as the normal rule so a hidden side list cannot be missed.

This opening does not set one lifetime for every reading. Practitioner designs the raw, summary, archive, hold, and approval route. Under the Hood examines late arrivals, partition removal, refresh windows, platform commands, and the proof needed before detail is gone.

Retention becomes serious the first time someone asks to delete raw readings. Before that job runs, the team should know which summaries replace the detail, which archive can be restored, which investigation holds stop deletion, and who approved the loss of resolution. Downsampling is safe only when that evidence survives.

Data Dora

“Every reading has a time and a cost — decide retention before you decide the database.”

In this chapter, Dora keeps her ledger open at every deletion gate: what arrived, what survives as rollup or archive, and what a restore will cost.

11.2 In 60 Seconds

This section: In 60 Seconds — Downsampling is only safe when the aggregate preserves enough evidence: sample count, min, max, quality status, gap flags, source window, and refresh time.

Retention is not just “delete old data.” It is a lifecycle contract that says which raw readings stay queryable, which summaries replace them, which archives can be restored, which data is held for investigation or compliance, and who approved each loss of detail.

Downsampling is only safe when the aggregate preserves enough evidence: sample count, min, max, quality status, gap flags, source window, and refresh time. A smaller table that hides missing data or erases incident spikes is not an optimization. It is a data-quality problem.

11.3 Retention Is a Release Contract

This section: Retention Is a Release Contract — Retention decisions are product decisions.

Retention decisions are product decisions. A storage engineer can implement the job, but the owner of the data must decide what detail can be lost, when it can be lost, and how an incident can still be reconstructed later.

11.4 Lifecycle Map

This section: Lifecycle Map — Retention is a sequence of evidence-bearing state changes, not a single expiry number.

Retention is a sequence of evidence-bearing state changes, not a single expiry number. Use Figure 11.1 to follow raw readings into rollups and archive, then through restore checks, hold exceptions, and deletion approval.

Use Figure 11.1 to follow raw readings into rollups and archive, then through restore checks, hold exceptions, and deletion approval.

+

Read Figure 11.1 from recent raw detail through quality-bearing rollups and a tested archive, then check the hold branch before the final governed deletion transition.

In Figure 11.1, raw detail remains available while investigations and late arrivals need it. Rollups preserve declared statistics and coverage, the archive preserves a recoverable copy, and a restore test proves that copy can answer a real query. Holds interrupt the normal route before approval. Deletion is the final governed transition only after every replacement path has evidence.

11.5 Downsampling That Preserves Evidence

This section: Downsampling That Preserves Evidence — Downsampling replaces many high-resolution samples with fewer summary rows.

Downsampling replaces many high-resolution samples with fewer summary rows. The mistake is treating the average as the whole story. For IoT telemetry, a useful rollup normally needs fields that preserve coverage, extremes, and quality.

-- Example rollup shape. Adapt table names and policy details to your platform.
SELECT
  time_bucket('15 minutes', observed_at) AS bucket_start,
  site_id,
  device_id,
  metric_name,
  avg(value) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS avg_value,
  min(value) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS min_value,
  max(value) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS max_value,
  count(*) FILTER (WHERE quality_status IN ('valid', 'late_valid')) AS sample_count,
  count(*) FILTER (WHERE (quality_status IN ('valid', 'late_valid')) IS NOT TRUE) AS nonvalid_count,
  max(received_at) AS latest_received_at
FROM readings
WHERE observed_at >= :source_start
  AND observed_at <  :source_end
GROUP BY bucket_start, site_id, device_id, metric_name;

11.6 Retention Dry Run

This section: Retention Dry Run — A dry run should run before the first automated deletion and before any policy change that shortens raw retention.

A dry run should run before the first automated deletion and before any policy change that shortens raw retention. It should report the raw partitions that would be affected, the rollups that replace them, archive status, restore status, query replay results, hold exceptions, and approval owner. Use Figure 11.2 to inspect those gates in the order a release reviewer should clear them.

Use Figure 11.2 to inspect the replacement, recovery, hold, and approval gates in the order a release reviewer should clear them.

+

Read Figure 11.2 from rollup completeness to archive evidence. Every source interval needs a matching summary with plausible sample counts; every raw partition needs a manifest, schema version, checksum, object path, and owner. Next restore one sample partition and replay a source query so its row count and result can be compared. The hold gate then checks investigations, disputes, compliance rules, and warranty cases that still need raw detail. Only a named owner may approve deletion after every gate passes; any failed check delays deletion or revises the policy.

retention_dry_run:
  policy_name: room_temperature_raw_expiry
  candidate_window: before 2026-05-01T00:00:00Z
  raw_partitions_found: list_only
  rollup_check: sample_count, min_value, max_value, gap_count, refreshed_at
  archive_check: manifest, schema_version, checksum, object_path
  restore_check: restored_partition_query_matches_expected_result
  hold_check: no_active_investigation_or_contract_exception
  decision: approve, delay, or revise_policy

11.7 Platform Patterns

This section: Platform Patterns — Retention is implemented differently across systems.

Retention is implemented differently across systems. The review habit should stay stable even when the command changes.

-- Example: drop raw chunks after the approved raw-retention window.
SELECT add_retention_policy(
  'readings',
  drop_after => INTERVAL '30 days',
  if_not_exists => true
);

influxdb_retention_review:
  edition: core_or_enterprise
  scope: database_or_table
  duration: approved_by_data_owner
  downsample_target: separate_database_or_table
  migration_needed_if_duration_changes: yes_or_no

monitoring_retention_review:
  metrics_role: service_health
  retention_time: supports_on_call_investigation_window
  retention_size: fits_disk_budget
  remote_write_or_archive: documented_if_needed
  not_source_of_truth_for: regulated_or_customer_raw_history

11.8 Partition-Aligned Deletion Mechanics

This section: Partition-Aligned Deletion Mechanics — Retention is cheap only when the storage layout gives the engine a whole unit to remove.

Retention is cheap only when the storage layout gives the engine a whole unit to remove. In time-partitioned systems, old raw data should expire as a complete chunk, partition, shard, or block. That is very different from a large DELETE WHERE observed_at < cutoff job that scans old rows, writes tombstones or dead tuples, updates indexes, and then needs vacuum or compaction cleanup.

Use the same scale as the capacity worksheet. A fleet with 5,000 devices, 8 metrics per device, and one sample per second creates 40,000 points per second. One raw day contains 3,456,000,000 points before indexes, replicas, metadata, and compression. A row-by-row delete for that day has to evaluate billions of row-level decisions. Dropping a one-day chunk removes the partition that already owns that time range. The exact implementation differs by engine, but the review question is stable: does the deletion unit match the retention boundary?

Boundary alignment is the trap. If chunks are one day long and the approved cutoff is midnight UTC, the engine can drop whole day chunks. If the job uses local time and the cutoff lands at noon UTC, the chunk straddles the boundary; the policy either waits until the whole chunk is expired or falls back to a slow partial-window path. Monitoring stores have the same idea with blocks: a Prometheus-style store removes old complete blocks, not arbitrary rows inside a block.

11.9 Late Arrivals and Rollup Refresh

This section: Late Arrivals and Rollup Refresh — Late data makes retention tricky because the reading belongs to an old observed time but arrives after a rollup may already have been calculated.

Late data makes retention tricky because the reading belongs to an old observed time but arrives after a rollup may already have been calculated. A retention contract should define how late is still acceptable and what gets refreshed.

11.10 Capacity Worksheet

This section: Capacity Worksheet — Capacity math is useful as a design input, but it is not release evidence by itself.

Capacity math is useful as a design input, but it is not release evidence by itself. Use formulas, then compare the estimate with actual sample records, compression output, rollup size, archive size, and query behavior.

raw_points_per_day = device_count * samples_per_device_per_day
raw_bytes_per_day = raw_points_per_day * average_record_bytes

rollup_points_per_day = device_count * buckets_per_day * metrics_per_device
rollup_bytes_per_day = rollup_points_per_day * average_rollup_record_bytes

archive_manifest_count = partitions_per_period * data_classes
restore_test_scope = sample_partition + representative_queries + reviewer_signoff

11.11 Label the Retention Evidence

This section: Label the Retention Evidence

11.12 Code Challenge: Retention Decision Record

This section: Code Challenge: Retention Decision Record

11.13 Retention Triage

This section: Retention Triage

11.14 Common Pitfalls

This section: Common Pitfalls

11.15 Release Checklist

This section: Release Checklist

Contract

Lifecycle owner

Raw window, rollup tiers, archive window, delete rule, hold scope, and data owner are named.

Rollup

Evidence fields

Sample count, min/max, quality counts, gap flags, source window, and refresh timestamp are present.

Archive

Restore proof

Manifest, schema version, checksum, partition key, access owner, and restore sample are checked.

Delete

Dry-run approval

Candidate partitions are listed, replacement coverage is proven, and holds are checked before deletion.

Exception

Hold workflow

Investigation, compliance, warranty, and customer-dispute holds are machine-readable and reviewable.

Review

Hands-on lab · 240 min · Python 3 + test.mosquitto.org

Store and retain MQTT time-series data in SQLite

I have explained retention choices; now let's test a storage pipeline and preserve the evidence behind its settings.

Open the lab

Next review date

Retention is revisited when device count, sample rate, legal needs, query patterns, or product promises change.

11.16 Self-Assessment

This section: Self-Assessment

11.17 Summary

This section: Summary — Data retention is the lifecycle discipline behind time-series systems.

Data retention is the lifecycle discipline behind time-series systems. It decides how long raw detail remains, what summaries replace it, what archive can be restored, which data is held, and when deletion is approved.

The strongest retention design is evidence-first: preserve quality in rollups, dry-run every deletion policy, restore a sample before trusting an archive, make holds visible to automation, and record owner approval. That keeps storage manageable without letting quality drift or incident evidence disappear.

11.18 Concept Relationships

This section: Concept Relationships — Use these relationships to follow retention’s dependencies and consequences.

Use these relationships to follow retention’s dependencies and consequences. Fundamentals defines chunks and timestamps, query optimization shows which tiers serve users, platforms supplies implementation controls, practice rehearses restore and deletion gates, and stream processing can create quality-bearing summaries before long-term storage takes ownership.

Time-Series Database Fundamentals explains timestamp contracts, chunks, compression, and lifecycle mechanics. Time-Series Query Optimization explains how query tiers, rollups, freshness, and raw replay affect dashboard performance. Time-Series Database Platforms compares platform roles and version-sensitive implementation differences. Time-Series Practice and Labs turns retention dry runs, restore checks, and release packets into hands-on drills. Stream Processing can create rollups and quality flags before data reaches long-term storage.

11.19 What’s Next

This section: What’s Next

11.20 Official References

This section: Official References — Consult the platform references only after the policy names its raw window, rollup replacement, archive, hold, and approval owner.

Consult the platform references only after the policy names its raw window, rollup replacement, archive, hold, and approval owner. Confirm the exact version’s scheduling and deletion behavior, then retain a dry-run and restore result because documentation alone does not prove the local policy is safe.

Tiger Data data retention - retention overview and relationship to continuous aggregates. Tiger Data add_retention_policy - scheduled chunk deletion policies for hypertables and continuous aggregates. Tiger Data continuous aggregates - rollup behavior and refresh concepts. InfluxDB 3 Core data retention - current database retention-period behavior and constraints. Prometheus storage - local storage retention time and retention size settings.

11.21 Key Takeaway

This section: Key Takeaway — Retention policy is a product and cost decision.

Retention policy is a product and cost decision. Keep high-resolution data only while it is useful, then downsample or archive so storage cost does not grow faster than insight.