search

collectiveidea / delayed_job_active_record

ActiveRecord backend integration for DelayedJob 3.0+
MIT License
343 stars 337 forks source link

Optimize PG reservation query for PG >=9.5 #215

Closed stevenharman closed 1 month ago

stevenharman commented 1 year ago

What does this optimization do?

It handles an issue where two DelayedJob servers (i.e., "workers") would try to pull the top delayed_jobs record at the same time. One would succeed and the second would block.

SKIP LOCKED, introduced in PostgreSQL 9.5, allows the second query to skip records that are already locked by another transaction. This results in significant improvement in performance when there are multiple worker servers all trying to get the next available delayed_jobs row at the same time.

What about older PostgreSQL versions?

ActiveRecord requires Postgres >= 9.3, so we cannot rely on the built-in version checks. But we can use the same mechanisms that ActiveRecord provides to check the current version and enable an optimization in that case. This means newer versions of ActiveRecord (at least 5.0+) will inspect the current Postgres version and make the optimization if they can. We're not going to worry about older ActiveRecord versions as everything that old is EOL'd anyhow. And if they're running a Rails that old, the Postgres is likely older too.

I understand there's been a hesitance to introduce Postgres version-specific functionality in the past, but this optimization drops our time to dequeue a job from several seconds to milliseconds. We've been running with this patch for years in production and I'm hoping this is a more favorable time to upstream it.

If so, I can adjust the code to be more intention revealing - extracting some methods so we need fewer comments, and to make it easier to pull this out when Postgres 9.5+ is required in some future version of this gem. We might also memoize the result of the check so we only do it once per connection object.

Thank you for your consideration.

mostlyobvious commented 6 months ago

That should work for MySQL too: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

stevenharman commented 1 month ago

Thanks for pulling this in, @albus522. Do you have a plan for an eventual release? Given the number of changes being pulled in, are you thinking a new major version, or just a minor (4.2.0) bump?