Contents

• PostgreSQL TTL Index Extension
  • Features
  • Installation
  • Usage
  • Configuration
  • How It Works

PostgreSQL TTL Index Extension

A PostgreSQL extension providing automatic TTL (Time To Live) functionality for data expiration.

Features

• Automatic data expiration based on timestamp columns
• Background worker runs cleanup every minute automatically
• Multiple tables supported with different expiry times
• Production ready with SQL injection protection
• Configurable cleanup intervals

Installation

make clean && make
sudo make install

Add to shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'pg_ttl_index'

Restart PostgreSQL and create the extension:

CREATE EXTENSION pg_ttl_index;

Usage

-- Create TTL indexes (data expires automatically)
SELECT ttl_create_index('user_sessions', 'created_at', 3600);  -- 1 hour
SELECT ttl_create_index('temp_logs', 'log_time', 1800);        -- 30 minutes

-- Remove TTL index
SELECT ttl_drop_index('user_sessions', 'created_at');

-- Manual cleanup (optional - runs automatically via background worker)
SELECT ttl_runner_safe();

Configuration

-- Change cleanup interval (default: 60 seconds)
ALTER SYSTEM SET pg_ttl_index.naptime = 30;
SELECT pg_reload_conf();

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

How It Works

1. Create TTL indexes on timestamp/date columns
2. Background worker runs every minute (configurable)
3. Expired data is automatically deleted
4. Multiple tables supported with different expiry times
5. All operations logged for monitoring

The extension is fully automatic - once configured, no manual intervention needed!