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

241–250 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#241

Post by brightball »

ehansdais wrote: post #167
tsimionescu wrote: post #155

Earlier quoted context omitted.

The way you model data and store it in your database is fundamentally a part of your business logic. The same data can be modeled in many different ways, with different trade-offs for different use cases. Especially if you have a large amount of data, you can't just work with it as is, you need to know how you will use it and model it in a way that makes the common operations fast enough. As your application evolves,…

Can't upvote this enough. The point is not that procedures outside of the DB is wrong, nor is it that procedures should always go into the DB. It's that you should look at the context and decide what the best way to solve the problem is.

Agreed. I used triggers frequently for things like incrementing/decrementing count fields for dashboards because it's the only way to guarantee those numbers are correct while ensuring something in the application hasn't bypassed a callback or handler to modify the data.

You only need to cover three scenarios and it's very simple to implement. Recorded added +1, Record removed -1, Record moved +1 & -1.

If you have counts that are more complicated, it doesn't work but this solution easily beats semi-frequent COUNT queries.

Re: Postgres LISTEN/NOTIFY does not scale

#242

Post by cryptonector »

chatmasta wrote: post #148
fathomdeez wrote: post #125

Earlier quoted context omitted.

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.

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.

One can replace LISTEN/NOTIFY with logical replication / CDC. And it's funny because somehow, somewhere, PG must be serializing the writing of the WAL to some degree. So it's not clear to me why LISTEN/NOTIFY needs additional serialization. Perhaps PG should turn NOTIFY into INSERTs on a special table that a worker process watches and turns those inserts into notifies (and deletes the inserts).

Re: Postgres LISTEN/NOTIFY does not scale

#243

Post by cryptonector »

IgorPartola wrote: post #145
platzhirsch wrote: post #130

Earlier quoted context omitted.

If you want your database to just store bytes, use a key-value store. But SQL gives you schemas and constraints for a reason; they're guardrails for your business logic. Just don’t ask your tables to run the business for you.

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.

Re: Postgres LISTEN/NOTIFY does not scale

#244

Post by brightball »

gunnarmorling wrote: post #151
williamdclt wrote: post #27

Earlier quoted context omitted.

I found recently that you can write directly to the WAL with transactional guarantees, without writing to an actual table. This sounds like it would be amazing for queue/outbox purposes, as the normal approaches of actually inserting data in a table cause a lot of resource usage (autovacuum is a major concern for these use cases). Can’t find the function that does that, and I’ve not seen it used in the wild yet, idk…

pg_logical_emit_message() is how I recommend users on Postgres to implement the outbox pattern [1]. No table overhead as you say, no need for housekeeping, etc. It has some other cool applications, e.g. providing application-specific metadata for CDC streams or transactional logging, wrote about it at [2] a while ago. Another one is making sure replication slots can advance also if there's no traffic in the database…

You know, this would be a great talk at the 2026 Carolina Code Conference...

Re: Postgres LISTEN/NOTIFY does not scale

#245

Post by cryptonector »

whstl wrote: post #201
fathomdeez wrote: post #125

Earlier quoted context omitted.

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.

This is one of those absolute statements that cause the kind of problem stated by grandparent. There are lots of those: "Use Postgres for everything", "No business data on the DB", "No methods bigger than 10 lines", "Abstractions only after 3 usages". Back to the topic: Lots of potential bugs and data corruption issues are solved by moving part of the business logic to the database. Other people already covered two t…

Thank you for bringing some sanity into this discussion.

Re: Postgres LISTEN/NOTIFY does not scale

#246

Post by cryptonector »

parthdesai wrote: post #214
panzi wrote: post #129

Earlier quoted context omitted.

So what are your thoughts on constraints then? Foreign keys? Should that only be handled by the application, like Rails does (or did, haven't used in a long time).

What happens to FKs when you've to partition/shard the db? At a certain scale, they actually hinder the inserts.

FKs are nothing special. It's just more INSERTs/UPDATEs/DELETEs. If you can't have a few more DMLs in your transactions in your sharded DB then you've already got scaling problems.

Really, FKs are typically implemented internally by RDBMSes as TRIGGERs that do what you expect FKs to do, which means they really are nothing more than syntactic sugar.

Re: Postgres LISTEN/NOTIFY does not scale

#247

Post by mike_hearn »

ownagefool wrote: post #212
mike_hearn wrote: post #177

Earlier quoted context omitted.

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…

It actually depends on the workload. Sending webhooks, as an example, often has zero need to go back and update the database, but I've seen that exact example take down several different managed databases ( i.e., not just postgres ).

Yes that's true but in good implementations you will want to surface to the recipient via some dashboard if delivery consistently fails. So at some point a message on the exception queue will want to update the db.

Re: Postgres LISTEN/NOTIFY does not scale

#249

Post by v5v3 »

sgarland wrote: post #236
v5v3 wrote: post #232

Earlier quoted context omitted.

>... and frankly anyone accessing an RDBMS as a regular part of their job should know it. With entity framework code first, Microsoft made it possible for generations of developers to barely touch a database. A lot of Devs have poor database skills nowadays. Which suits the cloud sellers who want to push managed platforms

Agreed. What’s worse is when they confidently proclaim that they had to scale up N times “to handle the load,” but then a brief reading of of their schema and queries reveals that an RPi could probably handle it if they’d designed a better schema, and had a basic understanding of B+trees.

A lot of SQL consultants had/have a great job going into companies having issues and producing a report of the obvious!!

Re: Postgres LISTEN/NOTIFY does not scale

#250

Post by cryptonector »

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 constraints becomes more tolerable, but if you add a `CREATE CHANNEL` you might as well add something there regarding payload types, or you might as well just make it so it has to always be JSON.

With a `CREATE CHANNEL` PG could provide:

  - authz for listen
  - authz for notify
  - payload content type constraints
    (maybe always JSON if you CREATE
    the channel)
  - select different serialization
    semantics (to avoid this horrible,
    no good, very bad locking behavior)
  - backwards-compatibility for listen/
    notify on non-created channels
Post reply on HN