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

41–50 of 209 posts

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

#41

Post by staticassertion »

So SKIP LOCKED is a pretty well worn optimization at this point. People have been doing this for a while.

What kind of TPS are people seeing on queues based on psql?

edit: https://gist.github.com/chanks/7585810

10k/s here, but that was on a postgres from years ago - there have been like 4 or 5 major versions since then I think.

That's a good amount and I'm betting you can push it forward. Further, a queue is trivially sharded since messages are entirely isolated.

That said, Kafka can do hundreds of thousands if not millions of messages per second, so clearly there's room for optimizations.

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

#42

Post by osigurdson »

This seems to come up on HN at least once a year. Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Also, mitigation strategies like PgBouncer cannot be used with this approach (nor can scale out solutions like CitusDB I don't think).

Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres - this has improved in 14), this would be a very viable approach.

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

#43

Post by daenz »

merb wrote: post #15
daenz wrote: post #14

Earlier quoted context omitted.

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 bette…

>well most cloud queues do not support priorities you can only create multiple subscriptions

At least on GCP PubSub, a subscription is a separate concept from a topic/queue. If you want different priorities, you create multiple topics. You create multiple subscriptions when you want to fan out a single message to multiple workers. As far as I know, multiple subscriptions have nothing to do with priorities. Can you explain?

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

#44

Post by andrew_ »

I just spent two months unrolling an unruly pg_boss implementation and that experience has soured me on using postgres for pubsub, jobs, or messaging. For my money, Github Actions is fine for infrequent cron jobs, or hell even a tiny lambda with a CloudWatch rule is super cheap, with infra as code to make it easy (relatively so) for anyone to deploy. After all, I'd rather spend the majority of my time on writing code than being a DevOps. If I need pub/sub, I'd much rather use SNS quick and dirty, or SNS+SQS for the heavy lifts. Separation of concerns and separation of tiers is still important to me, and I have no desire to maintain a server moving forward.

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

#45

Post by marcosdumay »

johnisgood wrote: post #36
boomskats wrote: post #26

Earlier quoted context omitted.

> doing hundreds of thousands of messages per day > The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. Am I the only one?

Are you implying that given the specs, hundreds of thousands of messages per day is not good enough? I think you are, or at least that is what I was thinking myself.

Only for hundreds of thousands of messages per day, that's way too big of a server. But if you look on the rest of the thread, it doesn't do only that.

Anyway, for a server that only does pub/sub with ACID guarantees, those specs are so large that there is certainly a bottleneck before they matter. So it wouldn't be strange if somebody gets one that can't even handle that, it just would mean that there is some issue somewhere we don't see.

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

#46

Post by peterhunt »

daenz wrote: post #40
simonw wrote: post #31

Earlier quoted context omitted.

I'm increasingly of the opinion that relational databases are absolutely the right way to build queue systems for most projects. One of the biggest advantages comes when you start thinking about them in terms of transactions. Transactional guarantees are really useful here: guarantee that a message will be written to the queue if the transaction commits successfully, and guarantee that a message will NOT be written t…

The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.

If you're big enough to worry about the scalability of Postgres, you're big enough to experience this failure fairly often IMO.

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

#48

Post by jpgvm »

staticassertion wrote: post #32
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.

Kafka is not a queue. Kafka's parallelism is limited by the number of partitions you allocate, and you have to be sure to avoid head of line blocking. Not the case with a queue.

Exactly. If you do want something very scalable that fixes these problems but shares a lot of architectural similarity with Kafka then you should check out Apache Pulsar.

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

#49

Post by daenz »

peterhunt wrote: post #46
daenz wrote: post #40

Earlier quoted context omitted.

The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.

If you're big enough to worry about the scalability of Postgres, you're big enough to experience this failure fairly often IMO.

Scalability was the second of two concerns I listed. The first was additional application complexity that real message queues hide from you by virtue of being a system built for that usage pattern.

>you're big enough to experience this failure fairly often IMO

Please explain how? You would either have to suffer from frequent network connectivity issues that affects only your db and not your queue, or your process must be mysteriously dying in the microseconds between those 2 operations. Either of those cases are not something I would consider things that happen "fairly often," even if you were processing trillions of messages per day.

In my experience, the vast majority of message processing failures happen at the worker level.

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

#50

Post by petilon »

jastr wrote: post #27
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.

This is advice that seems reasonable but is actually pretty harmful. Take a startup with a few users. The senior engineer decides they need pub/sub to ship a new feature. With Kafka, the team goes to learn about Kafka best practices, choose client libraries, and learn the Kafka quirks. They also need to spin up Kafka instances. They ship it in a month. With postgres, they’ve got an MVP in a day, and shipped within a…

> With postgres, they’ve got an MVP in a day, and shipped within a week.

And the next week they realize they want reader processes to block until there is work to do. Oops that's not supported. Now you have to code that feature yourself... and soon you're reinventing Kafka.

Post reply on HN