Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres is a great pub/sub and job server (2019)

webapp.io

Post reply on HN
Print view View original

11–20 of 209 posts

Re: Postgres is a great pub/sub and job server (2019)

#11

Post by merb »

daenz wrote: post #6

Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers. 0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig... EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.

well rabbitmq is really really hard to setup correctly and stuff like priority, time based scheduling are not that much easier than rabbitmq. in fact a queue adds more complexity and it is not necessary until you outscale your database. not saying that rabbitmq might be a better fit, it's just not a good fit to start with. if you have a small team < 8 it's better to stay with as few things as possible and especially with things you know (well).

Re: Postgres is a great pub/sub and job server (2019)

#12

Post by montroser »

This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long.

But once you get even just up to say, 40 messages per second with 100 worker processes, you're now up to 4000 updates per second just to see which worker got to claim which job, and up from there becomes untenable.

Re: Postgres is a great pub/sub and job server (2019)

#13

Post by paxys »

kasey_junk wrote: post #3

Largely agree at the scale this article is working with. But frankly, if 10k/s inserts is the scale you are talking about even worrying about a pub/sub solution seems odd. Introducing something like Kafka for anything less than an order of magnitude more than that seems like an architectural blunder. By the time you are there Postgres will have obviously disqualified itself.

What does scale have to do with it? Pub/sub as an architectural pattern could be equally relevant for your use case whether there are a hundred users in your system or a billion.

And Kafka isn't the only solution for it. There are many lightweight pub/sub and queuing systems which also don't involve needlessly adding abstraction layers and application code into an RDBMS.

Re: Postgres is a great pub/sub and job server (2019)

#14

Post by daenz »

merb wrote: post #11
daenz wrote: post #6

Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers. 0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig... EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.

well rabbitmq is really really hard to setup correctly and stuff like priority, time based scheduling are not that much easier than rabbitmq. in fact a queue adds more complexity and it is not necessary until you outscale your database. not saying that rabbitmq might be a better fit, it's just not a good fit to start with. if you have a small team < 8 it's better to stay with as few things as possible and especially…

I wouldn't recommend setting up your own message queue infrastructure either. The cloudamqp link was more about the content than the product. All cloud providers come with extremely simple, scalable, and inexpensive message queue services with bindings to most languages.

A message queue is one of those things that is easy enough and worth the effort to do "right" early on, because it is not something you want to rip out and rewrite when you hit your scaling bottlenecks, given how critical it is and how many things it will end up touching.

Re: Postgres is a great pub/sub and job server (2019)

#15

Post by merb »

daenz wrote: post #14
merb wrote: post #11

Earlier quoted context omitted.

well rabbitmq is really really hard to setup correctly and stuff like priority, time based scheduling are not that much easier than rabbitmq. in fact a queue adds more complexity and it is not necessary until you outscale your database. not saying that rabbitmq might be a better fit, it's just not a good fit to start with. if you have a small team < 8 it's better to stay with as few things as possible and especially…

I wouldn't recommend setting up your own message queue infrastructure either. The cloudamqp link was more about the content than the product. All cloud providers come with extremely simple, scalable, and inexpensive message queue services with bindings to most languages. A message queue is one of those things that is easy enough and worth the effort to do "right" early on, because it is not something you want to rip…

well most cloud queues do not support priorities you can only create multiple subscriptions and prefer the messages from the higher one. so in the end you would built a system on a system anyway. also these queues lock you in quite hardly (and do not work on premise)

Edit: also keep in mind most queues do not like "slow consumers" i.e. if your workload is bursty with long processing times, a database might be a better fit (i.e. rabbitmq does not like it)

Edit2: we implemented a queue with postgres since we need acid and having 10k inserts per second is highly unlikely since a customer upload takes longer than a second (we deal with files) we mostly have burst workloads short period of high volume followed by long pauses (i.e. nobody uploads stuff at night)

Re: Postgres is a great pub/sub and job server (2019)

#16

Post by EdwardDiego »

petilon wrote: post #10

Just because something can be used to do something doesn't mean it should. Kafka is specifically designed for this purpose, it is free, and it is easy to learn and use. If "starting with Postgres and then switching out when the time comes" saves money then I can understand. Otherwise use the right tool for the right job, right from the start.

I've been working with Kafka since 0.8, I mildly beg to differ on "easy to learn and use", just based on the fact that to use it well, you have to design your applications for its semantics, and that tuning it requires a lot of indepth understanding of its mechanics.

And I've seen a looot of bad designs and misconfigurations.

All that said, I'm a massive fan of Kafka, I'm the first to admit it's a complex tool, but it needs to be for the problem space it targets.

Re: Postgres is a great pub/sub and job server (2019)

#17

Post by peterhunt »

daenz wrote: post #6

Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers. 0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig... EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.

The scalability problem is way overblown. Setting the correct isolation level and locking mode isn't that hard and a modern cloud-hosted PG/MySQL can push 10s of thousands of inserts/s no problem.

IMO, the downsides of hosting a queue inside your primary relational DB are very much outweighed by the downsides of 1) having to run a new piece of infra like rabbit and 2) having to coordinate consistency between your message queue and your relational DB(s)

Re: Postgres is a great pub/sub and job server (2019)

#18

Post by renewiltord »

daenz wrote: post #6

Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers. 0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig... EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.

In the end, engineering is just whether the thing works with minimal maintenance. And ultimately, I've had great experiences using DBaaQ for low volume data movement. It works as a persistent queue with easy to do retries etc.

For high throughput (we had ad tech servers with 1E7 hits/s) we used a home-built low-latency queue that supported real time and persisted data. But for low throughput stuff, the DBaaQ worked fine.

And ultimately, maybe it was a lack of imagination on our part since Segment was successful with a mid-throughput DBaaQ https://segment.com/blog/introducing-centrifuge/

Re: Postgres is a great pub/sub and job server (2019)

#19

Post by colinchartier »

Author here! A few updates since this was published two years ago:

- The service mentioned (now called https://webapp.io ) eventually made it into YC (S20) and still uses postgres as its pub/sub implementation, doing hundreds of thousands of messages per day. The postgres instance now runs on 32 cores and 128gb of memory and has scaled well.

- We bolstered Postgres's PUBLISH with Redis pub/sub for high traffic code paths, but it's been nice having ACID guarantees as the default for less popular paths (e.g., webhook handling)

- This pattern only ever caused one operational incident, where a transaction held a lock which caused the notification queue to start growing, and eventually (silently) stop sending messages, starting postgres with statement_timeout=(a few days) was enough to solve this

Previous discussion: https://news.ycombinator.com/item?id=21484215

Happy to answer any questions!

Re: Postgres is a great pub/sub and job server (2019)

#20

Post by ramraj07 »

montroser wrote: post #12

This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long. But once you get even just up to say, 40 messages per second with 100 worker processes, you're now up to 4000 updates per second just to see which worker got to claim which job, and up from there becomes untenable.

Why jump from Postgres to Kafka? Celery + rabbitmq or redis is a great middle ground.
Post reply on HN