TimescaleDB: Compression, Continuous Aggregates & Retention

Native Compression

ALTER TABLE sensor_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

-- Automatically compresses chunks older than 7 days using columnar
-- compression -- significantly reduces storage while remaining queryable
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

Continuous Aggregates

-- Incrementally maintained pre-computed aggregate -- only the
-- portions affected by new data get updated, not a full recompute
CREATE MATERIALIZED VIEW hourly_avg
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  avg(temperature) AS avg_temp
FROM sensor_data
GROUP BY bucket, device_id;

SELECT add_continuous_aggregate_policy('hourly_avg',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

Retention Policy

-- Automatically drops raw chunks older than 90 days -- avoids
-- unbounded storage growth without a manually-scheduled cleanup job
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

The Downsampling Pattern

A common combination: keep raw per-second readings for 7 days (recent detailed analysis), maintain an hourly continuous aggregate indefinitely (long-term trend queries), and drop the raw data past 7 days via a retention policy -- 'what was the average temperature last year' stays fast even after the per-second data is gone.

Filling Gaps for Charting

-- Fills missing buckets (e.g. a sensor went offline) so a chart
-- shows a complete, evenly-spaced series instead of silent gaps
SELECT
  time_bucket_gapfill('1 minute', time) AS bucket,
  avg(temperature) AS avg_temp
FROM sensor_data
WHERE time > now() - INTERVAL '1 hour'
GROUP BY bucket
ORDER BY bucket;