pg_ttl_index

pig build pkg pg_ttl_index         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

shared_preload_libraries = 'pg_ttl_index';

CREATE EXTENSION pg_ttl_index;

-- 启动后台工作进程
SELECT ttl_start_worker();

-- 创建带时间戳列的表
CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    session_data JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 1 小时（3600 秒）后过期
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();