4 ms·
This is interesting because I’ve seen a queue that was implemented in Postgres that had performance problems before: the job which wrote new work to the queue t
by CobaltHorizon 3y ago
This is interesting because I’ve seen a queue that was implemented in Postgres that had performance problems before: the job which wrote new work to the queue table would have DB contention with the queue marking the rows as processed. I wonder if they have the same problem but the scale is such that it doesn’t matter or if they’re marking the rows as processed in a way that doesn’t interfere with rows being added.
- throwaway5959 3y ago> To make all of this run smoothly, we enqueue and dequeue thousands of jobs every day. The scale isn't large enough for this to at all be a worry. The biggest worry here I imagine is ensuring that a job isn't processed by multiple workers, which they solve with features built into Postgres. Usually I caution against using a database as a queue, but in this case it removes a piece of the architecture that they have to manage and they're clearly more comfortable with SQL than RabbitMQ so it sounds like a good call.
- KrugerDunnings 3y agoIt is easy to avoid multiple workers processing the same task: `delete from task where id = (select id from task for update skip locked limit 1) returning *;`
- throwaway5959 3y agoI didn't say it was difficult, I just said it was the biggest concern. That looks correct.
- zrail 3y ago(not sure why this comment was dead, I vouched for it) There are a lot of ways to implement a queue in an RDBMS and a lot of those ways are naive to locking behavior. That said, with PostgreSQL specifically, there are some techniques that result in an efficient queue without locking problems. The article doesn't really talk about their implementation so we can't know what they did, but one open source example is Que[1]. Que uses a combination of advisory locking rather than row-level locks and notification channels to great effect, as you can read in the README. [1]: https://github.com/que-rb/que https://github.com/que-rb/que
- cldellow 3y agoThey claim "thousands of jobs every day", so the volume sounds very manageable. In a past job, I used postgres to handle millions of jobs/day without too much hassle. They also say that some jobs take hours and that they use 'SELECT ... FOR UPDATE' row locks for the duration of the job being processed. That strongly implies a small volume to me, as you'd otherwise need many active connections (which are expensive in Postgres!) or some co-ordinator process that handles the locking for multiple rows using a single connection (but it sounds like their workers have direct connections).
- bpodgursky 3y agoSharding among workers by ID isn't hard.
- riogordo2go 3y agoI think the 'select for update' query is used by a worker to fetch jobs ready for pickup, then update the status to something like 'processing' and the lock is released. The article doesn't mention holding the lock for the entire duration of the job.
- sgarman 3y agoI wish they actually wrote about their exact implementation. Article is kinda light on any content without that. I suspect you are right, I have implemented this kinda thing in a similar way.
- ketchupdebugger 3y agowhat happens if the task cannot be completed? or a worker goes down? Is there a retry mechanism? maintaining a retry mechanism might be a huge hassle.
- marcosdumay 3y agoFor something this size, my guess is it creates an alert and somebody looks at the problem.
- 3y ago
- zem 3y agoyep, i had precisely this issue in a previous job, where i tried to build a hacky distributed queue on top of postgres. almost certainly my inexperience with databases rather than the volume of jobs, but i figured i was being shortsighted trying to roll my own and replaced it with rabbitmq (which we had a hell of a time administering, but that's a different story)