Implement TimescaleDB data retention policies for time-series optimization

Intermediate 25 min Apr 23, 2026 819 views
Ubuntu 24.04 Debian 12 AlmaLinux 9 Rocky Linux 9

Learn to configure automated data retention policies in TimescaleDB for efficient storage management. Set up compression, continuous aggregates, and automated data lifecycle policies to optimize time-series database performance and reduce storage costs.

Prerequisites

  • Root or sudo access
  • At least 4GB RAM
  • PostgreSQL 12+ compatible system

What this solves

TimescaleDB data retention policies automatically manage your time-series data lifecycle by dropping old data, compressing chunks, and maintaining continuous aggregates. This prevents storage bloat, reduces query costs, and maintains database performance as your time-series data grows. You'll implement automated retention rules that keep recent data accessible while compressing or removing historical data based on your business requirements.

Step-by-step configuration

Update system packages

Start by updating your package manager to ensure you have the latest PostgreSQL and system packages available.

sudo apt update && sudo apt upgrade -y
sudo apt install -y wget ca-certificates
sudo dnf update -y
sudo dnf install -y wget ca-certificates

Install PostgreSQL 16

TimescaleDB requires PostgreSQL 12 or higher. Install PostgreSQL 16 for the best performance and feature compatibility.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16 postgresql-contrib-16
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql16-server postgresql16-contrib
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

Start and enable PostgreSQL

Enable PostgreSQL to start on boot and start the service immediately.

sudo systemctl enable --now postgresql
sudo systemctl status postgresql
sudo systemctl enable --now postgresql-16
sudo systemctl status postgresql-16

Install TimescaleDB

Add the TimescaleDB repository and install the extension for PostgreSQL 16.

echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo apt update
sudo apt install -y timescaledb-2-postgresql-16
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <

Configure PostgreSQL for TimescaleDB

Run the timescaledb-tune utility to optimize PostgreSQL configuration for time-series workloads.

sudo timescaledb-tune --quiet --yes

Restart PostgreSQL

Restart PostgreSQL to apply the TimescaleDB configuration changes.

sudo systemctl restart postgresql
sudo systemctl restart postgresql-16

Create a database and enable TimescaleDB

Create a new database for time-series data and enable the TimescaleDB extension.

sudo -u postgres createdb metrics_db
sudo -u postgres psql -d metrics_db -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

Create a sample time-series table

Create a hypertable to demonstrate retention policies with sensor data.

sudo -u postgres psql -d metrics_db << 'EOF'
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    location TEXT
);

SELECT create_hypertable('sensor_data', 'time');
EOF

Insert sample data

Add sample time-series data spanning several months to test retention policies.

sudo -u postgres psql -d metrics_db << 'EOF'
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location)
SELECT 
    generate_series(NOW() - INTERVAL '6 months', NOW(), INTERVAL '1 hour') AS time,
    (random() * 10 + 1)::INTEGER AS sensor_id,
    (random() * 30 + 20) AS temperature,
    (random() * 40 + 40) AS humidity,
    'Building-' || (random() * 5 + 1)::INTEGER AS location;
EOF

Configure data retention policies

Create a basic retention policy

Set up a retention policy to automatically drop data older than 3 months.

sudo -u postgres psql -d metrics_db -c "
SELECT add_retention_policy('sensor_data', INTERVAL '3 months');
"

Enable compression

Enable compression for chunks older than 7 days to reduce storage usage while maintaining data accessibility.

sudo -u postgres psql -d metrics_db << 'EOF'
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');
EOF

Create continuous aggregates

Set up continuous aggregates for efficient querying of historical data with automatic refresh policies.

sudo -u postgres psql -d metrics_db << 'EOF'
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    location,
    AVG(temperature) AS avg_temperature,
    AVG(humidity) AS avg_humidity,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature
FROM sensor_data
GROUP BY bucket, sensor_id, location;

CREATE MATERIALIZED VIEW sensor_data_daily
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', time) AS bucket,
    sensor_id,
    location,
    AVG(temperature) AS avg_temperature,
    AVG(humidity) AS avg_humidity,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature
FROM sensor_data
GROUP BY bucket, sensor_id, location;
EOF

Configure continuous aggregate refresh policies

Add refresh policies to keep continuous aggregates up to date automatically.

sudo -u postgres psql -d metrics_db << 'EOF'
SELECT add_continuous_aggregate_policy('sensor_data_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('sensor_data_daily',
    start_offset => INTERVAL '3 days', 
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day');
EOF

Set up retention for continuous aggregates

Configure retention policies for the continuous aggregates to manage their lifecycle independently.

sudo -u postgres psql -d metrics_db << 'EOF'
SELECT add_retention_policy('sensor_data_hourly', INTERVAL '1 year');
SELECT add_retention_policy('sensor_data_daily', INTERVAL '3 years');
EOF

Monitor and optimize retention performance

Check compression status

Monitor which chunks are compressed and their compression ratios to optimize storage.

sudo -u postgres psql -d metrics_db -c "
SELECT 
    chunk_schema,
    chunk_name,
    compression_status,
    before_compression_total_bytes,
    after_compression_total_bytes,
    ROUND(100 - (after_compression_total_bytes::float / before_compression_total_bytes::float * 100), 2) AS compression_ratio_percent
FROM chunk_compression_stats('sensor_data')
ORDER BY chunk_name;
"

View retention policy status

Check all active retention and compression policies to ensure they're running as expected.

sudo -u postgres psql -d metrics_db -c "
SELECT 
    application_name,
    schedule_interval,
    max_runtime,
    max_retries,
    retry_period
FROM timescaledb_information.jobs 
WHERE proc_name IN ('policy_retention', 'policy_compression', 'policy_refresh_continuous_aggregate');
"

Monitor job execution history

Review the execution history of retention and compression jobs to identify any failures or performance issues.

sudo -u postgres psql -d metrics_db -c "
SELECT 
    job_id,
    start_time,
    finish_time,
    success,
    error_message
FROM timescaledb_information.job_stats 
ORDER BY start_time DESC 
LIMIT 20;
"

Configure job scheduling

Adjust the schedule for retention and compression jobs to run during low-traffic periods.

sudo -u postgres psql -d metrics_db << 'EOF'
-- Run retention policy daily at 2 AM
SELECT alter_job(
    (SELECT job_id FROM timescaledb_information.jobs WHERE proc_name = 'policy_retention' AND hypertable_name = 'sensor_data'),
    schedule_interval => INTERVAL '1 day',
    max_runtime => INTERVAL '1 hour'
);

-- Run compression policy every 6 hours
SELECT alter_job(
    (SELECT job_id FROM timescaledb_information.jobs WHERE proc_name = 'policy_compression' AND hypertable_name = 'sensor_data'),
    schedule_interval => INTERVAL '6 hours',
    max_runtime => INTERVAL '2 hours'
);
EOF

Create monitoring views

Set up monitoring views to track storage usage and policy effectiveness over time.

sudo -u postgres psql -d metrics_db << 'EOF'
CREATE OR REPLACE VIEW storage_stats AS
SELECT 
    hypertable_name,
    total_size_bytes,
    pg_size_pretty(total_size_bytes) AS total_size,
    compressed_heap_size_bytes,
    pg_size_pretty(compressed_heap_size_bytes) AS compressed_size,
    CASE 
        WHEN total_size_bytes > 0 THEN 
            ROUND(100 - (compressed_heap_size_bytes::float / total_size_bytes::float * 100), 2)
        ELSE 0
    END AS compression_ratio_percent
FROM hypertable_detailed_size('sensor_data');

CREATE OR REPLACE VIEW chunk_status AS
SELECT 
    chunk_name,
    range_start,
    range_end,
    is_compressed,
    compressed_heap_size,
    pg_size_pretty(compressed_heap_size) AS compressed_size
FROM timescaledb_information.chunks 
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start DESC;
EOF

Advanced retention configurations

Create conditional retention policies

Set up more complex retention rules based on data characteristics using custom functions.

sudo -u postgres psql -d metrics_db << 'EOF'
-- Create function for conditional retention based on sensor importance
CREATE OR REPLACE FUNCTION conditional_retention_policy()
RETURNS VOID AS $$
DECLAREBEGIN
    -- Keep critical sensor data (sensor_id 1-3) for 1 year
    -- Keep normal sensor data for 3 months
    DELETE FROM sensor_data 
    WHERE time < NOW() - INTERVAL '1 year' 
    AND sensor_id IN (1, 2, 3);
    
    DELETE FROM sensor_data 
    WHERE time < NOW() - INTERVAL '3 months' 
    AND sensor_id NOT IN (1, 2, 3);
END;
$$ LANGUAGE plpgsql;

-- Schedule the custom retention policy
SELECT add_job('conditional_retention_policy', '1 day');
EOF

Configure alerting for retention policies

Create a function to monitor retention policy execution and alert on failures.

sudo -u postgres psql -d metrics_db << 'EOF'
CREATE OR REPLACE FUNCTION check_retention_health()
RETURNS TABLE(job_name TEXT, last_success TIMESTAMPTZ, status TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        j.application_name::TEXT,
        js.last_successful_finish,
        CASE 
            WHEN js.last_successful_finish < NOW() - INTERVAL '2 days' THEN 'STALE'
            WHEN js.consecutive_failures > 3 THEN 'FAILING'
            ELSE 'OK'
        END::TEXT
    FROM timescaledb_information.jobs j
    JOIN timescaledb_information.job_stats js ON j.job_id = js.job_id
    WHERE j.proc_name IN ('policy_retention', 'policy_compression');
END;
$$ LANGUAGE plpgsql;
EOF

Verify your setup

Check that your retention policies are working correctly and data is being managed as expected.

sudo -u postgres psql -d metrics_db -c "SELECT * FROM storage_stats;"
sudo -u postgres psql -d metrics_db -c "SELECT * FROM chunk_status LIMIT 10;"
sudo -u postgres psql -d metrics_db -c "SELECT * FROM check_retention_health();"
sudo -u postgres psql -d metrics_db -c "SELECT COUNT(*) AS total_records FROM sensor_data;"
sudo -u postgres psql -d metrics_db -c "SELECT COUNT(*) AS compressed_chunks FROM timescaledb_information.chunks WHERE is_compressed = true AND hypertable_name = 'sensor_data';"

Common issues

SymptomCauseFix
Retention policy not executingBackground worker not runningSELECT timescaledb_pre_restore(); then restart PostgreSQL
Compression failingActive transactions on chunksWait for transactions to complete or restart PostgreSQL
High storage usage despite retentionVACUUM not running after deletionVACUUM ANALYZE sensor_data;
Continuous aggregate not refreshingPolicy disabled or failingCheck job status: SELECT * FROM timescaledb_information.job_stats;
Query performance degradedMissing indexes on compressed chunksAdd indexes before enabling compression
Extension not loadingMissing shared_preload_librariesAdd 'timescaledb' to postgresql.conf and restart

Next steps

Running this in production?

Want this handled for you? Setting up TimescaleDB retention policies once is straightforward. Keeping them monitored, tuned, and integrated with backup strategies across environments is the harder part. See how we run infrastructure like this for European SaaS and analytics teams.

Automated install script

Run this to automate the entire setup

#timescaledb #postgresql #data-retention #time-series #compression

Verwandte Tutorials

databases Advanced

Configure PostgreSQL 17 SSL encryption and certificate-based authentication

Set up a private CA, issue server and client certificates, enforce TLS 1.2+ with strong ciphers, and configure pg_hba.conf for mutual TLS client certificate authentication in PostgreSQL 17.

45 min 4 distros 82 views
databases Intermediate

Configure PostgreSQL 17 connection pooling with PgBouncer for high availability

Deploy PgBouncer in front of PostgreSQL 17 with session, transaction and statement pooling modes, SCRAM-SHA-256 authentication, keepalived-based HA, and Prometheus monitoring for production workloads.

45 min 4 distros 184 views
databases Advanced

Setup ScyllaDB backup validation and automated restore testing

Configure automated ScyllaDB backups with nodetool snapshots, implement validation scripts to verify backup integrity, and set up systemd timers for automated restore testing with Prometheus monitoring.

45 min 4 distros 446 views
Browse all tutorials in our complete directory →

Sie möchten das nicht selbst verwalten?

Wir betreiben Infrastruktur für Unternehmen, die auf Verfügbarkeit angewiesen sind. Vollständig verwaltet, mit einem festen Ansprechpartner, der Ihre Umgebung kennt.

Sie erhalten einen festen Ansprechpartner, der Ihr Setup kennt

Rotterdam 12:18 · erreichbar per Nachricht, kein Ticketformular

+31 10 477 5362 WhatsApp