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

21–30 of 162 posts

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

#21

Post by colinchartier »

MuffinFlavored wrote: post #20
colinchartier wrote: post #3

Earlier quoted context omitted.

This pattern falls down if you need to poll the database, because if you have 3 queues and 100 workers you're making 300 queries per poll interval. The feature of postgres that makes this viable in comparison to most other databases is the "channel"

What if your listeners crashed/were down at the time of the `PUB` message? Does this mean the message falls into oblivion (since it will never receive a reply/ACK/get worked on)?

The pattern I follow is that workers immediately poll once when they start - this means that when your workers restart after crashing, they'll pick up any missing jobs.

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

#23

Post by evv »

MuffinFlavored wrote: post #19
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.

Can you show what your schema for your job table looked like?

Check this out: https://github.com/graphile/worker/blob/3540df5ab4eb73f846d5...

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

#24

Post by pm90 »

colinchartier wrote: post #21
MuffinFlavored wrote: post #20

Earlier quoted context omitted.

What if your listeners crashed/were down at the time of the `PUB` message? Does this mean the message falls into oblivion (since it will never receive a reply/ACK/get worked on)?

The pattern I follow is that workers immediately poll once when they start - this means that when your workers restart after crashing, they'll pick up any missing jobs.

I'm not seeing how this recovers from the failure scenario where a worker grabbed an event and then immediately crashed.

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

#25

Post by philips »

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.

It is really good! I started a Go implementation for a project I am working on https://github.com/philips/pg-go-queue

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

#26

Post by tjungblut »

pm90 wrote: post #24
colinchartier wrote: post #21

Earlier quoted context omitted.

The pattern I follow is that workers immediately poll once when they start - this means that when your workers restart after crashing, they'll pick up any missing jobs.

I'm not seeing how this recovers from the failure scenario where a worker grabbed an event and then immediately crashed.

You need some timeout and/or leasing mechanism to make it work in this scenario.

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

#27

Post by Rapzid »

pm90 wrote: post #24
colinchartier wrote: post #21

Earlier quoted context omitted.

The pattern I follow is that workers immediately poll once when they start - this means that when your workers restart after crashing, they'll pick up any missing jobs.

I'm not seeing how this recovers from the failure scenario where a worker grabbed an event and then immediately crashed.

The comment you are replying to wasn't addressing that scenario.

Typically you would implement visibility timeouts and other such stuff. Depending on the use case you could make specific optimizations or keep it generic and have SQS like semantics or something.

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

#28

Post by 2bitencryption »

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?

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

#29

Post by Rapzid »

gigatexal wrote: post #7
colinchartier wrote: post #6

Earlier quoted context omitted.

Yeah, but even without tuning, you can have hundreds of "sparse" queues with this pattern - you don't have to care about the frequency of insertions or deletions or the size of the rows.

I mean I agree I think with what I think you’re saying: as much as I love Postgres and SQL I’d much rather use Redis for this.

Obviously "it depends" but here are a few reasons one may want to use PostgreSQL instead:

1.) *They want to transactionally commit work along with the change that caused it

2.) They are already using Postgresql not using Redis

3.) Requiring users install yet another service(Redis) just for this one item isn't worth the costs

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

#30

Post by colinchartier »

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)

Post reply on HN