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

121–130 of 162 posts

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

#121

Post by gspetr »

nostrademons wrote: post #120
rhizome wrote: post #114

Earlier quoted context omitted.

All the replies to this and no mention of the workflow pattern! I guess Google went ahead and NIH'ed it too, to boot! Surely some of those people had programmed a workflow before, somewhere along the line. I'm not going to say "I don't know why anybody is surprised," but I'll tell you that I'm surprised! This is good, you're all one of 10,000![1] I'd call parent's a "basic" workflow, but Wikipedia seems to tag it as…

This was actually for a few of my post-Google projects. At Google we would just use MapReduce regardless of how inappropriate it was because of the difficulty of standing up an RDBMS on Google's cloud infrastructure, plus CPU cycles were basically free for engineers. Maybe it's different now that internal projects are encouraged to use Google Cloud; Cloud SQL + Compute Engine works just fine for this. And this is def…

> CPU cycles were basically free for engineers.

So what happened to a guy who had the bright idea to do CPU mining on Google's infra?

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

#122

Post by jordic »

We use a lot this kind of tooling.. say, you need to check 20k URLs and you want to rate limit them.. add them to a Pg table (with state and result fields). A single thread worker that just takes a row (marks it as pending) and later updates it. With select for update and skip tricks you can horitzontal scale it to the number of workers you need.

I had seen it also for soft that sends massmail (our case around 100k/day).. it's state is a postgres queue.

We also use Pg for transactional mail. We insert it on a table. (There is a process that sends the row mails).. the so nice part is that the mail is joining the dB transaction for free.. (all or nothing)

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

#123

Post by wahern »

nostrademons wrote: post #69
nurettin wrote: post #65

Earlier quoted context omitted.

>> spin up a bunch of worker processes as EC2 spot instances that check for the next PENDING task Does that mean workers poll the database?

Yup, but they do so only once per shard (well, twice, once at the beginning and once at the end). If you've got a big job where each shard takes a few minutes to process and a hundred or so workers, the DB gets about 1 req/sec, which is well within the capabilities of Postgres.

Postgres provides LISTEN/NOTIFY for precisely this sort of use case.

https://www.postgresql.org/docs/9.1/sql-notify.html

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

#124

Post by nurettin »

nostrademons wrote: post #118
nurettin wrote: post #115

Earlier quoted context omitted.

I would lean towards maintaining a simple list on redis and letting the workers pop the next job from it whenever they are done.

That works fine too. The advantage of Postgres is that the master is a single point of failure, and if Postgres goes down all the state is persistent and you can just bring it back up again. Also, ad-hoc query tools are a little better for Postgres, which gives you a really simple interface to inspect the status of the job and the partial results. This architecture originally evolved from an Amazon-SQS based queue sy…

To me, the message queue is simply a substitute for polling the DB, not a way of persisting message status. That is a job for the DB.

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

#125

Post by rumanator »

xmprt wrote: post #78
Thaxll wrote: post #76

Earlier quoted context omitted.

Adding postgres there and there where it's clearly the wrong tool is a bad advice.

I probably would have gone with something like Kafka in this case, but it seems like Postgres serves them pretty well even if it's the "wrong" tool. If it works, fits your current needs, leads to faster development time, and isn't needlessly slow, then I say go for it.

Picking Postgres for alternative applications such as pub/sub services also simplifies both deployment and development. You'll already be using Postgres for persistence, so why waste time deploying another set of services and develop another set of clients just to get the same functionalities?

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

#126

Post by Twisell »

cle wrote: post #66
colinchartier wrote: post #61

Earlier quoted context omitted.

- postgres doesn't maintain a queue for notify/listen, it's purely pubsub. I'm not quite sure how the backpressure could make it to the database - A lot of use cases involve not dropping messages after they are processed (like CI jobs, in this example), so you don't have to vacuum the rows - If you're comfortable with SQS there's no really big reason to switch, but it makes it so that your project can only run on ama…

https://www.postgresql.org/docs/current/sql-notify.html > There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. My understanding of MVCC (correct me if I'm wrong), is e…

Please notice the usual wisdom of PostgreSQL developers "(8GB in a standard installation)" meaning they probably set up a config parameter that you can tweak if you ever reach this limit while scaling.

This is an often untold superpower of PostgreSQL when arguing about how it supposedly "don't scale well". You often have a large array of optimizations available before your project really scale out of scope.

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

#127

Post by londons_explore »

Hacks like this work at first, but long running transactions and postgres don't do well together.

After a few weeks of running on a multi-TB table, you'll find the dead tuples massively outnumber the live tuples, and database performance is dropping faster than the Vacuum can keep up. Vacuum is inherently single-threaded, while your processes making dead tuples as part of queries are multi-threaded, so it's obviously the vacuum that fails first if most queries are long running and touch most rows. Your statistics will get old because they're also generated by the vacuum process, making everything yet slower.

Even if you can live with gradually dropping performance, eventually the whole thing will fail when your txids wrap around and the whole database goes read-only.

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

#128

Post by ansible »

londons_explore wrote: post #127

Hacks like this work at first, but long running transactions and postgres don't do well together. After a few weeks of running on a multi-TB table, you'll find the dead tuples massively outnumber the live tuples, and database performance is dropping faster than the Vacuum can keep up. Vacuum is inherently single-threaded, while your processes making dead tuples as part of queries are multi-threaded, so it's obviously…

Long running transactions? I didn't think the article implied doing something like that. The job state changes shouldn't take long to execute.

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

#129

Post by ubu7737 »

codesnik wrote: post #87
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.

me too. I was surprised it wasn't that common at the time.

Right, I was working mostly off one very well-written article on the topic, it was an easy search because there is really only one correct way to implement atomic job-handling with PG.
Post reply on HN