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

101–110 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#101

Post by const_cast »

0xbadcafebee wrote: post #43

RBDMS are not designed for write-heavy applications, they are designed for read-heavy analysis. Also, an RDBMS is not a message queue or an RPC transport. I feel like somebody needs to write a book on system architecture for Gen Z that's just filled with memes. A funny cat pic telling people not to use the wrong tool will probably make more of an impact than an old fogey in a comment section wagging his finger.

People have been using RDBMS' for write-heavy workflows for forever. Some people even use stored procs or triggers for getting complicated write operations to work properly.

Databases can do a lot of stuff, and if you're not hurting for DB performance it can be a good idea to just... do it in the database. The advantage is that, if the DB does it, you're much less likely to break things. Putting data constraints in application code can be done, but then you're just waiting for the day those constraints are broken.

Re: Postgres LISTEN/NOTIFY does not scale

#103

Post by caleblloyd »

to11mtm wrote: post #71

Seriously people just layer shit with NATS for pubsub after persist and make sure there's a proper way to place a 'on restart recoonect' thing.

Amen! NATS is how we do AI streaming! JetStream subject per thread with an ordered consumer on the client.

Re: Postgres LISTEN/NOTIFY does not scale

#105

Post by acdha »

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

If you think they’re a bot, flag and move on. No need for a derail about writing style.

Re: Postgres LISTEN/NOTIFY does not scale

#106

Post by sorentwo »

leontrolski wrote: post #17

I'd be interested as to how dumb-ol' polling would compare here (the FOR UPDATE SKIP LOCKED method https://leontrolski.github.io/postgres-as-queue.html ). One day I will set up some benchmarks as this is the kind of thing people argue about a lot without much evidence either way. Wasn't aware of this AccessExclusiveLock behaviour - a reminder (and shameless plug 2) of how Postgres locks interact: https://leontrolski.…

Ping requires something persistent to check. That requires creating tuples, and most likely deleting them after they’ve been consumed. That puts pressure on the database and requires vacuuming in ways that pubsub doesn’t because it’s entirely ephemeral.

Not to mention that pubsub allows multiple consumers for a single message, whereas FOR UPDATE is single consumer by design.

Re: Postgres LISTEN/NOTIFY does not scale

#107

Post by acdha »

deadbabe wrote: post #76

Honestly this article is ridiculous. Most people do not have tens of thousands of concurrent writers. And most applications out there are read heavy, not write. Which means you probably have read replicas distributing loads. Use LISTEN/NOTIFY. You will get a lot of utility out of it before you’re anywhere close to these problems.

I would phrase this as “know where your approach hits scaling walls”. You’re right that many people never need more than LISTEN/NOTIFY but the reason that advice became so popular was the wave of people who had jumped straight into running some complicated system like Kafka when they hadn’t done any analysis to justify it; it would be nice if the lesson we taught was that you should do some analysis rather than just picking one popular option.

Re: Postgres LISTEN/NOTIFY does not scale

#108

Post by 0xbadcafebee »

incoming1211 wrote: post #37
dumbfounder wrote: post #35

Transactional databases are not really the best tool for writing tons of (presumably) immutable records. Why are you using it for this? Why not Elastic?

Because transactional databases are perfectly fine for this type of thing when you have 0 to 100k users.

The total number of users in your system is not a performance characteristic. And transactions are generally wrong for write-heavy anything. Further, if you can just append then the transaction is meaningless.

Re: Postgres LISTEN/NOTIFY does not scale

#109

Post by CBLT »

time0ut wrote: post #83
singron wrote: post #34

Earlier quoted context omitted.

Polling is the way to go, but it's also very tricky to get right. In particular, it's non-trivial to make a reliable queue that's also fast when transactions are held open and vacuum isn't able to clean tuples. E.g. "get the first available tuple" might have to skip over 1000s of dead tuples. Holding transactions open is an anti-pattern for sure, but it's occasionally useful. E.g. pg_repack keeps a transaction open w…

An approach that has worked for me is to hash partition the table and have each worker look for work in one partition at a time. There are a number of strategies depending on how you manage workers. This allows you to only consider 1/Nth of the dead tuples, where N is the number of partitions, when looking for work. It does come at the cost of strict ordering, but there are many use cases where strict ordering is not…

This is how Kafka does it. Kafka has spent years working on the rough edges (e.g. partition resizing), haven't used it recently though.

Re: Postgres LISTEN/NOTIFY does not scale

#110

Post by osigurdson »

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 case by someone having to read comments in the Postgres source code itself. Then if it turns out that it cannot work for your situation it may be very hard to back away from as you may have tightly integrated it with your normal Postgres stuff.

I've landed on Postgres/ClickHouse/NATS since together they handle nearly any conceivable workload managing relational, columnar, messaging/streaming very well. It is also not painful at all to use as it is lightweight and fast/easy to spin up in a simple docker compose. Postgres is of course the core and you don't always need all three but compliment each other very well imo. This has been my "go to" for a while.

Post reply on HN