中文
devexamples
← Recipes

阅读中文版本

Add a Time-Series Retention Policy

Partition metrics by time, drop or roll up old partitions on a schedule, and keep queries bounded so storage and latency stay predictable.

~45 mintimeseriespostgresqldata-lifecycle

Task

Implement retention for a high-volume time-series table using time partitions and scheduled drops.

When to use this

A metrics table grows without bound, so queries slow down, storage costs climb, and deleting old rows with DELETE causes vacuum pressure.

Prerequisites

  • PostgreSQL with declarative partitioning or a time-series extension
  • A scheduler to create and drop partitions

Steps

1. Create the table partitioned by range on the timestamp column.

Expected output: Rows land in time-based partitions.

2. Pre-create upcoming partitions and an index on each.

Expected output: New data always has a target partition with an index.

3. Drop partitions older than the retention window instead of deleting rows.

Expected output: Old data disappears instantly with no vacuum cost.

4. Roll up or archive a partition before dropping it if history is needed.

psql -c "SELECT drop_partition('metrics', now() - interval '90 days');"

Expected output: Long-term aggregates survive the raw data.

Troubleshooting

Problem

Query scans all partitions

Solution

Always filter on the partition key so pruning can exclude old partitions.

Problem

Missing partition for incoming data

Solution

Pre-create partitions ahead of time and alert when the next partition is missing.

Limitations

  • Partitioning adds management overhead and does not speed up queries that ignore the time column.
  • Dropping partitions irreversibly removes raw data, so roll-ups must run before the drop.

Context

Implement retention for a high-volume time-series table using time partitions and scheduled drops. A metrics table grows without bound, so queries slow down, storage costs climb, and deleting old rows with DELETE causes vacuum pressure.

Steps

  1. Create the table partitioned by range on the timestamp column. Expected: Rows land in time-based partitions.
  2. Pre-create upcoming partitions and an index on each. Expected: New data always has a target partition with an index.
  3. Drop partitions older than the retention window instead of deleting rows. Expected: Old data disappears instantly with no vacuum cost.
  4. Roll up or archive a partition before dropping it if history is needed. Expected: Long-term aggregates survive the raw data.
CREATE TABLE metrics (ts timestamptz NOT NULL, name text, value real)
  PARTITION BY RANGE (ts);
CREATE TABLE metrics_p2026_10 PARTITION OF metrics
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
-- retention: instant, cheap
DROP TABLE metrics_p2026_07;

Verify

  • Rows land in time-based partitions.
  • New data always has a target partition with an index.
  • Old data disappears instantly with no vacuum cost.
  • Long-term aggregates survive the raw data.

Limits

  • Partitioning adds management overhead and does not speed up queries that ignore the time column.
  • Dropping partitions irreversibly removes raw data, so roll-ups must run before the drop.