Functions Reference

Complete API reference for all pg_ttl_index functions.

TTL Management Functions

ttl_create_index()

Creates or updates a TTL index configuration for automatic data expiration.

Signature

ttl_create_index(
    p_table_name TEXT,
    p_column_name TEXT,
    p_expire_after_seconds INTEGER,
    p_batch_size INTEGER DEFAULT 10000,
    p_soft_delete_column TEXT DEFAULT NULL
) RETURNS BOOLEAN

Parameters

Parameter Type Required Description
p_table_name TEXT Yes Table to apply TTL to (schema-qualified is recommended, e.g. app.sessions)
p_column_name TEXT Yes Name of the timestamp column for expiration
p_expire_after_seconds INTEGER Yes Number of seconds before data expires
p_batch_size INTEGER No Rows to delete per batch (default: 10000)
p_soft_delete_column TEXT No Nullable timestamp/timestamptz column to mark soft deletes (e.g. deleted_at)

Return Value

  • true - TTL index created/updated successfully
  • false - Operation failed (check logs for details)

Behavior

  1. Creates an index on the timestamp column if it doesn’t exist
    • Index name: idx_ttl_{table}_{column}
    • If a suitable index already exists on the column, it is reused
  2. Registers or updates the TTL configuration in ttl_index_table
  3. Activates automatic cleanup for the table
  4. Idempotent - Safe to call multiple times (updates configuration)
  5. Schema-aware - Stores normalized schema_name + table_name internally
  6. Hardened execution - Uses fixed function search_path to prevent search-path hijacking
  7. Soft delete optional - If p_soft_delete_column is provided, expired rows are marked instead of deleted

Examples

Basic usage: sql -- Sessions expire after 1 hour SELECT ttl_create_index('public.user_sessions', 'created_at', 3600);

With custom batch size: sql -- High-volume table with large batch size SELECT ttl_create_index('public.app_logs', 'logged_at', 604800, 50000);

Immediate expiration (cache use case): sql -- Expire based on expires_at column SELECT ttl_create_index('public.cache_entries', 'expires_at', 0);

Update existing TTL: sql -- Change expiry from 1 hour to 2 hours SELECT ttl_create_index('public.user_sessions', 'created_at', 7200);

Soft delete mode: sql -- Mark expired rows instead of deleting them SELECT ttl_create_index('public.user_sessions', 'created_at', 3600, 10000, 'deleted_at');

Error Handling

The function returns false on error and logs warnings: sql WARNING: TTL create_index failed: column "invalid_col" does not exist

ttl_drop_index()

Removes a TTL index configuration and drops the associated index.

Signature

ttl_drop_index(
    p_table_name TEXT,
    p_column_name TEXT
) RETURNS BOOLEAN

Parameters

Parameter Type Required Description
p_table_name TEXT Yes Name of the table
p_column_name TEXT Yes Name of the timestamp column

Return Value

  • true - TTL index removed successfully
  • false - No matching TTL index found

Behavior

  1. Drops the auto-created index if it exists
  2. Removes the configuration from ttl_index_table
  3. Stops automatic cleanup for this table/column

Examples

-- Remove TTL from sessions table
SELECT ttl_drop_index('public.user_sessions', 'created_at');

-- Verify removal
SELECT * FROM ttl_summary();

ttl_runner()

Manually executes TTL cleanup for all active TTL indexes.

Signature

ttl_runner() RETURNS INTEGER

Parameters

None.

Return Value

  • INTEGER - Total number of rows deleted across all tables

Behavior

  1. Acquires advisory lock to prevent concurrent runs
  2. Processes each active TTL index sequentially
  3. Cleans expired rows in batches according to configured batch size
    • hard delete mode: DELETE
    • soft delete mode: UPDATE ... SET <soft_delete_column> = NOW()
  4. Updates statistics (rows_deleted_last_run, total_rows_deleted)
  5. Per-table error handling - errors in one table don’t affect others
  6. Releases advisory lock when complete

Examples

-- Manually trigger cleanup
SELECT ttl_runner();
-- Returns: 1523 (total rows deleted)

-- View deletion details
SELECT * FROM ttl_summary();

Performance Notes

  • Uses ctid for efficient batch deletion
  • Sleeps 10ms between batches to yield to other processes
  • Skips run if another instance is already running (via advisory lock)

Worker Management Functions

ttl_start_worker()

Starts the background worker for automatic TTL cleanup.

Signature

ttl_start_worker() RETURNS BOOLEAN

Parameters

None.

Return Value

  • true - Worker started successfully
  • false - Failed to start worker

Behavior

  • Launches a dedicated background worker for the current database
  • Worker runs ttl_runner() every pg_ttl_index.naptime seconds (default: 60)
  • Must be called manually per database (not automatic)
  • Only one worker runs per database

Examples

-- Start the worker
SELECT ttl_start_worker();

-- Verify it's running
SELECT * FROM ttl_worker_status();

:::warning Important The worker does not persist across PostgreSQL restarts. You must start it again after server restart. :::

ttl_stop_worker()

Stops the background worker for TTL cleanup.

Signature

ttl_stop_worker() RETURNS BOOLEAN

Parameters

None.

Return Value

  • true - Worker stopped successfully
  • false - No worker was running

Behavior

  • Terminates the background worker for the current database
  • Cleanup will no longer run automatically
  • TTL configurations remain in ttl_index_table

Examples

-- Stop the worker
SELECT ttl_stop_worker();

-- Verify it stopped
SELECT * FROM ttl_worker_status();
-- Should return no rows

Monitoring Functions

ttl_worker_status()

Returns the status of TTL background workers.

Signature

ttl_worker_status() RETURNS TABLE(
    worker_pid INTEGER,
    application_name TEXT,
    state TEXT,
    backend_start TIMESTAMPTZ,
    state_change TIMESTAMPTZ,
    query_start TIMESTAMPTZ,
    database_name TEXT
)

Parameters

None.

Return Columns

Column Type Description
worker_pid INTEGER Process ID of the worker
application_name TEXT Always “TTL Worker DB {dbname}”
state TEXT Current state (usually “idle”)
backend_start TIMESTAMPTZ When the worker started
state_change TIMESTAMPTZ Last state change time
query_start TIMESTAMPTZ When current query started
database_name TEXT Database name

Examples

-- Check if worker is running
SELECT 
    worker_pid,
    state,
    backend_start,
    NOW() - backend_start AS uptime
FROM ttl_worker_status();

Output: worker_pid | state | backend_start | uptime ------------+---------+-----------------------------+----------------- 12345 | idle | 2026-01-03 02:00:00+00 | 00:42:15.123

ttl_summary()

Returns a comprehensive summary of all TTL configurations and statistics.

Signature

ttl_summary() RETURNS TABLE(
    schema_name TEXT,
    table_name TEXT,
    column_name TEXT,
    expire_after_seconds INTEGER,
    batch_size INTEGER,
    active BOOLEAN,
    last_run TIMESTAMPTZ,
    time_since_last_run INTERVAL,
    rows_deleted_last_run BIGINT,
    total_rows_deleted BIGINT,
    index_name TEXT,
    soft_delete_column TEXT,
    cleanup_mode TEXT
)

Parameters

None.

Return Columns

Column Type Description
schema_name TEXT Schema containing the TTL-enabled table
table_name TEXT Table with TTL enabled
column_name TEXT Timestamp column used for expiration
expire_after_seconds INTEGER Expiration time in seconds
batch_size INTEGER Rows deleted per batch
active BOOLEAN Whether TTL is active
last_run TIMESTAMPTZ When cleanup last ran
time_since_last_run INTERVAL Time since last cleanup
rows_deleted_last_run BIGINT Rows deleted in last run
total_rows_deleted BIGINT Total rows deleted all-time
index_name TEXT Name of the auto-created index
soft_delete_column TEXT Soft-delete timestamp column if configured, else NULL
cleanup_mode TEXT hard_delete or soft_delete

Examples

Basic monitoring: sql SELECT schema_name, table_name, expire_after_seconds / 3600.0 AS expire_hours, rows_deleted_last_run, total_rows_deleted FROM ttl_summary();

Active tables only: sql SELECT * FROM ttl_summary() WHERE active = true;

Recent activity: sql SELECT schema_name, table_name, last_run, time_since_last_run, rows_deleted_last_run FROM ttl_summary() WHERE last_run > NOW() - INTERVAL '1 hour' ORDER BY last_run DESC;

Function Usage Patterns

Complete Setup Workflow

-- 1. Start worker
SELECT ttl_start_worker();

-- 2. Create TTL indexes
SELECT ttl_create_index('public.sessions', 'created_at', 1800);
SELECT ttl_create_index('public.logs', 'logged_at', 604800);

-- 3. Monitor
SELECT * FROM ttl_summary();
SELECT * FROM ttl_worker_status();

Maintenance Workflow

-- Check worker health
SELECT * FROM ttl_worker_status();

-- Review cleanup statistics
SELECT 
    table_name,
    time_since_last_run,
    rows_deleted_last_run
FROM ttl_summary()
WHERE active = true;

-- Manual cleanup if needed
SELECT ttl_runner();

Disable/Enable Workflow

-- Disable TTL temporarily
UPDATE ttl_index_table 
SET active = false 
WHERE schema_name = 'public' AND table_name = 'sessions';

-- Or stop worker completely
SELECT ttl_stop_worker();

-- Re-enable
UPDATE ttl_index_table 
SET active = true 
WHERE schema_name = 'public' AND table_name = 'sessions';
SELECT ttl_start_worker();

See Also