Frequently Asked Questions

Common questions about pg_ttl_index.

General Questions

What is pg_ttl_index?

A PostgreSQL extension that automatically deletes expired data based on timestamp columns. Think of it as “auto-expire” for your database tables.

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 2.0.0 includes: - Batch deletion for high-load scenarios - Concurrency control via advisory locks - Per-table error handling - Comprehensive stats tracking

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: sql 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/Run Batch Size
< 1,000 1,000 - 5,000
1,000 - 10,000 10,000 (default)
10,000 - 50,000 25,000 - 50,000
> 50,000 50,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? sql SELECT * FROM ttl_worker_status();

  2. TTL active? sql SELECT active FROM ttl_index_table WHERE table_name = 'your_table';

  3. Data actually expired? sql SELECT COUNT(*) FROM your_table WHERE created_at < NOW() - INTERVAL '1 hour';

  4. Manually trigger cleanup: sql 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: sql UPDATE ttl_index_table SET active = false WHERE table_name = 'my_table';

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

Stop worker: sql 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: sql SELECT COUNT(*) FROM ttl_worker_status();

  2. Cleanup effectiveness: sql SELECT * FROM ttl_summary();

  3. Integration with monitoring tools (Datadog, Prometheus): sql 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: