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

131–140 of 162 posts

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

#131

Post by z92 »

namanyayg wrote: post #111

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?

The key features used here for PG seems SKIP LOCKED, and the PG notification system, which informs all clients that are directly connected to the database that "this table has changed" so that they can restart their work.

The notification system comes with a cost. You can't scale up the number of connection to a PG database instance.

Therefore you need to go pooling.

And for SKIP LOCKED, I just checked and mysql/maria do have it.

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

#132

Post by seunosewa »

wahern wrote: post #123
nostrademons wrote: post #69

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

Postgres provides LISTEN/NOTIFY for precisely this sort of use case. https://www.postgresql.org/docs/9.1/sql-notify.html

Yes, any idea about why isn't it used in the original article above?

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

#134

Post by stephenr »

I'm not sure I quite follow this statement:

> In the list above, I skipped things similar to pub/sub servers called "job queues" - they only let one "subscriber" watch for new "events" at a time, and keep a queue of unprocessed events:

If your job queue only allows one single worker (even per named queue), I'd argue it's a shit job queue.

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

#135

Post by ibejoeb »

stephenr wrote: post #134

I'm not sure I quite follow this statement: > In the list above, I skipped things similar to pub/sub servers called "job queues" - they only let one "subscriber" watch for new "events" at a time, and keep a queue of unprocessed events: If your job queue only allows one single worker (even per named queue), I'd argue it's a shit job queue.

The article mentions Celery and Gearman.

I'm not here to defend celery generally, but it has no such limitation.

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

#136

Post by ergo14 »

ibejoeb wrote: post #135
stephenr wrote: post #134

I'm not sure I quite follow this statement: > In the list above, I skipped things similar to pub/sub servers called "job queues" - they only let one "subscriber" watch for new "events" at a time, and keep a queue of unprocessed events: If your job queue only allows one single worker (even per named queue), I'd argue it's a shit job queue.

The article mentions Celery and Gearman. I'm not here to defend celery generally, but it has no such limitation.

Yeah, we are running about 400 celery processes over 30 queues and at this point I think I would have noticed :)

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

#137

Post by gunnarmorling »

makkesk8 wrote: post #77
gunnarmorling wrote: post #54

Earlier quoted context omitted.

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

I fail to see how Debezium would solve this as it's just as likely Debezium wouldn't be watching the WAL as if you had a minimal number of workers always listening and those went down for some reason. Could you please elaborate?

Debezium stores the WAL position it has read ("offset"). So while it's down, of course it won't be able to read any events. But once it's restarted, it will continue to read the WAL from the last offset. So no events will be missed also when the connector isn't running for some time (WAL segments are retained by the DB until acknowledged by a logical decoding client such as Debezium).

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

#138

Post by zejn »

colinchartier wrote: post #30
2bitencryption wrote: post #28

I'm curious if the same holds true if you drop in Sqlite/MS Sql Server/Mysql. I.e. is this good advice because Postgres in particular is a great implementation of sql, or because sql in general is good enough to solve this problem, or a mix of the two?

Postgres in particular: - has strong performance vs, say, sqlite - has "channel" and "trigger" support so you can avoid polling (which either slows down your jobs or limits your number of workers) - is actually OSS (versus, say, mysql)

SQLite is significantly undervalued.

I agree that PostgreSQL has some very nice extensions and if you know you are going to have multiple users (or multiple processes using it), SQLite can't compete.

But if you only have one process using the DB, then SQLite is immune to N+1 query problem since everything is in-process and there are no round-trips.

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

#139

Post by blondin »

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.

do you mean that there should be something like a controller that can spin up workers and also get rid of those that hanged (or retry) etc?

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

#140

Post by stephenr »

ibejoeb wrote: post #135
stephenr wrote: post #134

I'm not sure I quite follow this statement: > In the list above, I skipped things similar to pub/sub servers called "job queues" - they only let one "subscriber" watch for new "events" at a time, and keep a queue of unprocessed events: If your job queue only allows one single worker (even per named queue), I'd argue it's a shit job queue.

The article mentions Celery and Gearman. I'm not here to defend celery generally, but it has no such limitation.

Right, I spent some time earlier this year researching a number of job queue systems for a client and never came across one that had this limitation, which kind of makes me wonder what exactly the article is trying to claim.
Post reply on HN