Cache hit ratio on oban_jobs at 65% - large completed backlog, is this expected?

Questions & Help Troubleshooting
1

We recently started running Oban with Oban Web on PostgreSQL (AWS RDS gp3) and noticed our oban_jobs buffer cache hit ratio sitting at 65%, with ~600 block reads per second. We investigated and wanted to share findings and ask a few questions.

What we found

Our table has ~434k rows, of which 433,371 (99.97%) are in the completed state. We have max_age: 7 days configured on the Pruner. Table size is 301 MB, index size 229 MB (560 MB total). Autovacuum is healthy, dead tuples at 8.1%.

The main query we traced the block reads to was Oban Web’s queue typeahead suggestion:

SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0

The composite index from v08, (state, queue, priority, scheduled_at, id), has state leading, so this query can’t use it efficiently and falls back to a large sequential scan. It’s worth noting, we also suspect some of our sequential scan count was inflated by developers running diagnostic queries during the investigation itself.

Actual performance metrics are all fine: avg read latency 0.6ms (peak 0.8ms), ReadIOPS at 650 of 3,000 available, 1.2GB freeable memory. So this isn’t causing a real problem today.

Questions

  1. Is a 65% cache hit ratio on oban_jobs expected at this scale with a large completed backlog? Given our real metrics are healthy, is this simply a normal consequence of the table size relative to shared_buffers?

  2. We’re reconsidering our 7-day retention, but job retrospection is genuinely useful to us. What does effective pruning look like in production for teams that want to retain some history without the table growing unbounded?

  3. For the Web typeahead query: queue names are statically defined in our config, so a full table scan to populate suggestions feels unnecessary. The hint_query_limit(:queues) resolver callback seems like the right lever to limit the scan, but has a resolve_queues style callback been considered, to allow serving suggestions directly from known config rather than the DB?