search

rails / solid_queue

Database-backed Active Job backend
MIT License
1.95k stars 130 forks source link

Improve DISTINCT query for finding queue names in PostgreSQL #397

Closed bubiche closed 3 days ago

bubiche commented 3 weeks ago

workerB

Related issue in Mission Control: https://github.com/rails/mission_control-jobs/issues/160

We use SolidQueue with PostgreSQL and sometimes viewing Mission Control slows the database down due to the SELECT DISTINCT query. The discussion in the linked issue suggests using recursive CTE, which seems to have better performance in practice for us.

This PR uses recursive CTE for PostgreSQL if it is supported for better performance.

bubiche commented 4 days ago

Hi @rosa, sorry for pinging but it's been a while, is this something in the plans for Solid Queue? 1 use case other than Mission Control I can think of is when we need to pause queue(s) when the database/workers are overwhelmed, the query to get non-paused queues won't worsen the problem.

I'll close this PR if it's not something Solid Queue needs.

rosa commented 3 days ago

Hey @bubiche, sorry for the delay!

1 use case other than Mission Control I can think of is when we need to pause queue(s) when the database/workers are overwhelmed, the query to get non-paused queues won't worsen the problem.

This query is used only in Mission Control. For paused queues, the solid_queue_ready_executions table is checked, which should be much smaller than the solid_queue_jobs table. I wrote a bit about that here.

I'm interested in this, but the code here is very complicated and big. I'd like to have an alternative to this, something simpler or at least encapsulated differently, not in your face like this for only a specific database. I haven't spent enough time thinking how it'd be, sorry.