Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres LISTEN/NOTIFY does not scale

recall.ai

Post reply on HN
Print view View original

291–300 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#291

Post by lytedev »

jelder wrote: post #221
osigurdson wrote: post #110

I like this article. Lots of comments are stating that they are "using it wrong" and I'm sure they are. However, it does help to contrast the much more common, "use Postgres for everything" type sentiment. It is pretty hard to use Postgres wrong for relational things in the sense that everyone knows about indexes and so on. But using something like L/N comes with a separate learning curve anyway - evidenced in this c…

"use Postgres for everything" is certainly wrong, eventually. It's still the second-best choice for every new project, and most products will never see the traffic levels that justify using something more specialized. Obviously, recall.ai hit the level of traffic where Postgres was no longer ideal. I bet they don't regret it for the other parts of their product.

What is the first-best choice for a new project? SQLite?

Re: Postgres LISTEN/NOTIFY does not scale

#292

Post by WhyNotHugo »

JoelJacobson wrote: post #196

Hey folks, I ran into similar scalability issues and ended up building a benchmark tool to analyze exactly how LISTEN/NOTIFY behaves as you scale up the number of listeners. Turns out that all Postgres versions from 9.6 through current master scale linearly with the number of idle listeners — about 13 μs extra latency per connection. That adds up fast: with 1,000 idle listeners, a NOTIFY round-trip goes from ~0.4 ms…

Thanks for attacking this issue (even if still in a research phase, that's definitely a needed start).

I'm amused at how op brags about the huge scale at which they operate, but instead of even considering fixing the issue (both for themselves and for others), they just switched to something else for pubsub.

Re: Postgres LISTEN/NOTIFY does not scale

#293

Post by osigurdson »

dathinab wrote: post #227
osigurdson wrote: post #110

I like this article. Lots of comments are stating that they are "using it wrong" and I'm sure they are. However, it does help to contrast the much more common, "use Postgres for everything" type sentiment. It is pretty hard to use Postgres wrong for relational things in the sense that everyone knows about indexes and so on. But using something like L/N comes with a separate learning curve anyway - evidenced in this c…

Honestly whatever kind of DB you are speaking about always be wary of "niche/side features" which don't fit it's core design goals, they tend to have unexpected limitations. listen/notify isn't necessary a replacement for redis or other pub/sub systems, redis pub/sub and similar isn't necessary a replacement for idk. Kafka or similar queue/messaging system but a lot of companies have (for modern standards) surprising…

>> also same is true the other way around, depending on you application you can go redis only

Really the primary reason not to try stuff like this is (at least for me), feel that I won't paint myself into a corner with Postgres. I can always add a table here or a join there and things will work. If I need columnar, I use ClickHouse and NATS for messaging. I know these well but still gravitate toward Postgres because I feel it can grow in whatever direction is needed. However, it is true, I have thought about trying to just use NATS KV and make all services stateful receiving notifications when things change. It does seem that it could massively simplify some things but expect there could be some sharp edges in the face of unknown requirements. If one could just design for exactly the problem at hand it would be different but it never seems to work out like that.

Re: Postgres LISTEN/NOTIFY does not scale

#294

Post by osigurdson »

indeyets wrote: post #225
whaleofatw2022 wrote: post #200

Earlier quoted context omitted.

NATS does KV pretty well now (didn't have expiration till earlier this year)

Nats is getting there, but not yet. Redis is still much more powerful: lists, sorted sets and bazillion of other data structures

NATS has a bit more in terms of durability guarantees so I have found that it hits more use cases. I'm trying to strike a bit of a balance between "use the exact right tool for the job, even if that means you have 25 different services" and "just use Postgres". I do think Postgres/Redis/ClickHouse is probably fine as well but durable streaming would be hard to give up.

Re: Postgres LISTEN/NOTIFY does not scale

#295

Post by osigurdson »

Jailbird wrote: post #127
bevr1337 wrote: post #126

Earlier quoted context omitted.

> the data does not get to decide what happens next based on itself. Then why bother with a relational database? Relations and schemas are business logic, and I'll take all the data integrity I can get.

I've seen both of these philosophies. I liken them to religions, the believers are devout. Code is King vs the DB is King. I'm personally Code is King, and I have my reasons (like everyone else)

Maybe not DB, but getting data from wherever it may be to the registers in the computer is certainly is the King of Kings.

Re: Postgres LISTEN/NOTIFY does not scale

#296

Post by osigurdson »

0xFEE1DEAD wrote: post #209
ako wrote: post #192

Earlier quoted context omitted.

It’s really not about code is better or database it better, it’s mostly about locality: if you want to update thousands of records, you can’t pull those records into a separate process, update them there and then write back. So you put your code next to the data in the database. Stored procedures are just code deployed to a database container…

Sure you can, I've done it plenty of times. I'm genuinely curious why you think it's not possible. The only reasons I can think of: - you're rewriting a legacy system and migrate parts incrementally - data compliance - you're running a dangerous database setup I try my best to avoid putting any business logic inside databases and see stored procedures only as a temporary solution.

Its possible but of course slow because of https://gist.github.com/jboner/2841832. Data locality matters a lot. Moving data around the network when it doesn't really need to be moved is heresy (unless performance doesn't matter, then who cares). Remember the computer doesn't care about your religion which says only this can do this and only that can do that.

Re: Postgres LISTEN/NOTIFY does not scale

#297

Post by osigurdson »

bevr1337 wrote: post #226
0xFEE1DEAD wrote: post #209

Earlier quoted context omitted.

Sure you can, I've done it plenty of times. I'm genuinely curious why you think it's not possible. The only reasons I can think of: - you're rewriting a legacy system and migrate parts incrementally - data compliance - you're running a dangerous database setup I try my best to avoid putting any business logic inside databases and see stored procedures only as a temporary solution.

Although I'm partial to a SPROC, I do not deploy them because I understand my colleagues might throw me from a window. But without going full tilt DB-as-the-application, The DB can make much stronger guarantees about transactions and updates the closer that logic happens to itself. In the world of cloud computing, this can be a cost savings for ingress/egress too.

>> deploy them because I understand my colleagues might throw me from a window

Maybe throw your colleagues out the window instead if they don't know what they are talking about. I'm not anti/pro SPROC at all, but I am anti anti-reality. People that don't understand the vast differences in latencies between in process and out of process work should not exist in the industry.

Re: Postgres LISTEN/NOTIFY does not scale

#298

Post by osigurdson »

IgorPartola wrote: post #146
Jailbird wrote: post #127

Earlier quoted context omitted.

I've seen both of these philosophies. I liken them to religions, the believers are devout. Code is King vs the DB is King. I'm personally Code is King, and I have my reasons (like everyone else)

I am mostly on the side of business logic should live in applications and relationships between data types are not business logic so much as just the layout of the data. But I typically access data via an ORM and they typically don’t have support for triggers and stored procedures. If they did, I would certainly use it because projects I work on might have multiple people writing application code but everyone uses a…

>> I am mostly on the side of...

Generally customers don't care about religious views. Make understanding the actual machine and associated latencies your religion instead. The reason to write a stored proc or do some processing in the database is entirely about data locality, not to keep the drooling masses from messing things up. A library is fine for that.

Re: Postgres LISTEN/NOTIFY does not scale

#299

Post by osigurdson »

IgorPartola wrote: post #288
sgarland wrote: post #230

Earlier quoted context omitted.

Every ORM I’m aware of allows you to drop down to raw SQL. Write your stored procedure, store it in VCS, add it as a migration, and then call it. If you want to make it friendlier, wrap the call in a function in your language so you can add helpers, better error handling, etc.

What I would prefer is integration at the model definition level. For example let’s say that I have a Customer model and an Order model. I don’t always want to pull in the customer fields when listing orders. Most ORMs would allow me to create a join and specify the field from Customer I want when fetching Orders but those joins add up quickly. I could denormalize the data and put things like the customer name and em…

>> I don’t always want to pull in the customer fields when listing orders

Data locality is king. Everything comes down to physical things such as blocks on the SSD, network interconnect, RAM, L3, L2, L1 cache and registers. Are those customer fields in the same page as whatever else you need? If so, most of the work is already done. Yes, you can save some network bandwidth transferring things that aren't needed but does it matter? It might but it might not. The key is to know what matters and reason about things from the perspective of the machines actually doing the work.

Re: Postgres LISTEN/NOTIFY does not scale

#300

Post by osigurdson »

lytedev wrote: post #291
jelder wrote: post #221

Earlier quoted context omitted.

"use Postgres for everything" is certainly wrong, eventually. It's still the second-best choice for every new project, and most products will never see the traffic levels that justify using something more specialized. Obviously, recall.ai hit the level of traffic where Postgres was no longer ideal. I bet they don't regret it for the other parts of their product.

What is the first-best choice for a new project? SQLite?

No, generally Postgres, just not for everything. If you understand the tradeoffs SQLite can be fine. Once you have more than one service (even just for HA) SQLite means doing kind of crazy things like using NFS in your infra. If you know you will only have one service and can bind it to an EBS like volume it is totally fine.
Post reply on HN