Configure TimescaleDB continuous aggregates for real-time analytics with PostgreSQL optimization

Verify TimescaleDB installation

Ensure TimescaleDB is properly installed and the extension is enabled in your database. If you need to install TimescaleDB first, follow our TimescaleDB installation guide.

sudo -u postgres psql -d your_database -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';"
sudo -u postgres psql -d your_database -c "SELECT timescaledb_version();"

Create sample hypertable and data

Create a hypertable with sample time-series data to demonstrate continuous aggregates. This represents typical IoT sensor data with device metrics over time.

sudo -u postgres psql -d your_database

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    cpu_usage DOUBLE PRECISION,
    memory_usage DOUBLE PRECISION
);

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

-- Insert sample data
INSERT INTO sensor_data 
SELECT 
    generate_series('2024-01-01'::timestamptz, '2024-01-31'::timestamptz, '1 minute') as time,
    (random() * 100)::integer as device_id,
    20 + random() * 15 as temperature,
    30 + random() * 40 as humidity,
    random() * 100 as cpu_usage,
    random() * 100 as memory_usage;

\q

Create basic continuous aggregate

Create your first continuous aggregate to compute hourly averages. The WITH clause specifies that this is a materialized view that will be automatically maintained.

sudo -u postgres psql -d your_database

CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', time) AS bucket,
    device_id,
    COUNT(*) as data_points,
    AVG(temperature) as avg_temperature,
    MAX(temperature) as max_temperature,
    MIN(temperature) as min_temperature,
    AVG(humidity) as avg_humidity,
    AVG(cpu_usage) as avg_cpu_usage,
    AVG(memory_usage) as avg_memory_usage
FROM sensor_data
GROUP BY bucket, device_id;

Configure refresh policy

Set up automatic refresh policies to keep the continuous aggregate up-to-date. The refresh policy determines how often and which time ranges get refreshed.

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

Create multi-level continuous aggregates

Create daily and weekly aggregates based on the hourly aggregate for different analytical time scales. This creates a hierarchy of pre-computed views.

-- Daily aggregate
CREATE MATERIALIZED VIEW sensor_data_daily
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', bucket) AS bucket,
    device_id,
    SUM(data_points) as total_data_points,
    AVG(avg_temperature) as avg_temperature,
    MAX(max_temperature) as max_temperature,
    MIN(min_temperature) as min_temperature,
    AVG(avg_humidity) as avg_humidity,
    AVG(avg_cpu_usage) as avg_cpu_usage,
    AVG(avg_memory_usage) as avg_memory_usage
FROM sensor_data_hourly
GROUP BY bucket, device_id;

-- Weekly aggregate
CREATE MATERIALIZED VIEW sensor_data_weekly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 week', bucket) AS bucket,
    device_id,
    SUM(total_data_points) as total_data_points,
    AVG(avg_temperature) as avg_temperature,
    MAX(max_temperature) as max_temperature,
    MIN(min_temperature) as min_temperature,
    AVG(avg_humidity) as avg_humidity,
    AVG(avg_cpu_usage) as avg_cpu_usage,
    AVG(avg_memory_usage) as avg_memory_usage
FROM sensor_data_daily
GROUP BY bucket, device_id;

Configure refresh policies for all aggregates

Set appropriate refresh intervals for daily and weekly aggregates. Longer-term aggregates typically need less frequent updates.

-- Daily aggregate refresh policy
SELECT add_continuous_aggregate_policy('sensor_data_daily',
    start_offset => INTERVAL '1 week',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 hour');

-- Weekly aggregate refresh policy
SELECT add_continuous_aggregate_policy('sensor_data_weekly',
    start_offset => INTERVAL '4 weeks',
    end_offset => INTERVAL '1 week',
    schedule_interval => INTERVAL '6 hours');

\q

Create real-time continuous aggregate

Enable real-time aggregation to include the most recent data that hasn't been materialized yet. This provides completely up-to-date results.

sudo -u postgres psql -d your_database

CREATE MATERIALIZED VIEW sensor_data_realtime
WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS
SELECT 
    time_bucket('15 minutes', time) AS bucket,
    device_id,
    COUNT(*) as data_points,
    AVG(temperature) as avg_temperature,
    MAX(temperature) as max_temperature,
    MIN(temperature) as min_temperature,
    AVG(cpu_usage) as avg_cpu_usage
FROM sensor_data
GROUP BY bucket, device_id;

SELECT add_continuous_aggregate_policy('sensor_data_realtime',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '15 minutes',
    schedule_interval => INTERVAL '15 minutes');

Optimize continuous aggregate performance

Create indexes on continuous aggregates to improve query performance and configure compression for older data.

-- Create indexes on continuous aggregates
CREATE INDEX idx_sensor_hourly_bucket_device ON sensor_data_hourly (bucket, device_id);
CREATE INDEX idx_sensor_daily_bucket_device ON sensor_data_daily (bucket, device_id);
CREATE INDEX idx_sensor_weekly_bucket_device ON sensor_data_weekly (bucket, device_id);
CREATE INDEX idx_sensor_realtime_bucket_device ON sensor_data_realtime (bucket, device_id);

-- Enable compression on continuous aggregates
ALTER MATERIALIZED VIEW sensor_data_hourly SET (timescaledb.compress = true);
ALTER MATERIALIZED VIEW sensor_data_daily SET (timescaledb.compress = true);
ALTER MATERIALIZED VIEW sensor_data_weekly SET (timescaledb.compress = true);

-- Add compression policies
SELECT add_compression_policy('sensor_data_hourly', INTERVAL '7 days');
SELECT add_compression_policy('sensor_data_daily', INTERVAL '30 days');
SELECT add_compression_policy('sensor_data_weekly', INTERVAL '90 days');

Configure PostgreSQL optimization

Tune PostgreSQL settings specifically for continuous aggregate workloads. These settings improve background worker performance and memory usage.

\q

# TimescaleDB continuous aggregate optimization
timescaledb.max_background_workers = 16
max_worker_processes = 32
shared_preload_libraries = 'timescaledb'

Memory settings for aggregation
work_mem = '256MB'
maintenance_work_mem = '1GB'
effective_cache_size = '4GB'

Checkpoint and WAL settings
checkpoint_completion_target = 0.9
wal_buffers = '16MB'
max_wal_size = '2GB'
min_wal_size = '1GB'

Background writer settings
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0

Restart PostgreSQL and verify settings

Restart PostgreSQL to apply the configuration changes and verify the settings are loaded correctly.

sudo systemctl restart postgresql
sudo systemctl status postgresql

sudo systemctl restart postgresql
sudo systemctl status postgresql

Set up monitoring views

Create monitoring views to track continuous aggregate job performance and system resource usage.

sudo -u postgres psql -d your_database

-- Create monitoring view for continuous aggregate jobs
CREATE VIEW continuous_aggregate_stats AS
SELECT 
    format('%I.%I', schema_name, view_name) as view_name,
    materialization_hypertable_name,
    job_id,
    last_run_started_at,
    last_successful_finish,
    last_run_status,
    total_runs,
    total_successes,
    total_failures
FROM timescaledb_information.continuous_aggregates ca
LEFT JOIN timescaledb_information.jobs j ON j.hypertable_name = ca.materialization_hypertable_name;

-- Create view for job statistics
CREATE VIEW job_stats AS
SELECT 
    job_id,
    application_name,
    job_type,
    schedule_interval,
    max_runtime,
    max_retries,
    retry_period,
    last_run_started_at,
    last_successful_finish,
    next_start,
    total_runs,
    total_successes,
    total_failures
FROM timescaledb_information.jobs
ORDER BY job_id;