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

91–100 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#91

Post by cyberax »

williamdclt wrote: post #48
cyberax wrote: post #42

Earlier quoted context omitted.

One annoying thing is that there is no counterpart for an operation to wait and read data from WAL. You can poll it using pg_logical_slot_get_binary_changes, but it returns immediately. It'd be nice to have a method that would block for N seconds waiting for a new entry. You can also use a streaming replication connection, but it often is not enabled by default.

I think replication is the way to go, it’s kinda what it’s for. Might be a bit tricky to get debezium to decode the logical event, not sure

Sure, but the replication protocol requires a separate connection. And the annoying part is that it requires a separate `pg_hba.conf` entry to be allowed. So it's not enabled for IAM-based connections on AWS, for example.

pg_logical_slot_get_binary_changes returns the same entries as the replication connection. It just has no support for long-polling.

Re: Postgres LISTEN/NOTIFY does not scale

#92

Post by j16sdiz »

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…

Can't change the number of partition dynamically.

Additional challenge if jobs comes in funny sizes

Re: Postgres LISTEN/NOTIFY does not scale

#93

Post by Groxx »

parthdesai wrote: post #61
hombre_fatal wrote: post #45

Earlier quoted context omitted.

Yeah, but pub/sub systems already need to be robust to missed messages. And, sending the notify after the transaction succeeds usually accomplishes everything you really care about (no false positives).

What happens when transaction succeeds but the execution of NOTIFY fails if it's outside of transaction, in it's own separate connection?

The same thing that happens if the notified process dies suddenly.

If you're not handling that, then whatever you're doing is unreliable either way.

Re: Postgres LISTEN/NOTIFY does not scale

#94

Post by j16sdiz »

kristianc wrote: post #84
callamdelaney wrote: post #69

My kneejerk reaction to the headline is ‘why would it?’. It’s unsurprising to me that an AI company appears to have chosen exactly the wrong tool for the job.

Sounds like a deliberate attempt to avoid spinning up Redis, Kafka, or an outbox system early on.. and then underestimated how quickly their scale would make it blow up. Story as old as time.

Kafka head of line blocking sucks.

Re: Postgres LISTEN/NOTIFY does not scale

#96

Post by const_cast »

kristianc wrote: post #84
callamdelaney wrote: post #69

My kneejerk reaction to the headline is ‘why would it?’. It’s unsurprising to me that an AI company appears to have chosen exactly the wrong tool for the job.

Sounds like a deliberate attempt to avoid spinning up Redis, Kafka, or an outbox system early on.. and then underestimated how quickly their scale would make it blow up. Story as old as time.

I find the opposite story more true: additional complexity in the form of caching early, for a scale that never comes. I've worked on one too many sprawling, distributed systems with too little users to justify it.

Re: Postgres LISTEN/NOTIFY does not scale

#97

Post by nightfly »

sleepy_keita wrote: post #90

LISTEN/NOTIFY was always a bit of a puzzler for me. Using it means you can't use things like pgbouncer/pgpool and there are so many other ways to do this, polling included. I guess it could be handy for an application where you know it won't scale and you just want a simple, one-dependency database.

> I guess it could be handy for an application where you know it won't scale and you just want a simple, one-dependency database

That's where we use it at my work. We have host/networking deployment pipelines that used to have up to one minute latency on each step because each was ran on a one-minute cron. A short python script/service that handled the LISTENing + adding NOTIFYs when the next step was ready removed the latency and we'll never do enough for the load on the db to matter

Re: Postgres LISTEN/NOTIFY does not scale

#98

Post by const_cast »

candiddevmike wrote: post #80
SoftTalker wrote: post #75

Earlier quoted context omitted.

Do it in a stored procedure not a trigger. Triggers have their place but a stored procedure is almost always better. Triggers can surprise you.

I don't follow how you would do that in a stored procedure outside of a trigger.

I think instead of performing an INSERT you call a stored proc that does the insert and some extra stuff.

Re: Postgres LISTEN/NOTIFY does not scale

#99

Post by sorentwo »

MuffinFlavored wrote: post #79
sorentwo wrote: post #28

Earlier quoted context omitted.

We have Postgres based pubsub, but encourage people to use a distributed Erlang based notifier instead whenever possible. Another important change was removing insert triggers, partially for the exact reasons mentioned in this post.

> Another important change was removing insert triggers, partially for the exact reasons mentioned in this post. What did you replace them with instead?

In app notifications, which can be disabled. Our triggers were only used to get subsecond job dispatching though.
Post reply on HN