> 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.

It 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 *;`

I didn't say it was difficult, I just said it was the biggest concern. That looks correct.

(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

They 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).

I 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.

I 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.

what 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.

For something this size, my guess is it creates an alert and somebody looks at the problem.

It's a bit of work but allows for fine grained retry logic. Some jobs should not be retried but escalated, others might need a retry in one hour, others can be retried at a lower priority. I prefer a flatter stack and total control over the queue. That being said, I have RabbitMQ running in a production project that I haven't touched in years, it's rock solid and I'm pretty sure these guys had it misconfigured.

Sharding among workers by ID isn't hard.

yep, 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)