I've simply been using MySQL/Maria everywhere and am meaning to switch, but I'm not sure what makes Postgres this much better.
Can this hack not be achieved by a mariadb table too?
System design hack: Postgres is a great pub/sub and job server
111–120 of 162 posts
Re: System design hack: Postgres is a great pub/sub and job server
#112Should the "ci_job_status" in the line be "ci_jobs" instead?
INSERT INTO ci_job_status(repositor ...
Re: System design hack: Postgres is a great pub/sub and job server
#113Can someone share their experience with scaling pg's NOTIFY and LISTEN? The use case I have in mind has a lot of logically separate queues. Is it better for each queue to have its own channel (so subscribers can listen to only the queue they need) or have all queues notify a global channel (and have subscribers filter for messages relevant to them). I am mainly confused about whether I need a dedicated db connection…
Based on what I have read and some experimentation, I concluded that it will not scale very well beyond ~500 listener processes because there needs to be one persistent connection per listener. Furthermore, my understanding is using pgbouncer will not help since it needs to use session pooling in order to support LISTEN.
Re: System design hack: Postgres is a great pub/sub and job server
#114Another 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…
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 "state-based."[2] I'm glad someone pointed out that failed steps do not update the db unless you have good traps in the process itself. I'd probably suggest a housekeeping process that resets any errors after some amount of time so that they're run through again from the beginning. As long as the error is surfaced in the meantime, that is.
Re: System design hack: Postgres is a great pub/sub and job server
#115Earlier 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.
I would lean towards maintaining a simple list on redis and letting the workers pop the next job from it whenever they are done.
Re: System design hack: Postgres is a great pub/sub and job server
#116Another neat hack would be to use Postgres as a graph db
Re: System design hack: Postgres is a great pub/sub and job server
#117If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que
Anyone know of a good Java library for PostgreSQL-backed queues using the "SKIP LOCKED" technique? I've scoured GitHub but can't seem to find anything.
Re: System design hack: Postgres is a great pub/sub and job server
#118Earlier quoted context omitted.
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.
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 system, where I'd stick the jobs in SQS and pop them off. Then I realized that I needed the DB anyway for my reducer-substitute, and if I'm writing SQL/ORM code anyway it was simpler to use the DB to manage the queue than to use SQS, and it gave me some other nifty abilities like being able to record timing & size stats with each shard, being able to query progress while the job is going, and being able to manually restart shards after correcting the errors that led to their failure. If you're running dozens-to-hundreds of workers, the cost of a t2 RDS instance (that's all you need for 1 QPS) is a rounding error, and a lot cheaper than EMR would be.
Re: System design hack: Postgres is a great pub/sub and job server
#119Earlier quoted context omitted.
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.
I used to do something a little like this with postgres advisory locks. Worker takes out a lock that signifies it's working on a task, if the worker fails or loses its connection in any way the lock gets dropped along with the transaction being rolled back. Example: https://gist.github.com/risicle/f4807bd706c9862f69aa
Yeah, in MySQL you can also do this with a named lock. Really like this workflow.
Re: System design hack: Postgres is a great pub/sub and job server
#120Another 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…
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 definitely not a new pattern - if you wanna go back in time, this is how things often worked in the mainframe era. My point - and IIUIC the point of the article - is that people often reach for flavor-of-the-month heavyweight frameworks when they can easily adapt tools they're already using to solve the problem within hours.
(Another great example of this is the single-process in-RAM architecture used by PlentyOfFish, StackOverflow, Mailinator, and Hacker News when they were solo-developer projects, where you store all state in RAM and run the whole site on a single box, periodically dumping out checkpoints to disk in case the process crashes. This can scale to thousands of requests per second - that's millions of queries per day - and is a lot simpler to program than 3-tier architectures where everything runs through a data abstraction layer and you spend 90+% of the code shuffling data around.)