Skip to main content

Frequently Asked Questions

Common questions about pg_ttl_index.

General Questions​

What is pg_ttl_index?​

A PostgreSQL extension that automatically cleans expired data based on timestamp columns, using either hard delete or optional soft delete mode.

How is this different from just running DELETE queries?​

  • Automatic: No manual queries or cron jobs needed
  • Optimized: Batch deletions with advisory locks
  • Safe: Per-table error handling, ACID compliant
  • Monitored: Built-in statistics tracking

Which PostgreSQL versions are supported?​

PostgreSQL 12.0 and higher.

Is it production-ready?​

Yes! Version 3.0.0 includes:

  • Batch deletion for high-load scenarios
  • Concurrency control via advisory locks
  • Per-table error handling
  • Comprehensive stats tracking
  • Optional soft delete mode via soft_delete_column

Installation & Setup​

Do I need to restart PostgreSQL?​

Yes, when:

  • First installing (to load shared library)
  • Updating to new version (shared library changes)

No, when:

  • Changing configuration parameters (just reload)
  • Creating/dropping TTL indexes

Does the background worker start automatically?​

No. You must manually start it after installing:

SELECT ttl_start_worker();

Do I need superuser privileges?​

  • Installation: Yes (CREATE EXTENSION requires superuser)
  • Daily usage: No (grant permissions to regular users)

Can I use it on a read replica?​

No. TTL requires write access to delete rows. Only run on primary.

Configuration​

How often does cleanup run?​

Default: Every 60 seconds (pg_ttl_index.naptime)

Configurable:

ALTER SYSTEM SET pg_ttl_index.naptime = 30;  -- 30 seconds

What's an appropriate batch size?​

Rule of thumb: 2-3x your expected deletions per cleanup run

Expected Deletions/RunBatch Size
< 1,0001,000 - 5,000
1,000 - 10,00010,000 (default)
10,000 - 50,00025,000 - 50,000
> 50,00050,000 - 100,000

Can I have different TTLs for different tables?​

Yes! Each table can have its own expiration time:

SELECT ttl_create_index('sessions', 'created_at', 1800);    -- 30 min
SELECT ttl_create_index('logs', 'logged_at', 604800);       -- 7 days

Can I have multiple TTL columns per table?​

Yes, but it's not common:

SELECT ttl_create_index('table', 'created_at', 3600);
SELECT ttl_create_index('table', 'expires_at', 0);
-- Both will be checked and enforced

Performance​

Will TTL impact my database performance?​

Minimal impact when properly configured:

  • Runs in small batches (reduces locking)
  • Sleeps between batches (yields to other queries)
  • Uses efficient ctid-based deletion

Monitor and tune if needed:

  • Adjust batch size
  • Change cleanup frequency (naptime)
  • Schedule during off-peak hours

How do I know if cleanup is keeping up?​

SELECT 
    table_name,
    rows_deleted_last_run,
    batch_size,
    CASE 
        WHEN rows_deleted_last_run >= batch_size 
        THEN 'Consider increasing batch_size'
        ELSE 'Keeping up'
    END AS status
FROM ttl_summary();

What if I have millions of expired rows?​

Increase batch size:

SELECT ttl_create_index('huge_table', 'timestamp', 3600, 100000);

Or temporarily disable automatic cleanup and run manual cleanup:

ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

-- Manual cleanup during maintenance window
SELECT ttl_runner();

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

Troubleshooting​

Why isn't data being deleted?​

Check these in order:

  1. Worker running?
SELECT * FROM ttl_worker_status();
  1. TTL active?
SELECT active FROM ttl_index_table WHERE table_name = 'your_table';
  1. Data actually expired?
SELECT COUNT(*) FROM your_table 
WHERE created_at < NOW() - INTERVAL '1 hour';
  1. Manually trigger cleanup:
SELECT ttl_runner();

Worker stopped after PostgreSQL restart - why?​

This is expected behavior. Restart it manually:

SELECT ttl_start_worker();

Workaround: Add to database startup script or use cron:

@reboot psql -d your_db -c "SELECT ttl_start_worker();"

How do I check if TTL is working?​

-- Check cleanup activity
SELECT * FROM ttl_summary();

-- Should show last_run, rows_deleted stats

Data Management​

Can I disable TTL temporarily?​

Yes, multiple ways:

Per table:

UPDATE ttl_index_table SET active = false WHERE table_name = 'my_table';

Globally:

ALTER SYSTEM SET pg_ttl_index.enabled = false;
SELECT pg_reload_conf();

Stop worker:

SELECT ttl_stop_worker();

What happens to data that's about to expire if I disable TTL?​

Nothing - it stays in the table. Data is only deleted when:

  1. Worker is running
  2. TTL is active
  3. Table's TTL is active

Can I preview what will be deleted?​

Yes:

-- See what would be deleted
SELECT COUNT(*), MIN(created_at), MAX(created_at)
FROM your_table
WHERE created_at < NOW() - INTERVAL '1 hour';

Does TTL work with table partitions?​

Yes! Apply TTL to the parent table:

-- Parent table
CREATE TABLE logs (...) PARTITION BY RANGE (created_at);

-- TTL on parent handles all partitions
SELECT ttl_create_index('logs', 'created_at', 604800);

Advanced Usage​

Can I archive data before TTL deletes it?​

Yes, set up archival before expiration:

-- TTL deletes after 7 days
SELECT ttl_create_index('logs', 'created_at', 604800);

-- Archive data > 6 days old (before TTL kicks in)
-- Run this as a cron job or scheduled task
INSERT INTO logs_archive
SELECT * FROM logs
WHERE created_at < NOW() - INTERVAL '6 days';

Can I use TTL with unlogged tables?​

Yes, but be careful - unlogged tables lose data on crash:

CREATE UNLOGGED TABLE temp_cache (...);
SELECT ttl_create_index('temp_cache', 'created_at', 300);

How do I monitor TTL in production?​

  1. Worker health:
SELECT COUNT(*) FROM ttl_worker_status();
  1. Cleanup effectiveness:
SELECT * FROM ttl_summary();
  1. Integration with monitoring tools (Datadog, Prometheus):
CREATE VIEW ttl_metrics AS
SELECT table_name, total_rows_deleted, rows_deleted_last_run
FROM ttl_summary();

Comparison with Alternatives​

vs. PostgreSQL table partitioning with DROP?​

pg_ttl_index:

  • ✅ Row-level granularity
  • ✅ Change TTL anytime
  • ❌ Slower for very large deletions

Partitioning:

  • ✅ Very fast (DROP partition)
  • ❌ Partition-level granularity
  • ❌ Schema changes required

Use both for best results: partition + TTL on each partition.

vs. cron job with DELETE?​

pg_ttl_index:

  • ✅ Native PostgreSQL integration
  • ✅ Built-in monitoring
  • ✅ Automatic batch sizing

Cron:

  • ❌ External dependency
  • ❌ Manual coordination
  • ❌ Custom monitoring needed

vs. application-level cleanup?​

pg_ttl_index:

  • ✅ Centralized in database
  • ✅ Works across all apps
  • ✅ Guaranteed execution

Application:

  • ❌ Scattered across codebase
  • ❌ Per-application overhead
  • ❌ May not run if app crashes

Getting Help​

Where can I report bugs?​

GitHub Issues

Where's the source code?​

GitHub Repository

How do I contribute?​

See our Contributing Guide!

Can I get commercial support?​

Open source project - community support via GitHub Issues and Discussions.

Still Have Questions?​

Check our comprehensive documentation: