9 ms·
Author here! A few updates since this was published two years ago: - The service mentioned (now called https://webapp.io https://webapp.io ) eventually made it
by colinchartier 5y ago
Author here! A few updates since this was published two years ago:
- The service mentioned (now called https://webapp.io https://webapp.io ) eventually made it into YC (S20) and still uses postgres as its pub/sub implementation, doing hundreds of thousands of messages per day. The postgres instance now runs on 32 cores and 128gb of memory and has scaled well.
- We bolstered Postgres's PUBLISH with Redis pub/sub for high traffic code paths, but it's been nice having ACID guarantees as the default for less popular paths (e.g., webhook handling)
- This pattern only ever caused one operational incident, where a transaction held a lock which caused the notification queue to start growing, and eventually (silently) stop sending messages, starting postgres with statement_timeout=(a few days) was enough to solve this
Previous discussion: https://news.ycombinator.com/item?id=21484215 https://news.ycombinator.com/item?id=21484215
Happy to answer any questions!
- anonu 5y agoThanks for the great blog post - still relevant after a few years! > statement_timeout=(a few days) wouldnt you want this to be a few seconds or minutes? Maybe I miss the point of setting this to days...
- colinchartier 5y agoDidn't want to deal with ramifications of statement timeouts in a complex system, the failure mode mentioned (queue filling up) happened on the scale of 6 weeks, so it was very cheap operationally to set this timeout to some high value.
- runeks 5y agoSo, just to make sure I understand correctly: notifications are delivered while the notification queue size is increasing (due to the transaction holding a lock), and it doesn’t become a problem until the queue size reaches its maximum, at which point it causes dropped notifications? But the queue grows precisely because some notifications aren’t getting delivered, right?
- colinchartier 5y agoSince it's pub/sub, you just need one misbehaving client that LISTENs in a transaction to have problems, the other clients can still receive and process the NOTIFY event
- boomskats 5y ago> doing hundreds of thousands of messages per day > The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. Am I the only one?
- colinchartier 5y agoSuch a server is 400$/mo, a backend developer that can confidently maintain kafka in production is significantly more expensive!
- macksd 5y agoI think the point of interest was 32 cores to handle what sounds like 10 messages per second at most. That's not really a ton of throughput... It's certainly a valid point that an awful lot of uses cases don't need Twitter-scale firehoses or Google-size Hadoop clusters.
- rowanG077 5y agoIt said nothing about the distribution of traffic. It might well be thousands and thousands of pub sub messages at some point of the day and 0 for others.
- colinchartier 5y agoAh, the database does a lot more than just pub/sub - especially since the high traffic pub/sub goes through redis. I guess my point was that we never regretted setting up postgres as the "default job queue" and it never required much engineering work to maintain. For an example, it handles stripe webhooks when users change their pricing tier - if you drop that message, users would be paying for something they wouldn't receive.
- rockwotj 5y agoFwiw I don't know the shape of the data, but I feel like you could do this with Firebase for a few bucks a month...
- ledgerdev 5y agoI've always been curious, what kind of latency do you see between an insert, and when the notify goes out over the channel?
- colinchartier 5y ago10ms or so
- moneywoes 5y agoSilly question but how does this compare to SQS? More cost friendly I assume?
- rmbyrro 5y agoSQS vs "Postgres Queue", I think mainly: - Closed/lock-in vs. Open/lock-free - Rigid data access pattern vs. Very flexible SQL access -Managed by AWS vs. Managed by you/your team (although you could use one of those managed Postgres services to reduce ops burden) - Integrates well with other AWS services (e.g. Lambda, SNS, DynamoDB, etc) vs. No integrations with AWS ecossystem out of the box
- fnord77 5y agoI'd be curious to know what the drawbacks of using PG for a pub/sub server are.
- dirkt 5y agoWhat are the options to use Postgres pub/sub with Java? Because the usual Java libraries don't seem to support the pub/sub functionality well, you have to actively poll when you want to subscribe.
- paulryanrogers 5y agoThis may depend on the JDBC driver support Listen/Notify. Though if queue traffic is relatively steady then maybe polling isn't so bad?
- oftenwrong 5y agohttps://impossibl.github.io/pgjdbc-ng/docs/0.8.9/user-guide/#extensions-notifications https://impossibl.github.io/pgjdbc-ng/docs/0.8.9/user-guide/... or https://github.com/pgjdbc/r2dbc-postgresql#listennotify https://github.com/pgjdbc/r2dbc-postgresql#listennotify
- saberience 5y ago"hundreds of thousands of messages per day" This is not much load at all, an iPhone running RabbitMQ could process many millions of messages per day. Even 1M messages per day is only 11 messages per second average. i.e. not taxing at all.
- bob1029 5y agoI've built software that can process millions of messages per second on a single thread. I find it amusing that we happily play these AAA gaming experiences that are totally fantastical in their ability to deal with millions of things per frame and then turn around and pretend like hundreds of thousands of things per day is some kind of virtue.
- almog 5y agoWhat was the isolation level used when that incident occurred?
- colinchartier 5y agoThe default postgres one, serializable (if I remember correctly?)
- oftenwrong 5y agoThe default isolation level in postgres is read committed.
- matt2000 5y agoI very happily use this technique and I believe I found out about it from your original blog post. Thanks for the original writeup and for the update on how it's going a few years in!
- Justsignedup 5y agoI assume you use polling workers looking for the next job to grab for themselves? Personally I do see the niceness of having a good pattern implemented using existing technology. Less deployment nonsense, less devops, less complexity, a few tables at most. I've done similar things in the past, it is nice. For anyone who'd criticize, having complex deployments can be just about as much dev time, AND if implemented well, they can theoretically covert this whole thing to rabbitmq with minimal effort just by swapping the queueing system. In any case, happy to see people mentioning how using existing simple tech can lead to fairly simple to manage systems, and still solve the problems you're trying to solve.