pg_ttl_index
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_ttl_index | 3.0.0 | FEAT | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 2730 | pg_ttl_index | No | Yes | Yes | Yes | No | Yes | - |
| Related | temporal_tables periods hll rum pg_partman pg_cron pg_task timescaledb |
|---|
pg 14 breaks; rm README from RPM/DEB
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PIGSTY | 3.0.0 | 1817161514 | pg_ttl_index | - |
| RPM | PIGSTY | 3.0.0 | 1817161514 | pg_ttl_index_$v | - |
| DEB | PIGSTY | 3.0.0 | 1817161514 | postgresql-$v-ttl-index | - |
Build
You can build the RPM / DEB packages for pg_ttl_index using pig build:
pig build pkg pg_ttl_index # build RPM / DEB packages
Install
You can install pg_ttl_index directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_ttl_index; # Install for current active PG version
pig ext install -y pg_ttl_index -v 18 # PG 18
pig ext install -y pg_ttl_index -v 17 # PG 17
pig ext install -y pg_ttl_index -v 16 # PG 16
pig ext install -y pg_ttl_index -v 15 # PG 15
dnf install -y pg_ttl_index_18 # PG 18
dnf install -y pg_ttl_index_17 # PG 17
dnf install -y pg_ttl_index_16 # PG 16
dnf install -y pg_ttl_index_15 # PG 15
apt install -y postgresql-18-ttl-index # PG 18
apt install -y postgresql-17-ttl-index # PG 17
apt install -y postgresql-16-ttl-index # PG 16
apt install -y postgresql-15-ttl-index # PG 15
Preload:
shared_preload_libraries = 'pg_ttl_index';
Create Extension:
CREATE EXTENSION pg_ttl_index;
Usage
pg_ttl_index provides automatic data expiration by associating a TTL (time-to-live) with a timestamp column. A background worker periodically deletes rows whose timestamp exceeds the configured expiration interval.
Quick Start
-- 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);
Functions
| Function | Description |
|---|---|
ttl_start_worker() | Start the background worker for automatic cleanup |
ttl_worker_status() | Check if the worker is running |
ttl_runner() | Manually trigger cleanup |
ttl_create_index(table, column, expire_seconds [, batch_size]) | Configure TTL expiration |
ttl_drop_index(table, column) | Remove TTL configuration |
ttl_summary() | List all TTL indexes with stats |
Examples
Session management with 24-hour expiry:
SELECT ttl_create_index('sessions', 'created_at', 86400, 5000);
Log retention for 7 days:
SELECT ttl_create_index('app_logs', 'logged_at', 604800);
Cache entries with custom expiry column (0 means the column itself holds the absolute expiry time):
SELECT ttl_create_index('cache_entries', 'expires_at', 0);
Monitoring
SELECT * FROM ttl_summary();
Pause cleanup for a specific table:
UPDATE ttl_index_table SET active = false WHERE table_name = 'user_sessions';
Configuration
| Parameter | Description | Default |
|---|---|---|
pg_ttl_index.naptime | Cleanup interval in seconds | 60 |
pg_ttl_index.enabled | Enable/disable worker globally | on |
ALTER SYSTEM SET pg_ttl_index.naptime = 30;
SELECT pg_reload_conf();
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.