6 ms·
Using an RDBMS as a work queue is an anti-pattern, but if you're going to do it, you probably can't do much better than LISTEN/NOTIFY.
by rosser 10y ago
Using an RDBMS as a work queue is an anti-pattern, but if you're going to do it, you probably can't do much better than LISTEN/NOTIFY.
- Veratyr 10y ago> Using an RDBMS as a work queue is an anti-pattern I don't want to appear too ignorant but why? I hear many claims of this but few explanations. The best I've heard is something about not being able to handle the load but I don't really buy that unless you don't need persistence/integrity.
- rosser 10y agoPrimarily, because it's hard to get right. Of course it can be done well, or correctly (and as The Fine Article points out, adding SKIP LOCKED to SELECT ... FOR UPDATE will go a long way towards that). But most people do them terribly. As just one example, from a previous job: they were using SQL Alchemy in the code that interacted with the work queue, but somehow didn't realize that the DBAPI being implicitly transactional meant they'd need to manage transaction-state themselves. Consequently, they never released the implicitly opened transactions that every poll for new work created. No-one thought it was strange that the work queue system needed to be shut down weekly, so that a VACUUM FULL could reclaim the gibibytes of space that were wasted because the db couldn't clean up tuples (row version) that still appeared live to stale, abandoned transactions. Until I was all, "Uh, guys?" The fix was obvious: disable implicit transactions. But that's just one example, if particularly egregious. I've seen and heard countless more.
- ioquatix 10y agoThat's a nice summary. But, does that really make it an anti-pattern?
- rosser 10y agoI'd argue that a pattern as prone to subtly (or grossly) incorrect implementation as databases-as-work-queues is qualifies, yes.
- einhverfr 10y agoBut all work queue architectures I have worked with have subtle problems which are easy to get wrong. Might we conclude that the use of a work queue is an antipattern?
- Veratyr 10y agoThat sounds like a problem with using SQL at all more than a problem with using it for a work queue. And really it's not even a problem with SQL, it's a problem with Python's weirdass DBAPI. Wouldn't using a basic wrapper like Celery take care of most of that for you though?
- Sami_Lehtinen 10y agoThat's just the point. People seem to think that because we don't even know how to use the current technology correctly, adding dozens of new alternate technologies would somehow make the situation better. - Then they're surprised that adding all those new cool technologies didn't actually fix the issue(s), probably made those just worse. Because nobody knows how to use the new tools correctly either.
- mkesper 10y agoIt's Python DBAPI used wrongly. Use the connection in a connection manager (e.g. "with sqlite3.connect(":memory:") as connection:").
- einhverfr 10y agoIt is definitely hard to get right, for sure. I am actually planning on taking what I have learned on this and releasing it soon. And a lot of cookie-cutter solutions out there don't scale. But a lot of things are less about outlines than details and that is true here. If you do get it right, you get a work queue where jobs disappear properly when completed and if they fail remain in the queue to be retried. The fact is, work queues are hard to get right on any technology. You have a ridiculous number of corner cases and failure conditions you need to account for and the question ends up coming down to what you want the default failure state to be.
- grzm 10y agoPgQ, part of Skype's SkyTools package, is a good example of queues in Postgres. It's used by their Londiste, a replication tool that was developed before built-in replication was mature. https://github.com/markokr/skytools https://github.com/markokr/skytools
- einhverfr 10y agoBut you have a fairly major problem with all these solutions and that is when a message leaves a queue. For a message queue you want it to leave the queue when delivered. For a job queue you want it to leave the queue when the work is completed but not to be sent out in the mean time. Now imagine you have processing jobs that take two weeks with 16 cores to complete. That's one thing that makes this a hard problem. If you have open transactions for two weeks "Deleting this...." then autovacuum is going to be totally ineffective. So failure detection and recovery is an important (and probably the hardest) part of the problem. PgQ looks to me like it is a solution to a message queue problem not a work queue problem.
- grzm 10y agoIIRC (it's been a while), PgQ doesn't keep transactions open while events are processed: events are fetched and marked as in process; they're then marked succeeded or failed to finish the event. The bulk copy of initial data which bootstraps replication is a long duration process: I don't believe transactions are held open this entire time. If I understand you correctly, this handles your work queue case.
- renesd 10y agoAuthor here. Yeah, pretty much everything in the article is an anti-pattern. However, people are doing these things, and the implementations are correct, and perform well enough for us. There are downsides, and dragons... beware!
- gbrits 10y agoWhat wasn't clear to me from the article is if Postgres can guarantee at-least-once semantics? I.e.: can it be guaranteed that messages are delivered to the consumer at least once given all kinds of failure situations?
- renesd 10y agoI think these slides describe it best. https://www.pgcon.org/2016/schedule/attachments/414_queues-pgcon-2016.pdf https://www.pgcon.org/2016/schedule/attachments/414_queues-p... Page 34. The key is SKIP LOCKED, so you do not try and grab an item off the queue which is already locked.
- gbrits 10y agoThanks. Reading the 'problems' section: a simple solution for "Resilience against crashing or hanging workers" would be having a TTL on the lock. If only...
- smilliken 10y agoListen/notify doesn't queue messages if the listener isn't online, so isn't usually appropriate as a work queue. I encourage everyone to challenge the conventional wisdom that using postgresql as a work queue is an anti-pattern. I've never found a queing software, out of countless options, that I was satisfied with. In concept, a queue is very simple. In practice, I've never seen the end of messy real world constraints that need to be imposed on queues. We have somewhere around 50-100 queues in postgresql databases at MixRank, nearly all of them with millions of records or more, being hammered constantly. We get transactional consistency, automatic insertion with triggers, expression and partial indexes for prioritization and partitioning, joins, constraints and foreign keys, and everything else that comes with postgresql. It works great.
- einhverfr 10y agoAlso if your queue takes a month to clear because you bulk inserted a few hundred million jobs..... Listen/Notify isn't going to help you.
- manigandham 10y agoHave you tried AMPS: http://www.crankuptheamps.com/ http://www.crankuptheamps.com/
- einhverfr 10y agoIt depends. There is a tradeoff for sure. But there is the advantage that you can tie the job completion to the db transactions of the main job. Like a lot of things, craftmanship depends on detail far more than it does on outlines.
- creo 10y agoEverything is anti-pattern when applied in wrong place.
- einhverfr 10y agoExcept following blanket advance. Following blanket advice is always antipattern ;-)
- Yetanfou 10y agoUsing an RDBMS as a work queue is an anti-pattern... Good. If there is one thing we need less of, it is dogmatism about 'patterns'. Patterns are guidelines, not targets to be reached.
- anarazel 10y agoMight want to look into pgq and/or logical decoding. Both can give more guarantees than LISTEN/NOTIFY.