8 ms·

Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kaf

Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis. This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a hammer to put in screws is a bad idea. But the outcome of this all should be "Use a proper tool for the job", not "try to come up with a neat trick to make this work in the wrong system".

swah 9y ago
> Why use a database as a queue? To avoid running a second system.
dom0 9y ago
> Why use a database as a queue? Already have a central, configured and monitored server and need "just a small queue" for something. This is not per se a bad decision. For the same reason it doesn't have to be a bad idea to cache things in the main database, instead of using a dedicated cache like Redis.
- IgorPartola 9y ago
  This. Also, things like RabbitMQ are complex and their durability properties can be different than those provided by your RDBMS. This can get problematic if you are mixing tasks in the queue that have different priorities. For example, emailing the invoice to a client should not fail silently and should happen at most once. Same with a notification from your doctor that you need to call to discuss your test results. Tossing that into an ephemeral queue is probably not the best solution. Having said that, RabbitMQ does have a ton of settings where you can turn durability up/down as much as you want.
  - wbl 9y ago
    Email is unreliable. You should number invoices, and tell recipients to ignore duplicates.
- BurningFrog 9y ago
  In my experience, the cost of adding and maintaining another storage subsystem to a project is often hugely underestimated. It's easy to see the benefits and ignore the costs. If I can solve a problem reasonably well by adding a table to my Postgres DB, that will always beat out adding the specialized ScrewdriverDB that does it perfectly.
  - mbell 9y ago
    Depends on what you're adding. Running Redis is dead simple and easy to have visibility into. RabbitMQ / Kafka are much larger undertakings.
    - brianwawok 9y ago
      RabbitMQ is on docker, its more or less the same work to launch as Redis these days. (It can take a bit more tuning so I think it is unfair to say it is the SAME work, but it is seriously not a huge deal to run RabbitMQ in the post docker world)
      - true_religion 9y ago
        Is Docker really necessary for this? I've never had any trouble just installing RabbitMQ from the package manager, and running it with only a tiny bit of initial configuration.
        brianwawok 9y ago
        Well, if for example your entire infrastructure is in K8s, then yes you kind of need docker. Redis vs RabbitMQ stateful-set.yaml would be me or less the same, and something like 20 lines of yaml. I was never a big fan of docker for running some thing on a server, but once you start to go down the K8s path, it finally makes sense.
    - LoSboccacc 9y ago
      Depends on the scale mostly. People often forget how not every project is Google/Facebook/Twitter.
- emmelaich 9y ago
  Yep. Better asked as "Why use a conventional|relational database as a queue?" Because all queues have to be databases i.e. have the same qualities / assurances as databases.
- tyingq 9y ago
  It also makes it easier to delete or update the queue entry in an atomic transaction that spans other tables. If that has value for the specific use case.
  - Smudge 9y ago
    Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.
    - ruslan_talpa 9y ago
      you can still have the notion of a transaction while integrating rabbitmq like this you do your mutations in a transaction and also in that transaction you execute a NOTIFY command. If transaction is successful, the notify will go through at the end of the transaction. the notify events and be "bridged" to a messaging server like rabbitmq (see my other comment)
      - balfirevic 9y ago
        What if your network has a glitch when you try to write to RabbitMQ after you receive NOTIFY? You will lose that message, correct?
        ruslan_talpa 9y ago
        see my comment above
      - Smudge 9y ago
        Nice -- pg-amqp-bridge seems like a clever solution for coordinating the two. It still puts some amount of the queuing load on the DB, but I'd be comfortable with that trade-off if I really wanted to use rabbitmq as the primary queue. Question though -- Does it guarantee at-least-once or at-most-once on the NOTIFY? (Like, if there is a network blip, will it retry the NOTIFY?) And if it is at-least-once, I assume that consumer apps will have to handle deduplication/idempotency.
- brianwawok 9y ago
  > Already have a central, configured and monitored server and need "just a small queue" for something. Fine. So use SQS or cloud pub sub. Both take 0 "server configuration work", and you aren't adding load to likely the single most expensive part of your infrastructure (RDBMS). (The exception to where a RDBMS is not the most expensive part of your infastructure is where you have very large data with either nosql something, or a machine learning GPU array.. but not sure that is super relevant here)
  - dom0 9y ago
    That's an entirely valid line of reasoning, but it only applies to a certain set of applications. Further, SQS or whatever have the same drawbacks as other external queues compared to an in-DB queue; see all the sibling comments in this big thread.
ruslan_talpa 9y ago
Well, maybe because most application don't need/have the scale that require a messaging server? A lot of projects i would say have a 80/20 read/write split, thus, there are not so many events and they don't need the complexity of rabbitmq when the db can handle a few events. Having said all that, we did actually build a tool to connect together postgresql and rabbitmq :) https://github.com/subzerocloud/pg-amqp-bridge https://github.com/subzerocloud/pg-amqp-bridge When you get to a big number of events or maybe there are lots of types and they all need to be routed in complex ways (real time updates) then indeed, rabbitmq is the right tool And this component enables you to execute NOTIFY in a trigger or stored procedure and have that message sent to rabbitmq.
- true_religion 9y ago
  When you have a messaging server.... you can start using it to help parallelize all kinds of tasks. Things you wouldn't previously think about because they'd take days or even weeks to complete, even in parallel, can all run on your infrastructure now. Spinning up 20 or even a 100 new workers is easy, and follows the same principle as just spinning up 1 or 2. That's been my experience. Before we did everything in the DB, and implicitly ignored the mere possibility of doing some tasks because we knew it'd be too hard with the DB as the event store.
  - brianwawok 9y ago
    Agreed. To keep it simple day 1, on a single box put your entire stack. 1 Django web server 1 redis queue (can both caching and work queue and results queue) 1 Celery process for async tasks 1 PostgreSQL instance (You can do the same with other stacks, I just know them less well) Fairly standard stack. You can find preconfigured setups that can get this going in 10 minutes or less. And boom, you just made a stack that can scale from a 1 user POC to a 10 million user product. This lasts until you get past 1 box stage.. and at that point you split it up by function. At that point you can go to 1 celery worker to 1000 celery workers by just pushing a button to make more boxes. Seems a pretty good setup. Vs say, trying to skip the redis part (which took all of 4 minutes) and writing a bunch of database logic, and then you have to rewrite it all down the road. I am all for start simple. But start simple with a path to the future. If your path to the future is "rip this all out and rewrite it", you should at least ponder what you are doing. Did I REALLY save that much time by using my database as a message queue??
BenoitEssiambre 9y ago
There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance and may result in silent conflicts or corruption when different services read and update the same data. DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed over multiple network hops having uncertain timing, congestion and reliability. The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network. Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server. Of course there are concerns about scalability with a monolith. Manually sharding monoliths is a bit of work but it forces you to do your homework and find the natural and optimal sharding points. In a lot of cases, there are natural separation lines such as between groups of customers that you can use to shard and scale things up. Blindly distributing all data across a bunch of nodes has huge performance and reliability implications. A cpu's connection to it's ram and disks is orders of magnitude more reliable and fast than connections over the network. Unless you are building something like a social network where everything is connected to everything, you don't need things to default to run over clusters and over the network.
- brianwawok 9y ago
  > There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance What? Most job queues are inherently to do something outside of the database. For example: I need to send some emails, or I need to resize some images. You cannot wrap sending an email in the same transaction as your queue work, nor can you wrap resizing some images in the same transaction. The ENTIRE reason you are using a message queue is to go to some external work. So this literally makes no sense. > and may result in silent conflicts or corruption when different services read and update the same data. You need to program this in one way or another no matter what. If you programmed your code to do "silent conflicts or corruption" then I guess you are going to be in trouble. So don't do that. > DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed over multiple network hops having uncertain timing, congestion and reliability. Again, you are missing the point. MOST job queue work is stuff outside of the database anyway. You still have stuff outside the database. > The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network. Not that relevant. If you are doing 10,000 messages a second (or more!) to your job queue, and are looking to hold open a bunch of transactions, you are going to be in for some pain. > Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server. And trying to use PostgreSQL as a job queue is going to give you 1% or .1% of using RabbitMQ or Kafka or SQS or Cloud Pub Sub as a job queue. You are trying too hard to use a database for the wrong thing.
mbell 9y ago
Why would you use Kafka as a queue? It's not a great fit, I mean, you could make it work, but it's a similar square peg in round hole design choice as using a relational DB as a queue.
- rpedela 9y ago
  I thought that was one of the primary use cases? Is that not true?
  - mbell 9y ago
    Kafka's primary use case is as an intermediary for a stream processing system. e.g. to ingest a firehouse of events, make them durable, then feed them to a number of different processors. More common use cases here: https://kafka.apache.org/uses https://kafka.apache.org/uses. You _could_ use it's data model to implement a 'job queue', but it's not a great fit for a number of reasons.
  - barrkel 9y ago
    Think of Kafka as a replayable append-only log. It's tuned for ingestion of enormous quantities of data; it's best at buffering big data, or perhaps as an infinitely growing log of everything that happens in a system, than simply communication in a distributed system. It doesn't have back pressure and its namespacing is primitive (ie DIY with prefixes, and avoid Confluent libraries that assume they're the only client in the system).
- brianwawok 9y ago
  Touche. I think you are right. Kafka can work as a queue like a RDBMS can work as a queue, but it's not a great idea.
jlg23 9y ago
> Why use a database as a queue? Quote from the article: "You don’t need to import a large and complex 3rd party app or library to implement a queue, and you don’t need to deal with the key mapping and namespace issues with advisory locking."
- brianwawok 9y ago
  That is a strawman though. "Why would you need to buy an expensive screwdriver? Your hammer will do a just dandy job of putting in screws"
  - int_19h 9y ago
    Well, if the hammer does actually do a good enough job of putting in screws for your use case, what's the problem? And let's be honest: for most people, it's good enough, scalability-wise. "You are not Google" and all that.
elygre 9y ago
Why would it not work well? Oracle has built their OracleAQ [1] in the database, and it works quite nicely. It can be accessed using native APIs, or a JMS api if that is your thing. Also, having the queue in the database gives you easy access to the queue for management of all sorts -- SQL gives you full access to the queue data. [1]: http://www.oracle.com/technetwork/database/oracleadvancedqueuingwp-2524215.pdf http://www.oracle.com/technetwork/database/oracleadvancedque...
- brianwawok 9y ago
  Oracle sells the license to use their products at 500k per server. of COURSE they are going to build products to use it. Doesn't matter if it's a subpar choice, they just made 500k per server. I am not sure it makes sense to compare some guy throwing some messages in a queue or in postgreSQL to someone spending millions to get steak dinners from Oracle and use Oracle products.
deleted 9y ago
[deleted]
znep 9y ago
The biggest reason I've found for using a database as a queue is that you can actually run queries against it, both writes and reads. This is very useful when different entries in the queue can have some relationship that matters when processing. Do you want to wait 30 minutes after an item is queued then process all queued items for a given entity (eg. user) at once? Do you want to support more complex retry logic, such as efficiently handling some subset of jobs failing due to external dependencies being unavailable while others can complete? Do you want to be able to track what jobs are in progress (doesn't really work with skip locked), and even have the ability to update their status while processing for finer grained tracking? Do you want to be able to effectively monitor the contents of the queue beyond just the size (and some queues make it quite inefficient just to get that)? Some of these are possible with purpose made queueing systems but, in my experience, even if they are they can be quite inefficient, complicated, and are often poorly tested and supported areas of functionality. For example the last time I tried to use JMS selectors with ActiveMQ (selectors let you filter messages based on a subset of SQL) it was a nightmare both performance wise and functionality wise and I wished I just had a database that was actually built to support SQL. Other points about the cost of introducing a new technology (especially if you need strong durability, availability, etc.) and being able to update your database in the same transaction are also valid, but can often be dealt with by some extra effort. Don't get me wrong, if you just need a queuing system the first thing to consider is a system designed for that purpose, especially if you aren't very knowledgeable about databases and have basic requirements. It can be nuanced to correctly and efficiently use a general purpose database as queue. At the end of the day, however, keep in mind that under the hood a queueing system is just a database that exposes a limited API that makes it easier to get basic operations right and hopefully easier to scale (sadly I have seen all too many queuing systems that utterly fail at that part) at the expense of flexibility.
codesnik 9y ago
this article recently posted on HN is addressing this http://programmingisterrible.com/post/162346490883/how-do-you-cut-a-monolith-in-half http://programmingisterrible.com/post/162346490883/how-do-yo...
Thaxll 9y ago
Pretty much like Rust users, they advertise Postgres for everything, even usage pattern that don't make sense.
Thaxll 9y ago
Pretty much like Rust users, they advertise Postgres for everything, even usage pattern that don't make sense.
macdice 9y ago
Author of PostgreSQL SKIP LOCKED feature here. I gave a talk about queues in PostgreSQL covering the question "why do this in a database", for what it's worth: https://www.pgcon.org/2016/schedule/track/Applications/929.en.html https://www.pgcon.org/2016/schedule/track/Applications/929.e... Mostly the same arguments made by commentators here. SKIP LOCKED exists in several major RDMSs. We borrowed Oracle's syntax and MySQL has just done the same. The Gray transaction processing book calls it "read past" isolation.
- strictfp 9y ago
  "...you like PostgreSQL enough to attend a conference". Future classic right there. (Not being sarcastic, I loved that joke)
mianos 9y ago
Also Skype created pgpool to use Postgresql as a queue at massive scale. As much it is often called an 'anti-pattern', it is durable and can massively scale way beyond the likely size of most companies, unless they need more scale than Skype.
- mianos 9y ago
  oops, misstype, although pgpool also exists, the queing framework is pgqueue.
atombender 9y ago
In addition to the many good replies: One thing I like to point out whenever this comes up is that you should aim for a hybrid solution. Don't use a queue to store state, use it to coordinate. Databases are great at dealing with transactional state, queues are good at sequentially handing out data in order. For example: Say we're generating reports. First you create a table "report_tasks", each row representing a report to be generated. Let each task have a status field and a description of what's to be done or whatever. You can use foreign keys here. Then create the rows, and for each row, publish a queue message with the ID of your row. The queue consumer then needs to read the town by its ID, do the necessary processing and update the status. You can of course also do things like avoid duplicate processing by looking at the status field. What this solves is introspection/queriability. Queues are for the most part opaque (although Kafka at least keeps the queue around even after processing). This allows you to know exactly what work is pending and what has been completed, and do things like periodically retry the tasks or even preemptively cancel them. With RabbitMQ alone this is much more difficult, since you can't look into the queue without republishing the contents. The best you can do is to funnel ACKed messages into another queue, but you can't see what's pending.
bsder 9y ago
> Why use a database as a queue? It is known this doesn't scale well nor work particularly well. PostgreSQL scales to 100K writes per second on my laptop. I can probably get another x10 from a big cloud instance. You can probably run 99+% of real applications in that range.

swah 9y ago

> Why use a database as a queue? To avoid running a second system.

dom0 9y ago

> Why use a database as a queue? Already have a central, configured and monitored server and need "just a small queue" for something. This is not per se a bad decision. For the same reason it doesn't have to be a bad idea to cache things in the main database, instead of using a dedicated cache like Redis.

IgorPartola 9y ago
This. Also, things like RabbitMQ are complex and their durability properties can be different than those provided by your RDBMS. This can get problematic if you are mixing tasks in the queue that have different priorities. For example, emailing the invoice to a client should not fail silently and should happen at most once. Same with a notification from your doctor that you need to call to discuss your test results. Tossing that into an ephemeral queue is probably not the best solution. Having said that, RabbitMQ does have a ton of settings where you can turn durability up/down as much as you want.
- wbl 9y ago
  Email is unreliable. You should number invoices, and tell recipients to ignore duplicates.
BurningFrog 9y ago
In my experience, the cost of adding and maintaining another storage subsystem to a project is often hugely underestimated. It's easy to see the benefits and ignore the costs. If I can solve a problem reasonably well by adding a table to my Postgres DB, that will always beat out adding the specialized ScrewdriverDB that does it perfectly.
- mbell 9y ago
  Depends on what you're adding. Running Redis is dead simple and easy to have visibility into. RabbitMQ / Kafka are much larger undertakings.
  - brianwawok 9y ago
    RabbitMQ is on docker, its more or less the same work to launch as Redis these days. (It can take a bit more tuning so I think it is unfair to say it is the SAME work, but it is seriously not a huge deal to run RabbitMQ in the post docker world)
    - true_religion 9y ago
      Is Docker really necessary for this? I've never had any trouble just installing RabbitMQ from the package manager, and running it with only a tiny bit of initial configuration.
      - brianwawok 9y ago
        Well, if for example your entire infrastructure is in K8s, then yes you kind of need docker. Redis vs RabbitMQ stateful-set.yaml would be me or less the same, and something like 20 lines of yaml. I was never a big fan of docker for running some thing on a server, but once you start to go down the K8s path, it finally makes sense.
  - LoSboccacc 9y ago
    Depends on the scale mostly. People often forget how not every project is Google/Facebook/Twitter.
emmelaich 9y ago
Yep. Better asked as "Why use a conventional|relational database as a queue?" Because all queues have to be databases i.e. have the same qualities / assurances as databases.
tyingq 9y ago
It also makes it easier to delete or update the queue entry in an atomic transaction that spans other tables. If that has value for the specific use case.
- Smudge 9y ago
  Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.
  - ruslan_talpa 9y ago
    you can still have the notion of a transaction while integrating rabbitmq like this you do your mutations in a transaction and also in that transaction you execute a NOTIFY command. If transaction is successful, the notify will go through at the end of the transaction. the notify events and be "bridged" to a messaging server like rabbitmq (see my other comment)
    - balfirevic 9y ago
      What if your network has a glitch when you try to write to RabbitMQ after you receive NOTIFY? You will lose that message, correct?
      - ruslan_talpa 9y ago
        see my comment above
    - Smudge 9y ago
      Nice -- pg-amqp-bridge seems like a clever solution for coordinating the two. It still puts some amount of the queuing load on the DB, but I'd be comfortable with that trade-off if I really wanted to use rabbitmq as the primary queue. Question though -- Does it guarantee at-least-once or at-most-once on the NOTIFY? (Like, if there is a network blip, will it retry the NOTIFY?) And if it is at-least-once, I assume that consumer apps will have to handle deduplication/idempotency.
brianwawok 9y ago
> Already have a central, configured and monitored server and need "just a small queue" for something. Fine. So use SQS or cloud pub sub. Both take 0 "server configuration work", and you aren't adding load to likely the single most expensive part of your infrastructure (RDBMS). (The exception to where a RDBMS is not the most expensive part of your infastructure is where you have very large data with either nosql something, or a machine learning GPU array.. but not sure that is super relevant here)
- dom0 9y ago
  That's an entirely valid line of reasoning, but it only applies to a certain set of applications. Further, SQS or whatever have the same drawbacks as other external queues compared to an in-DB queue; see all the sibling comments in this big thread.

ruslan_talpa 9y ago

Well, maybe because most application don't need/have the scale that require a messaging server? A lot of projects i would say have a 80/20 read/write split, thus, there are not so many events and they don't need the complexity of rabbitmq when the db can handle a few events. Having said all that, we did actually build a tool to connect together postgresql and rabbitmq :) https://github.com/subzerocloud/pg-amqp-bridge https://github.com/subzerocloud/pg-amqp-bridge When you get to a big number of events or maybe there are lots of types and they all need to be routed in complex ways (real time updates) then indeed, rabbitmq is the right tool And this component enables you to execute NOTIFY in a trigger or stored procedure and have that message sent to rabbitmq.

true_religion 9y ago
When you have a messaging server.... you can start using it to help parallelize all kinds of tasks. Things you wouldn't previously think about because they'd take days or even weeks to complete, even in parallel, can all run on your infrastructure now. Spinning up 20 or even a 100 new workers is easy, and follows the same principle as just spinning up 1 or 2. That's been my experience. Before we did everything in the DB, and implicitly ignored the mere possibility of doing some tasks because we knew it'd be too hard with the DB as the event store.
- brianwawok 9y ago
  Agreed. To keep it simple day 1, on a single box put your entire stack. 1 Django web server 1 redis queue (can both caching and work queue and results queue) 1 Celery process for async tasks 1 PostgreSQL instance (You can do the same with other stacks, I just know them less well) Fairly standard stack. You can find preconfigured setups that can get this going in 10 minutes or less. And boom, you just made a stack that can scale from a 1 user POC to a 10 million user product. This lasts until you get past 1 box stage.. and at that point you split it up by function. At that point you can go to 1 celery worker to 1000 celery workers by just pushing a button to make more boxes. Seems a pretty good setup. Vs say, trying to skip the redis part (which took all of 4 minutes) and writing a bunch of database logic, and then you have to rewrite it all down the road. I am all for start simple. But start simple with a path to the future. If your path to the future is "rip this all out and rewrite it", you should at least ponder what you are doing. Did I REALLY save that much time by using my database as a message queue??

BenoitEssiambre 9y ago

There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance and may result in silent conflicts or corruption when different services read and update the same data. DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed over multiple network hops having uncertain timing, congestion and reliability. The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network. Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server. Of course there are concerns about scalability with a monolith. Manually sharding monoliths is a bit of work but it forces you to do your homework and find the natural and optimal sharding points. In a lot of cases, there are natural separation lines such as between groups of customers that you can use to shard and scale things up. Blindly distributing all data across a bunch of nodes has huge performance and reliability implications. A cpu's connection to it's ram and disks is orders of magnitude more reliable and fast than connections over the network. Unless you are building something like a social network where everything is connected to everything, you don't need things to default to run over clusters and over the network.

brianwawok 9y ago
> There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance What? Most job queues are inherently to do something outside of the database. For example: I need to send some emails, or I need to resize some images. You cannot wrap sending an email in the same transaction as your queue work, nor can you wrap resizing some images in the same transaction. The ENTIRE reason you are using a message queue is to go to some external work. So this literally makes no sense. > and may result in silent conflicts or corruption when different services read and update the same data. You need to program this in one way or another no matter what. If you programmed your code to do "silent conflicts or corruption" then I guess you are going to be in trouble. So don't do that. > DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed over multiple network hops having uncertain timing, congestion and reliability. Again, you are missing the point. MOST job queue work is stuff outside of the database anyway. You still have stuff outside the database. > The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network. Not that relevant. If you are doing 10,000 messages a second (or more!) to your job queue, and are looking to hold open a bunch of transactions, you are going to be in for some pain. > Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server. And trying to use PostgreSQL as a job queue is going to give you 1% or .1% of using RabbitMQ or Kafka or SQS or Cloud Pub Sub as a job queue. You are trying too hard to use a database for the wrong thing.

mbell 9y ago

Why would you use Kafka as a queue? It's not a great fit, I mean, you could make it work, but it's a similar square peg in round hole design choice as using a relational DB as a queue.

rpedela 9y ago
I thought that was one of the primary use cases? Is that not true?
- mbell 9y ago
  Kafka's primary use case is as an intermediary for a stream processing system. e.g. to ingest a firehouse of events, make them durable, then feed them to a number of different processors. More common use cases here: https://kafka.apache.org/uses https://kafka.apache.org/uses. You _could_ use it's data model to implement a 'job queue', but it's not a great fit for a number of reasons.
- barrkel 9y ago
  Think of Kafka as a replayable append-only log. It's tuned for ingestion of enormous quantities of data; it's best at buffering big data, or perhaps as an infinitely growing log of everything that happens in a system, than simply communication in a distributed system. It doesn't have back pressure and its namespacing is primitive (ie DIY with prefixes, and avoid Confluent libraries that assume they're the only client in the system).
brianwawok 9y ago
Touche. I think you are right. Kafka can work as a queue like a RDBMS can work as a queue, but it's not a great idea.

jlg23 9y ago

> Why use a database as a queue? Quote from the article: "You don’t need to import a large and complex 3rd party app or library to implement a queue, and you don’t need to deal with the key mapping and namespace issues with advisory locking."

brianwawok 9y ago
That is a strawman though. "Why would you need to buy an expensive screwdriver? Your hammer will do a just dandy job of putting in screws"
- int_19h 9y ago
  Well, if the hammer does actually do a good enough job of putting in screws for your use case, what's the problem? And let's be honest: for most people, it's good enough, scalability-wise. "You are not Google" and all that.

elygre 9y ago

Why would it not work well? Oracle has built their OracleAQ [1] in the database, and it works quite nicely. It can be accessed using native APIs, or a JMS api if that is your thing. Also, having the queue in the database gives you easy access to the queue for management of all sorts -- SQL gives you full access to the queue data. [1]: http://www.oracle.com/technetwork/database/oracleadvancedqueuingwp-2524215.pdf http://www.oracle.com/technetwork/database/oracleadvancedque...

brianwawok 9y ago
Oracle sells the license to use their products at 500k per server. of COURSE they are going to build products to use it. Doesn't matter if it's a subpar choice, they just made 500k per server. I am not sure it makes sense to compare some guy throwing some messages in a queue or in postgreSQL to someone spending millions to get steak dinners from Oracle and use Oracle products.

deleted 9y ago

[deleted]

znep 9y ago

The biggest reason I've found for using a database as a queue is that you can actually run queries against it, both writes and reads. This is very useful when different entries in the queue can have some relationship that matters when processing. Do you want to wait 30 minutes after an item is queued then process all queued items for a given entity (eg. user) at once? Do you want to support more complex retry logic, such as efficiently handling some subset of jobs failing due to external dependencies being unavailable while others can complete? Do you want to be able to track what jobs are in progress (doesn't really work with skip locked), and even have the ability to update their status while processing for finer grained tracking? Do you want to be able to effectively monitor the contents of the queue beyond just the size (and some queues make it quite inefficient just to get that)? Some of these are possible with purpose made queueing systems but, in my experience, even if they are they can be quite inefficient, complicated, and are often poorly tested and supported areas of functionality. For example the last time I tried to use JMS selectors with ActiveMQ (selectors let you filter messages based on a subset of SQL) it was a nightmare both performance wise and functionality wise and I wished I just had a database that was actually built to support SQL. Other points about the cost of introducing a new technology (especially if you need strong durability, availability, etc.) and being able to update your database in the same transaction are also valid, but can often be dealt with by some extra effort. Don't get me wrong, if you just need a queuing system the first thing to consider is a system designed for that purpose, especially if you aren't very knowledgeable about databases and have basic requirements. It can be nuanced to correctly and efficiently use a general purpose database as queue. At the end of the day, however, keep in mind that under the hood a queueing system is just a database that exposes a limited API that makes it easier to get basic operations right and hopefully easier to scale (sadly I have seen all too many queuing systems that utterly fail at that part) at the expense of flexibility.

codesnik 9y ago

this article recently posted on HN is addressing this http://programmingisterrible.com/post/162346490883/how-do-you-cut-a-monolith-in-half http://programmingisterrible.com/post/162346490883/how-do-yo...

Thaxll 9y ago

Pretty much like Rust users, they advertise Postgres for everything, even usage pattern that don't make sense.

Thaxll 9y ago

Pretty much like Rust users, they advertise Postgres for everything, even usage pattern that don't make sense.

macdice 9y ago

Author of PostgreSQL SKIP LOCKED feature here. I gave a talk about queues in PostgreSQL covering the question "why do this in a database", for what it's worth: https://www.pgcon.org/2016/schedule/track/Applications/929.en.html https://www.pgcon.org/2016/schedule/track/Applications/929.e... Mostly the same arguments made by commentators here. SKIP LOCKED exists in several major RDMSs. We borrowed Oracle's syntax and MySQL has just done the same. The Gray transaction processing book calls it "read past" isolation.

strictfp 9y ago
"...you like PostgreSQL enough to attend a conference". Future classic right there. (Not being sarcastic, I loved that joke)

mianos 9y ago

Also Skype created pgpool to use Postgresql as a queue at massive scale. As much it is often called an 'anti-pattern', it is durable and can massively scale way beyond the likely size of most companies, unless they need more scale than Skype.

mianos 9y ago
oops, misstype, although pgpool also exists, the queing framework is pgqueue.

atombender 9y ago

In addition to the many good replies: One thing I like to point out whenever this comes up is that you should aim for a hybrid solution. Don't use a queue to store state, use it to coordinate. Databases are great at dealing with transactional state, queues are good at sequentially handing out data in order. For example: Say we're generating reports. First you create a table "report_tasks", each row representing a report to be generated. Let each task have a status field and a description of what's to be done or whatever. You can use foreign keys here. Then create the rows, and for each row, publish a queue message with the ID of your row. The queue consumer then needs to read the town by its ID, do the necessary processing and update the status. You can of course also do things like avoid duplicate processing by looking at the status field. What this solves is introspection/queriability. Queues are for the most part opaque (although Kafka at least keeps the queue around even after processing). This allows you to know exactly what work is pending and what has been completed, and do things like periodically retry the tasks or even preemptively cancel them. With RabbitMQ alone this is much more difficult, since you can't look into the queue without republishing the contents. The best you can do is to funnel ACKed messages into another queue, but you can't see what's pending.

bsder 9y ago

> Why use a database as a queue? It is known this doesn't scale well nor work particularly well. PostgreSQL scales to 100K writes per second on my laptop. I can probably get another x10 from a big cloud instance. You can probably run 99+% of real applications in that range.