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

151–160 of 209 posts

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

#151

Post by killingtime74 »

gurjeet wrote: post #101
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.

Does Supabase have HA or failover yet? Asked a few months ago and got no answer

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

#152

Post by kgeist »

Rolling your own pub/sub server implementation is often riddled with subtle concurrency bugs. For example, in our first iteration, when finding new events to dispatch, the implementation simply used the "last dispatched event ID" from the previous run. It sounded logical, because ID's are guaranteed to grow monotonically. But in MySQL, this approach is problematic under high load. There's a race condition: first, the server reserves a new autoincrement ID, and only then (in a different, non-atomic step) it inserts an actual row. So sometimes, the goroutine would retrieve a newer row skipping an older row, if two sessions were concurrently reserving autoincrement IDs (and you could get a wrong order of events, with ORDER BY id). We fixed that, but a month later another arcane concurrency bug was found due to wrong transaction model assumptions (I don't already remember the details). So if I was to choose between rolling your own implementation, and using an existing well-tested tool, now I'd choose the latter.

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

#153

Post by fanf2 »

daenz wrote: post #130
tomc1985 wrote: post #122

Earlier quoted context omitted.

What is your idea of 'most cases'? I've personally written real-time back-of-house order-tracking with rails and postgres pubsub (no redis!), and wrote a record synchronization queuing system with a table and some clever lock semantics that has been running in production for several years now -- which marketing relies upon as it oversees 10+ figures of yearly topline revenue. Neither of those projects were FAANG scal…

>some clever lock semantics Most senior+ engineers that I know would hear that and recoil. Getting "clever" with concurrency handling in your home-rolled queuing system is not something that coworkers, especially more senior coworkers, will appreciate inheriting, adapting, and maintaining. Believe me. I get that you're trying to flex some cool thing that you built, but it doesn't really have any bearing on the concep…

I guess the clever lock semantics are SKIP LOCKED, which is designed to support efficient queues. The cleverness is inside PostgreSQL rather than in the application, other than the cleverness of knowing about this feature. https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...

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

#156

Post by Lhiw »

edmundsauto wrote: post #103
marcosdumay wrote: post #45

Earlier quoted context omitted.

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

The point is that the update doesn't really tell us much about how well the approach scales despite aiming to do so.

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

#157

Post by saberience »

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…

"hundreds of thousands of messages per day"

This is not much load at all, an iPhone running RabbitMQ could process many millions of messages per day. Even 1M messages per day is only 11 messages per second average. i.e. not taxing at all.

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

#158

Post by kpmah »

To spell out good reasons for doing this:

If you're already using Postgres, you can avoid increasing operational complexity by introducing another database. Less operational complexity means better availability.

You can atomically modify jobs and the rest of your database. For example, you can atomically create a row and create a job to do processing on it.

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

#159

Post by Dowwie »

daenz wrote: post #132
foepys wrote: post #125

Earlier quoted context omitted.

Why should I rely on yet another microservice when I have PostgreSQL right there?

Everything is a nail, why should I use anything but this hammer?

I think you're helping bring balance to the enthusiasm here for using Postgres as a multi-purpose tool. However, there is a lot of room for you and the advocates favoring Postgres to both be right about tooling. I adopted RabbitMQ because I decided I didn't want to grow into needing it by dealing with many of the problems that motivated engineers to bring RabbitMQ into existence. However, I probably would have been fine with Postgres-pubsub, or Redis-pubsub/streams, both databases that I already used for their general purpose and have established capabilities for messaging. I noticed your earlier agreement with the person who mentioned using Firebase, and Firebase is yet another multi-purpose tool good enough at many things but still not better than the customized domain systems. If you agree with the claim for Firebase, others can now agree about Supabase. It's all Postgres beneath, though.

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

#160

Post by boomskats »

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

I should've clarified, the database handles more than just the "regular" pub/sub, some of the tables have over a billion rows.

Thank you. That makes a lot more sense and explains the value of the approach much better.
Post reply on HN