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

51–60 of 209 posts

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

#51

Post by hermanradtke »

andrew_ wrote: post #44

I just spent two months unrolling an unruly pg_boss implementation and that experience has soured me on using postgres for pubsub, jobs, or messaging. For my money, Github Actions is fine for infrequent cron jobs, or hell even a tiny lambda with a CloudWatch rule is super cheap, with infra as code to make it easy (relatively so) for anyone to deploy. After all, I'd rather spend the majority of my time on writing code…

I have mostly great results using pg_boss. There was one upgrade which had a major bug, but the response was quick and the work around was easy.

The benefit of having a distributed cron that is version controlled and type checked in my repo has been amazing. I am also confident that my pg_boss implementation has higher uptime than GitHub actions.

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

#52

Post by rockwotj »

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

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

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

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

#53

Post by mdavidn »

vlmutolo wrote: post #8

I had thought about using postgres as a job queue before, but I couldn't figure out in my head how to make sure two processes didn't both take the same job. The "FOR UPDATE" and "SKIP LOCKED" were the keys to make this work in the article. Essentially, as far as I can tell, "SELECT FOR UPDATE" locks the rows as they're selected (locks are apparently visible outside the transaction), and "SKIP LOCKED" skips over rows…

If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.

Of course, the database client and server together form a distributed system. The client might continue processing a job under the mistaken impression that it still holds the lock. Jobs still need to be idempotent, as with the streaming platforms.

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

#54

Post by [deleted] »

staticassertion wrote: post #41

So SKIP LOCKED is a pretty well worn optimization at this point. People have been doing this for a while. What kind of TPS are people seeing on queues based on psql? edit: https://gist.github.com/chanks/7585810 10k/s here, but that was on a postgres from years ago - there have been like 4 or 5 major versions since then I think. That's a good amount and I'm betting you can push it forward. Further, a queue is triviall…

[deleted]

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

#55

Post by SahAssar »

boomskats wrote: post #26
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…

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

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

#56

Post by SahAssar »

daenz wrote: post #6

Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers. 0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig... EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.

Basically all of those reasons are solved by using LISTEN/NOTIFY and FOR UPDATE SKIP LOCKED, which every queue built on pg will use.

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

#57

Post by dreyfan »

boomskats wrote: post #26
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…

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

It’s such a low throughput requirement I think even bitcoin could support it.

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

#58

Post by staticassertion »

mdavidn wrote: post #53
vlmutolo wrote: post #8

I had thought about using postgres as a job queue before, but I couldn't figure out in my head how to make sure two processes didn't both take the same job. The "FOR UPDATE" and "SKIP LOCKED" were the keys to make this work in the article. Essentially, as far as I can tell, "SELECT FOR UPDATE" locks the rows as they're selected (locks are apparently visible outside the transaction), and "SKIP LOCKED" skips over rows…

If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried. Of course, the database client and server together form a distributed system. The client might continue processing a job under the mistaken impression that it still holds the lock. Jobs still need to be idempotent, as with the streaming platforms.

This assumes that you're creating a transaction per message, which I think is not advisable.

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

#59

Post by NavinF »

daenz wrote: post #40
simonw wrote: post #31

Earlier quoted context omitted.

I'm increasingly of the opinion that relational databases are absolutely the right way to build queue systems for most projects. One of the biggest advantages comes when you start thinking about them in terms of transactions. Transactional guarantees are really useful here: guarantee that a message will be written to the queue if the transaction commits successfully, and guarantee that a message will NOT be written t…

The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.

Oh that happens fairly often. In fact, some message will be lost every time your queue server reboots due to a power outage, PSU failure, kernel panic, OOM, etc. (Unless it spends almost all of its time idle in which case I guess no messages will be in flight)

You’re guaranteed to break the invariant sooner or later so you end up with all the usual complexity of keeping stuff in sync.

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

#60

Post by Spivak »

ramraj07 wrote: post #20
montroser wrote: post #12

This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long. But once you get even just up to say, 40 messages per second with 100 worker processes, you're now up to 4000 updates per second just to see which worker got to claim which job, and up from there becomes untenable.

Why jump from Postgres to Kafka? Celery + rabbitmq or redis is a great middle ground.

And RabbitMQ is a solid middle ground that you can scale like crazy.
Post reply on HN