Skip to main content

Configuration Parameters

PostgreSQL configuration parameters (GUCs) for pg_ttl_index.

Parameters Overview​

ParameterTypeDefaultRestart RequiredDescription
pg_ttl_index.naptimeinteger60NoCleanup interval in seconds
pg_ttl_index.enabledbooleantrueNoEnable/disable background worker

pg_ttl_index.naptime​

Controls how often the background worker runs cleanup operations.

Details​

  • Type: Integer
  • Unit: Seconds
  • Default: 60
  • Min: 1
  • Max: 3600 (1 hour)
  • Context: SIGHUP (reload configuration)

Usage​

-- View current setting
SHOW pg_ttl_index.naptime;

-- Change to 30 seconds
ALTER SYSTEM SET pg_ttl_index.naptime = 30;
SELECT pg_reload_conf();

-- Or in postgresql.conf
-- pg_ttl_index.naptime = 30

Recommendations​

Use CaseRecommended ValueReason
High-volume cleanup30More frequent cleanup prevents backlog
Normal workload60Default, balances frequency and overhead
Low-volume tables120-300Less frequent checks reduce overhead
Development/testing10-20Faster feedback during testing

Performance Impact​

  • Lower values = More frequent cleanup = Higher CPU usage
  • Higher values = Less frequent cleanup = Potential data buildup

pg_ttl_index.enabled​

Globally enables or disables the background worker.

Details​

  • Type: Boolean
  • Default: true
  • Context: SIGHUP (reload configuration)

Usage​

-- View current setting
SHOW pg_ttl_index.enabled;

-- Disable background worker
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- Re-enable
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

Use Cases​

Disable during maintenance:

-- Before heavy operations
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- Perform maintenance
-- ...

-- Re-enable
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

Temporarily stop all cleanup:

-- Alternative to stopping worker
ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

shared_preload_libraries​

Required Configuration

pg_ttl_index must be in shared_preload_libraries to function.

Configuration​

Edit postgresql.conf:

shared_preload_libraries = 'pg_ttl_index'

Multiple extensions:

shared_preload_libraries = 'pg_stat_statements,pg_ttl_index'

Verification​

-- Check if extension is preloaded
SHOW shared_preload_libraries;

-- Should include: 'pg_ttl_index'

Note: Requires PostgreSQL restart to take effect.

Viewing All Settings​

Show All pg_ttl_index Parameters​

SELECT 
    name,
    setting,
    unit,
    context,
    short_desc
FROM pg_settings
WHERE name LIKE 'pg_ttl_index%';

Example Output:

        name           | setting | unit  | context |           short_desc
-----------------------+---------+-------+---------+----------------------------------
 pg_ttl_index.enabled  | on      |       | sighup  | Enable TTL background worker
 pg_ttl_index.naptime  | 60      | s     | sighup  | Cleanup interval in seconds

Configuration File Management​

Using ALTER SYSTEM​

-- Change settings (preferred method)
ALTER SYSTEM SET pg_ttl_index.naptime = 45;
ALTER SYSTEM SET pg_ttl_index.enabled = true;

-- Reload configuration
SELECT pg_reload_conf();

-- Verify changes
SHOW pg_ttl_index.naptime;

This modifies postgresql.auto.conf automatically.

Using postgresql.conf​

Edit the main configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Add or modify:

# TTL Extension Configuration
shared_preload_libraries = 'pg_ttl_index'
pg_ttl_index.naptime = 60
pg_ttl_index.enabled = true

Reload:

sudo systemctl reload postgresql
# or
SELECT pg_reload_conf();

Reload vs Restart​

ChangeRequires Restart?Command
shared_preload_librariesYessystemctl restart postgresql
pg_ttl_index.naptimeNoSELECT pg_reload_conf();
pg_ttl_index.enabledNoSELECT pg_reload_conf();

Configuration Best Practices​

Production Settings​

# Production recommended settings
pg_ttl_index.naptime = 60        # Standard cleanup interval
pg_ttl_index.enabled = true      # Always enabled

Development Settings​

# Development/testing settings
pg_ttl_index.naptime = 10        # Faster feedback during testing
pg_ttl_index.enabled = true

High-Load Systems​

# High-volume deletion workloads
pg_ttl_index.naptime = 30        # More frequent cleanup
pg_ttl_index.enabled = true

Low-Priority Cleanup​

# When cleanup is low priority
pg_ttl_index.naptime = 300       # 5-minute interval
pg_ttl_index.enabled = true

Troubleshooting Configuration​

Settings Not Taking Effect​

Problem: Changed naptime but worker still uses old value.

Solution:

-- Reload configuration
SELECT pg_reload_conf();

-- Verify new value
SHOW pg_ttl_index.naptime;

-- Restart worker to pick up changes
SELECT ttl_stop_worker();
SELECT ttl_start_worker();

Worker Not Starting​

Problem: Worker doesn't start even with correct configuration.

Check:

-- 1. Verify extension is preloaded
SHOW shared_preload_libraries;

-- 2. Check if enabled
SHOW pg_ttl_index.enabled;

-- 3. Look for errors in PostgreSQL logs
-- tail -f /var/log/postgresql/postgresql-*.log

-- 4. Try manual start
SELECT ttl_start_worker();

Permission Denied on ALTER SYSTEM​

Problem: ERROR: permission denied to set parameter

Solution:

-- Must be superuser or have pg_write_server_files role
\du

-- Or edit postgresql.conf directly (requires OS access)

Monitoring Configuration Changes​

Track Configuration History​

-- View current configuration
SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name LIKE 'pg_ttl_index%';

Log Configuration on Startup​

Add to postgresql.conf:

log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_connections = on
log_disconnections = on

Then check logs for extension loading messages.