SELECT ... FOR UPDATE SKIP LOCKED was introduced in Postgres 9.5 to solve (some of) these issues. With it, dequeueing a set of jobs looks like this. No need to hang on to a long running transaction either as long as you have some sort of process to requeue dropped jobs.
Oh, I wasn’t aware it got fixed in Postgres 9.5. However, I still do like the article as it goes into depth on various intricacies of Postgres and gives a good insight on how things work. A good, albeit outdated, learning material.
That’s right. That’s an issue for any table that has high churn in terms of updates and deletes and that’s something you’d work around by tuning auto vacuum (to run frequent, fast vacuums – the goal being predictable performance over very high throughput) and things like the fillfactor for the jobs table specifically.
Another option I’ve heard about but never tried is having two jobs tables and periodically switching between them and truncating the other. Presumably, that’d involve turning off autovacuum for the two tables since you’re effectively performing a full vacuum “in software” at every switch. Personally, I think that if you get to the point where you need to do something like that, it’s probably time to move on to a real message queue (like RabbitMQ).
SELECT ... FOR UPDATE SKIP LOCKEDwas introduced in Postgres 9.5 to solve (some of) these issues. With it, dequeueing a set of jobs looks like this. No need to hang on to a long running transaction either as long as you have some sort of process to requeue dropped jobs.The article was published in 2015.
Oh, I wasn’t aware it got fixed in Postgres 9.5. However, I still do like the article as it goes into depth on various intricacies of Postgres and gives a good insight on how things work. A good, albeit outdated, learning material.
It doesn’t however solve the “dead tuples problem” mentioned in the article. Correct me if I’m wrong.
That’s right. That’s an issue for any table that has high churn in terms of updates and deletes and that’s something you’d work around by tuning auto vacuum (to run frequent, fast vacuums – the goal being predictable performance over very high throughput) and things like the
fillfactorfor the jobs table specifically.Another option I’ve heard about but never tried is having two jobs tables and periodically switching between them and truncating the other. Presumably, that’d involve turning off autovacuum for the two tables since you’re effectively performing a full vacuum “in software” at every switch. Personally, I think that if you get to the point where you need to do something like that, it’s probably time to move on to a real message queue (like RabbitMQ).