Pruner is losing the db connection from timeout about oban HOT 4 CLOSED

Well that shouldn’t happen! The prune limit is configurable, so as a quick fix you can lower the limit to something like 1000 to prevent timeouts.

The real fix is to set the prune timeout to :infinity.

from oban.

My previous comment was wrong (not technically, just the wrong approach). The prune_limit setting was meant to prevent this exact issue, but it wasn't used for the beats table. Now it is!

from oban.

We were seeing this too, but on jobs not beats.

We have ~60MM rows in our jobs table. The prune query wasn't being satisfied by any existing index and taking 20+ minutes.

To speed things up I added the following index:

CREATE INDEX CONCURRENTLY oban_jobs_id_state_index ON oban_jobs (id desc) WHERE state = 'completed' OR state = 'discarded';

Let me know if you think this should be in Oban itself and I can make a PR.

from oban.

We have ~60MM rows in our jobs table. The prune query wasn't being satisfied by any existing index and taking 20+ minutes.

That's a lot of jobs to have sticking around. I can see how the query wasn't working for that without the proper indexes. The limit doesn't help if the query itself isn't fast enough.

My initial instinct is that removing the order by clause allows a much faster index only scan of the table:

=> explain analyze select id from oban_jobs where state in ('completed', 'discarded') offset 1000 limit 10000;
                                                                                  QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Limit  (cost=52.80..580.00 rows=10000 width=8) (actual time=0.547..5.642 rows=10000 loops=1)
   ->  Index Only Scan using oban_jobs_queue_state_scheduled_at_id_index on oban_jobs  (cost=0.08..38805.29 rows=736073 width=8) (actual time=0.197..4.956 rows=11000 loops=1)
         Filter: (state = ANY ('{completed,discarded}'::oban_job_state[]))
         Rows Removed by Filter: 37
         Heap Fetches: 2854
 Planning Time: 0.090 ms
 Execution Time: 6.009 ms
(7 rows)

That avoids adding a single purpose index. However, it doesn't help if somebody is doing time based pruning; that requires a different index (or set of indexes) entirely.

Would you mind opening up a new issue to discuss this?

from oban.