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

Post by foota » Fri, Nov 08, 2019, 10:13 PM UTC

ones_and_zeros wrote: post #60

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 by flavor8 » Fri, Nov 08, 2019, 10:31 PM UTC

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.

Post by taffer » Fri, Nov 08, 2019, 10:40 PM UTC

1996 wrote: post #62

directionless wrote: post #41

Postgres generally has a fairly low maximum connections. If you're running your own servers, you can adjust this, but in the cloud you may not be able to. For example, Google CloudSQL maxes at 1000, Heroku at 500. At that point, people usually start looking at the connection pooling tools. Depending on how much work you need from the DB, connections pools can be a win. Anyone know how connection pooling works with li…

Can't say about listeners. But connection poolers introduced more problems than I cared to fix. So now I routinely use 3000 connections, and have 9000 set up for peaks. It eats a little more ram but it is more stable.

Post by makkesk8 » Fri, Nov 08, 2019, 11:34 PM UTC

gunnarmorling wrote: post #54

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

Post by ubu7737 » Fri, Nov 08, 2019, 11:48 PM UTC

alberth wrote: post #47

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.

Why the “LIMIT 1”?