Contents

• PostgreSQL TTL Index Extension - User Guide
  • Table of Contents
  • Overview
    • Key Features
  • Prerequisites
  • Installation
    • Option 1: Install via PGXN (Recommended)
• Ubuntu/Debian
• macOS
• Or install via pip
  • • Step 2: Configure PostgreSQL
    • Step 3: Restart PostgreSQL
    • Step 4: Create the Extension
  • Quick Start
    • 1. Start the Background Worker
    • 2. Create a Sample Table
    • 3. Set Up TTL Index
    • 4. Verify TTL Index
    • 5. Test the Cleanup
  • Usage Examples
    • Example 1: Session Management
    • Example 2: Log Cleanup
    • Example 3: Cache Management
    • Managing TTL Indexes
  • Background Worker Management
    • Starting the Background Worker
    • Important Notes
  • Configuration
    • Background Worker Settings
    • View Current Configuration
  • Monitoring
    • Check Background Worker Status
    • Monitor Cleanup Activity
  • Troubleshooting
    • Common Issues
• Check if extension files are installed
• Reinstall if missing
  • • Debug Mode
  • Support

PostgreSQL TTL Index Extension - User Guide

Table of Contents

1. Overview
2. Prerequisites
3. Installation
4. Quick Start
5. Usage Examples
6. Background Worker Management
7. Configuration
8. Monitoring
9. Troubleshooting
10. Support

Overview

The pg_ttl_index extension provides automatic Time-To-Live (TTL) functionality for PostgreSQL tables. It automatically deletes expired data based on timestamp columns, helping you maintain clean databases without manual intervention.

Key Features

• ✅ Automatic data expiration - Set it and forget it
• ✅ Background worker - Runs cleanup at configurable intervals
• ✅ Batch deletion - Handles millions of rows efficiently (v2.0+)
• ✅ Auto-indexing - Creates index on timestamp column automatically (v2.0+)
• ✅ Stats tracking - Monitor rows deleted per table (v2.0+)
• ✅ Concurrency control - Advisory locks prevent overlapping runs (v2.0+)
• ✅ Multiple tables support - Different expiry times per table
• ✅ Production ready - ACID compliant with SQL injection protection

Prerequisites

Before installing the extension, ensure you have:

• Development tools (make, gcc, postgresql-server-dev)
• Superuser privileges for installation
• Database restart capability (for shared_preload_libraries)

Installation

Option 1: Install via PGXN (Recommended)

# Install using PGXN (PostgreSQL Extension Network)
pgxn install pg_ttl_index

Note: PGXN installation requires the pgxn client tool. Install it with: ```bash

Ubuntu/Debian

sudo apt-get install pgxnclient

macOS

brew install pgxnclient

Or install via pip

pip install pgxnclient ```

Step 2: Configure PostgreSQL

Add the extension to your PostgreSQL configuration:

# Edit postgresql.conf
sudo nano /etc/postgresql/12/main/postgresql.conf

Add or modify this line: shared_preload_libraries = 'pg_ttl_index'

Note: If you already have other extensions in shared_preload_libraries, separate them with commas: shared_preload_libraries = 'pg_stat_statements,pg_ttl_index'

Step 3: Restart PostgreSQL

# Ubuntu/Debian
sudo systemctl restart postgresql

# macOS (Homebrew)
brew services restart postgresql

Step 4: Create the Extension

Connect to your database and create the extension:

-- Connect to your database
\c your_database_name

-- Create the extension
CREATE EXTENSION pg_ttl_index;

-- Start the background worker (required for automatic cleanup)
SELECT ttl_start_worker();

-- Verify installation
\dx pg_ttl_index

Quick Start

1. Start the Background Worker

-- Start the background worker for automatic cleanup
SELECT ttl_start_worker();

2. Create a Sample Table

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

-- Insert some test data
INSERT INTO user_sessions (user_id, session_data) VALUES
(1, '{"login_time": "2024-01-01 10:00:00"}'),
(2, '{"login_time": "2024-01-01 11:00:00"}'),
(3, '{"login_time": "2024-01-01 12:00:00"}');

3. Set Up TTL Index

-- Data expires after 1 hour (3600 seconds)
-- Optional: specify batch_size for high-load tables (default: 10000)
SELECT ttl_create_index('user_sessions', 'created_at', 3600);

-- Or with custom batch size for high-volume tables
SELECT ttl_create_index('user_sessions', 'created_at', 3600, 5000);

4. Verify TTL Index

-- Check active TTL indexes with stats
SELECT * FROM ttl_summary();

5. Test the Cleanup

-- Manually trigger cleanup (optional - runs automatically via background worker)
SELECT ttl_runner();

Usage Examples

Example 1: Session Management

-- Create sessions table
CREATE TABLE sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Sessions expire after 24 hours (batch size 5000 for high load)
SELECT ttl_create_index('sessions', 'created_at', 86400, 5000);

Example 2: Log Cleanup

-- Create application logs table
CREATE TABLE app_logs (
    id SERIAL PRIMARY KEY,
    level VARCHAR(10) NOT NULL,
    message TEXT NOT NULL,
    metadata JSONB,
    logged_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Logs expire after 7 days
SELECT ttl_create_index('app_logs', 'logged_at', 604800);

Example 3: Cache Management

-- Create cache table
CREATE TABLE cache_entries (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value TEXT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL
);

-- Cache expires based on expires_at column
SELECT ttl_create_index('cache_entries', 'expires_at', 0);

Managing TTL Indexes

-- List all TTL indexes with stats
SELECT * FROM ttl_summary();

-- Returns:
-- table_name, column_name, expire_after_seconds, batch_size,
-- active, last_run, time_since_last_run, rows_deleted_last_run,
-- total_rows_deleted, index_name

-- Update expiry time
SELECT ttl_create_index('user_sessions', 'created_at', 7200); -- Change to 2 hours

-- Disable TTL index (temporarily)
UPDATE ttl_index_table
SET active = false
WHERE table_name = 'user_sessions' AND column_name = 'created_at';

-- Re-enable TTL index
UPDATE ttl_index_table
SET active = true
WHERE table_name = 'user_sessions' AND column_name = 'created_at';

-- Remove TTL index completely (also drops the auto-created index)
SELECT ttl_drop_index('user_sessions', 'created_at');

Background Worker Management

Starting the Background Worker

The background worker is not started automatically. You must start it manually:

-- Start the background worker
SELECT ttl_start_worker();

-- Check if worker is running
SELECT * FROM ttl_worker_status();

Important Notes

• Manual Start Required: The worker does not start automatically after extension installation
• Per-Database: You need to start the worker in each database where you want TTL functionality
• Restart Required: If PostgreSQL restarts, you’ll need to start the worker again
• Single Worker: Only one TTL worker runs per database

Configuration

Background Worker Settings

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

-- Re-enable background worker
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

View Current Configuration

-- Check current settings
SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE 'pg_ttl_index%';

Monitoring

Check Background Worker Status

-- View background worker status
SELECT * FROM ttl_worker_status();

Monitor Cleanup Activity

-- Check deletion stats and last cleanup times
SELECT
    table_name,
    rows_deleted_last_run,
    total_rows_deleted,
    last_run,
    time_since_last_run
FROM ttl_summary();

Troubleshooting

Common Issues

1. Extension Not Loading

Error: ERROR: extension "pg_ttl_index" is not available

Solution: ```bash

Check if extension files are installed

ls -la /usr/share/postgresql/12/extension/pg_ttl_index*

Reinstall if missing

pgxn install pg_ttl_index

#### 2. Background Worker Not Starting

**Error:** No TTL worker processes visible

**Solution:**

– Check if extension is created \dx pg_ttl_index

– Check shared_preload_libraries setting SHOW shared_preload_libraries;

– Restart PostgreSQL after adding to shared_preload_libraries ```

3. Permission Denied

Error: ERROR: permission denied for table ttl_index_table

Solution: sql -- Grant necessary permissions GRANT USAGE ON SCHEMA public TO your_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ttl_index_table TO your_user;

4. Invalid Column Type

Error: Column must be a date/timestamp type

Solution: ```sql – Check column data type SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ‘your_table’;

– Supported types: timestamp, timestamptz, date ```

Debug Mode

-- Enable debug logging
ALTER SYSTEM SET log_min_messages = debug1;
SELECT pg_reload_conf();

-- Check logs for detailed TTL activity

Support

• GitHub Repository: https://github.com/ibrahimkarimeddin/postgres-extensions-pg_ttl
• Issues: https://github.com/ibrahimkarimeddin/postgres-extensions-pg_ttl/issues
• Documentation: This user guide