System design hack: Postgres is a great pub/sub and job server
1–10 of 162 posts
Re: System design hack: Postgres is a great pub/sub and job server
#2Isn't hijacking a DB as a "distributed" message queue a pretty well trodden path? Enterprises have been doing this for decades.
Re: System design hack: Postgres is a great pub/sub and job server
#3Isn't hijacking a DB as a "distributed" message queue a pretty well trodden path? Enterprises have been doing this for decades.
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"
Re: System design hack: Postgres is a great pub/sub and job server
#4Isn't hijacking a DB as a "distributed" message queue a pretty well trodden path? Enterprises have been doing this for decades.
Yeah but the propaganda around the "brave new world" solutions is so strong it makes it sound novel again.
Re: System design hack: Postgres is a great pub/sub and job server
#5Isn't hijacking a DB as a "distributed" message queue a pretty well trodden path? Enterprises have been doing this for decades.
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"
The caveat to this is in the first paragraph: all of the data is stored in memory id each row is ~1kb and the table size has some sort of TTL you’ll be good.
Re: System design hack: Postgres is a great pub/sub and job server
#6Earlier 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"
The caveat to this is in the first paragraph: all of the data is stored in memory id each row is ~1kb and the table size has some sort of TTL you’ll be good.
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.
Re: System design hack: Postgres is a great pub/sub and job server
#7Earlier quoted context omitted.
The caveat to this is in the first paragraph: all of the data is stored in memory id each row is ~1kb and the table size has some sort of TTL you’ll be good.
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.
Re: System design hack: Postgres is a great pub/sub and job server
#8Earlier 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.
I actually used redis pub/sub before switching to triggers - it is significantly easier to just:
INSERT INTO JOBS
INSERT INTO JOBS
redis-cli LPUSH job-queue job-id
Re: System design hack: Postgres is a great pub/sub and job server
#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.
Re: System design hack: Postgres is a great pub/sub and job server
#10Used 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.