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

221–230 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#221

Post by jelder »

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.

Re: Postgres LISTEN/NOTIFY does not scale

#222

Post by FZambia »

Many here recommend using Kafka or RabbitMQ for real-time notifications. While these tools work well with a relatively stable, limited set of topics, they become costly and inefficient when dealing with a large number of dynamic subscribers, such as in a messaging app where users frequently come and go. In RabbitMQ, queue bindings are resource-intensive, and in Kafka, creating new subscriptions often triggers expensive rebalancing operations. I've seen a use case for a messenger app with 100k concurrent subscribers where developers used RabbitMQ and individual queues for each user. It worked at 60 CPU on Rabbit side during normal situation and during mass reconnections of users (due to some proxy reload in infra) – it took up to several minutes for users to reconnect. I suggested switching to https://github.com/centrifugal/centrifugo with Redis engine (combines PUB/SUB + Redis streams for individual queues) – and it went to 0.3 CPU on Redis side. Now the system serves about 2 million concurrent connections.

Re: Postgres LISTEN/NOTIFY does not scale

#223

Post by CaliforniaKarl »

Gigachad wrote: post #88
CaliforniaKarl wrote: post #6

I appreciate this post for two reasons: * It gives an indication of how much you need to grow before this Postgres functionality starts being a blocker. * Folks encountering this issue—and its confusing log line—in the future will be able to find this post and quickly understand the issue.

Sounds like ChatGPT appreciated the post

I did not use ChatGPT—nor any AI—in writing the post. I'm curious, would you mind emailing—or replying—with what made you think that it was written by AI? Or why you do not believe my statement?

Re: Postgres LISTEN/NOTIFY does not scale

#224

Post by sgarland »

mike_hearn wrote: post #177
ownagefool wrote: post #166

Earlier quoted context omitted.

Largely agree. Functionality wise if you don't have many jobs, using the database as the queue is fine. However, I've been in several situations where scaling the queue brings down the database, and therefore the app, and am thus of the opinion you probably shouldn't couple these systems too tightly. There are pros and cons, of course.

Using the database for queues is more than fine, it's often essential to correctness. In many use cases for queues you need to atomically update the database with respect to popping from the queue, and if they're separate systems you end up needing either XA or brittle and unreliable custom idempotency logic. I've seen this go wrong before and it's not nice, the common outcome is business-visible data corruption that…

> "We discovered X about Postgres" is a eng blog cliché by this point.

It really is, and it’s often surprising to me how basic some of the issues are being discovered. Like Figma, when they waited a shocking amount of time add [0] PgBouncer and read replicas. This is such a well-trod path that it’s baffling to me why you wouldn’t add it once it’s clear you have a winning product. At the very least, PgBouncer (or PgCat, or any other connection pooler / proxying service) - it adds negligible cost per month (in comparison to DB read replicas) to run a couple of containers with a load balancer.

Re: Oracle, as much as I despise the company for its litigious practices, I’ll hand it to you that the features your DB has are astonishing. RAC is absolutely incredible (on paper - I’ve never used it).

[0]: https://www.figma.com/blog/how-figma-scaled-to-multiple-data...

Re: Postgres LISTEN/NOTIFY does not scale

#225

Post by indeyets »

whaleofatw2022 wrote: post #200
KronisLV wrote: post #198

Earlier quoted context omitted.

> Postgres/ClickHouse/NATS Maybe throw in a dedicated key-value store like Redis or Valkey. Oh and maybe something S3 compatible like MinIO, Garage or SeaweedFS for storing bunches of binary data. With all of that, honestly it should cover most of the common workloads out there! Of course, depends on how specialized vs generic you like your software to be.

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

Re: Postgres LISTEN/NOTIFY does not scale

#226

Post by bevr1337 »

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.

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.

Re: Postgres LISTEN/NOTIFY does not scale

#227

Post by dathinab »

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) surprisingly small amounts of data, very even a increase by 2,3,4x still isn't that big. In that case listen/notify and similar might just work fine :shrug:

also same is true the other way around, depending on you application you can go redis only, as long as you data volume stays small enough and needs for transactional/sync are reasonable simple enough (with watch+exec, NX,XX options etc. and maybe some redis side lua scripts you can do quite a lot for data synchronization). Issue with that is that stylistically redis data sync/transaction code is often much more similar to writing atomic data-structures then to SQL transactions, and even for SQL transactions there is a trend of devs severely overestimating what they provide, so often you are better of not touching on it when you can avoid it, also BTW. redis has something very similar to sqlite or Notify where "basically" (oversimplified by a lot) there is only one set of writes done at a time ;) (and then afterwards distributed to replicas), just that outside of some micro lua scripts you don't really run much logic outside of some NX, XX checks etc. so it's not blocking much and it's "more or less" all just in memory not touching a WAL (again oversimplified).

Re: Postgres LISTEN/NOTIFY does not scale

#228

Post by sgarland »

fathomdeez wrote: post #125
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…

This kind of issue always comes up when people put business logic inside the database. Databases are for data. The data goes in and the data goes out, but the data does not get to decide what happens next based on itself. That's what application code is for.

Disagree; these issues come up when people use more advanced features of DBs without having the requisite DB expertise on staff. I’ll give OP that Postgres’ docs do not mention this gotcha (and props to them for drilling down to source code!), but by and large, these issues are from people operating via tech blogs.

The DB is - or should be - the source of truth for your application. Also, since practically everyone is using cloud RDBMS with (usually) networked storage, the latency is atrocious. Given those, it seems silly to rely on an application to react to and direct changes to related data.

For example, if you want to soft-delete customer data while maintaining the ability to hard-delete, then instead of having an is_deleted and/or deleted_at column, have a duplicate table or tables, and an AFTER DELETE trigger on the originals that move the tuples to the other tables.

Or if you want to have get_or_create without multiple round trips (and you don’t have Postgres’ MERGE … RETURNING), you can easily accomplish this with a stored procedure.

Using database features shouldn’t be seen as verboten or outdated. What should be discouraged is not treating things like stored procedures and triggers as code. They absolutely should be in VCS, should go the same review process as anything else, and should be well-documented.

Re: Postgres LISTEN/NOTIFY does not scale

#229

Post by sgarland »

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)

Every company I’ve been at that relied on application code to handle referential integrity had orphaned rows, and incidents related to data errors or the absurd pipelines they had built to recreate what FK constraints and triggers already do.

RDBMS are extremely well-tested pieces of software that do their job incredibly well. To think that you could do better, or even equally as well, is hubris. If you want to trade those guarantees for “velocity” go right ahead, but you also need to take into account the inevitable incidents and recoveries that will occur.

Re: Postgres LISTEN/NOTIFY does not scale

#230

Post by sgarland »

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…

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