Retention for pgqueuer_log and pgqueuer_statistics, and statistics aggregation granularity

[yqiang]

My understanding is that both pgqueuer_log and pgqueuer_statistics grow without bound. In my case, many jobs are very short-lived, but they run at high volume, so these tables could become quite large over time.

Do you have any recommendations for how long to retain data in these tables, or when it makes sense to clear them out?

I also noticed that pgqueuer_statistics appears to be aggregated at one-second granularity. That seems useful for live monitoring, but less useful for tracking job counts and states over longer time periods.

Are there any plans to support coarser aggregation intervals, such as per-minute or per-hour statistics?

[janbjorge]

You're right, both tables grow without bound today.

How the two tables relate

pgqueuer_log records every job state transition (enqueue, pick, complete, etc.). When you query statistics (e.g. via pgq stats or the log_statistics() method), it first aggregates pending log rows into pgqueuer_statistics at 1-second granularity, marking them aggregated = TRUE. This happens on-demand, not via a background task. Neither table is pruned automatically.

Safe pruning

Once rows in pgqueuer_log are aggregated (aggregated = TRUE), they're only needed for per-job tracebacks or audit trails. For high-volume, short-lived jobs you can safely delete them:

-- Delete aggregated log rows older than 1 day
DELETE FROM pgqueuer_log
WHERE aggregated AND created < NOW() - INTERVAL '1 day';

For pgqueuer_statistics:

-- Keep last 7 days of statistics
DELETE FROM pgqueuer_statistics
WHERE created < NOW() - INTERVAL '7 days';

You can run this as a pg_cron job, or use PgQueuer's own cron scheduler to handle it. For example, a nightly cleanup at midnight:

@sm.schedule("prune_pgqueuer_logs", "0 0 * * *")
async def prune_pgqueuer_logs(schedule: Schedule) -> None:
    await driver.execute(
        "DELETE FROM pgqueuer_log WHERE aggregated AND created < NOW() - INTERVAL '1 day'"
    )
    await driver.execute(
        "DELETE FROM pgqueuer_statistics WHERE created < NOW() - INTERVAL '7 days'"
    )

Retention period depends on your needs. Both tables are observational only and deleting from them has zero impact on queue operation.

Disabling logging entirely

If you don't need statistics or audit trails at all, you can subclass QueryQueueBuilder and override the query methods to remove the INSERT INTO pgqueuer_log CTEs. The core queue works fine without them.

I'll try to make this easier to configure natively after v1. There's a large refactor underway right now, and it might be simpler to add once that lands.

Coarser aggregation

No built-in coarser intervals yet. You can work around this with a separate summary table:

INSERT INTO pgqueuer_statistics_hourly (hour, entrypoint, status, priority, total)
SELECT date_trunc('hour', created), entrypoint, status, priority, SUM(count)
FROM pgqueuer_statistics
WHERE created < NOW() - INTERVAL '1 hour'
GROUP BY 1, 2, 3, 4
ON CONFLICT (hour, entrypoint, status, priority)
DO UPDATE SET total = EXCLUDED.total;

Then prune the fine-grained rows. You get 1s resolution for recent data and compact hourly totals for long-term trends.

I'll open an issue to track built-in retention policies and configurable aggregation intervals.

[yqiang]

This is very helpful. Thanks for the detailed reply and the great work on this project!