pg_ttl_index

pig build pkg pg_ttl_index;		# build rpm/deb

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

pig install pg_ttl_index;		# install via package name, for the active PG version

pig install pg_ttl_index -v 18;   # install for PG 18
pig install pg_ttl_index -v 17;   # install for PG 17
pig install pg_ttl_index -v 16;   # install for PG 16
pig install pg_ttl_index -v 15;   # install for PG 15

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();