Data Retention Policy with Drop Chunks

This snippet implements a data retention policy that automatically drops chunks older than 90 days from the [metrics] hypertable. The add_drop_chunks_policy function schedules a background job that deletes entire chunks—the fundamental storage unit in TimescaleDB—rather than individual rows. Internally, dropping a chunk is a metadata operation that removes the corresponding PostgreSQL table partition, which is much faster than DELETE statements and avoids VACUUM overhead.

In production, this is critical for compliance with data retention regulations like GDPR or PCI-DSS, or simply to limit storage costs in high-volume logging systems. For instance, a CDN provider might retain raw access logs for 30 days but aggregated reports for a year. Use this policy when you have a legal or operational requirement to purge old data and want to avoid manual cleanup scripts.

Security implications: dropped data is irrecoverable unless you have backups, so always test with a dry run first. Performance-wise, the drop operation is lightweight but can cause brief locks on the hypertable; schedule it during low-traffic windows. A common pitfall is setting the retention interval too short, accidentally deleting data needed for historical analysis. Also, ensure the policy does not conflict with compression policies—drop chunks after compression completes.

> System.Execute()

SELECT add_drop_chunks_policy('metrics', INTERVAL '90 days', if_not_exists => true);

> Architecture & Security Context

Verified for Valters IT Hub. When deploying this configuration in a production homelab or enterprise environment, ensure that you monitor system resources and implement strict access controls. Administrator privileges are required.

ID: VALTERSIT-7AFA3CC7DE38