Skip to main content

Configuration Guide

Fine-tune pg_ttl_index for optimal performance and behavior.

Background Worker Configuration​

Cleanup Interval​

Control how often cleanup runs:

-- Default: every 60 seconds
SHOW pg_ttl_index.naptime;

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

-- Slower cleanup (5 minutes)
ALTER SYSTEM SET pg_ttl_index.naptime = 300;
SELECT pg_reload_conf();

Choose based on your workload:

  • High-volume inserts → Lower naptime (30s)
  • Low-volume tables → Higher naptime (300s)
  • Standard workload → Default (60s)

Enable/Disable Worker​

-- Disable background worker globally
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- Re-enable
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

Per-Table Configuration​

Setting Expiration Time​

-- 30 minutes (sessions)
SELECT ttl_create_index('sessions', 'created_at', 1800);

-- 1 hour (short-term cache)
SELECT ttl_create_index('cache', 'expires_at', 3600);

-- 7 days (application logs)
SELECT ttl_create_index('logs', 'logged_at', 604800);

-- 30 days (audit trail)
SELECT ttl_create_index('audit', 'created_at', 2592000);

Batch Size Tuning​

Batch size affects performance and resource usage:

-- Small tables (< 10K rows): small batch
SELECT ttl_create_index('notifications', 'created_at', 86400, 1000);

-- Medium tables (10K-1M rows): default
SELECT ttl_create_index('sessions', 'created_at', 3600, 10000);

-- Large tables (> 1M rows): large batch
SELECT ttl_create_index('events', 'recorded_at', 604800, 50000);

-- Very high-volume tables: extra large batch
SELECT ttl_create_index('metrics', 'timestamp', 86400, 100000);

Batch Size Guidelines:

Table SizeDeletion RateRecommended Batch Size
< 10K rowsAny1,000 - 5,000
10K-100K rowsLow5,000 - 10,000
100K-1M rowsMedium10,000 - 25,000
> 1M rowsHigh25,000 - 100,000

Temporarily Disable TTL​

Disable Specific Table​

-- Disable without removing configuration
UPDATE ttl_index_table
SET active = false
WHERE table_name = 'sessions';

-- Re-enable later
UPDATE ttl_index_table
SET active = true
WHERE table_name = 'sessions';

Disable All TTL​

-- Method 1: Stop worker
SELECT ttl_stop_worker();

-- Method 2: Disable via config
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

Update Existing Configuration​

Change Expiry Time​

-- Update from 1 hour to 2 hours
SELECT ttl_create_index('sessions', 'created_at', 7200);

Change Batch Size​

-- Direct update
UPDATE ttl_index_table
SET batch_size = 50000
WHERE table_name = 'events';

-- Or recreate with new batch size
SELECT ttl_create_index('events', 'recorded_at', 604800, 50000);

PostgreSQL Configuration File​

postgresql.conf Settings​

# Required: Load extension at startup
shared_preload_libraries = 'pg_ttl_index'

# Optional: Tune behavior
pg_ttl_index.naptime = 60       # Cleanup every 60 seconds
pg_ttl_index.enabled = true     # Enable background worker

Reload Configuration​

# Method 1: SQL
SELECT pg_reload_conf();

# Method 2: Command line
pg_ctl reload -D /path/to/data

# Method 3: System service
sudo systemctl reload postgresql

Multi-Database Setup​

Each database needs its own worker:

-- Database 1
\c database1
SELECT ttl_start_worker();
SELECT ttl_create_index('sessions', 'created_at', 3600);

-- Database 2
\c database2
SELECT ttl_start_worker();
SELECT ttl_create_index('logs', 'logged_at', 604800);

Production Configuration Examples​

High-Traffic Web Application​

-- Aggressive cleanup for sessions
SELECT ttl_create_index('user_sessions', 'last_activity', 900, 25000);  -- 15 min

-- Moderate cleanup for logs
SELECT ttl_create_index('access_logs', 'timestamp', 86400, 50000);  -- 1 day

-- Long retention for audit
SELECT ttl_create_index('audit_log', 'created_at', 2592000, 10000);  -- 30 days

-- Worker runs every 30 seconds
ALTER SYSTEM SET pg_ttl_index.naptime = 30;
SELECT pg_reload_conf();

Analytics Platform​

-- Short-term raw events
SELECT ttl_create_index('raw_events', 'timestamp', 3600, 100000);  -- 1 hour

-- Medium-term aggregated data
SELECT ttl_create_index('hourly_stats', 'hour', 604800, 10000);  -- 7 days

-- Long-term summaries
SELECT ttl_create_index('daily_stats', 'day', 7776000, 1000);  -- 90 days

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

Development Environment​

-- Fast cleanup for testing
SELECT ttl_create_index('test_data', 'created_at', 60, 1000);  -- 1 minute!

-- Frequent runs for quick feedback
ALTER SYSTEM SET pg_ttl_index.naptime = 10;  -- 10 seconds
SELECT pg_reload_conf();

Best Practices​

1. Match Expiry to Data Lifecycle​

-- Session data: match session timeout
SELECT ttl_create_index('sessions', 'created_at', 1800);  -- 30 min

-- Cache: match cache strategy
SELECT ttl_create_index('cache', 'expires_at', 0);  -- Immediate

-- Logs: match retention policy
SELECT ttl_create_index('logs', 'timestamp', 604800);  -- 7 days

2. Size Batch to Peak Load​

-- If you insert 100K rows/hour and cleanup runs every 60 seconds:
-- Expected deletions per run ≈ 100K / 60 ≈ 1,666 rows
-- Set batch size to 2-3x that: 5,000
SELECT ttl_create_index('high_volume_table', 'created_at', 3600, 5000);

3. Monitor and Adjust​

-- Check actual deletion rates
SELECT 
    table_name,
    rows_deleted_last_run,
    batch_size,
    CASE 
        WHEN rows_deleted_last_run >= batch_size 
        THEN 'Consider increasing batch_size'
        ELSE 'OK'
    END AS recommendation
FROM ttl_summary();

4. Use Maintenance Windows​

-- Disable during high-load periods
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- Re-enable during low traffic
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

Troubleshooting Configuration​

Worker Not Respecting naptime​

-- Verify setting
SHOW pg_ttl_index.naptime;

-- Restart worker to pick up changes
SELECT ttl_stop_worker();
SELECT ttl_start_worker();

Changes Not Taking Effect​

-- Reload configuration
SELECT pg_reload_conf();

-- Verify change
SHOW pg_ttl_index.naptime;
SHOW pg_ttl_index.enabled;

Cleanup Running Too Often​

-- Increase naptime
ALTER SYSTEM SET pg_ttl_index.naptime = 300;  -- 5 minutes
SELECT pg_reload_conf();

See Also​