pg_ttl_index

pig build pkg pg_ttl_index         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

shared_preload_libraries = 'pg_ttl_index';

CREATE EXTENSION pg_ttl_index;

-- Start the background worker
SELECT ttl_start_worker();

-- Create a table with a timestamp column
CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    session_data JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Expire rows after 1 hour (3600 seconds)
SELECT ttl_create_index('user_sessions', 'created_at', 3600);

SELECT ttl_create_index('sessions', 'created_at', 86400, 5000);

SELECT ttl_create_index('app_logs', 'logged_at', 604800);

SELECT ttl_create_index('cache_entries', 'expires_at', 0);

SELECT * FROM ttl_summary();

UPDATE ttl_index_table SET active = false WHERE table_name = 'user_sessions';

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