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

281–290 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#281

Post by singron »

atombender wrote: post #277
singron wrote: post #275

Earlier quoted context omitted.

Unfortunately, updating the row also creates dead tuples. It's very tricky!

It does, but because of how indexes work, I believe it won't be skewed by the presence of dead tuples (though the bloat can cause the live dat to be spread across a lot more blocks and therefore generate more I/O) as long as you run autoanalyze semi-regularly.

It depends on if you are getting Heap Only Tuples (HOT) updates or not. https://www.postgresql.org/docs/current/storage-hot.html

In this case, you might have enough dead tuples across your heap that you might get a lot of HOT updates. If you are processing in insertion order, you will also probably process in heap order, and you can actually get 0 HOT updates since the other tuples in the page aren't fully dead yet. You could try using a lower fillfactor to avoid this, but that's also bad for performance so it might not help.

Re: Postgres LISTEN/NOTIFY does not scale

#282

Post by tracker1 »

imtringued wrote: post #251
dathinab wrote: post #235

Earlier quoted context omitted.

if you need transaction across a queue into a normal SQL DB or similar I believe you are doing something very wrong. Sure you need transaction about processing things in a queue (mark as "taken out", but not yet remove then remove or "place back in (or into a failed messages inbox)" on timeout or similar can be _very_ important for queue systems. But the moment the "fail save if something dies while processing a mess…

>But the moment the "fail save if something dies while processing a message" becomes a directly coupled with DB transactions you have created something very brittle and cumbersome. The standard workflow for processing something from a queue is to keep track of all the messages you have already processed in the transactional database and simply request the remaining unprocessed messages. Often this is as simple as sto…

My approach is to have fields for started/completed where started includes the system/process/timestamp of when an item was started... this gets marked as part of the process to tag and take the next item by the worker(s). It also allows for sweep and retry.

That said, I tend to reach for redis/rabbit or kafka relatively early depending on my specific needs and what's in use. Main use of a dbms queue historically is sending/tracking emails where the email service I had been using was having hiccups.

Re: Postgres LISTEN/NOTIFY does not scale

#283

Post by atombender »

singron wrote: post #281
atombender wrote: post #277

Earlier quoted context omitted.

It does, but because of how indexes work, I believe it won't be skewed by the presence of dead tuples (though the bloat can cause the live dat to be spread across a lot more blocks and therefore generate more I/O) as long as you run autoanalyze semi-regularly.

It depends on if you are getting Heap Only Tuples (HOT) updates or not. https://www.postgresql.org/docs/current/storage-hot.html In this case, you might have enough dead tuples across your heap that you might get a lot of HOT updates. If you are processing in insertion order, you will also probably process in heap order, and you can actually get 0 HOT updates since the other tuples in the page aren't fully dead yet.…

If you have a "done" column that you filter on using a partial index, then it would never use HOT updates anyway, since HOT requires that none of the modified columns have an index.

Re: Postgres LISTEN/NOTIFY does not scale

#284

Post by tracker1 »

cryptonector wrote: post #243
IgorPartola wrote: post #145

Earlier quoted context omitted.

If only different ORMs had more support for triggers and stored procedures. Things would be so much easier if I could do things like denormalize certain frequently accessed fields across tables but with proper ability to update them automatically without having to do them in application code.

ORMs are crutches. You don't need them if you're able-bodied. Just ditch them. Just say no to ORMs.

I largely agree.. though data mapper libraries (such as Dapper for .Net) can be pretty helpful, even if there's a minor disconnect from the SQL used and the POCO/Record definitions used... It's far simpler than most ORMs and keeps you a little closer to the DB.

Re: Postgres LISTEN/NOTIFY does not scale

#285

Post by maxbond »

cryptonector wrote: post #250
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…

That's pretty cool. IMO LISTEN/NOTIFY is badly designed as an interface to begin with because there is no way to enforce access controls (who can notify; who can listen) nor is there any way to enforce payload content type (e.g., JSON). It's very unlike SQL to not have a `CREATE CHANNEL` and `GRANT` commands for dealing with authorization to listen/notify. If you have authz then the lack of payload content type const…

> there is no way to enforce access controls

(I thought this was a fun puzzle, so don't take this as advice or as disagreement with your point.)

There is the option to use functions with SECURITY DEFINER to hack around this, but the cleanest way to do it (in the current API) would be to encrypt your messages on the application side using an authenticated system (eg AES-GCM). You can then apply access control to the keys. (Compromised services could still snoop on when adjacent channels were in use, however.)

Re: Postgres LISTEN/NOTIFY does not scale

#286

Post by tracker1 »

cryptonector wrote: post #256
TrackerFF wrote: post #185

Earlier quoted context omitted.

A decent way to classify human vs bot when it comes to dashes, is that all bots use ‘em-dashes(—), while almost none use regular dashes (-) in writing. While plenty of humans will use regular dashes, because they won’t bother to look for ‘em-dashes on the keyboard, or phone. Of course, you have the people that correctly use em-dashes, too.

On iPhones the input methods turn -- into —. If you see me using em-dashes it's cause I wrote on an iPhone. But I prefer -- to —.

I've had it happen with various editors on the desktop as well. It's kind of annoying at times.

Re: Postgres LISTEN/NOTIFY does not scale

#287

Post by menthe »

atombender wrote: post #283
singron wrote: post #281

Earlier quoted context omitted.

It depends on if you are getting Heap Only Tuples (HOT) updates or not. https://www.postgresql.org/docs/current/storage-hot.html In this case, you might have enough dead tuples across your heap that you might get a lot of HOT updates. If you are processing in insertion order, you will also probably process in heap order, and you can actually get 0 HOT updates since the other tuples in the page aren't fully dead yet.…

If you have a "done" column that you filter on using a partial index, then it would never use HOT updates anyway, since HOT requires that none of the modified columns have an index.

False.

As of PG16, HOT updates are tolerated against summarizing indexes, such as BRIN.

https://www.postgresql.org/docs/16/storage-hot.html

Besides, you probably don't want "done" jobs in the same table as pending or retriable jobs - as you scale up, you likely want to archive them as it provides various operational advantages, at no cost.

Re: Postgres LISTEN/NOTIFY does not scale

#288

Post by IgorPartola »

sgarland wrote: post #230
IgorPartola wrote: post #146

Earlier quoted context omitted.

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.

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 email onto each order but if the customer changes either value now the application code has to remember to update it. And yes I could put that in the model’s save() method but that is fragile too because what if someone else does run code that updates stuff at the raw SQL level and doesn’t include these updates.

Now if I could specify that I want Order.customer_name to come from a specific other model and be updated automatically the ORM could automatically create a trigger to update that field when the customer table is updated.

Obviously this is a very simplistic example but there are many more, including versioning and soft deletes that could be incredibly useful. But the key is that the ORM has to generate the code for the triggers and stored procedures. Doing that manually is possible now but (a) uses a different language even than regular SQL which not everyone is familiar with, and (b) there is no type checking for what you are doing. The ORM model definitions are the main source of truth about the shape of your database, so I want to use them as such.

Re: Postgres LISTEN/NOTIFY does not scale

#289

Post by atombender »

menthe wrote: post #287
atombender wrote: post #283

Earlier quoted context omitted.

If you have a "done" column that you filter on using a partial index, then it would never use HOT updates anyway, since HOT requires that none of the modified columns have an index.

False. As of PG16, HOT updates are tolerated against summarizing indexes, such as BRIN. https://www.postgresql.org/docs/16/storage-hot.html Besides, you probably don't want "done" jobs in the same table as pending or retriable jobs - as you scale up, you likely want to archive them as it provides various operational advantages, at no cost.

Not false. Nobody would ever use BRIN for this. I'm talking about regular indexes, which do prevent HOT.

If you read my earlier comment properly, you'll notice a "done" column is to avoid deleting columns on the hot path and avoid dead tuples messing up the planner. I agree that a table should not contain done jobs, but then you risk running into the dead tuple problem. Both approaches are a compromise.

Re: Postgres LISTEN/NOTIFY does not scale

#290

Post by IgorPartola »

cryptonector wrote: post #243
IgorPartola wrote: post #145

Earlier quoted context omitted.

If only different ORMs had more support for triggers and stored procedures. Things would be so much easier if I could do things like denormalize certain frequently accessed fields across tables but with proper ability to update them automatically without having to do them in application code.

ORMs are crutches. You don't need them if you're able-bodied. Just ditch them. Just say no to ORMs.

I used to think like this, but over the past decade and a half they have gotten a lot more performant and usable and the speed with which you can develop using them is just unmatched by writing raw SQL. Again, I say this as someone who used to be very much team just write SQL and even created a quasi-ORM that allowed me to write all the queries by hand but returned model instances that could have methods as a sort of in-between solution. I still routinely use raw SQL but only when it is actually necessary.
Post reply on HN