Postgres is an acceptable relational database / nosql database / pub/ sub/ job server / blockchain.
System design hack: Postgres is a great pub/sub and job server
31–40 of 162 posts
Re: System design hack: Postgres is a great pub/sub and job server
#32I'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)
How is MySQL not OSS?
Re: System design hack: Postgres is a great pub/sub and job server
#33I'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?
SQL Server has Service Broker (my prize winner for most undervalued and unknown feature in a mainstream software product) that does a tremendously good job of solving these types of problems. I can't say how well it scales, but it has done a reasonable job at every workload I've thrown at it.
Re: System design hack: Postgres is a great pub/sub and job server
#34Earlier quoted context omitted.
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)
How is MySQL not OSS?
There’s MySQL EE and the CLA that allows it to exist, I guess.
Re: System design hack: Postgres is a great pub/sub and job server
#35Isn'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 can have 3 queues and 3 dispatchers, arrange some backpressure, a push-based consumer-producer model and you will have 3 queues, 300 queries and n polls per interval.
There is a solution to every perceived problem.
Re: System design hack: Postgres is a great pub/sub and job server
#36I agree. I have prototyped this in the past, but our current pub/sub was not painful enough for us to go full steam ahead with PG.
However, my design was more bare bones. I was picking jobs by chaining CTE's that did the status update as they returned the first element of the queue.
Re: System design hack: Postgres is a great pub/sub and job server
#37Keep in mind that a notify performed when nobody is listening is lost. The workers need then to catch up.
Re: System design hack: Postgres is a great pub/sub and job server
#38Earlier quoted context omitted.
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)
How is MySQL not OSS?
It is somewhat split. Not all MySQL features are open source (enterprise edition feature). Postgres is completely open source.
Re: System design hack: Postgres is a great pub/sub and job server
#39Earlier quoted context omitted.
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.
at this point why not use something like rabbitmq and not reinvent it in postgres.
Re: System design hack: Postgres is a great pub/sub and job server
#40Postgres is an acceptable relational database / nosql database / pub/ sub/ job server / blockchain.
I'd say it's the most acceptable of the pre-packaged relational databases, and the best option to extend once pre-packaged software is not enough.
It's also the easiest one to start with, excelling in all the mainstream categories for relational databases.