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

181–190 of 209 posts

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

#181

Post by Dowwie »

nicoburns wrote: post #167
Dowwie wrote: post #159

Earlier quoted context omitted.

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…

Agree with your point about multiple tools being good enough, but IMO firebase is not one of them. In my experience despite it claiming to be excellent at scaling, it performs worse than even a small Postgres instance. It’s good at the “real-time subscriptions”, but that’s about it.

Noted. Thanks for sharing your experiences with that. We need to hear more about lackluster investments in tech.

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

#182

Post by paulryanrogers »

kgeist wrote: post #133
aneeshd16 wrote: post #127

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…

>If some API is responsible for creating a job, storing it in the database AND publishing it can never be an atomic operation. We use transactional outbox for that - we insert a record about the event into the event table in the same transaction as the rest of the operation (providing atomicity), and then a special goroutine reads this table and pushes new events to the message broker on a different server. In our de…

Distributed transactions are hard. And if queue data is the source of truth for something then they must be durable. All that said, when traffic volume, payload size, or throughout demands something specialized then it makes sense to do it.

If I'm building one shed then maybe I only need 5 tools, while a shed factory might use 100. Context matters.

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

#183

Post by ClumsyPilot »

brodouevencode wrote: post #177
earleybird wrote: post #124

Earlier quoted context omitted.

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: "421b37…

Curious as to why you aren’t tracking that with a time series database?

The IoT hubs are an embedded system, built with a minimal memory footprint and overhead, 512 mb of ram is typical, sometimes less. Here is an example: https://www.gl-inet.com/products/gl-s1300/

That means you can't have docker and different versions of Java, node and .Net all running in parallel.

You run a single process and Sqlite is a library that allows SQL operations and database to be inbuilt. You 'budget' is like 100 mb of Ram, becauae other stuff has to run too.

All the time-series databases I know are a large, memory hungry hippo, built for distributed compute/kubernetes. Just very different usecase. If one was built with minimalism in mind, then it could be used.

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

#184

Post by colinchartier »

runeks wrote: post #169
colinchartier wrote: post #28

Earlier quoted context omitted.

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.

So, just to make sure I understand correctly: notifications are delivered while the notification queue size is increasing (due to the transaction holding a lock), and it doesn’t become a problem until the queue size reaches its maximum, at which point it causes dropped notifications? But the queue grows precisely because some notifications aren’t getting delivered, right?

Since it's pub/sub, you just need one misbehaving client that LISTENs in a transaction to have problems, the other clients can still receive and process the NOTIFY event

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

#185

Post by colinchartier »

ledgerdev wrote: post #86
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've always been curious, what kind of latency do you see between an insert, and when the notify goes out over the channel?

10ms or so

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

#186

Post by colinchartier »

almog wrote: post #161
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…

What was the isolation level used when that incident occurred?

The default postgres one, serializable (if I remember correctly?)

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

#187

Post by oftenwrong »

dirkt wrote: post #120
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…

What are the options to use Postgres pub/sub with Java? Because the usual Java libraries don't seem to support the pub/sub functionality well, you have to actively poll when you want to subscribe.

https://impossibl.github.io/pgjdbc-ng/docs/0.8.9/user-guide/...

or

https://github.com/pgjdbc/r2dbc-postgresql#listennotify

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

#188

Post by AtlasBarfed »

kpmah wrote: post #158

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.

But one of the golden rules of databases is to not use them as queues/integration.

Granted I didn't even read the main article because it seems like such a casual headline.

Edit post-read: yeah, using it as a CI jobs database. He lists the alternatives, but seriously, Kafka? Kafka is for linear scaling pub/sub. This guy has a couple CI jobs infrequently run.

Sure this works if the entire thing is throwaway for a non critical pub/sub system.

"It's possible to scale Postgres to storing a billion 1KB rows entirely in memory - This means you could quickly run queries against the full name of everyone on the planet on commodity hardware and with little fine-tuning."

Yeah just because it can does not mean it is suited for this purpose.

Don't do this for any integration at even medium scale.

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

#189

Post by marcosdumay »

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

I guess the point is that the scale is actually not that large, but that's perfectly ok because most problems will never need that large scale either.

In fact, the article makes a very good point how just doing it in postgres is great, it doesn't really scale (because of ACID), and adapting it for scale after you need it will lead to a better design than what you would do if you started optimizing without any information.

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

#190

Post by gregors »

I know there used to be some O(n^2) logic associated with notify. Anyone know if that ever got fixed? Cursory search only turns up https://postgrespro.com/list/thread-id/2407396

  The benchmark shows that the time to send notifications grows 
  quadratically with the number of notifications per transaction without 
  the patch while it grows linearly with the patch applied and 
  notification collapsing disabled.
Post reply on HN