53 ms·
Postgres LISTEN/NOTIFY does not scale
- hombre_fatal 1y agoInteresting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
- soursoup 1y agoIsn’t it standard practice to have a separate TCP stream for NOTIFY or am I mistaken
- remram 1y agoYou mean for LISTEN?
- nick_ 1y agoMy thought as well. You could add notify commands to a temp table during the transaction, then run NOTIFY on each row in that temp table after the transaction commits successfully?
- foota 1y agoWouldn't you need to then commit to remove the entries from the temp table?
- zbentley 1y agoNo, so long as the rows in there are transactionally guaranteed to be present or not, a sweeper script can handle removing failed “publishes” (notifys that didn’t delete their row) later. This does sacrifice ordering and increases the risk of duplicates in the message stream, though.
- zbentley 1y agoThis is roughly the “transactional outbox” pattern—and an elegant use of it, since the only service invoked during the “publish” RPC is also the database, reducing distributed reliability concerns. …of course, you need dedup/support for duplicate messages on the notify stream if you do this, but that’s table stakes in a lot of messaging scenarios anyway.
- parthdesai 1y agoYou lose transactional guarantees if you notify outside of the transaction though
- hombre_fatal 1y agoYeah, 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).
- parthdesai 1y agoWhat happens when transaction succeeds but the execution of NOTIFY fails if it's outside of transaction, in it's own separate connection?
- saltcured 1y agoFor reliability, you can make the recipient poll the table(s) of record for relevant state and use the out-of-band notification channel as a latency-reducer. So, the poller is eventually consistent at some configured polling interval, but opportunistically can respond much sooner when told to check again ahead of the next scheduled poll time. In my experience, this means you make sure the polling solution is complete and correct, and the notifier gets reduced to a wake-up signal. This signal doesn't even need to carry the actionable change content, if the poller can already pose efficient queries for whatever "new stuff" it needs. This approach also allows the poller to keep its own persistent cursor state if there is some stateful sequence to how it consumes the DB content. It automatically resynchronizes and the notification channel does not need to be kept in lock-step with the consumption.
- parthdesai 1y agofwiw - that's what Oban did for the most part. It sent a signal to a worker that there was a new job to pick up and work on. At scale, even that was an issue.
- 1y ago
- zerd 1y agoThat would make the locked time shorter, but it would still contend on the global lock, right?
- polote 1y agoRls and triggers dont scale either
- shivasaxena 1y agoYeah, I'm going to remove triggers in next deploy of a POS system since they are adding 10-50ms to each insert. Becomes a problem if you are inserting 40 items to order_items table.
- GuinansEyebrows 1y agothat, and keeping your business logic in the database makes everything more opaque!
- lelanthran 1y ago> that, and keeping your business logic in the database makes everything more opaque! Opaque to who? If there's a piece of business logic that says "After this table's record is updated, you MUST update this other table", what advantages are there to putting that logic in the application? When (not if) some other application updates that record you are going to have a broken database. Some things are business constraints, and as such they should be moved into the database if at all possible. The application should never enforce constraints such as "either this column or that column is NULL, but at least one must be NULL and both must never be NULL at the same time". Your database enforces constraints; what advantages are there to code the enforcement into every application that touches the database over simply coding the constraints into the database?
- thisoneisreal 1y agoI think the dream is that business requirements are contained to one artifact and everything else responds to that driver. In an ideal world, it would be great to have databases care only about persistence and be able to swap them out based on persistence needs only. But you're right, in the real world the database is much better at enforcing constraints than applications.
- cpursley 1y agoRight, plus there's character limitations (column size). This is why I prefer listening to the Postgres WAL for database changes: https://github.com/cpursley/walex?tab=readme-ov-file#walex https://github.com/cpursley/walex?tab=readme-ov-file#walex (there's a few useful links in here)
- williamdclt 1y agoI 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 if there’s gotchas Edit: found it, it’s pg_logical_emit_message
- cyberax 1y agoOne 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.
- williamdclt 1y agoI 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
- cyberax 1y agoSure, 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.
- CaliforniaKarl 1y agoI 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.
- Gigachad 1y agoSounds like ChatGPT appreciated the post
- acdha 1y agoIf you think they’re a bot, flag and move on. No need for a derail about writing style.
- jjgreen 1y agoJust for the em-dashes? Some humans also use them.
- Gigachad 1y agoIt’s also the fact it’s just a summary of the post content without anything extra or any opinions.
- jjgreen 1y agoFair point
- TrackerFF 1y agoA 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.
- NightMKoder 1y agoFacebook’s wormhole seems like a better approach here - just tailing the MySQL bin log gets you commit safety for messages without running into this kind of locking behavior.
- h1fra 1y agoYou had one problem with listen notify which was a fair one, but now you have a problem with http latency, network issues, DNS, retries, self-DDoS, etc.
- GuinansEyebrows 1y agoit sounds like the impact of LISTEN/NOTIFY scaling issues was much greater on the overall DB performance than the actual load/scope of the task being performed (based on the end of the article), and they're aware that if they needed something more performant for that offloaded task, they have options (pub/sub via redis or w/e).
- mulmen 1y agoSounds like one centralized Postgres instance, am I understanding that correctly? Wouldn’t meeting bots be very easy to parallelize across single-tenant instances?
- supportengineer 1y agoLISTEN/NOTIFY isn’t just a lock-free trigger. It can jeopardize concurrency under load. Features that seem harmless at small scale can break everything at large scale.
- edoceo 1y agoIt's true and folk should also choose the right tool at their scale and monitor it. There are plenty of cases where LISTEN/NOTIFY is the right choice. However, in 2025 I'd pick Redis or MQTT for this kind of role. I'm typically in multi-lamg environments. Is there something better?
- andrewstuart 1y agoThere’s lots of ways to invoke NOTIFY without doing it from with the transaction doing the work. The post author is too focused on using NOTIFY in only one way. This post fails to explain WHY they are sending a NOTIFY. Not much use telling us what doesn’t work without telling us the actual business goal. It’s crazy to send a notify for every transaction, they should be debounced/grouped. The point of a NOTIFY is to let some other system know something has changed. Don’t do it every transaction.
- 0xCMP 1y agoAgreed, I am struggling to understand why "it does not scale" is not "we used it wrong and hit the point where it's a problem" here. Like if it needs to be very consistent I would use an unlogged table (since we're worried about "scale" here) and then `FOR UPDATE SKIP LOCKED` like others have mentioned. Otherwise what exactly is notify doing that can't be done after the first transaction? Edit: in-fact, how can they send an HTTP call for something and not be able to do a `NOTIFY` after as well? One possible way I could understand what they wrote is that somewhere in their code, within the same transaction, there are notifies which conditionally trigger and it would be difficult to know which ones to notify again in another transaction after the fact. But they must know enough to make the HTTP call, so why not NOTIFY?
- andrewstuart 1y agoAgreed. They’re using it wrong and blaming Postgres. Instead they should use Postgres properly and architect their system to match how Postgres works. There’s correct ways to notify external systems of events via NOTIFY, they should use them.
- tomrod 1y agoAssuming you skip select transaction, or require logging on it because your regulated industry had bad auditors, then every transaction changes something.
- thom 1y agoYeah, the way I've always used LISTEN/NOTIFY is just to tell some pool of workers that they should wake up and check some transactional outbox for new work. False positives are basically harmless and therefore don't need to be transactional. If you're sending sophisticated messages with NOTIFY (which is a reasonable thing to think you can do) you're probably headed for pain at some point.
- sorentwo 1y agoPostgres LISTEN/NOTIFY was a consistent pain point for Oban (background job processing framework for Elixir) for a while. The payload size limitations and connection pooler issues alone would cause subtle breakage. It was particularly ironic because Elixir has a fantastic distribution and pubsub story thanks to distributed Erlang. That’s much more commonly used in apps now compared to 5 or so years ago when 40-50% of apps didn’t weren’t clustered. Thanks to the rise of platforms like Fly that made it easier, and the decline of Heroku that made it nearly impossible.
- cpursley 1y agoHow did you resolve this? Did you consider listening to the WAL?
- sorentwo 1y agoWe 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.
- MuffinFlavored 1y ago> Another important change was removing insert triggers, partially for the exact reasons mentioned in this post. What did you replace them with instead?
- sorentwo 1y agoIn app notifications, which can be disabled. Our triggers were only used to get subsecond job dispatching though.
- parthdesai 1y agoDistributed Erlang if application is clustered, redis if it is not. Source: Dev at one of the companies that hit this issue with Oban
- cshimmin 1y agoIf I understood correctly, the global lock is so that notify events are emitted in order. Would it make sense to have a variant that doesn't make this ordering guarantee if you don't care about it, so that you can "notify" within transactions without locking the whole thing?
- GuinansEyebrows 1y agopossibly, but i think at that point it would make more sense to move the business logic outside of the database (you can wait for a successful commit before triggering an external process via the originating app, or monitor the WAL with an external pub/sub system, or something else more clever than i can think of).
- leontrolski 1y agoI'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 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.github.io/pglockpy.html https://leontrolski.github.io/pglockpy.html
- aurumque 1y agoI'll take the shameless plug. Thank you for putting this together! Very helpful overview of pg locks.
- notarobot123 1y agoIt's funny how "shameless plug" actually means "excuse the self-promotion" and implies at least a little bit of shame even when the reference is appropriate and on-topic.
- cpursley 1y agoHave you played with pgmq? It's pretty neat: https://github.com/pgmq/pgmq https://github.com/pgmq/pgmq
- RedShift1 1y ago
- shivasaxena 1y agoOut of curiosity: Would appreciate if others can share what other things like AccessExclusiveLock should postgres users beware of? What I already know - Unique indexes slow inserts since db has to acquire a full table lock - Case statements in Where break query planner/optimizer and require full table scans - Read only postgres functions should be marked as `STABLE PARALLEL SAFE`
- franckpachot 1y agoCan you provide more details? Inserting with unique indexes do not lock the table. Case statements are ok in where clause, use expression indexes to index it
- hans_castorp 1y ago> Unique indexes slow inserts since db has to acquire a full table lock An INSERT never results in a full table lock (as in "the lock would prevent other inserts or selects on the table) Any expression used in the WHERE clause that isn't indexed will probably result in a Seq Scan. CASE expressions are no different than e.g. a function call regarding this. A stable function marked as "STABLE" (or even immutable) can be optimized differently (e.g. can be "inlined"), so yes that's a good recommendation.
- 1a527dd5 1y agohttps://pglocks.org/?pglock=AccessExclusiveLock https://pglocks.org/?pglock=AccessExclusiveLock is my go to reference. My other reference for a slightly different problem is https://www.thatguyfromdelhi.com/2020/12/what-postgres-sql-causes-table-rewrite.html https://www.thatguyfromdelhi.com/2020/12/what-postgres-sql-c...
- cellis 1y agoIt does scale. Just not to recall levels of traffic. Come on guys let's not rewrite everything in cassandra and rust now.
- dumbfounder 1y agoTransactional databases are not really the best tool for writing tons of (presumably) immutable records. Why are you using it for this? Why not Elastic?
- incoming1211 1y agoBecause transactional databases are perfectly fine for this type of thing when you have 0 to 100k users.
- 0xbadcafebee 1y agoThe 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.
- incoming1211 1y agoMost systems are based on the number of users performing operations on the application. Majority of people on HN never work on anything with more than 100k users, yet they introduce mountains of infrastructure and blame cloud for being expensive when they never needed that infrastructure to begin with.
- dumbfounder 1y agoThe article is about scaling issues with tons of writes, which I referenced by saying “tons of writes”. Yes, if there is no reason to scale it up you can just use a database. But that’s not the context.
- Kwpolska 1y ago[citaiton needed]
- immibis 1y agoTransactional databases are great, provided your write workload is low enough to fit on one server. If you have to scale up past that, you might have to use a different kind of database. But if transactions work for you, as they do for 99% of small-medium sites, they're amazing. Multi-master transactional databases are an open area of research, as far as I'm aware, but read-only replication is a solved problem. Therefore your write traffic, including your transaction overhead, has to fit within one server's capacity, while your read traffic can scale horizontally as much as you like.
- anonu 1y agowas hoping the solution was: we forked postgres. cool writeup!
- threecheese 1y agoI had a similar thought, as I was clicking through to TFA; “NOTIFY does not scale, but our new Widget can! Just five bucks”
- randall 1y agowow thanks for the heads up! no idea this was a thing.
- wordofx 1y agoIt’s not a thing.
- randall 1y agoi don’t understand. is the serialized write global lock a thing or no?
- wordofx 1y agoIf you’re going to use a tool. Make an attempt to use it properly. If you do dumb things. Dumb things will happen. Example. This blog post. This is not a problem for everyone using LISTEN/NOTIFY. It’s a problem with not knowing how to use it, then spreading bad info.
- 0xbadcafebee 1y agoRBDMS 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.
- hombre_fatal 1y agoBut those rules of thumb aren't true. People use Postgres for job queues and write-heavy applications. You'd have to at least accompany your memes with empirics. What is write-heavy? A number you might hit if your startup succeeds with thousands of concurrent users on your v1 naive implementation? Else you just get another repeat of everyone cargo-culting Mongo because they heard that Postgres wasn't web scale for their app with 0 users.
- 0xbadcafebee 1y agoThere are lots of ways to empirically tell what solutions are right for what applications. The simplest is using basic computer science like applying big-O notation, or using something designed as a message queue to do message queueing, etc. Slightly more complicated are simple benchmarks with immutable infrastructure.
- kccqzy 1y agoThere are OLTP and OLAP RDBMSes. Only OLAP ones are designed for read-heavy analyses.
- const_cast 1y agoPeople 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.
- doc_manhat 1y agoGot up to the TL;DR paragraph. This was a major red flag given the initial presentation of the discovery of a bottleneck: ''' When a NOTIFY query is issued during a transaction, it acquires a global lock on the entire database (ref) during the commit phase of the transaction, effectively serializing all commits. ''' Am I missing something - this seems like something the original authors of the system should have done due diligence on before implementing a write heavy work load.
- whatevaa 1y agoYou don't know how heavy it will be in new systems. As another commenter mentioned, you might never reach that point. Simplier is always better.
- kccqzy 1y agoI think it's just difficult to predict how heavy is heavy enough to make this a problem. FWIW I had worked at a startup with a much more primitive data storage system where serialized commits were actually totally fine. The startup never outgrew that bottleneck.
- Someone 1y agoIf “doing due diligence” involves reading the source code of a database server to verify a design, I doubt many people writing such systems do due diligence. The documentation doesn’t mention any caveats in this direction, and they had 3 periods of downtime in 4 days, so I don’t think it’s a given that testing would have hit this problem.
- callamdelaney 1y agoMy 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.
- bravesoul2 1y agoYeah I have no idea whether it would. But I'd load test it if it needed to scale. SQS may have been a good "boring" choice for this?
- kristianc 1y agoSounds 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.
- j16sdiz 1y agoKafka head of line blocking sucks.
- chrnola 1y agoGuaranteeing order has its tradeoffs. There is work happening currently to make Kafka behave more like a queue: https://cwiki.apache.org/confluence/display/KAFKA/KIP-932%3A+Queues+for+Kafka https://cwiki.apache.org/confluence/display/KAFKA/KIP-932%3A...
- LgWoodenBadger 1y agoIsn't this one of the things partitioning is meant to ameliorate? Either through partitions themselves, or through an appropriate partitioning strategy?
- const_cast 1y agoI 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.
- to11mtm 1y agoSeriously 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.
- caleblloyd 1y agoAmen! NATS is how we do AI streaming! JetStream subject per thread with an ordered consumer on the client.
- freeasinbeer2 1y agoAm I supposed to be able to tell from these graphs that one was faster than the other? Because I sure can't. What were the TPS numbers? What was the workload like? How big is the difference in %?
- deadbabe 1y agoHonestly 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.
- acdha 1y agoI 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.
- konsalexee 1y agoI think the title is stating this: "Postgres LISTEN/NOTIFY does not scale" That means for moderate cases you do not even have to care about this. 99% of PostgreSQL instances out there are not big "scale". As a sr. engineer is your responsibility to make a decision if you will build for "scale" from day zero or ignore this as you are mindful that this will not affect you until a certain point.
- maxdo 1y agoWhat a discovery , even Postgres itself doesn’t scale easy. There are so many solutions that are dedicated and cost you less.
- sleepy_keita 1y agoLISTEN/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.
- nightfly 1y ago> 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
- valenterry 1y agoHow about using a service that runs continuously and brings it's own pool? So basically all Java/JVM based solutions that use something like HiKariCP.
- nhumrich 1y agoYou can setup notify to run as a trigger on an events table. The job that listens shouldn't need a pool, it's a long lived connection anyway. Now you can keep using pgbouncer everywhere else.
- winterissnowing 1y ago[dead]
- spoaceman7777 1y agoThis is part of the basis for Supabase offering their realtime service, and broadcast, rather than supporting native LISTEN/NOTIFY. The scaling issues are well known.
- osigurdson 1y agoI 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.
- goodkiwi 1y agoI’ve been meaning to check out NATS - I’ve tended to default to Redis for pubsub. What are the main advantages? I use clickhouse and Postgres extensively
- sbstp 1y agoI've been disappointed by Nats. Core Nats is good and works well, but if you need stronger delivery guarantees you need to use Jetstream which has a lot of quirks, for instance it does not integrate well with the permission system in Core Nats. Their client SDKs are very buggy and unreliable. I've used the Python, Rust and Go ones, only the Go one worked as expected. I would recommend using rabbitmq, Kafka or redpanda instead of Nats.
- chatmasta 1y agoAre those recommendations based on using them all in the same context? Curious why you chose Kafka (or Redpanda which is effectively the same) over NATS.
- 1y ago
- baristaGeek 1y agoPostgres is a great DB, but it's the wrong tool for a write-heavy, high-concurrency, real-time system with pub-sub needs. You should split your system into specialized components: - Kafka for event transport (you're likely already doing this). - An LSM-tree DB for write-heavy structured data (eg: Cassandra) - Keep Postgres for queries that benefit from relational features in certain parts of your architecture
- baristaGeek 1y agoVery good article! Succinct, and very informative.
- ryanjshaw 1y agoIMO They don’t have a high concurrency DB writing system, they just think they do. Recordings can and should be streamed to an object store. Parallel processes can do transcription on those objects; bonus: when they inevitably have a bug in transcription, retranscribing meetings is easy. The output of transcription can be a single file also stored in the object store with a single completion message notification, or if they really insist on “near real-time”, a message on a queue for every N seconds. Much easier to scale your queue than your DB, eg Kafka partitions. A handful of consumers can read those messages and insert into the DB. Benefit is you have a fixed and controllable write load into the database, and your client workload never overloads the DB because you’re buffering that with the much more distributed object store (which is way simpler than running another database engine).
- fmajid 1y agoListen/Notify is potentially lossy and should not be used. At one of my previous companies we used it for cache invalidation (a trigger on tables would sent notify messages to invalidation Redis keys for potentially affected cache entries). We ended up ripping it out and replacing it with NSQ.io as a reliable transport.
- aaa12365 1y agohi
- merb 1y agoWouldn’t it be better nowadays to listen to the Wal. With a temporary replication slot and a publication just for this table and the id column?
- bjornsing 1y agoIf I’m not mistaken LISTEN/NOTIFY doesn’t work with connection poolers, and you can’t have tens of thousands of connections to a Postgres database. Not sure you need a more elaborate analysis than that to reach the same conclusion.
- calderwoodra 1y agoWhy doesn't LISTEN/NOTIFY work with connection poolers?
- cryptonector 1y agoBecause if you have N connections in your pool you're going to have to execute LISTEN on all N, or else the connection pool needs to be LISTEN-aware so it can process async notifies by calling some registered callback. I.e., the connection pool API has to be designed with this in mind. For that matter connection pools also need to be designed with the ability to run code upon connecting to create TEMP schema elements because PG lacks GLOBAL TEMP.
- ilitirit 1y ago> The structured data gets written to our Postgres database by tens of thousands of simultaneous writers. Each of these writers is a “meeting bot”, which joins a video call and captures the data in real-time. Maybe I missed it in some folded up embedded content, or some graph (or maybe I'm probably just blind...), but is it mentioned at which point they started running into issues? The quoted bit about "10s of thousands of simultaneous writers" is all I can find. What is the qualitative and quantitative nature of relevant workloads? Depending on the answers, some people may not care. I asked ChatGPT to research it and this is the executive summary: For PostgreSQL’s LISTEN/NOTIFY, a realistic safe throughput is: Up to ~100–500 notifications/sec: Handles well on most systems with minimal tuning. Low risk of contention. ~500–2,000 notifications/sec: Reasonable with good tuning (short transactions, fast listeners, few concurrent writers). May start to see lock contention. ~2,000–5,000 notifications/sec: Pushing the upper bounds. Requires careful batching, dedicated listeners, possibly separate Postgres instances for pub/sub. >5,000 notifications/sec: Not recommended for sustained load. You’ll likely hit serialization bottlenecks due to the global commit lock held during NOTIFY.
- cap11235 1y ago[flagged]
- ilitirit 1y agoWhat is wrong with you? Why would you even bother posting a comment like this? Maybe you also don't know what ChatGPT Research is (the Enterprise version, if you really need to know), or what Executive Summary implies, but here's a snippet of the 28 sources used: https://imgur.com/a/eMdkjAh https://imgur.com/a/eMdkjAh
- ants_a 1y agoIn that snippet are links to Postgres docs and two blog posts, one being the blog post under discussion. None of those contain the information needed to make the presented claims about throughput. To make those claims it's necessary to know what work is being done while the lock is held. This includes a bunch of various resource cleanup, which should be cheap, and RecordTransactionCommit() which will grab a lock to insert a WAL record, wait for it to get flushed to disk and potentially also for it to get acknowledged by a synchronous replica. So the expected throughput is somewhere between hundreds and tens of thousands of notifies per second. But as far as I can tell this conclusion is only available from PostgreSQL source code and some assumptions about typical storage and network performance.
- DumBthInker007 1y agoMy understanding: i think as postgres takes an exclusive lock to enqueue the notifications into a shared queue in PreCommit_Notify(), as the actual commit happens after notification was enqueued into the queue,as other transactions also try to notify but wait becacause of the lock ,so does the commit waits.
- winterrx 1y agoFunny, I got to their homepage and get 504'd
- winterrx 1y agoThey're the same company that ran into this, at least they're learning! > How WebSockets cost us $1M on our AWS bill
- seunosewa 1y agoThey have a history of not prioritising performance.
- vb-8448 1y agoI didn't see it in the article, can some tell me what is the scale of " many writers."?
- redskyluan 1y agoPostgres users often hit scaling issues — whether it's with LISTEN/NOTIFY, PGVector, or even basic relational queries. For startups, Postgres is a fantastic first choice. But plan ahead: as your workload grows, you’ll likely need to migrate or augment your stack.
- grumple 1y agoI’m mostly a MySQL user. Two things stand out: 1) the Postgres documentation does not mention that Notify causes a global lock or lock of any sort (I checked). That’s crazy to me; if something causes a lock, the documentation should tell you it does and what kind. Performance notes also belong in documentation for dbs. 2) why the hell does notify require a lock in the first place? Reading the comment this design seems insane; there’s no good reason to queue up notifications for transactions that aren’t committed. Just add the notifications in commit order with no lock, you’re building a db with concurrency, get used to it.
- deleted 1y ago[deleted]
- JoelJacobson 1y agoHey 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 to ~14 ms. To better understand the bottlenecks, I wrote both a benchmark tool and a proof-of-concept patch that replaces the O(N) backend scan with a shared hash table for the single-listener case — and it brings latency down to near-O(1), even with thousands of listeners. Full benchmark, source, and analysis here: https://github.com/joelonsql/pg-bench-listen-notify https://github.com/joelonsql/pg-bench-listen-notify No proposals yet on what to do upstream, just trying to gather interest and surface the performance cliff. Feedback welcome.
- infogulch 1y agoCool! This article and thread has already been referenced on the mailing list, maybe its worth mentioning this benchmark and experiment. https://www.postgresql.org/message-id/flat/CAM527d_s8coiXDA4xbJRyVOcNnnjnf%2BezPYpn214y3-5ixn75w%40mail.gmail.com https://www.postgresql.org/message-id/flat/CAM527d_s8coiXDA4... https://www.postgresql.org/message-id/flat/175222328116.3157497.2260121997954044239%40wrigleys.postgresql.org https://www.postgresql.org/message-id/flat/175222328116.3157...
- cryptonector 1y agoThat'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
- daitangio 1y agoI wrapped together a simple yet powerful queue system: https://github.com/daitangio/pque https://github.com/daitangio/pque I evaluated Listen/notify but it seems to loose messages if no one is listening, so its use case seems pretty limited to me (my 2 cents). Anyway, If you need to scale, I suggest an ad hoc queue server like rabbitmq.
- FZambia 1y agoFor real-time notifications, I believe Nats (https://nats.io https://nats.io) or Centrifugo (https://centrifugal.dev https://centrifugal.dev) are worth checking out these days. Messages may be delivered to those systems from PostgreSQL over replication protocol through Kafka as an intermediary buffer. Reliable real-time messaging comes with lots of complexities though, like late message delivery, duplicate message delivery. If the system can be built around at most once guarantees – can help to simplify the design dramatically. Depends on the use case of course, often both at least once and at most once should co-exist in one app.
- cryptonector 1y agoAnd Debezium.
- westurner 1y agoRe: Postgres LISTEN/NOTIFY and PgQueuer, which is built on LISTEN/NOTIFY: https://news.ycombinator.com/item?id=41284703#41285614 https://news.ycombinator.com/item?id=41284703#41285614
- FZambia 1y agoMany here recommend using Kafka or RabbitMQ for real-time notifications. While these tools work well with a relatively stable, limited set of topics, they become costly and inefficient when dealing with a large number of dynamic subscribers, such as in a messaging app where users frequently come and go. In RabbitMQ, queue bindings are resource-intensive, and in Kafka, creating new subscriptions often triggers expensive rebalancing operations. I've seen a use case for a messenger app with 100k concurrent subscribers where developers used RabbitMQ and individual queues for each user. It worked at 60 CPU on Rabbit side during normal situation and during mass reconnections of users (due to some proxy reload in infra) – it took up to several minutes for users to reconnect. I suggested switching to https://github.com/centrifugal/centrifugo https://github.com/centrifugal/centrifugo with Redis engine (combines PUB/SUB + Redis streams for individual queues) – and it went to 0.3 CPU on Redis side. Now the system serves about 2 million concurrent connections.
- odie5533 1y agoI wonder who works on centrifugo. Could be anyone.
- aryav07 1y agoNice to know about this, good article.
- mattxxx 1y agoThe article is good, but maybe a bit negative on the postgres feature. I think the article reads much better with the slant: "LISTEN/NOTIFY got us to this level of concurrency; here's how we diagnosed the performance cliff, and here's what we're doing now." Which is like... cool, you were able to scale pretty far and create a lot of value before you needed to find a new solution.
- Matthias247 1y agoClarification question: > When a NOTIFY query is issued during a transaction, it acquires a global lock on the entire database (ref) during the commit phase of the transaction, effectively serializing all commits. It only serializes commits where NOTIFY was issued as part of the transaction, right? Transactions which did not call NOTIFY should not be affected?
- gwbas1c 1y ago> our Postgres database > tens of thousands of simultaneous writers I'm surprised they aren't sharding at this scale. I wonder why?
- bhollis 1y agoThe pattern I've always used for this, which I suspect is what they landed on, is to have an optimistic notification method in a separate message queue that says "something changed that's relevant to you". Then you can dedupe that, etc. Then structure the data to easily sync what's new, and let the client respond to that notification by calling the sync API. That even lets you use multiple notification methods for notification. None of that involves having to have the database coordinate notifications in the middle of a transaction.
- fatih-erikli-cg 1y ago[dead]
- jedwards1211 1y agoOof. Now I’m looking into using an extension someone wrote to publish to Redis straight from Postgres, as a replacement for NOTIFY statements in triggers. Kind of a mess but any other way of waking up our app logic that walks change queues in Postgres seems worse