Comment by colinchartier
5 years ago
Author here! A few updates since this was published two years ago:
- The service mentioned (now called https://news.ycombinator.com/item?id=21484215
Happy to answer any questions!
5 years ago
Author here! A few updates since this was published two years ago:
- The service mentioned (now called https://news.ycombinator.com/item?id=21484215
Happy to answer any questions!
> 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?
I should've clarified, the database handles more than just the "regular" pub/sub, some of the tables have over a billion rows.
Thank you. That makes a lot more sense and explains the value of the approach much better.
I assume that is their main database for everything, not just for pub/sub. One of the big benefits of doing it that way is that you have proper transaction handling across jobs and their related data.
Come on man… you can run the whole thing off if a few Gb instance. Such a huge instance should be able to do about 100k a second!
4 replies →
Such a server is 400$/mo, a backend developer that can confidently maintain kafka in production is significantly more expensive!
I 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.
2 replies →
Fwiw 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...
21 replies →
But Kafka does significantly more.
And if your needs are simpler like in this case then there are dozens of smaller pub/sub/queue systems that you could compare this to.
3 replies →
It's that much on a popular cloud platform, you can buy this for 3-4 times that amount and use it for years.
3 replies →
That's the job of a DevOps engineer not a backend developer attempted to be overworked.
It probably fits within the free tier limits of a managed pubsub service.
I imagine their scaling problem isn't messages/day, it's probably lots of concurrent, persistent connections. And I don't think a connection pooler would work with this job queue setup.
Yeah, every home IoT hub processes more messages than that with less thsn raspberri pi worth of compute
I certainly appreciate the sentiment though I'm pretty sure I don't have the same reliability and uptime guarantees on my little Rpi3/MQTT/NodeRed/SQLite/ESP8266 home system :-)
That said, it's been running for upwards of 4 years and accumulated an insane number of temperature readings inside and above heating vents (heat source is heat pump)
SELECT count() as count FROM temperatures : msg : Object { _msgid: "421b3777.908118", topic: "SELECT count() as count FROM …", payload: 23278637 }
Ok, I need therapy for my data hoarding - 23 million temp samples is not a good sign :-)
3 replies →
Are you implying that given the specs, hundreds of thousands of messages per day is not good enough? I think you are, or at least that is what I was thinking myself.
Only for hundreds of thousands of messages per day, that's way too big of a server. But if you look on the rest of the thread, it doesn't do only that.
Anyway, for a server that only does pub/sub with ACID guarantees, those specs are so large that there is certainly a bottleneck before they matter. So it wouldn't be strange if somebody gets one that can't even handle that, it just would mean that there is some issue somewhere we don't see.
3 replies →
People are jumping on this. Question is—do the resource requirements outlined align with usage you described, or is that combined workload? By combined workload, I mean working set plus messaging. It’s not a useful exercise to criticize a service that’s multifaceted based on a single use case. Full disclosure—-not a Postgres user, nor am I invested in the tech.
It’s such a low throughput requirement I think even bitcoin could support it.
No, this is prob still spiky enough to have more than 7 transactions per second, that’s too much for Bitcoin.
To cherry pick two details of the post and insinuate something about it?
No.
Thanks 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...
Didn'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.
So, 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?
1 reply →
"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.
I'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.
I 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.
I've always been curious, what kind of latency do you see between an insert, and when the notify goes out over the channel?
10ms or so
I'd be curious to know what the drawbacks of using PG for a pub/sub server are.
What 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.
https://impossibl.github.io/pgjdbc-ng/docs/0.8.9/user-guide/...
or
https://github.com/pgjdbc/r2dbc-postgresql#listennotify
This may depend on the JDBC driver support Listen/Notify. Though if queue traffic is relatively steady then maybe polling isn't so bad?
I 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!
Silly question but how does this compare to SQS? More cost friendly I assume?
SQS 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
What was the isolation level used when that incident occurred?
The default postgres one, serializable (if I remember correctly?)
The default isolation level in postgres is read committed.