Skip to main content

Troubleshooting Guide

Solutions to common problems and error messages.

Extension Installation Issues​

Extension Files Not Found​

Error: ERROR: could not open extension control file

Cause: Extension files not properly installed

Solution:

# Verify installation
ls -la $(pg_config --sharedir)/extension/pg_ttl_index*

# Reinstall if missing
sudo make install

# Or via PGXN
pgxn install pg_ttl_index

Shared Library Not Loading​

Error: ERROR: could not load library

Cause: Extension not in shared_preload_libraries

Solution:

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

# Add this line:
shared_preload_libraries = 'pg_ttl_index'

# Restart PostgreSQL (required!)
sudo systemctl restart postgresql

Verification:

SHOW shared_preload_libraries;
-- Should include 'pg_ttl_index'

Background Worker Issues​

Worker Not Starting​

Error: ttl_worker_status() returns no rows

Solution 1: Start the worker manually

SELECT ttl_start_worker();

Solution 2: Check if enabled

SHOW pg_ttl_index.enabled;

-- If false, enable it
ALTER SYSTEM SET pg_ttl_index.enabled = true;
SELECT pg_reload_conf();

Solution 3: Verify extension loaded

\dx pg_ttl_index
-- Should show version 2.0.0

Worker Crashes Immediately​

Check logs:

sudo tail -f /var/log/postgresql/postgresql-*.log | grep -i "ttl\|error"

Common causes:

  • Permission issues
  • Database doesn't exist
  • Extension not properly initialized

Worker Stops After PostgreSQL Restart​

This is normal behavior. Worker doesn't auto-start.

Solution: Add to database startup script

-- Run after every PostgreSQL restart
SELECT ttl_start_worker();

Automate with cron (example):

# /etc/cron.d/ttl-worker
@reboot postgres psql -d your_database -c "SELECT ttl_start_worker();"

Cleanup Not Working​

Data Not Being Deleted​

Diagnosis:

-- 1. Check worker status
SELECT * FROM ttl_worker_status();

-- 2. Check TTL configuration
SELECT * FROM ttl_summary();

-- 3. Check if TTL is active
SELECT active FROM ttl_index_table 
WHERE table_name = 'your_table';

Solutions:

If worker not running:

SELECT ttl_start_worker();

If TTL inactive:

UPDATE ttl_index_table 
SET active = true 
WHERE table_name = 'your_table';

If cleanup hasn't run recently:

-- Manually trigger
SELECT ttl_runner();

Wrong Column Being Used​

Error: Cleanup doesn't work as expected

Diagnosis:

-- Check which column is configured
SELECT table_name, column_name, expire_after_seconds
FROM ttl_index_table
WHERE table_name = 'your_table';

Solution: Recreate with correct column

-- Drop old TTL
SELECT ttl_drop_index('your_table', 'wrong_column');

-- Create with correct column
SELECT ttl_create_index('your_table', 'correct_column', 3600);

Cleanup Runs But Doesn't Delete​

Check for expired data:

SELECT COUNT(*)
FROM your_table
WHERE created_at < NOW() - INTERVAL '1 hour';
-- Should match expected deletions

Check timestamp column:

-- Verify column data type
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table'
  AND column_name = 'created_at';
-- Must be timestamp, timestamptz, or date

Permission Errors​

Permission Denied to Create Extension​

Error: ERROR: permission denied to create extension

Solution: Need superuser privileges

-- As superuser
\c your_database postgres

CREATE EXTENSION pg_ttl_index;

-- Or grant superuser temporarily
ALTER USER your_user SUPERUSER;
-- Create extension
ALTER USER your_user NOSUPERUSER;

Permission Denied on ttl_index_table​

Error: ERROR: permission denied for table ttl_index_table

Solution: Grant necessary permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON ttl_index_table TO your_user;
GRANT EXECUTE ON FUNCTION ttl_create_index TO your_user;
GRANT EXECUTE ON FUNCTION ttl_drop_index TO your_user;
GRANT EXECUTE ON FUNCTION ttl_runner TO your_user;

Configuration Issues​

Settings Not Taking Effect​

Diagnosis:

-- Check current settings
SHOW pg_ttl_index.naptime;
SHOW pg_ttl_index.enabled;

-- Check configuration source
SELECT name, setting, source
FROM pg_settings
WHERE name LIKE 'pg_ttl_index%';

Solution: Reload configuration

-- Reload PostgreSQL config
SELECT pg_reload_conf();

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

Can't Set Configuration Parameter​

Error: ERROR: unrecognized configuration parameter

Cause: Extension not in shared_preload_libraries

Solution:

# Edit postgresql.conf
shared_preload_libraries = 'pg_ttl_index'

# Restart PostgreSQL
sudo systemctl restart postgresql

Performance Issues​

Cleanup Takes Too Long​

Diagnosis:

-- Check deletion volumes
SELECT 
    table_name,
    rows_deleted_last_run,
    batch_size
FROM ttl_summary()
ORDER BY rows_deleted_last_run DESC;

Solutions:

Increase batch size:

UPDATE ttl_index_table
SET batch_size = 50000
WHERE table_name = 'high_volume_table';

Verify index exists:

SELECT index_name
FROM ttl_index_table
WHERE table_name = 'your_table';

-- Verify index is actually there
\di+ idx_ttl_your_table_*

Run VACUUM:

VACUUM ANALYZE your_table;

High CPU Usage​

Diagnosis:

-- Check cleanup frequency
SHOW pg_ttl_index.naptime;

-- Check recent activity
SELECT * FROM pg_stat_activity
WHERE application_name LIKE 'TTL Worker%';

Solutions:

Reduce cleanup frequency:

ALTER SYSTEM SET pg_ttl_index.naptime = 300;  -- 5 minutes
SELECT pg_reload_conf();

Smaller batch sizes:

UPDATE ttl_index_table
SET batch_size = 5000
WHERE table_name = 'your_table';

Table Bloat​

Diagnosis:

-- Check table size
SELECT pg_size_pretty(pg_total_relation_size('your_table'));

-- Check bloat (requires pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('your_table');

Solution:

-- More aggressive autovacuum
ALTER TABLE your_table SET (
    autovacuum_vacuum_scale_factor = 0.05
);

-- Manual vacuum (doesn't lock table)
VACUUM ANALYZE your_table;

-- Full vacuum (locks table - use with caution!)
VACUUM FULL your_table;

Error Messages​

"Another instance is already running"​

Message: NOTICE: TTL runner: Another instance is already running, skipping

This is normal: Advisory lock prevents overlapping runs

Action: No action needed unless it happens frequently

If frequent:

  • Increase batch size (cleanup finishes faster)
  • Increase naptime (runs less often)

"Column must be a date/timestamp type"​

Error: During ttl_create_index()

Cause: Specified column is not a timestamp type

Solution:

-- Check column type
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table'
  AND column_name = 'your_column';

-- Must be one of: timestamp, timestamptz, date

Fix: Use correct column or change column type

-- Change column type
ALTER TABLE your_table 
ALTER COLUMN your_column TYPE TIMESTAMPTZ;

-- Then create TTL
SELECT ttl_create_index('your_table', 'your_column', 3600);

"Failed to cleanup table"​

Warning: In PostgreSQL logs

Cause: Error during cleanup (e.g., table dropped, column changed)

Diagnosis:

# Check logs for full error
sudo tail -f /var/log/postgresql/postgresql-*.log | grep -A 5 "Failed to cleanup"

Solution: Fix the underlying issue or drop TTL

-- If table no longer exists
SELECT ttl_drop_index('old_table', 'created_at');

-- If column was renamed
SELECT ttl_drop_index('table', 'old_column');
SELECT ttl_create_index('table', 'new_column', 3600);

Debugging Tips​

Enable Debug Logging​

-- Enable detailed logging
ALTER SYSTEM SET log_min_messages = 'debug1';
ALTER SYSTEM SET log_error_verbosity = 'verbose';
SELECT pg_reload_conf();

-- Watch logs
-- tail -f /var/log/postgresql/postgresql-*.log

Manual Testing​

-- Test cleanup manually
SELECT ttl_runner();

-- Check what would be deleted (doesn't actually delete)
SELECT COUNT(*)
FROM your_table
WHERE created_at < NOW() - INTERVAL '1 hour';

Check Extension Version​

-- Verify extension version
SELECT * FROM pg_available_extensions 
WHERE name = 'pg_ttl_index';

-- Check installed version
\dx pg_ttl_index

Getting Help​

If you're still stuck:

  1. Check logs: /var/log/postgresql/postgresql-*.log
  2. Gather diagnostics:
-- Run this and share output
SELECT * FROM ttl_worker_status();
SELECT * FROM ttl_summary();
SHOW pg_ttl_index.naptime;
SHOW pg_ttl_index.enabled;
\dx pg_ttl_index
  1. GitHub Issues: Report a bug
  2. Discussions: Ask questions

See Also​