Multi-node postgrex listener resulting in more queries than necessary.

[brandonjoyce]

We've been noticing some increased CPU usage that seems to be due to an abundance of calls for available jobs to the database.

We have in the neighborhood of 100's of jobs inserted per minute. We have max_demand setup at 100 and we currently have 2 nodes churning away. The workers seem to keep up with the queue so we're almost always buffering demand.

The way I understand the issue, it seems like each insert into the table will result in a call to dispatch_jobs on both nodes so they are competing to get these individual jobs. Also, due to the number of rows we're inserting and how the workers are almost always ahead of what's being queued, we end up doing this A LOT.

I think the desired behavior would actually be to just have the jobs queue up a little more before dispatching. I'm wondering if I should just kill the PG_Notify stuff in my implementation and just rely on polling? Any better ideas?

[mbuhot]

each insert into the table will result in a call to dispatch_jobs on both nodes so they are competing to get these individual jobs

Yes when there is demand from multiple nodes, they will compete for the jobs.

just kill the PG_Notify stuff in my implementation and just rely on polling

That sounds like a pretty simple way to go 👍
Alternatively we'd need to implement some kind of throttling mechanism in the library.

I think you'll need to drop the trigger on the jobs table that is performing the pg_notify, similar to the down migration: https://github.com/mbuhot/ecto_job/blob/master/lib/ecto_job/migrations.ex#L129

[brandonjoyce]

Awesome. Thanks for getting back to me so quickly!

I think we will just rely on polling and see from there.