Earlier quoted context omitted.
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?
It is used in the article.
System design hack: Postgres is a great pub/sub and job server
151–160 of 162 posts
Re: System design hack: Postgres is a great pub/sub and job server
#152I'll soon have to do a pub/sub for an application that's close to a multiplayer video game. Most advices I have seen say that I'll probably want to code it myself, but I was wondering about the latency of that solution? I'll likely have a SQL store and that would be a good argument to use postgres...
Use Redis pubsub instead.
Looking into it, it seems to have something like 2ms of latency which is good. Do you think it would be a good idea to use for exchanging, say, realtime position of players in a virtual world?
Re: System design hack: Postgres is a great pub/sub and job server
#153I did something very similar with MySQL since that was the DB we already had setup for data (not my choice). Basically any database that has atomic operations can do this. It definitely is way more convenient and cheaper to do use your existing infra for pub/sub and then only scale out to other services once performance becomes subpar. Although if you already have a messaging service up and running, it’s probably better to use that
Re: System design hack: Postgres is a great pub/sub and job server
#154Earlier quoted context omitted.
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
Redis modifications are transactional: https://redis.io/topics/transactions Though I agree on points 2 and 3, especially 3 since it adds complexity.
> Redis modifications are transactional
That's only true if they do not error—there is no "rollback" feature in Redis, and if you do error, anything you've done up to that point remains.
That's not transactional, it's more "you can, sometimes, execute a bunch of separate statement atomically".
Re: System design hack: Postgres is a great pub/sub and job server
#155Earlier quoted context omitted.
Redis modifications are transactional: https://redis.io/topics/transactions Though I agree on points 2 and 3, especially 3 since it adds complexity.
> Redis modifications are transactional That's only true if they do not error—there is no "rollback" feature in Redis, and if you do error, anything you've done up to that point remains. That's not transactional, it's more "you can, sometimes, execute a bunch of separate statement atomically".
I’ll admit I don’t know the internals of Redis as well as I do relational databases. I’ve learned much from this thread.
Re: System design hack: Postgres is a great pub/sub and job server
#156Earlier quoted context omitted.
It is somewhat split. Not all MySQL features are open source (enterprise edition feature). Postgres is completely open source.
Well, yeah, but then there's stuff like enterprisedb which is postgresql + some proprietary stuff on top, and the company employs some of the postgresql core developers. So in some sense it's "PostgreSQL Enterprise Edition" in all but name. I believe Citus DB(?) is something similar.
> I believe Citus DB(?) is something similar.
"Citus Unforks From PostgreSQL, Goes Open Source"
https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes...
https://news.ycombinator.com/item?id=11353322
As for mysql - I don't know why anyone wouldn't prefer mariadb - but I suppose it's conceivable mysql has features that makes it worth dealing with oracle licensing. But I doubt it.
Re: System design hack: Postgres is a great pub/sub and job server
#157Isn'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"
You should never need to poll. Polling is bad.
Re: System design hack: Postgres is a great pub/sub and job server
#158Earlier quoted context omitted.
Use Redis pubsub instead.
Looking into it, it seems to have something like 2ms of latency which is good. Do you think it would be a good idea to use for exchanging, say, realtime position of players in a virtual world?
Not with pubsub. You could use it as shared memory store for player states though, I guess.
Re: System design hack: Postgres is a great pub/sub and job server
#159Earlier 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.
We had an "interesting" bug where we inadvertently ended up polling postgres advisory locks around 6 million times per minute from a cluster of 200 servers. It added about 20% load to the database, enough to be anomalous but not enough to outright trip any alarms. The overall database performance was fine.
(That's 100,000 req/sec)
Was "the database" a single node, or multiple systems?
Re: System design hack: Postgres is a great pub/sub and job server
#160Earlier quoted context omitted.
All the replies to this and no mention of the workflow pattern! I guess Google went ahead and NIH'ed it too, to boot! Surely some of those people had programmed a workflow before, somewhere along the line. I'm not going to say "I don't know why anybody is surprised," but I'll tell you that I'm surprised! This is good, you're all one of 10,000![1] I'd call parent's a "basic" workflow, but Wikipedia seems to tag it as…
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…
>people often reach for flavor-of-the-month heavyweight frameworks when they can easily adapt tools they're already using to solve the problem within hours.
Absolutely, which is why I think it's important to call concepts the same thing they called it in the olden times if you know it's an old thing!