Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

System design hack: Postgres is a great pub/sub and job server

layerci.com

Post reply on HN
Print view View original

51–60 of 162 posts

Re: System design hack: Postgres is a great pub/sub and job server

#51

Post by Rapzid »

gigatexal wrote: post #42
Rapzid wrote: post #29

Earlier quoted context omitted.

Obviously "it depends" but here are a few reasons one may want to use PostgreSQL instead: 1.) *They want to transactionally commit work along with the change that caused it 2.) They are already using Postgresql not using Redis 3.) Requiring users install yet another service(Redis) just for this one item isn't worth the costs

Redis modifications are transactional: https://redis.io/topics/transactions Though I agree on points 2 and 3, especially 3 since it adds complexity.

Internally but if the change causing the work is in PostgreSQL Redis would need to support 2-phase commits to commit the change and the work together atomically(and durably).

Re: System design hack: Postgres is a great pub/sub and job server

#52

Post by davidbanham »

philips wrote: post #25
ubu7737 wrote: post #10

Used the "FOR UPDATE SKIP LOCKED LIMIT 1" trick to implement a job server in PG a few years ago for the first time. It's a great solution.

It is really good! I started a Go implementation for a project I am working on https://github.com/philips/pg-go-queue

I should really get around to writing some docs for this, but you may find this useful:

https://github.com/davidbanham/kewpie_go

I’ve been using it for years across multiple products.

I also built this for a client lately. It lets them interop their Haskell services with kewpie a bit easier without needing to write a native wrapper.

https://github.com/paidright/kewpie-http

Re: System design hack: Postgres is a great pub/sub and job server

#54

Post by gunnarmorling »

zyngaro wrote: post #37

Keep in mind that a notify performed when nobody is listening is lost. The workers need then to catch up.

This is where the Debezium connector for Postgres [1] comes in: it will retrieve change events from the TX log and push it to brokers such as Apache Kafka or Pulsar, or directly to your application. When not listening, any consumer will continue to read from where it left off before (applying "at least once" semantics).

Disclaimer: I work on Debezium

[1] https://debezium.io/documentation/reference/0.10/connectors/...

Re: System design hack: Postgres is a great pub/sub and job server

#55

Post by pjungwir »

I've done this before with good results.

I was pleased to see they are using `SELECT FOR UPDATE SKIP LOCKED`. That is what this 2nd Quadrant article recommends, which I think is required reading if you want to implement this yourself:

https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...

It goes into more detail about wrong ways to implement a queue and what the downsides are for its preferred approach.

Re: System design hack: Postgres is a great pub/sub and job server

#56

Post by fjp »

zrail wrote: post #13
theptip wrote: post #12

Earlier quoted context omitted.

Is there a similar library in Python? I really like the look of this approach, but don't want to build it myself if I can avoid it.

https://pypi.org/project/pq/ looks similar, although it looks like they use `SKIP LOCKED` instead of advisory locks. I'm not sure what the tradeoff is.

SKIP LOCKED is much simpler and doesn't really have any gotchas.

We had an internal library that used advisory locks which had all sorts of strange behavior we couldn't figure out until we just moved to SKIP LOCKED

Re: System design hack: Postgres is a great pub/sub and job server

#59

Post by cle »

At the scale I operate at, I wouldn't consider this a viable option. What's the backpressure like on NOTIFY/LISTEN? (Docs mention a maximum backlog of 8GB on the message queue, is that configurable? Monitorable?) Tons of constant churn on a table means we have to worry about vacuuming right? Now I have to monitor that too to make sure it's keeping up. Not to mention all the usual operational issues with running relational databases.

No thanks, I'll stick with GCP PubSub or AWS SQS, which are explicitly designed for this use case, and for which I have to setup no infrastructure.

Re: System design hack: Postgres is a great pub/sub and job server

#60

Post by ones_and_zeros »

nostrademons wrote: post #44

Another neat hack is to use Postgres as a quick & dirty replacement for Hadoop/MapReduce if you have a job that has big (100T+) input data but small (~1G) output data. A lot of common tasks fall into this category: generating aggregate statistics from large log files, searching Common Crawl for relevant webpages, identifying abusive users or transactions, etc. The architecture is to stick a list of your input shards…

If a worker fails or gets pre-empted, how does it retry anything? It's gone at that point, no?

Sounds like you'd end up with a bunch of dangling shards orphaned in WORKING state. And now you need timeouts and health checks and something to coordinate all that.

Post reply on HN