System design hack: Postgres is a great pub/sub and job server • Hacker News Board

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

#141

Post by WesternStar » Sat, Nov 09, 2019, 12:42 PM UTC

gspetr wrote: post #121

nostrademons wrote: post #120

Earlier quoted context omitted.

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?

He was politely asked to stop. I believe it was folding at home(?) if I remember the story I heard correctly.

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

#142

Post by rco8786 » Sat, Nov 09, 2019, 1:17 PM UTC

Interesting enough, but I’m not sure why this is called a hack. It’s a fully supported feature of Pg

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

#143

Post by retox » Sat, Nov 09, 2019, 2:44 PM UTC

fouc wrote: post #116

Another neat hack would be to use Postgres as a graph db

Check out AgensGraph, it's graph DB engine built on postgres. I haven't used it for much more than toy projects at this point though.

https://github.com/bitnine-oss/agensgraph

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

#144

Post by dplgk » Sat, Nov 09, 2019, 3:05 PM UTC

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…

Forgive my ignorance, how is this specific to postgres? Or what are the advantages of doing this in pg rather than mysql or redis?

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

#145

Post by BukhariH » Sat, Nov 09, 2019, 3:11 PM UTC

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…

That's a very similar pattern to what I've been using at TW.

At the core of our implementation is this library to schedule the jobs to different workers: https://github.com/kagkarlsson/db-scheduler

Would highly recommend that library!

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

#146

Post by earthboundkid » Sat, Nov 09, 2019, 3:21 PM UTC

davidw wrote: post #9

> It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes. This is pretty good advice in general.

PG is the default database. You can use something else if you need it, but PG is where you default to start with.

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

#147

Post by luord » Sat, Nov 09, 2019, 3:39 PM UTC

Postgres is now pretty much the ultimate nearly-all purpose backend, it seems. At this point, I won't need to use anything else.

And I'm more than perfectly fine with that.

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

#148

Post by ithkuil » Sat, Nov 09, 2019, 3:57 PM UTC

ithkuil wrote: post #70

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…

It reminds me of the taskmaster tool my team used at Google ( https://landing.google.com/sre/sre-book/chapters/data-proces... )

Care to explain the downvotes?

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

#149

Post by nostrademons » Sat, Nov 09, 2019, 4:43 PM UTC

dplgk wrote: post #144

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…

Forgive my ignorance, how is this specific to postgres? Or what are the advantages of doing this in pg rather than mysql or redis?

It's not really, although you really want to do it with a data store that supports transactional semantics. Postgres, MySQL with InnoDB,Redis, Amazon SQS, DynamoDB will all work. First time I used this pattern was with Amazon SQS; second time was MySQL with InnoDB tables. The article's about Postgres hacks, though, and Postgres is pretty commonly used for other parts of your system.

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

#150

Post by cle » Sat, Nov 09, 2019, 4:45 PM UTC

Twisell wrote: post #126

cle wrote: post #66

Earlier quoted context omitted.

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.

Or I can use a hosted queue service and not have to worry about it at all. The less I have to worry/configure/optimize, the less room for mistakes, and the more operationally solid my system will be.

My point isn’t that Postgres can’t do it. It can. But it just requires so much more effort to get it right. And this article glosses over so many critically important details about doing these things at scale in production environments. Most of them are things I don’t have to worry about when using a real queue service.