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:

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

With custom batch size:

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

Immediate expiration (cache use case):

-- Expire based on expires_at column
SELECT ttl_create_index('public.cache_entries', 'expires_at', 0);

Update existing TTL:

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

Soft delete mode:

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

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

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:

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:

SELECT * FROM ttl_summary() WHERE active = true;

Recent activity:

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

• Configuration Parameters - GUC settings
• Tables Reference - ttl_index_table schema
• Monitoring Guide - Detailed monitoring strategies