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

91–100 of 162 posts

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

#91

Post by takeda »

1996 wrote: post #62
directionless wrote: post #41

Postgres generally has a fairly low maximum connections. If you're running your own servers, you can adjust this, but in the cloud you may not be able to. For example, Google CloudSQL maxes at 1000, Heroku at 500. At that point, people usually start looking at the connection pooling tools. Depending on how much work you need from the DB, connections pools can be a win. Anyone know how connection pooling works with li…

Can't say about listeners. But connection poolers introduced more problems than I cared to fix. So now I routinely use 3000 connections, and have 9000 set up for peaks. It eats a little more ram but it is more stable.

connections aren't free[1] after reducing number of connections from 200 to 60 my commit throughput doubled.

[1] https://brandur.org/postgres-connections

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

#94

Post by ris »

ones_and_zeros wrote: post #60
nostrademons wrote: post #44

Another neat hack is to use Postgres as a quick & dirty replacement for Hadoop/MapReduce if you have a job that has big (100T+) input data but small (~1G) output data. A lot of common tasks fall into this category: generating aggregate statistics from large log files, searching Common Crawl for relevant webpages, identifying abusive users or transactions, etc. The architecture is to stick a list of your input shards…

If a worker fails or gets pre-empted, how does it retry anything? It's gone at that point, no? Sounds like you'd end up with a bunch of dangling shards orphaned in WORKING state. And now you need timeouts and health checks and something to coordinate all that.

I used to do something a little like this with postgres advisory locks. Worker takes out a lock that signifies it's working on a task, if the worker fails or loses its connection in any way the lock gets dropped along with the transaction being rolled back.

Example: https://gist.github.com/risicle/f4807bd706c9862f69aa

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

#95

Post by existencebox »

foou wrote: post #53

How do you manage the bloat?

Typically (at least when I've implemented variations of this pattern) a batch job run on a schedule performing something like "expire stale/completed jobs past a threshold."

If you needed the existence of completed jobs to compute future jobs, use a supplementary datastructure that stores spans or other heuristics, updated in a transaction with job completion

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

#96

Post by wheelerwj »

Thaxll wrote: post #76
davidw wrote: post #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.

Adding postgres there and there where it's clearly the wrong tool is a bad advice.

if it were clearly the wrong tool, you'd be right. But its clearly not the wrong tool for this specific situation.

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

#97

Post by dfox »

cle wrote: post #66
colinchartier wrote: post #61

Earlier quoted context omitted.

- postgres doesn't maintain a queue for notify/listen, it's purely pubsub. I'm not quite sure how the backpressure could make it to the database - A lot of use cases involve not dropping messages after they are processed (like CI jobs, in this example), so you don't have to vacuum the rows - If you're comfortable with SQS there's no really big reason to switch, but it makes it so that your project can only run on ama…

https://www.postgresql.org/docs/current/sql-notify.html > There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. My understanding of MVCC (correct me if I'm wrong), is e…

The LISTEN/NOTIFY queue has nothing to do with MVCC. Only reason why it exists is that postgresql synchronizes the notification events with transaction boundaries (ie. you get notifications only when you don’t have active transaction). Given this the only case when you would care about the depth of the notification queue is when your application is exceptionally misbehaved and in that case you will run into more critical problems (stale locks, MVCC bloat...) well before that will begin to be an issue.

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

#98

Post by dfox »

EvanAnderson wrote: post #33
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?

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.

IMO the reason why Service Broker is mostly unknown and undervaluated is the name itself. It sounds like something horribly complicated, while it in fact is relatively straightforward implementation of pub/sub.

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

#99

Post by Iv »

I'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...

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

#100

Post by codesushi42 »

Iv wrote: post #99

I'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.
Post reply on HN