this is the equivalent of solving Advent of Code with Postgress
Postgres is a great pub/sub and job server (2019)
121–130 of 209 posts
Re: Postgres is a great pub/sub and job server (2019)
#122Earlier quoted context omitted.
Dude you are seriously underestimating postgres' versatility. It does so many different things, and well!
I'm not underestimating anything. I am advocating for the right tool for the job. I have a hard time believing, despite the skewed sample size in this thread, that most people think using postgres as a message queue for most cases makes the most sense.
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 scale, but they work fine for what is needed and scale relatively cleanly with postgres itself.
Besides, in a lot of environments corporate will only approve the use of certain tools. And if you already have one approved that does the job, then why not?
Re: Postgres is a great pub/sub and job server (2019)
#123Earlier quoted context omitted.
Dude you are seriously underestimating postgres' versatility. It does so many different things, and well!
I'm not underestimating anything. I am advocating for the right tool for the job. I have a hard time believing, despite the skewed sample size in this thread, that most people think using postgres as a message queue for most cases makes the most sense.
[deleted]
Re: Postgres is a great pub/sub and job server (2019)
#124Earlier 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?
Yeah, every home IoT hub processes more messages than that with less thsn raspberri pi worth of compute
I certainly appreciate the sentiment though I'm pretty sure I don't have the same reliability and uptime guarantees on my little Rpi3/MQTT/NodeRed/SQLite/ESP8266 home system :-)
That said, it's been running for upwards of 4 years and accumulated an insane number of temperature readings inside and above heating vents (heat source is heat pump)
SELECT count() as count FROM temperatures : msg : Object { _msgid: "421b3777.908118", topic: "SELECT count() as count FROM …", payload: 23278637 }
Ok, I need therapy for my data hoarding - 23 million temp samples is not a good sign :-)
Re: Postgres is a great pub/sub and job server (2019)
#125Earlier 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.
Why should I rely on yet another microservice when I have PostgreSQL right there?
Re: Postgres is a great pub/sub and job server (2019)
#126Author 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 should've clarified, the database handles more than just the "regular" pub/sub, some of the tables have over a billion rows.
Re: Postgres is a great pub/sub and job server (2019)
#127I think the key concept here is atomicity. If some API is responsible for creating a job, storing it in the database AND publishing it can never be an atomic operation. Both the database and pub/sub servers are separate network connections from the application server. For example, if you save the record first and then publish, It's quite possibe that you save the record in the database and then lose the connection to the pub/sub server when publishing. If this happens, you can never know if the pub/sub server received the request and published it. In systems where it's critical to guarantee that a record was saved and guarantee that it was published as well, the only way to do that is by using a single external connection - in this case to a Postgres DB. We've used the same setup on an AWS RDS t2.medium machine to process over 600 records/second.
Re: Postgres is a great pub/sub and job server (2019)
#128A single core small Redis server can do wonders. For fire and forget type jobs you can use lists instead of pub/sub: save a job to a list by a producer, pop it on the other end by a consumer and execute it. It's also very easy to scale, just start more producers and consumers. We're currently using this technique, to process ~2M jobs per day, and we're just getting started. Redis needs very little memory for this, ju…
Beware of the scale up challenges with Redis. Redis can only utilize a single core. If you do anything sophisticated that needs to be atomic then you can't scale out to multiple servers, and you can't scale up to multiple cores. At least with Postgres you can scale up trivially. Postgres will efficiently take advantage of as many cores as you give it. For scale out you will need to move to a purpose built queuing sol…
Good point. My assumption is that the first hit would be memory usage, way before core usage.
There are many options for scaling:
- vertically scale by adding more memory
- start redis instance on another port (takes 1mb) if decided to add more cores on the same vm
- separate data into another vm
- sharding comes out of the box, but that would be my last resort
Re: Postgres is a great pub/sub and job server (2019)
#129This 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.
How many connected users does this scale to roughly?
Re: Postgres is a great pub/sub and job server (2019)
#130Earlier quoted context omitted.
I'm not underestimating anything. I am advocating for the right tool for the job. I have a hard time believing, despite the skewed sample size in this thread, that most people think using postgres as a message queue for most cases makes the most sense.
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 concept of "most cases" because it's an anecdote. Queuing systems are a thing for a reason, and in most cases, using them makes more sense than writing your own.