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

101–110 of 209 posts

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

#101

Post by gurjeet »

osigurdson wrote: post #42

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

Supabase's Realtime [1] is one of the solutions that can help with that. Although it doesn't exactly let you LISTEN at scale, but it allows the applications to be notified on changes in the database.

> Listen to changes in a PostgreSQL Database and broadcasts them over WebSockets

[1]: https://github.com/supabase/realtime

Disclosure: I'm a Supabase employee.

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

#102

Post by gurjeet »

javajosh wrote: post #100
osigurdson wrote: post #42

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

>LISTEN ties up a connection Wait, what?? I can't keep doing things with my connection after I issue a LISTEN? That doesn't seem right! I would assume it would isomorphic to how unix-y bg programs will occasionally write to the console (although I see how this might be hard to deal with at the driver level). Now I will have to go check.

You (the application) can certainly go on to issue other database commands. See the docs [1].

> With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received.

[1]: https://www.postgresql.org/docs/current/sql-listen.html

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

#103

Post by edmundsauto »

marcosdumay wrote: post #45
johnisgood wrote: post #36

Earlier quoted context omitted.

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…

Is your point that the server has room to grow? Or that you just “ain’t impressed by that”?

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

#104

Post by tyingq »

boomskats wrote: post #26
colinchartier wrote: post #19

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

> 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?

I imagine their scaling problem isn't messages/day, it's probably lots of concurrent, persistent connections. And I don't think a connection pooler would work with this job queue setup.

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

#105

Post by tomc1985 »

daenz wrote: post #63
rockwotj wrote: post #52

Earlier quoted context omitted.

Fwiw I don't know the shape of the data, but I feel like you could do this with Firebase for a few bucks a month...

you 100% could, and this thread feels like the twilight zone with how many people are advocating for using a rdbms for (what seems like) most peoples queuing needs.

Dude you are seriously underestimating postgres' versatility. It does so many different things, and well!

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

#106

Post by tmountain »

boomskats wrote: post #26
colinchartier wrote: post #19

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

> 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?

People are jumping on this. Question is—do the resource requirements outlined align with usage you described, or is that combined workload? By combined workload, I mean working set plus messaging. It’s not a useful exercise to criticize a service that’s multifaceted based on a single use case. Full disclosure—-not a Postgres user, nor am I invested in the tech.

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

#107

Post by remram »

colinchartier wrote: post #33
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?

Such a server is 400$/mo, a backend developer that can confidently maintain kafka in production is significantly more expensive!

It's that much on a popular cloud platform, you can buy this for 3-4 times that amount and use it for years.

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

#108

Post by mlyle »

petilon wrote: post #50
jastr wrote: post #27

Earlier quoted context omitted.

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.

The very source we're talking about describes how to block until there is work to do -- listener.Listen("ci_jobs_status_channel")

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

#109

Post by mlyle »

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.

Basically every piece of this article's criticism is wrong as applied to the source / link above.

- No need to poll the database table

- No table-level locks and manual handling: row locks used for handling the work in progress

- "Manual cleanup" -- uhhh

Etc.

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

#110

Post by fnord77 »

colinchartier wrote: post #19

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

I'd be curious to know what the drawbacks of using PG for a pub/sub server are.
Post reply on HN