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

91–100 of 209 posts

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

#91

Post by speed_spread »

threeseed wrote: post #67
colinchartier wrote: post #33

Earlier quoted context omitted.

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

But Kafka does significantly more. And if your needs are simpler like in this case then there are dozens of smaller pub/sub/queue systems that you could compare this to.

Limit the types of server used to reduce system complexity. If you can have all your business state in the same place, ops are much easier.

Kafka does more for streaming data, but doesn't do squat for relational data. You always need a database, but you sometimes can get by without a queuing system.

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

#92

Post by [deleted] »

jrussbowman wrote: post #90

Postgres is really just great for being able to build just about anything to get that first viable product built. It's basically the swiss army knife for anything data in my opinion. You got sql, nosql, job queues, full text indexing. It's great. I use it as a sql database and full text search for little personal project I work on off and on and it works great. I haven't touched it except to check every few weeks for…

[deleted]

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

#93

Post by k1w1 »

fouadf wrote: post #88

A 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 solution.

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

#95

Post by aniforprez »

jbverschoor wrote: post #73
SahAssar wrote: post #55

Earlier quoted context omitted.

I assume that is their main database for everything, not just for pub/sub. One of the big benefits of doing it that way is that you have proper transaction handling across jobs and their related data.

Come on man… you can run the whole thing off if a few Gb instance. Such a huge instance should be able to do about 100k a second!

Potential and actual usage aren't related. They might be having a lot of records and read/writes but maybe the actual pub/sub isn't that intensive. They seem to be using the same DB for everything

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

#96

Post by amarshall »

bjt wrote: post #76
amarshall wrote: post #5

It’s also possible to use advisory locks to implement a job queue in Postgres. See e.g. Que[1]. Note there are a fair number of corner cases, so studying Que is wise if trying to implement something like this, as well as some (a bit older) elaboration[2]. We implemented a similar design to Que for a specific use case in our application that has a known low volume of jobs and for a variety of reasons benefits from thi…

There's something even nicer than advisory locks, as of a few years ago. https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...

Some comments from brandur on SKIP LOCKED in https://github.com/brandur/sorg/pull/263. I haven’t looked into it too much since what we have works without any issues; but good to know, thanks!

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

#97

Post by cp9 »

petilon wrote: post #10

Just because something can be used to do something doesn't mean it should. Kafka is specifically designed for this purpose, it is free, and it is easy to learn and use. If "starting with Postgres and then switching out when the time comes" saves money then I can understand. Otherwise use the right tool for the right job, right from the start.

> Kafka is specifically designed for this purpose, it is free, and it is easy to learn and use

I think Kafka is great, but it is absolutely not “easy to learn and use”.

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

#98

Post by moneywoes »

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…

Silly question but how does this compare to SQS? More cost friendly I assume?

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

#99

Post by moneywoes »

threeseed wrote: post #67
colinchartier wrote: post #33

Earlier quoted context omitted.

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

But Kafka does significantly more. And if your needs are simpler like in this case then there are dozens of smaller pub/sub/queue systems that you could compare this to.

Briefly what are some mandatory kafka use cases?

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

#100

Post by javajosh »

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.

Post reply on HN