Skip to main content

Introduction to pg_ttl_index

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

What is TTL?​

Time-To-Live (TTL) is a mechanism for automatically expiring and removing data after a specified time period. This is essential for:

  • Session management - Auto-expire user sessions after inactivity
  • Log cleanup - Remove old application logs automatically
  • Cache management - Clear stale cache entries
  • Audit trail retention - Maintain compliance by deleting old audit records
  • Temporary data - Clean up temporary records that are no longer needed

Key Features​

✅ Automatic Data Expiration​

Set up TTL rules once, and the extension handles the rest. No cron jobs, no manual cleanup scripts.

✅ Background Worker​

Runs cleanup at configurable intervals (default: 60 seconds). Automatically processes all tables with active TTL indexes.

✅ High-Performance Batch Deletion (v2.0+)​

  • Configurable batch sizes (default: 10,000 rows)
  • Efficiently handles millions of rows
  • Uses ctid for optimal delete performance
  • Yields to other processes between batches

✅ Auto-Indexing (v2.0+)​

Automatically creates indexes on timestamp columns for fast cleanup operations.

✅ Stats Tracking (v2.0+)​

  • Monitor rows deleted per table
  • Track last cleanup time
  • View total deletion statistics
  • Built-in summary views

✅ Concurrency Control (v2.0+)​

Advisory locks prevent overlapping cleanup runs, ensuring safe operation in clustered environments.

✅ Multiple Tables Support​

Different expiry times per table - each table can have its own TTL configuration.

✅ Production Ready​

  • ACID compliant transactions
  • Per-table error handling (errors in one table don't affect others)
  • SQL injection protection
  • Comprehensive monitoring functions

Quick Example​

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

-- Start the background worker
SELECT ttl_start_worker();

-- Set up TTL: data expires after 1 hour (3600 seconds)
SELECT ttl_create_index('user_sessions', 'created_at', 3600);

-- That's it! The background worker will automatically delete 
-- rows older than 1 hour every 60 seconds.

Version History​

Version 2.0.0 (Current)​

  • Batch deletion with configurable batch size
  • Auto-indexing of timestamp columns
  • Stats tracking (rows deleted per table)
  • Advisory lock concurrency control
  • Improved per-table error handling

Version 1.0.x (Deprecated)​

  • Basic TTL functionality
  • Simple background worker
  • Limited to basic use cases

Use Cases​

Session Management​

Automatically clean up expired user sessions to prevent database bloat:

SELECT ttl_create_index('sessions', 'last_activity', 1800); -- 30 minutes

Application Logs​

Keep logs for analysis but automatically remove old entries:

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

Cache Tables​

Implement time-based cache expiration:

SELECT ttl_create_index('cache_entries', 'expires_at', 0); -- Immediate

Temporary Data​

Clean up temporary processing tables:

SELECT ttl_create_index('temp_imports', 'created_at', 3600); -- 1 hour

Architecture Overview​

┌─────────────────────────────────────────────┐
│         PostgreSQL Database                 │
│                                             │
│  ┌────────────────────────────────────┐    │
│  │  Background Worker (per database)  │    │
│  │  - Runs every 60 seconds          │    │
│  │  - Processes all active TTL rules │    │
│  └────────────────────────────────────┘    │
│                    │                        │
│                    ▼                        │
│  ┌────────────────────────────────────┐    │
│  │    ttl_index_table                │    │
│  │  - Stores TTL configuration       │    │
│  │  - Tracks deletion statistics     │    │
│  └────────────────────────────────────┘    │
│                    │                        │
│                    ▼                        │
│  ┌────────────────────────────────────┐    │
│  │  Your Tables (with TTL enabled)   │    │
│  │  - user_sessions                  │    │
│  │  - app_logs                       │    │
│  │  - cache_entries                  │    │
│  └────────────────────────────────────┘    │
└─────────────────────────────────────────────┘

Requirements​

  • PostgreSQL: Version 12.0 or higher
  • Privileges: Superuser access for installation
  • Restart capability: Required to load the extension

Next Steps​

Ready to get started? Follow our guides:

  1. Installation Guide - Install via PGXN or from source
  2. Quick Start - Get your first TTL index running
  3. Configuration - Fine-tune for your workload
  4. API Reference - Complete function documentation

Support​