Performance Tuning

Optimize pg_ttl_index for maximum performance and minimal impact.

Quick Wins

1. Right-Size Batch Deletions

-- Check current batch effectiveness
SELECT 
    table_name,
    batch_size,
    rows_deleted_last_run,
    rows_deleted_last_run::FLOAT / batch_size AS ratio
FROM ttl_summary()
ORDER BY ratio DESC;

-- If ratio consistently > 0.9, increase batch_size
UPDATE ttl_index_table
SET batch_size = 50000
WHERE table_name = 'high_volume_table';

2. Tune Cleanup Interval

-- Less frequent = lower overhead
ALTER SYSTEM SET pg_ttl_index.naptime = 120;  -- 2 minutes
SELECT pg_reload_conf();

3. Use Partitioning

-- Combine partitioning with TTL for huge tables
CREATE TABLE events (
    id BIGSERIAL,
    data JSONB,
    created_at TIMESTAMPTZ
) PARTITION BY RANGE (created_at);

-- Drop entire partitions instead of row-level deletions

Batch Size Tuning

Benchmarking Formula

Target batch size = (rows inserted per hour / 60) × 2

Example: - 120,000 rows/hour inserted - Cleanup runs every 60 seconds - Expected deletions/run: 120,000 / 60 = 2,000 - Recommended batch: 2,000 × 2 = 4,000

Finding Optimal Batch Size

-- Test different batch sizes in development
SELECT ttl_create_index('test_table', 'created_at', 3600, 10000);
-- Monitor performance, adjust

SELECT ttl_create_index('test_table', 'created_at', 3600, 25000);
-- Re-test

SELECT ttl_create_index('test_table', 'created_at', 3600, 50000);
-- Compare results

Batch Size Impact

Batch Size CPU Usage Lock Duration WAL Generated
1,000 Low Very Short Low
10,000 Medium Short Medium
50,000 Higher Medium High
100,000 High Long Very High

Index Optimization

Leverage Auto-Created Indexes

-- TTL uses the auto-created index efficiently
SELECT ttl_create_index('logs', 'created_at', 604800);
-- Creates: idx_ttl_logs_created_at

-- Verify index is used
EXPLAIN SELECT ctid FROM logs WHERE created_at < NOW() - INTERVAL '7 days';
-- Should show "Index Scan using idx_ttl_logs_created_at"

Composite Indexes for Queries

-- If you frequently query by user + timestamp:
CREATE INDEX idx_logs_user_time ON logs(user_id, created_at);

-- TTL still benefits from its own index for cleanup
SELECT ttl_create_index('logs', 'created_at', 604800);

Index Maintenance

-- Periodically reindex TTL indexes
REINDEX INDEX idx_ttl_sessions_created_at;

-- Or use auto_vacuum aggressively
ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.05);

Monitoring Performance

Track Cleanup Duration

-- Add timing to cleanup runs
CREATE OR REPLACE FUNCTION ttl_runner_timed()
RETURNS TABLE(duration INTERVAL, rows_deleted INTEGER) AS $$
DECLARE
    start_time TIMESTAMPTZ;
    result INTEGER;
BEGIN
    start_time := clock_timestamp();
    SELECT ttl_runner() INTO result;
    RETURN QUERY SELECT clock_timestamp() - start_time, result;
END;
$$ LANGUAGE plpgsql;

-- Test it
SELECT * FROM ttl_runner_timed();

Identify Slow Tables

-- Which tables take longest to clean?
-- (requires custom logging)

-- Instead, check row counts vs batch size
SELECT 
    table_name,
    rows_deleted_last_run,
    batch_size,
    CEILING(rows_deleted_last_run::NUMERIC / batch_size) AS batches_needed
FROM ttl_summary()
ORDER BY batches_needed DESC;

Reducing I/O Impact

Spread Out Cleanup

-- Increase naptime to reduce frequency
ALTER SYSTEM SET pg_ttl_index.naptime = 180;  -- 3 minutes
SELECT pg_reload_conf();

Use Off-Peak Hours

-- Disable during peak hours (8am-6pm)
-- Schedule via cron or pgAgent

-- 8am: Disable
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- 6pm: Enable
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

Maintenance Window Cleanup

-- Disable automatic cleanup
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- Manual cleanup during maintenance window
SELECT ttl_runner();

High-Volume Scenarios

Streaming Data

-- High-frequency inserts (100K+ rows/hour)
SELECT ttl_create_index('streaming_data', 'timestamp', 3600, 100000);

-- Frequent cleanup
ALTER SYSTEM SET pg_ttl_index.naptime = 30;
SELECT pg_reload_conf();

Time-Series Data

-- Partition by time period
CREATE TABLE metrics (
    id BIGSERIAL,
    value NUMERIC,
    timestamp TIMESTAMPTZ
) PARTITION BY RANGE (timestamp);

-- Create partitions monthly
-- Drop old partitions instead of row-level TTL
DROP TABLE metrics_2025_12;

WAL Reduction

Unlogged Tables (Caution!)

-- For truly temporary data only
CREATE UNLOGGED TABLE temp_cache (
    id SERIAL PRIMARY KEY,
    data TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

SELECT ttl_create_index('temp_cache', 'created_at', 300);
-- No WAL generated, but data lost on crash!

Fillfactor Tuning

-- Reduce table bloat from frequent deletes
ALTER TABLE sessions SET (fillfactor = 70);

-- More free space per page = less bloat

Memory Usage

Connection Pooling

-- TTL worker uses one connection
-- Ensure max_connections accounts for it

SHOW max_connections;
-- Should be: (app connections) + 1 (for TTL worker)

Shared Buffers

-- TTL benefits from cached indexes
-- Ensure sufficient shared_buffers

SHOW shared_buffers;
-- Recommended: 25% of RAM (PostgreSQL standard)

Benchmarking

Measure Baseline

-- Before optimization
SELECT 
    table_name,
    total_rows_deleted,
    last_run
FROM ttl_summary();

-- Measure over 1 hour
-- Record: rows deleted, CPU usage, I/O usage

Test Optimizations

-- Change batch size
UPDATE ttl_index_table SET batch_size = 50000 WHERE table_name = 'test';

-- Measure again after 1 hour
-- Compare metrics

Performance Metrics

Metric Target Monitoring
Cleanup duration < 1 second per table Custom timing function
Rows/second deleted > 10,000 rows_deleted / duration
CPU during cleanup < 20% top, pg_stat_activity
Lock wait time < 100ms pg_stat_activity

Production Tuning Examples

E-Commerce Site

-- High session churn
SELECT ttl_create_index('sessions', 'updated_at', 1800, 25000);

-- Moderate log volume
SELECT ttl_create_index('access_logs', 'timestamp', 86400, 50000);

-- Background worker: every 30 seconds
ALTER SYSTEM SET pg_ttl_index.naptime = 30;

Analytics Platform

-- Massive event streams
SELECT ttl_create_index('events', 'timestamp', 3600, 100000);

-- Aggregated data (slower cleanup OK)
SELECT ttl_create_index('hourly_stats', 'hour', 604800, 10000);

-- Background worker: every 2 minutes (reduce overhead)
ALTER SYSTEM SET pg_ttl_index.naptime = 120;

Troubleshooting Performance

Cleanup Takes Too Long

Symptoms: ttl_runner() execution > 5 seconds

Solutions: 1. Increase batch size 2. Add indexes on timestamp column (auto-created, but verify) 3. Run VACUUM ANALYZE on tables 4. Check for table bloat

High CPU Usage

Symptoms: CPU spikes during cleanup

Solutions: 1. Increase naptime (less frequent cleanup) 2. Decrease batch_size (smaller batches) 3. Run cleanup during off-peak hours

Table Bloat

Symptoms: Table size doesn’t decrease despite deletions

Solutions: ```sql – More aggressive autovacuum ALTER TABLE sessions SET ( autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_cost_delay = 10 );

– Manual vacuum VACUUM FULL sessions; – Locks table, use with caution ```

See Also