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

81–90 of 162 posts

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

#81

Post by privateSFacct »

xmprt wrote: post #78
Thaxll wrote: post #76

Earlier quoted context omitted.

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

I probably would have gone with something like Kafka in this case, but it seems like Postgres serves them pretty well even if it's the "wrong" tool. If it works, fits your current needs, leads to faster development time, and isn't needlessly slow, then I say go for it.

I have some light experience with this. If you have the PostgreSQL hammer you sometimes think - I really should be using something else, but this hammer works with a lot of nails - and the use cases that postgresql works well enough on keep increasing.

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

#82

Post by soumyadeb »

Geat post!! At Rudder (open-source segment), we used Postgres as our streaming layer in a similar fashion. It has been super stable in production, very easy to setup and we easily get to > 10K events/sec insertion performance.

The code is open-sourced here in case anyone wants to re-use

https://github.com/rudderlabs/rudder-server/blob/master/jobs...

We had to built additional logic to clean-up old jobs (similar to Level merges in similar queing systems)

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

#83

Post by faizshah »

theptip wrote: post #12
zrail wrote: post #11

If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que

Is there a similar library in Python? I really like the look of this approach, but don't want to build it myself if I can avoid it.

I was looking into python task queuing recently, here's some other choices from my notes:

https://github.com/malthe/pq - postgres queue based on rq and ruby queue_classic

https://github.com/coleifer/huey - sqlite, redis and in memory by coleifer (peewee creator). Possible to implement postgres storage layer simply.

https://github.com/closeio/tasktiger - flexible redis-based python task queue alternative to celery

https://dramatiq.io/ - actor based python job queue on redis/rabbitMQ

https://github.com/GoogleCloudPlatform/psq - gcp pub/sub based task queue

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

#84

Post by privateSFacct »

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)?

Is the activity wrapped in a transaction?

If so if worker crashes you get a connection timeout on postgresql and it rolls back the transaction and releases row locks - then next select SKIP picks it up.

If you want fast response with safety listen and on restart do a poll always to pick up anything that's ready that you might have missed a pub for. If you want lots of coverage do a poll every 10s - postgresql can handle it, you still get immediate response by listening to notify.

If you need fancier approaches you can do a last_updated time on the job and or watch waiting on pg_stat_activity or similar etc - but all totally overkill I think - the simple approach get's pretty far (not an expert though at all).

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

#85

Post by directionless »

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.

It's a good route, if you're running your own. I'm mostly in clouds, and they've got lower limits

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

#86

Post by takeda »

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…

as a person that had to maintain a hadoop cluster, well it's def not quick, but I can't imagine postgres being dirtier.

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

#88

Post by [deleted] »

xmprt wrote: post #78
Thaxll wrote: post #76

Earlier quoted context omitted.

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

I probably would have gone with something like Kafka in this case, but it seems like Postgres serves them pretty well even if it's the "wrong" tool. If it works, fits your current needs, leads to faster development time, and isn't needlessly slow, then I say go for it.

[deleted]

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

#89

Post by stubish »

cle wrote: post #59

At the scale I operate at, I wouldn't consider this a viable option. What's the backpressure like on NOTIFY/LISTEN? (Docs mention a maximum backlog of 8GB on the message queue, is that configurable? Monitorable?) Tons of constant churn on a table means we have to worry about vacuuming right? Now I have to monitor that too to make sure it's keeping up. Not to mention all the usual operational issues with running relat…

At scale, the first problem you will generally hit is that each subscriber needs to hold open a connection to the database, chewing precious database resources (1 process, a little RAM, maintenance overhead). Works great on smaller systems though. Hard to state where the cutoff is given it is a function of hardware capacity.

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

#90

Post by takeda »

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…

The reason for low connection limit is because every connection is a separate process. There are also various structures in postgres that are frequently traversed during various operations that large number of connections affect[1]. In my company we use aurora which supposedly was also modified connection handling to use threads to handle more connections. By default I think aurora is set to 500 connections.

In our case we had about ~200 connections to database. After we placed pgbouncer in front and reduced number of connections to 60. Our commit throughput doubled.

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

Post reply on HN