Download Browse

pg_ttl_index

This Release
pg_ttl_index 1.0.1
Date
Status
Stable
Other Releases
Abstract
Automatic Time-To-Live (TTL) data expiration for PostgreSQL tables
Description
A high-performance PostgreSQL extension that provides automatic Time-To-Live (TTL) functionality for data expiration. Features include background worker for automatic cleanup, multi-table support with different expiry times, configurable cleanup intervals, and production-ready implementation with ACID compliance.
Released By
ibrahimKd
License
PostgreSQL
Resources
Special Files
Tags

Extensions

pg_ttl_index 1.0.1
TTL extension functions and background worker

README

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. Best Practices
  11. 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 every minute (manual start required)
  • ✅ Multiple tables support - Different expiry times per table
  • ✅ Production ready - ACID compliant with SQL injection protection
  • ✅ Configurable - Adjustable cleanup intervals
  • ✅ Zero downtime - No impact on your application performance
  • ✅ Manual control - Start/stop worker as needed

Prerequisites

Before installing the extension, ensure you have:

  • PostgreSQL 12.0+ (tested on PostgreSQL 12-16)
  • 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:

# 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)
SELECT ttl_create_index('user_sessions', 'created_at', 3600);

4. Verify TTL Index

-- Check active TTL indexes
SELECT * FROM ttl_index_table;

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
SELECT ttl_create_index('sessions', 'created_at', 86400);

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

Example 4: Multiple TTL Indexes

-- Create a comprehensive logging table
CREATE TABLE system_events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    severity VARCHAR(10) NOT NULL,
    message TEXT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at TIMESTAMPTZ
);

-- Different retention policies
SELECT ttl_create_index('system_events', 'created_at', 2592000);  -- 30 days for all events
SELECT ttl_create_index('system_events', 'processed_at', 604800); -- 7 days for processed events

Managing TTL Indexes

-- List all TTL indexes
SELECT 
    table_name,
    column_name,
    expire_after_seconds,
    active,
    created_at,
    last_run
FROM ttl_index_table
ORDER BY created_at DESC;

-- 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
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 
    pid,
    usename,
    application_name,
    state,
    query_start
FROM pg_stat_activity 
WHERE application_name LIKE '%TTL%';

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 processes
SELECT 
    pid,
    usename,
    application_name,
    state,
    query_start,
    query
FROM pg_stat_activity 
WHERE application_name LIKE '%TTL%';

Monitor Cleanup Activity

-- Check last cleanup times
SELECT 
    table_name,
    column_name,
    last_run,
    NOW() - last_run AS time_since_last_run
FROM ttl_index_table 
WHERE active = true
ORDER BY last_run DESC;

View PostgreSQL Logs

# Check PostgreSQL logs for TTL activity
sudo tail -f /var/log/postgresql/postgresql-12-main.log | grep TTL

Troubleshooting

Common Issues

1. Extension Not Loading

Error: ERROR: extension "pg_ttl_index" is not available

Solution:

# Check if extension files are installed
ls -la /usr/share/postgresql/12/extension/pg_ttl_index*

# Reinstall if missing
sudo make install

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:

-- 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:

-- 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

Best Practices

1. Choose Appropriate Expiry Times

-- Good examples:
SELECT ttl_create_index('sessions', 'created_at', 3600);     -- 1 hour
SELECT ttl_create_index('logs', 'created_at', 2592000);     -- 30 days
SELECT ttl_create_index('cache', 'expires_at', 0);          -- Immediate expiry

-- Avoid very short intervals (less than 60 seconds)
-- The background worker runs every minute by default

2. Use Appropriate Column Types

-- Recommended column types:
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()  -- Best choice
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()  -- Good for updates
expires_at TIMESTAMPTZ NOT NULL                -- For explicit expiry
created_date DATE NOT NULL DEFAULT CURRENT_DATE -- For daily cleanup

3. Monitor Performance

-- Check table sizes before/after cleanup
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE tablename IN ('user_sessions', 'app_logs', 'cache_entries');

4. Backup Before Major Changes

-- Always backup before modifying TTL settings
pg_dump your_database > backup_before_ttl_changes.sql

5. Test in Development First

-- Test TTL functionality in development
SELECT ttl_create_index('test_table', 'created_at', 60); -- 1 minute for testing
-- Insert test data and verify cleanup

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