←back to thread

Postgres LISTEN/NOTIFY does not scale

(www.recall.ai)
570 points davidgu | 1 comments | 07 Jul 25 14:05 UTC | HN request time: 0.262s | source
Show context
osigurdson ◴[11 Jul 25 02:13 UTC] No.44527817[source]▶
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.

replies(12): >>44528211 #>>44528216 #>>44529511 #>>44529632 #>>44529640 #>>44529854 #>>44530773 #>>44531235 #>>44531722 #>>44532418 #>>44532993 #>>44534858 #
fathomdeez ◴[11 Jul 25 03:47 UTC] No.44528216[source]▶
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.
replies(12): >>44528249 #>>44528293 #>>44528307 #>>44528582 #>>44528918 #>>44529077 #>>44529583 #>>44530054 #>>44530782 #>>44530978 #>>44532428 #>>44533144 #
chatmasta ◴[11 Jul 25 06:22 UTC] No.44528918[source]▶
The first thing I did when I saw this article was to check the Postgres docs, because I thought "heh, surely they just didn't read the fine print," but the LISTEN/NOTIFY page has zero mentions of "lock" in the entire content.
replies(3): >>44529039 #>>44532925 #>>44533171 #
1. dathinab ◴[11 Jul 25 15:01 UTC] No.44532925[source]▶
I think, It's because the locking is part of the transaction commit locking, but yes it should be mentioned.

But it's oversimplified a case of "high queue load f* up the availability/timings for other DB operations" (and themself).

And thats a generic problem you have, even if just due to "generic CPU/WAL/disk load" if you put your queue into your DB even iff that specific lock would be somehow solved with some atomic concurrent algorithms or similar (not sure if that even is possible).

So in general make your storage db, and queue a different service (and you cache too), even if it uses the same kind of storage. (Through technically there are clever in-between solutions which run their own queue service but still use you DB for final storage but have a ton of caching, in memory locking etc. to remove a huge part of the load from the DB. )