161–170 of 209 posts
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…
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.
Earlier quoted context omitted.
It's that much on a popular cloud platform, you can buy this for 3-4 times that amount and use it for years.
Got a 128gb 32 core xeon workstation sitting under my desk off eBay and it was $400
Earlier quoted context omitted.
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?
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.
And Elixir is especially well suited for this type of workload. I actually extracted out much of the Supabase Realtime library so that I could work with the data directly in Elixir[2]
[1]: https://github.com/supabase/realtime#why-not-just-use-postgr...
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.
Avoiding increasing operational complexity is really important, but for pub/sub we are using Redis. While this does add complexity, it is very little, because it is incredibly easy to install and maintain.
Earlier quoted context omitted.
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 f…
I 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…
Earlier quoted context omitted.
Thanks for the great blog post - still relevant after a few years! > statement_timeout=(a few days) wouldnt you want this to be a few seconds or minutes? Maybe I miss the point of setting this to days...
Didn't want to deal with ramifications of statement timeouts in a complex system, the failure mode mentioned (queue filling up) happened on the scale of 6 weeks, so it was very cheap operationally to set this timeout to some high value.
But the queue grows precisely because some notifications aren’t getting delivered, right?
Earlier quoted context omitted.
Not necessarily. Many languages like Java already have queuing libraries that operate on rdbms through JPA. So not even a single additional line needs to be written for this to work. We got ours working in a day and it works great. I don't know if other languages have these libraries but I'm inclined to believe they do (at least nodejs has).
Do you have to set up the triggers, tables, and procedures beforehand or how does that work?