Quick Start Guide

Get your first TTL index running in under 5 minutes! This guide walks you through a complete example from start to finish.

Prerequisites

Before starting, ensure you have: - ✅ Installed the pg_ttl_index extension (Installation Guide) - ✅ Added pg_ttl_index to shared_preload_libraries - ✅ Restarted PostgreSQL - ✅ Created the extension in your database

Step 1: Start the Background Worker

The background worker is not started automatically. Start it first:

-- Start the TTL background worker
SELECT ttl_start_worker();

Output: ```

ttl_start_worker

t ```

:::tip Verify Worker is Running Check the worker status anytime: sql SELECT * FROM ttl_worker_status(); :::

Step 2: Create a Sample Table

Let’s create a simple user sessions table:

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

-- Add an index on user_id for better query performance
CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);

Step 3: Insert Sample Data

Add some test data with different timestamps:

-- Insert current sessions
INSERT INTO user_sessions (user_id, session_token, session_data)
VALUES
    (1, 'token_abc123', '{"ip": "192.168.1.1"}'),
    (2, 'token_def456', '{"ip": "192.168.1.2"}'),
    (3, 'token_ghi789', '{"ip": "192.168.1.3"}');

-- Insert old sessions (2 hours ago) for testing
INSERT INTO user_sessions (user_id, session_token, session_data, created_at)
VALUES
    (4, 'token_old001', '{"ip": "192.168.1.4"}', NOW() - INTERVAL '2 hours'),
    (5, 'token_old002', '{"ip": "192.168.1.5"}', NOW() - INTERVAL '2 hours');

-- Check what we have
SELECT id, user_id, created_at, 
       NOW() - created_at AS age
FROM user_sessions
ORDER BY created_at;

Output: id | user_id | created_at | age ----+---------+-------------------------------+----------------- 4 | 4 | 2026-01-03 00:42:00+00 | 02:00:00.123 5 | 5 | 2026-01-03 00:42:00+00 | 02:00:00.123 1 | 1 | 2026-01-03 02:42:00+00 | 00:00:00.123 2 | 2 | 2026-01-03 02:42:00+00 | 00:00:00.123 3 | 3 | 2026-01-03 02:42:00+00 | 00:00:00.123

Step 4: Set Up TTL Index

Configure the table to automatically delete sessions older than 1 hour (3600 seconds):

-- Create TTL index: expire after 1 hour
SELECT ttl_create_index(
    'user_sessions',     -- table name
    'created_at',        -- timestamp column
    3600                 -- expire after seconds (1 hour)
);

Output: ```

ttl_create_index

t ```

:::info What Just Happened? The function: 1. ✅ Created an index idx_ttl_user_sessions_created_at for fast cleanup 2. ✅ Registered the TTL rule in ttl_index_table 3. ✅ Activated automatic cleanup for this table :::

With Custom Batch Size

For high-volume tables, you can specify a custom batch size:

-- Larger batch size for tables with millions of rows
SELECT ttl_create_index(
    'user_sessions',
    'created_at',
    3600,
    50000               -- batch size (default is 10000)
);

Step 5: Verify TTL Configuration

Check your TTL configuration:

-- View all TTL indexes
SELECT * FROM ttl_summary();

Output: 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 ---------------+-------------+----------------------+------------+--------+----------+---------------------+-----------------------+--------------------+------------------------------- user_sessions | created_at | 3600 | 10000 | t | | | 0 | 0 | idx_ttl_user_sessions_created_at

Step 6: Test Manual Cleanup

Manually trigger cleanup to see it in action:

-- Manually run cleanup (normally happens every 60 seconds)
SELECT ttl_runner();

Output: ```

ttl_runner

      2

```

This means 2 rows were deleted (our old sessions from 2 hours ago).

Verify Deletion

-- Check remaining sessions
SELECT id, user_id, created_at,
       NOW() - created_at AS age
FROM user_sessions
ORDER BY created_at;

Output: id | user_id | created_at | age ----+---------+-------------------------------+----------------- 1 | 1 | 2026-01-03 02:42:00+00 | 00:00:00.456 2 | 2 | 2026-01-03 02:42:00+00 | 00:00:00.456 3 | 3 | 2026-01-03 02:42:00+00 | 00:00:00.456

The old sessions are gone! ✨

Step 7: Monitor Background Worker

The background worker runs automatically every 60 seconds. Monitor its activity:

-- Check deletion statistics
SELECT 
    table_name,
    expire_after_seconds,
    last_run,
    time_since_last_run,
    rows_deleted_last_run,
    total_rows_deleted
FROM ttl_summary();

Output: table_name | expire_after_seconds | last_run | time_since_last_run | rows_deleted_last_run | total_rows_deleted ---------------+----------------------+----------------------------+---------------------+-----------------------+-------------------- user_sessions | 3600 | 2026-01-03 02:42:05.123+00 | 00:00:30 | 2 | 2

Complete Example Script

Here’s the complete script you can copy and run:

-- 1. Start background worker
SELECT ttl_start_worker();

-- 2. Create table
CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    session_token TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Insert test data
INSERT INTO user_sessions (user_id, session_token, created_at)
VALUES
    (1, 'active_token', NOW()),
    (2, 'old_token', NOW() - INTERVAL '2 hours');

-- 4. Set up TTL (1 hour expiration)
SELECT ttl_create_index('user_sessions', 'created_at', 3600);

-- 5. Manually trigger cleanup
SELECT ttl_runner();

-- 6. Verify results
SELECT * FROM user_sessions;  -- Should only show recent session
SELECT * FROM ttl_summary();   -- Should show 1 row deleted

Real-World Examples

Session Management (30-minute expiry)

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

SELECT ttl_create_index('web_sessions', 'created_at', 1800); -- 30 minutes

Application Logs (7-day retention)

CREATE TABLE app_logs (
    id BIGSERIAL PRIMARY KEY,
    level VARCHAR(10),
    message TEXT,
    metadata JSONB,
    logged_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

Cache Entries (1-hour expiry)

CREATE TABLE cache_entries (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

Common Tasks

Update Expiry Time

-- Change from 1 hour to 2 hours
SELECT ttl_create_index('user_sessions', 'created_at', 7200);

Temporarily Disable TTL

-- Disable without removing
UPDATE ttl_index_table 
SET active = false 
WHERE table_name = 'user_sessions';

-- Re-enable
UPDATE ttl_index_table 
SET active = true 
WHERE table_name = 'user_sessions';

Remove TTL Completely

-- Removes TTL rule and drops the auto-created index
SELECT ttl_drop_index('user_sessions', 'created_at');

Check Worker Status

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

-- Check all TTL configurations
SELECT * FROM ttl_summary();

What’s Next?

Now that you have a working TTL setup:

Need Help?