24 ms·
Turning PostgreSQL into a queue serving 10k jobs per second (2013)
- golergka 7y agoSeems related to this one from a week ago: https://layerci.com/blog/postgres-is-the-answer/ https://layerci.com/blog/postgres-is-the-answer/
- faizshah 7y agoYup, the reason I posted this was the great discussion from last week: https://news.ycombinator.com/item?id=21484215 https://news.ycombinator.com/item?id=21484215
- dpedu 7y agoHow does this compare to Redis? Seems like Redis would handily beat it.
- dickeytk 7y agoThe author addresses Redis: > So, many developers have started going straight to Redis-backed queues (Resque, Sidekiq) or dedicated queues (beanstalkd, ZeroMQ...), but I see these as suboptimal solutions - they're each another moving part that can fail, and the jobs that you queue with them aren't protected by the same transactions and atomic backups that are keeping your precious relational data safe and consistent. Inevitably, a machine is going to fail, and you or someone else is going to be manually picking through your data, trying to figure out what it should look like. I disagree though. BLPOP is far easier to grok than any Postgres solution and Redis is rock-solid. Either using the new Redis streams or a good queueing library is going to guarantee you don’t miss any jobs and have a need for transactions across both systems either. I would also be very hesitant to add work to my database. That’s often a sensitive part of systems.
- shandor 7y agoI was also wondering about the "safe and consistent" part. Doesn't Redis Streams with persistence solve those pretty well? Edit: oh, someone mentioned this being from 2013. No Redis Streams back then.
- ukd1 7y agoYa, it doesn't mean streams - even if it's consistent in isolation, you're using it with Postgres - how do you make actions in one consistent with actions in the other? (TLDR: you won't/don't)
- ukd1 7y agoYou can disagree, but you're still at least partially wrong; if you're using postgres already, adding redis WILL make your system less reliable as you now have another point of failure to deal with (something else to patch, keep up to date, restart, etc, etc). Unless you have super high traffic, low latency requirements - using postgres adds some load, plus saves you having to write code to deal with rollback / race conditions with postgres vs a-redis-queue.
- erichocean 7y ago> Redis is rock-solid I've lost gigabytes of data with Redis; nothing with Postgres. I run both in production. Redis has a lot of obscure failure modes, lacks transactions (with rollback), and has no query language. For simple stuff where it's okay to lose data regularly, it's fantastic (that's when we use it). That said, we've been doing more and more with Postgres over time, and less and less with Redis. The benefits of having all of your data in the same storage, transactionally consistent, with a query planner for ad hoc visualizations and reporting is just too great.
- danharaj 7y agoPostgres is not best in class in many things it does well but it suits the needs most people will actually encounter in the wild while being built on a rock solid foundation. It truly is a fantastic hammer for every nail involving data integrity or synchronization. It's well designed so that when you do venture into territory where you need a specialized solution it's not difficult to replace. But you'll definitely miss ACID and relational data if you're leveraging them. There is a great operational economy to making Postgres your default solution for these sorts of problems.
- deleted 7y ago[deleted]
- tracer4201 7y agoIf your requirements don’t necessitate such scale, I can see Postgres being a viable alternative. I’d rather not introduce another dependency unless it’s absolutely needed.
- dickeytk 7y agoI’ve never in my life worked on something where we didn’t keep having to upgrade our database resources
- oftenwrong 7y agoWhen you scale your db, now you're scaling your queue, too. One thing to scale, one thing to monitor.
- n4r9 7y agoYou may be right in terms of performance (I've no idea) but article states: > many developers have started going straight to Redis-backed queues (Resque, Sidekiq) or dedicated queues (beanstalkd, ZeroMQ...), but I see these as suboptimal solutions - they're each another moving part that can fail, and the jobs that you queue with them aren't protected by the same transactions and atomic backups that are keeping your precious relational data safe and consistent
- dimino 7y agoI find this argument to be pretty weak, and seems to be a stand-in for, "I don't want to use a new technology because it'd require me to learn something, so I'm going to shoehorn something I feel more comfortable with, even though it's not the best tool for the job." Edit: I'm rate limited so to elaborate a bit. Queueing technologies are not "new interesting bit of technology", they're tailored solutions to solve a specific problem. Using a RDBMS for queueing is not what it was built to do, and you will run into issues doing so (I have). By trying to use one tool for everything, you lose out on all kinds of optimizations, features, and performance enhancements that are specific to the problem you're trying to solve. It's bad engineering to try and force Postgres into the role of queue when vastly superior technologies exist. You're actively hurting the engineers you work with, and the company you work for, if you force the same tech into use cases it isn't optimal for. I cannot stress this enough; fear of learning is anathema to software, and trying to hide it behind a veneer of caution is not only disingenuous but potentially malicious as well, maximizing exclusively for the benefit of the individual against the interests of the group.
- mlyle 7y agoFewer pieces in the technology stack is better, because each piece is operationally expensive (it is its own set of work in maintenance, upgrades, migrations, etc). There are also the mentioned costs (you can't atomically do things across divergent pieces of infrastructure, at least not very easily at all). Fighting the tendency of everyone wanting to draw in each new interesting bit of technology is important. Every piece of complexity you incur should be proven as required, and you should build things to just somewhat larger than near term projected scale... and not expect that you are a temporarily embarrassed unicorn with loads of unexpected demand showing up. If you run out of performance, you have some headroom from tuning; from that point, you can then choose whether it makes sense to retrofit to a higher scale technology or throw larger hardware at the problem (or both).
- Exuma 7y agoBecause Que has transactional/ACID guarantees, and Redis does not.
- adverbly 7y agoActually, redis makes several guarantees. Just not as acid as rds. Source: https://redis.io/topics/transactions https://redis.io/topics/transactions
- balfirevic 7y agoEnqueueing jobs in Redis is not transactional with regard to your primary data store.
- adverbly 7y agoOh I see what you mean. So ya its not transactional across systems. Ya good point. I usually manage that by just updating the primary data store in the job, but I can see what you mean, and how that might not always be an option.
- truth_seeker 7y agoRedis would be faster. In PG you can use UNLOGGED table which is basically a in-memory table.
- ukd1 7y agoUNLOGGED: You could, but if you get some types of crash, you'd be in trouble. More on non-durable settings here - https://www.postgresql.org/docs/current/non-durability.html https://www.postgresql.org/docs/current/non-durability.html
- mobilemidget 7y agoI'm still waiting for a work project to come by where I can replace Redis with https://keydb.dev https://keydb.dev and test that in action.
- wjd2030 7y agoI have tried this in our dev environment several times and failed b/c it didnt support all RESP2 commands (Using stackexchange.redis as the lib)
- echlebek 7y agoWith postgres, you have a chance to not lose data.
- bloody-crow 7y agoThis is an article from 2013. I suggest reflecting this in HN title, cause it's not immediately obvious.
- Diggsey 7y agoI have also found the lack of transactional guarantees in typical job queues to be very problematic. One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. For a MySQL database, this would be just fine, but PostgreSQL uses a process-per-connection model which caps the number of active connections to the database to a relatively low number (on the order of 1000x fewer connections than a similarly sized MySQL instance) and tools like PgBouncer do nothing to help with this. As a result, if your jobs take more than a few milliseconds to execute (let's say you make external HTTP requests as part of your job) this is not a good approach to take. I use a similar approach which avoids this problem, but it only works because I have relatively low throughput requirements. I essentially implement in-database advisory locks using a separate table - before taking a job, workers create a row in the table, and the primary key of this table is used as a worker ID. Jobs are "taken" by assigning them a worker ID. Each row in the worker table has an expiry date, so if workers die, the corresponding row will be deleted and any linked jobs released back into the queue. As well as transactional guarantees, using a database as a job queue gives you a lot of power over how jobs are executed: for example, our service for delivering webhooks has a separate queue per customer, and we can ensure that within a single queue jobs are processed strictly in order. Meanwhile, our service for search indexing supports different priority levels, so that newly created records are indexed with a higher priority.
- z92 7y ago// One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on. // Not necessarily. You lock the row for an instant update to a field, for example called "status" into "running" and then disconnect from the database within milliseconds. Finish your job taking as much time as you want. And then connect again to change the row's status to "finished". This is how it's always designed, as I have seen. The locking problem is for querying rows where status="waiting" and then instantly change it. It's not to "keep the record locked, and DB connection up, until I finish my batch job". That would be a bad design.
- Thaxll 7y agoBad idea to use that because if the worker crash the event is lost, don't use PG for that.
- bloody-crow 7y agoWait, what do you mean here? The job is a row in a database. You can crash as much as you want, the row is not going anywhere until you explicitly mark it as "worked" and delete it. There are different performance constraints to this approach, but data integrity and robustness of it are unmatched, really.
- ecnahc515 7y agoThis is pubsub and the data is in an ACID compliant database. The worker will just re-subscribe and poll for events on startup.
- dragonwriter 7y ago> Bad idea to use that because if the worker crash the event is lost The only case where this would be problematic is if the worker crashed after doing some side effect external to the queue server that rendered the job non-idempotent, but before committing an acknowledgement. But that's not really a “what you use as a queue server” issue as a “are you also using a distributed transaction system to coordinate all side effects including queue updates” issue. (If the same Postres DB is the operational DB and the queue server, you may be able to avoid distributed transactions by having all the side effects in the DB, though that creates its own issues.)
- bloody-crow 7y agoOne of the interesting unforeseen downsides of RDBMS-based queues is explored here: https://brandur.org/postgres-queues https://brandur.org/postgres-queues TDLR the lock time grows exponentially depending on the number of dead tuples in the table, which naturally grows as you use long running transactions.
- Exuma 7y agoIs that fixed by the VACUUM process?
- taesko 7y agoIt naturally grows due to long running transactions because the vacuum process cannot clean dead tuples that a transaction can "see". Though more aggressive vacuum settings might solve this problem.
- bloody-crow 7y agoNo, cause VACUUM can't kill those dead tuples while transaction is still running. Think of it this way... When you open a transaction, you need to have a guarantee that you can touch rows that existed at the moment when transaction has started. You job queue is chugging along and processes let's say a 1000 jobs per minute. Processing a job involves deleting the row from the queue, but since you have a transaction running, Postgres only marks the row as deleted, and keeps it around in case a transaction would want to access it at some point. Each time you need to process a job, Postgres needs to lock the row. The way this mechanism works involves iterating over the rows until you find one you can lock on. If your transaction is running 30 minutes, each job would have to iterate through 30k dead rows (deleted, but still around for the sake of the transaction). Slowing down lock time leads to overal degreaded performance of the job queue, which leads to jobs being added faster than they're being processed, which further exacerbates the problem
- biggestdecision 7y agoI wonder if this could be solved by moving in-progress jobs to a separate table... Guess you lose job atomicity that way.
- bgentry 7y agoThe author of this post, Chris Hanks, created the Que queueing library for Ruby: https://github.com/que-rb/que https://github.com/que-rb/que It’s changed significantly since this post as the 1.x betas use a very different structure which should actually be more efficient, use fewer Postgres connections, cause less lock contention, and cause less table bloat. Not sure if the benchmarks have been run recently or not but I’m definitely curious how things stack up to this post from 6 years ago :)
- aldoushuxley001 7y agoWish I could use that in a Django app. Doesn't seem to be a viable python queueing library that allows using postgresql
- deleted 7y ago[deleted]
- dimino 7y agorq is pretty good, have you checked it out?
- elamje 7y agoRecently was wishing this myself. I’ve been using rq, which although I hear is much faster than an rdbms is also another dependency and service to worry about failing. Trying to keep things simple is nice, I am already having trouble trying to grok what kind of weird state I can have in my application if Redis goes down, or my dB goes down but Redis remains
- codetrotter 7y ago> I am already having trouble trying to grok what kind of weird state I can have in my application if Redis goes down, or my dB goes down but Redis remains If it is possible to set up a smaller test environment with one or a few instances of your DB and Redis, and you have time to do it, you could do some testing where you purposely shut down each of them at various points in time and inspect what happens to your application state compared to how it behaves when all is well. Perhaps you could even outfit the testing version of your application with two proxies, one that will proxy the DB connection and one that will proxy the Redis connection, and have these proxies randomly decide within some threshold whether to pass the data along to their upstreams, or to simulate a broken connection. Then test the application with some different threshold values, for example, 1% probability of failure, 5% probability of failure, 50% probability of failure and 100% probability of failure. Repeat the tests some number of times for each threshold value and observe the behavior of the application each time. Also, make sure that you log the decisions made by the proxies each run, so that you are able to look at these afterwards.
- Exuma 7y agoThis is awesome, I love Que
- sbov 7y agoDepends on the project. We have some that use PostgreSQL backed queues. Some using Redis. As you get more traffic RDBMS resources are precious - wasting them on queues starts to become a poor tradeoff. Redis is so generally useful we tend to use it on most projects anyways, so it doesn't add another moving part. And we tend to be really conservative about adding new things to our production environment, and despite that Redis has found a home in our stack.
- mikeklaas 7y agoYou're allowed to use more than one PostgreSQL instance for your queue if RDMS resources are precious <g>
- kraih 7y agoThe Minion job queue is another implementation of this idea. But using the even more efficient and safe FOR UPDATE SKIP LOCKED. https://github.com/mojolicious/minion https://github.com/mojolicious/minion
- bufferoverflow 7y agoNow compare to OpenAMQ, ZeroMQ, RabbitMQ, NSQ, Kafka. I have seen benchmarks reaching millions of messages per second.
- madhadron 7y agoThe article is discussing job queues, not messaging. The MQ systems plus Kafka that you are referring to are message transport systems.
- bufferoverflow 7y agoWhat's the actual difference between a job queue and a message queue filled with job IDs?
- mlyle 7y agoIn a decent job queue you know when jobs get completed, whether the worker died, and whether they're otherwise stuck.
- icheishvili 7y agoMessage queues achieving the rate you said are typically not durable.
- wjd2030 7y agoThe difference is nothing. A message can be a serialized object representing a job. Or the message can be a jobId that points to a record in a db.
- ubu7737 7y agoWhen I used PG for my job queue, I was already using Kafka instead, to handle immediate job entries. The reason I needed PG was for jobs scheduled at a future time. I had no difficulty with long-running jobs because servicing jobs out of PG was simply a matter of pushing them onto the Kafka queue for immediate uptake there.
- baq 7y ago
- ukd1 7y agoThere is also QueueClassic (https://github.com/QueueClassic/queue_classic); https://github.com/QueueClassic/queue_classic); another Ruby based project. Recently I benchmarked some improvements we made to it vs Que (before and after) - it's now faster which is kinda cool -https://github.com/QueueClassic/queue_classic/pull/303#issuecomment-512970655 https://github.com/QueueClassic/queue_classic/pull/303#issue...
- ukd1 7y agoThis post is pretty out of date; Que and QueueClassic moved to SKIP LOCKED at some point, over using advisory locks / lock head methods. It's much faster, but only supported in Postgres >= 9.5.
- ukd1 7y agohttps://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... - is actually a pretty good post explaining the before (aka this gist) and after (aka skip locked). Another way of doing it was (now pointless) using http://www.cs.tau.ac.il/~shanir/nir-pubs-web/Papers/Lock_Free.pdf http://www.cs.tau.ac.il/~shanir/nir-pubs-web/Papers/Lock_Fre... (you can see an implementation here - https://github.com/QueueClassic/queue_classic/blob/v3.2.0.RC1/sql/ddl.sql#L6 https://github.com/QueueClassic/queue_classic/blob/v3.2.0.RC...)
- arcticfox 7y agoQue still doesn't use SKIP LOCKED, unfortunately. I think QueueClassic is just the better library at this point, as SKIP LOCKED simplifies everything tremendously and offers similar performance.
- danmg 7y agoI always thought that using a database server for RPC was considered an anti-pattern.
- rhacker 7y agoAbout 22 years back I got in trouble for recommending we drop Tuxedo Queue with just a database table since we can get all the transactional options for free, just the same, without all the headaches an operational issues involved in connecting all our code with the C++ middleware, especially since Java and PHP were in the mix and Tuxedo was kinda of in it's own world and was pretty much the only non-free software we were using and using in a horrible idiotic way.
- oftenwrong 7y agoWhy use a RDBMS as a queue? Because you already have it, and it works well. One day you may need a purpose-built component, but today YAGNI. http://boringtechnology.club/ http://boringtechnology.club/
- pas 7y agoBreaking up the monolith is hard. Yes YAGNI is terrible, over-engineering makes everything brittle and hard to test and so on, but at the same time I burnt myself with spaghetti obelisks more than with the too many docker containers way. That said, using a common persistence store initially makes sense, but trying to compartmentalize the jobqueue/batch-processing stuff never hurts.
- mianos 7y agoThis reminds me of pgqueue https://github.com/markokr/skytools https://github.com/markokr/skytools as used at skype. It works and it works well but needs a plugin so maybe not so good for RDS.
- cwalv 7y agoAnother potential benefit to use a RDBMS system as a queue is that it can make it much simpler to express priorities in cases where it's not a simple FIFO, i.e., if the next job a consumer should take depends on more than just the time the job was added to the queue. One place this has come up for me is when the next job that's picked depends on currently running jobs, e.g., each job is associated with a user, and if a single user already has N tasks running you may want to prioritize another user's tasks for the N+1 slot.
- deleted 7y ago[deleted]
- voldacar 7y agoI'm not very knowledgable about db internals so sorry if this comes off as ignorant, but in an era where cpus execute billions of instructions per second per core, is 10000 jobs per second supposed to be impressive? Is this kind of problem bottlenecked by memory?
- qaq 7y agoby IOPS you fsync transaction to Write Ahead Log (WAL) on commit.
- Noumenon72 7y agoIs this supposed to explain why 10000 per second is impressive? I don't follow.
- qaq 7y agoThis is supposed to explain that it's orthogonal to CPU performance.
- deleted 7y ago[deleted]
- tuldia 7y agoPostgreSQL never ceases to amaze me. The great thing is that it is a mature tool and you don't need to bring another beast to the zoo.
- ptrwis 7y agoI would love to see now PostgreSQL going more into improving developer's expierience. Job queues, job scheduler, packages.
- effnorwood 7y agoTrumpQL is the new name. 10K jobs per second!
- 29athrowaway 7y agoWhat is the problem with Kafka?
- maxpert 7y agoMay be I am being ignorant/arrogant, or have seen similar toy systems crash and burn in prod environments. And if you go through https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... it clearly states: > A queue implemented in the RDBMS will never match the performance of a fast dedicated queueing system, even one that makes the same atomicity and durability guarantees as PostgreSQL. Using SKIP LOCKED is better than existing in-database approaches, but you’ll still go faster using a dedicated and highly optimised external queueing engine. People only realize these warnings when they have built a toy system that is rolled out to a production/product that takes off. By that time you will realize "ohhh I don't need all the ACID guarantees for every job in my system", and I want to run my workers as lambdas/elastic scaling workers (you need more connections). That is where companies will be spending efforts from their best engineers to move away from Postgres as queue. Which comes down to question; why do it in first place? What makes it cheaper (other than local dev) to deploy a system that is bound to fail in future? Don't get me wrong I love Postgres, I just don't believe that it's the right tool for doing this job.