Postgres LISTEN/NOTIFY actually scales(dbos.dev)
dbos.dev
Postgres LISTEN/NOTIFY actually scales
https://www.dbos.dev/blog/postgres-listen-notify-scalability
34 comments
> 5 orders of magnitude too small for another.
Nitpick on an otherwise good post, but I don’t think there are very many 6billion RPS systems out there, and those that do exist are almost certainly using bespoke, purpose-built tools
Nitpick on an otherwise good post, but I don’t think there are very many 6billion RPS systems out there, and those that do exist are almost certainly using bespoke, purpose-built tools
The highest I could think of is WhatsApp, which gets ~1.6 million RPS on average, and they use MQTT
I used to scrape Meta sites at over 200k steady rps for a couple of years, all by myself.
They did send lawyers to my door in the end though.
Someone had forgot IPv6 rate limit bucketing :)
They did send lawyers to my door in the end though.
Someone had forgot IPv6 rate limit bucketing :)
Tell more sounds like a good story
I work in manufacturing, you would be surprised at the volumes of sensor data being processed there for predictive maintenance.
MQTT is everywhere, indeed, because of how simple the protocol is (it’s very ubiquitous in IoT / embedded systems).
MQTT is everywhere, indeed, because of how simple the protocol is (it’s very ubiquitous in IoT / embedded systems).
WhatsApp is primarily 1 to 1 messaging. Why would this be hard to scale? Scaling horizontally, sharding, and message routing is all it takes, surely?
AWS IAM handles over 1.2 billion requests per second, based on a 2024 post, im sure its higher now https://aws.amazon.com/blogs/security/an-unexpected-discover...
Now think about the fanout (and fan-in like the ingress path) to other Meta systems from the front door of WhatsApp and you'll see higher scale workloads.
Maybe there would be more if it were more straightforward to do so?
For reference, I've seen Visa marketing materials that suggest their network can do ~70k TPS. There are not very many systems one could conceive of that could do useful work 1/s for ~every human on the planet.
> There are not very many systems one could conceive of that could do useful work 1/s for ~every human on the planet.
Roughly a hundred servers on 100 GbE ports each could handle one small UDP packet per human every second, and would have a plenty of cycles and RAM per human left to actually do some work.
That fits in a shipping container.
Illustrative of course: the point is that "for every human" is not far out there given how crazy powerful hardware has become.
Roughly a hundred servers on 100 GbE ports each could handle one small UDP packet per human every second, and would have a plenty of cycles and RAM per human left to actually do some work.
That fits in a shipping container.
Illustrative of course: the point is that "for every human" is not far out there given how crazy powerful hardware has become.
>> For reference, I've seen Visa marketing materials that suggest their network can do ~70k TPS.
> Roughly a hundred servers on 100 GbE ports each could handle one small UDP packet per human every second ...
While there is no correlation between the Visa claim of 70k tps to people on the planet, there is also no correlation to the number of servers needed to process UDP packets.
The "Visa network" is more of a trusted collaboration between a small number of banks operating multi-step transactions than it is an OSI Layer 1 - 5 concern.
> Roughly a hundred servers on 100 GbE ports each could handle one small UDP packet per human every second ...
While there is no correlation between the Visa claim of 70k tps to people on the planet, there is also no correlation to the number of servers needed to process UDP packets.
The "Visa network" is more of a trusted collaboration between a small number of banks operating multi-step transactions than it is an OSI Layer 1 - 5 concern.
> Illustrative of course: the point is that "for every human" is not far out there given how crazy powerful hardware has become.
There is a tendency in tech to forget that there were highly scalable systems, processing huge numbers of transactions, before the internet and “hyper scalars” were a thing.
It's also worth noting that all of those systems could be served by your phone.
Considering Visa's user Time zone and population density. I wouldn't be surprised if WeChat in China or UPI in India have higher TPS numbers.
But then again it would still be no where near a million TPS.
But then again it would still be no where near a million TPS.
Hm, what am I not getting? One transaction per second for every human on the planet would be 8 billion TPS, not 70k, no?
Yes, the point is it's difficult to even conceive of something that needs anywhere near the billions of TPS, because generally speaking, humans are not doing something with a computer once per second on average. Far lower. I might use my credit card less than once per day on average.
Ah, yes, humans don't, but computers do. I'd imagine there are plenty of systems that ingest automated data (eg sensors) that need billions of TPS.
Doing 1 request for every person on this planet every second?
What are these, TCP packets?
What are these, TCP packets?
Fair.
Could be base 2. ~1.3M RPS is a lot higher than most will ever require, but still within the realm of possibility.
I think if you expect to be under 60K/s and suddenly find yourself at 20K/s heading for 200K/s-- you have a better problem than if you built for 1M/s and actual load is 20K/s. The unexpected success of the former will pay for a lot of band-aids and scaling, while you're pretty stuck with the cost structure and upfront spent capital in the latter.
IMO one should design for actual anticipated scale with moderate margin, only exceeding this when it's relatively "free" to do so. (If you can buy bigger hardware for a few K, or if solutions are equivalent other than scalability, pick the bigger solution).
IMO one should design for actual anticipated scale with moderate margin, only exceeding this when it's relatively "free" to do so. (If you can buy bigger hardware for a few K, or if solutions are equivalent other than scalability, pick the bigger solution).
This is conventional wisdom, but I wonder. Labor is a material cost against most software businesses. Doing the Whatsapp thing where you just make extremely good choices upfront so that you can reach $10B scale with hardly any employees or infra footprint seems like a pretty good deal for the founding team. Plenty of other 2010s darlings are in structural trouble with stock-based comp because they are locked into these insanely overcomplicated architectures due to bad decisions from the startup and hypergrowth phases + path dependency.
This is an excellent post.
I’ve never worked somewhere that couldn’t reach the appropriate scale with a bit of work.
I have worked for a startup that burnt up its runway and went bust because an ambitious architect convinced middle management that it needed to rewrite the core monolith for massive scale without spotting it operated in an illiquid market.
I’ve never worked somewhere that couldn’t reach the appropriate scale with a bit of work.
I have worked for a startup that burnt up its runway and went bust because an ambitious architect convinced middle management that it needed to rewrite the core monolith for massive scale without spotting it operated in an illiquid market.
In this case it does by hitting hardware maximum. It scales until the max it is possible, due the bottleneck being in the hardware, not in the database or i/o. Check the article again:
> At maximum throughput, Postgres CPU is fully utilized, showing the database is actually saturated instead of bottlenecked on contention.
> At maximum throughput, Postgres CPU is fully utilized, showing the database is actually saturated instead of bottlenecked on contention.
It doesn’t necessarily follow that because Postgres is “fully utilised” it is hitting “hardware maximum”, or even that it’s not bottlenecked on contention.
As an example, spinlocks can push CPU usage very high whilst being an obvious symptom of contention.
As an example, spinlocks can push CPU usage very high whilst being an obvious symptom of contention.
Just sharing a data point and experience.
We had a lot of success with LISTEN/NOTIFY when we paired it with a Rust graphql subscription broker. 10s of thousands of subscriptions, but only 3 or 4 LISTEN connections (one for each host). All changes would be pushed out to all hosts, who would each manage the actual user subscriptions and choose what to actually publish.
This worked super well. In general, moving from hundreds of Ruby or Node hosts to just a few Rust hosts just allows so many simplifications and things that "don't scale" to actually work quite well.
We had a lot of success with LISTEN/NOTIFY when we paired it with a Rust graphql subscription broker. 10s of thousands of subscriptions, but only 3 or 4 LISTEN connections (one for each host). All changes would be pushed out to all hosts, who would each manage the actual user subscriptions and choose what to actually publish.
This worked super well. In general, moving from hundreds of Ruby or Node hosts to just a few Rust hosts just allows so many simplifications and things that "don't scale" to actually work quite well.
I‘m interested, which graphql library for Rust can you recommend?
Any graphql library would work. I like async-graphql except compilation is a bear due to all the macros. We wired LISTEN up to subscriptions using tokio mpmc channels. It was a great moment of "fearless concurrency", it all just worked.
I like 'async-graphql'. I tried 'juniper' but, at the time, it didn't have as many features. That may have changed over the last year or so since I looked at it.
I once was the CTO of a company that serviced about 100k requests per day across all our services. We grew to millions and eventually 10's of millions, but, somewhere along the way, an engineer on our team decided he wanted to build a queue off LISTEN/NOTIFY semantics in order to take advantage of strong consistency with the rest of our data model, which seemed reasonable given LISTEN/NOTIFY is not that hard to understand and we did need consistency guarantees for this workflow and this would remove the need for yet another place data got stored and transfered.
In practice, this eventually ended up being very awkward because extending the functionality (since we had "built" it) and had to work around internal pg semantics (we should have just moved off much sooner). It also did not scale well. We ended up getting a ton of disk contention on our RDS instance in non-obvious ways, and the vacuum runs on that table was a nightmare. Additionally, it was hard to get other engineers to really debug and take ownership of the system because they automatically viewed a queue (very easy to understand) implemented in a foreign way (off pg internals) as something "scary". It was emotional, not rational, but we are emotional beings, and I do not blame them. These were good engineers with a lot of other things on their plates.
Obviously, this is all hand-wavy without discussing the internal schema, indeces, etc. that we had set up, but my main takeaway with core technology from this experience was to always reach for the dumb, expected, simple thing. Even if it adds another moving piece in the infra stack. Unless I need very strong data consistency guarantees, it's always better to use something like SQS, Redis queues, etc. where the understanding is that it is just a queue (or at least the API contract suggests simplicity), and then everything needs to work around it.
The fewer mechanistic responsibilities per core data store, the better in my experience.
In practice, this eventually ended up being very awkward because extending the functionality (since we had "built" it) and had to work around internal pg semantics (we should have just moved off much sooner). It also did not scale well. We ended up getting a ton of disk contention on our RDS instance in non-obvious ways, and the vacuum runs on that table was a nightmare. Additionally, it was hard to get other engineers to really debug and take ownership of the system because they automatically viewed a queue (very easy to understand) implemented in a foreign way (off pg internals) as something "scary". It was emotional, not rational, but we are emotional beings, and I do not blame them. These were good engineers with a lot of other things on their plates.
Obviously, this is all hand-wavy without discussing the internal schema, indeces, etc. that we had set up, but my main takeaway with core technology from this experience was to always reach for the dumb, expected, simple thing. Even if it adds another moving piece in the infra stack. Unless I need very strong data consistency guarantees, it's always better to use something like SQS, Redis queues, etc. where the understanding is that it is just a queue (or at least the API contract suggests simplicity), and then everything needs to work around it.
The fewer mechanistic responsibilities per core data store, the better in my experience.
Not saying this in a mean way:
It seems to me this can be boiled down to "using things without understanding how they work doesn't scale". Yes, vanilla listen/notify doesn't scale. But the OP actually figured out how to make it scale. So your engineers don't have to.
As the community builds, over time, distributed systems, it also understands which brick can do what, and it turns out that starting with less bricks and adding some when you actually need them makes for healthier systems.
It seems to me this can be boiled down to "using things without understanding how they work doesn't scale". Yes, vanilla listen/notify doesn't scale. But the OP actually figured out how to make it scale. So your engineers don't have to.
As the community builds, over time, distributed systems, it also understands which brick can do what, and it turns out that starting with less bricks and adding some when you actually need them makes for healthier systems.
Nah this isn't mean at all. I think this is the correct takeaway. I actually felt like I understood how the system worked and it wasn't that difficult for me, but I also understood how, as we were adding engineers who were all very under water, the idea of learning our queueing system to modify a core feature it powered seemed like a big mental context switch (bigger than it actually was). I noted this in another comment, but I am likely discounting how big of an effect our scale/growth impacted peoples' ability to adapt this system. That said, I will pretty much always choose the simple/dumb/easy to understand thing from the start even if it adds another moving component.
Also the results given it the article are still worse than redis with zero work and no risk of breaking later, both in nb of req and latency.
And you can move redis to another server next later if needed to split the db and notification load.
And you can move redis to another server next later if needed to split the db and notification load.
It is so rare that a new moving part beats other factors. But the one thing I will say is that there's tremendous advantages to of all things, your queuing system being independent of other architectural pieces : it's the one component built to natively store and forward, so if you can keep its lifecycle separate then you can harness that to decouple other systems from each other during updates, troubleshooting, patch windows etc.
Great point.
> It is so rare that a new moving part beats other factors
Fair! I will restate that we were growing very fast, so that's probably an outlier environmental factor that I am potentially overly discounting. If that's not the case (likely for most startups), then maybe this is less of a problem.
> It is so rare that a new moving part beats other factors
Fair! I will restate that we were growing very fast, so that's probably an outlier environmental factor that I am potentially overly discounting. If that's not the case (likely for most startups), then maybe this is less of a problem.
Pretty sure if you didn't run the DB on networked disks you would have been fine.
I feel this is a management problem, and I disagree with the takeaway about adding a piece to the infra stack. "My devs don't want to work on part of our stack" is not a valid reason to add a new network node imo. Just make them work on it.
What is cheaper? Forcing my team to work on something they don't want to or adding a network node (that happens to also be the industry standard)? I used to think in absolutes about these kinds of things, but I came to realize along the way that people management is a process of learning which fires to take on. If I can avoid stripping away someone's autonomy, I will always do that even if I know they are not doing the correct thing (unless the wrong thing is in the critical path of the company).
The problem still needs to be solved (queue working/being up/being built around), but I am ok with the solution that might not be the best engineering solution but still delivers on the product requirements and extends the team's happiness (these systems need to be owned). These were not lazy or incompetent engineers.
The problem still needs to be solved (queue working/being up/being built around), but I am ok with the solution that might not be the best engineering solution but still delivers on the product requirements and extends the team's happiness (these systems need to be owned). These were not lazy or incompetent engineers.
I continue to love DBOS for how it just leverages Postgres (and now SQLite) properly. It's effortless to drop into an existing CRUD stack.
Once you start down the "durable workflows" path, you start seeing them everywhere.
My latest experiments are treating individual emails as durable workflows, where you, the people you're communicating with, agents and tools like GitHub or Attio all take turns in the flow.
https://housecat.com/blog/gmail-durable-workflows-sandbox-vm
Once you start down the "durable workflows" path, you start seeing them everywhere.
My latest experiments are treating individual emails as durable workflows, where you, the people you're communicating with, agents and tools like GitHub or Attio all take turns in the flow.
https://housecat.com/blog/gmail-durable-workflows-sandbox-vm
I think a lot of these kind of posts are standalone assessment of your problems, understanding and solutions. Its debatable to term something as lack of expertise if one is trying to work with default settings of a tool and expecting a certain performance. Everyone is doing a continuous learning with the failures.
1. What I find interesting is that the experiment seems to be using a DB server with 96 cores, 384 GB RAM (https://github.com/dbos-inc/dbos-postgres-benchmark/blob/mai...). This is very critical part of any such experiment, it should have been called out. The database is vertically scalable and that too has its limits
2. Who is making connection, and from where has its own impact on performance and overall latency
3. 60k may seem big number, however in real world the things which bring the systems down are the bursts of traffic, not the regular traffic.
Personally I would never start with such a big server unless I am a big business. Its > 100K cost for one production DB cluster if I include read replicas and cross region redundancy
1. What I find interesting is that the experiment seems to be using a DB server with 96 cores, 384 GB RAM (https://github.com/dbos-inc/dbos-postgres-benchmark/blob/mai...). This is very critical part of any such experiment, it should have been called out. The database is vertically scalable and that too has its limits
2. Who is making connection, and from where has its own impact on performance and overall latency
3. 60k may seem big number, however in real world the things which bring the systems down are the bursts of traffic, not the regular traffic.
Personally I would never start with such a big server unless I am a big business. Its > 100K cost for one production DB cluster if I include read replicas and cross region redundancy
Agreed
I feel like the article is leaving the most important part out ; how to allocate that "offset" (sequence number) which allows consumers to keep tabs on how far they have read and query if there are new messages in the fallback. There are a few schemes, some more creative than others, but it is not a trivial problem to solve without complexity or possibility of lock contention (or races with consumers if you do it the wrong way). Most people I guess have writers acquire a lock, for instance on a single row in a state table, for allocating the next sequence number for an event topic.
What is the best way? Perhaps a tool that reads CDC and writes allocated event sequence numbers to another table would work ok, or would that have long latency?
And that CDC processor should then do the NOTIFY too.
Also on the consumer side in some usecases batching can drastically improve throughput. You don't even use LISTEN/NOTIFY then. Just run the consumer in a loop and each iteration process all new unprocessed messages, and store your last sequence number processed between iterations.
What is the best way? Perhaps a tool that reads CDC and writes allocated event sequence numbers to another table would work ok, or would that have long latency?
And that CDC processor should then do the NOTIFY too.
Also on the consumer side in some usecases batching can drastically improve throughput. You don't even use LISTEN/NOTIFY then. Just run the consumer in a loop and each iteration process all new unprocessed messages, and store your last sequence number processed between iterations.
Related, presumably:
Postgres LISTEN/NOTIFY does not scale - https://news.ycombinator.com/item?id=44490510 - July 2025 (321 comments)
Postgres LISTEN/NOTIFY does not scale - https://news.ycombinator.com/item?id=44490510 - July 2025 (321 comments)
I recall that in the first release that supported LISTEN/NOTIFY there was a performance issue around it (poor locking IIRC), which today the "bad post" mentioned in here corrects in a errata just after their first paragraph.
Since the correction apparently dates from May 8th, I think that a post from July 24th might want to acknowledge that the popular post asserting this feature doesn't (didn't?) scale was not made in bad faith or was even wrong about their claims at the time.
Since the correction apparently dates from May 8th, I think that a post from July 24th might want to acknowledge that the popular post asserting this feature doesn't (didn't?) scale was not made in bad faith or was even wrong about their claims at the time.
If you mean the optimizations coming in Postgres 19, the original post addresses this:
> As an aside, there’s been some online discussion of a Postgres patch (https://github.com/postgres/postgres/commit/282b1cde9dedf456...) related to this issue. This patch (to be released in Postgres 19) does not remove the global lock or fix the bottleneck we observed. Instead, it optimizes the narrower case where there are many notification channels and each listener is waiting only on a specific channel.
> As an aside, there’s been some online discussion of a Postgres patch (https://github.com/postgres/postgres/commit/282b1cde9dedf456...) related to this issue. This patch (to be released in Postgres 19) does not remove the global lock or fix the bottleneck we observed. Instead, it optimizes the narrower case where there are many notification channels and each listener is waiting only on a specific channel.
It did fix the original "Postgres LISTEN/NOTIFY does not scale" [1] post's problem though, which was mentioned in an update of that article:
Update: Fixed in Postgres core
This commit has eliminated the bottleneck in the postgres core.
Credit to Joel Jacobson and the core postgres contributors for resolving this.
One way it explicitly doesn't scale (unless something has changed since I last checked and my quick search failed me) is the hard limit on 8000 bytes of data in a notification. If your notification doesn't make sense to exist as a row (where you can just give an ID), then that makes it hard to use. I had a web game and the events were transient descriptions of changes in state that didn't make sense to store in the database, and could be bigger than that, so it didn't work for that use case.
As an implementer of a scalable notification system I'd be sure to cap the notification size.
- Keeps messages O(1) so I can focus on scaling in the amount of notifications
- Tells whoever runs into this that,
- Keeps messages O(1) so I can focus on scaling in the amount of notifications
- Tells whoever runs into this that,
- I didn't planned for arbitrarily large messages as they *might* otherwise grind performance to a halt.
- They *might* be misusing my notification systemThe tested machine appears big and with 96 vcpu + 384 gb yielding 20k writes sounds too low. Jumping to 60k is very good. But still too small a throughput for what the machine is capable of.
Batching ensures you run at cpu & memory speeds and only pay significant latency for the flush - which usually linux kernel coalesces well if concurrent.
Batching ensures you run at cpu & memory speeds and only pay significant latency for the flush - which usually linux kernel coalesces well if concurrent.
Yeah, with that machine spec, I think if anything, the post goes to show that LISTEN/NOTIFY does actually _not_ scale well. A CDC-based solution would give you a multiple of these numbers on much smaller (and cheaper) hardware.
My recommendation to everyone out there willing to put in the effort of writing an article; don't put a naff low effort AI generated image at the top of it (or anywhere in it..). It turns me off before I've even read the first word.
I went through this process when I was designing the sync server for Digital Carrot.
In the end, I decided to just go with the simplest solution possible. In my case it's just a barebones Go gRPC service that uses an in memory channel to send notifications between connected clients.
The reality is that this simple Go server will scale up to about 1000 simultaneously connected customers on about 2gb of RAM. I don't expect to have more than that many paying customers, and if I do I can always just throw a bigger VM at the problem.
Engineers love to over complicate things in the name of infinite scalability, when in reality you can save a lot of time and effort by just understanding the scope of the actual problem you're trying to solve. Fingers crossed that this will become an issue for me some day, but until then most of us just don't need to worry about it!
In the end, I decided to just go with the simplest solution possible. In my case it's just a barebones Go gRPC service that uses an in memory channel to send notifications between connected clients.
The reality is that this simple Go server will scale up to about 1000 simultaneously connected customers on about 2gb of RAM. I don't expect to have more than that many paying customers, and if I do I can always just throw a bigger VM at the problem.
Engineers love to over complicate things in the name of infinite scalability, when in reality you can save a lot of time and effort by just understanding the scope of the actual problem you're trying to solve. Fingers crossed that this will become an issue for me some day, but until then most of us just don't need to worry about it!
I had a similar setup, scaled pretty well with some GOGC tuning. I had a small, simple "router" using channels https://github.com/urjitbhatia/gopipe and except for the per connection 16ish kb network overhead per socket, you can get away with a lot of performance with a small hand rolled service.
1000 connections is a fairly low number by modern standards, c10k “challenges” are like twenty years old by now.
The real questions are:
- how many messages per second are you processing on that 2gb machine (and using how many cpus)?
- does your message processing involve transaction handling, including saving data ti disk durably?
No offense but it really seems you’re comparing apples and oranges, with your use case being much much simpler than the one described.
The real questions are:
- how many messages per second are you processing on that 2gb machine (and using how many cpus)?
- does your message processing involve transaction handling, including saving data ti disk durably?
No offense but it really seems you’re comparing apples and oranges, with your use case being much much simpler than the one described.
These are all very conservative back of the napkin calculations. Also, this isn't 1000 connections. It's 1000 customers, each of which can consume dozens of connections.
My point here is that it is important to match the tech stack to the challenge you're facing. When I started thinking about how to solve this problem my first reaction was to design an overly complicated distributed message queue using PG Notify, Redis, Kafka or something along those lines. The key takeaway here is that I realized that I probably wouldn't end up with more than 1000 customers, so I just needed to design a system that could comfortably handle that level of traffic without much effort. If, by some miracle, my business goes crazy viral, I know that my cloud provider can probably handle up to 200,000 customers by just updating a slider in my dashboard, which is way more business than I want anyway.
Engineers love to fantasize about Google levels of scale, but that's just not realistic for a lot of services.
My point here is that it is important to match the tech stack to the challenge you're facing. When I started thinking about how to solve this problem my first reaction was to design an overly complicated distributed message queue using PG Notify, Redis, Kafka or something along those lines. The key takeaway here is that I realized that I probably wouldn't end up with more than 1000 customers, so I just needed to design a system that could comfortably handle that level of traffic without much effort. If, by some miracle, my business goes crazy viral, I know that my cloud provider can probably handle up to 200,000 customers by just updating a slider in my dashboard, which is way more business than I want anyway.
Engineers love to fantasize about Google levels of scale, but that's just not realistic for a lot of services.
Engineers also love to reinvent the wheel.
What you wrote is essentially technical debt.
What you wrote is essentially technical debt.
the article mentions lock contention on the global queue, but i think doesn’t mention the other problem with a fixed size global queue: a single slow reader on one channel can block all writes to all channels. (at least this was a failure mode a few years ago! perhaps it’s changed since i last looked)
So in summary, Postgres NOTIFY actually scales if you make your application not write to the database? That's a bit of a tough sell...
There are so many in important things built that run perfectly fine on one postgres database, maybe redis as well and one to three of the smaller ec2 instances.
Even AI generated art needs taste, looks so bland & off-point.
Sure it "scales" if clients batch commands to amortize the per-request cost.
The word "scale" does a lot of load-bearing, maybe it's just not a useful or productive word in practice.
The word "scale" does a lot of load-bearing, maybe it's just not a useful or productive word in practice.
turns out the database already had pub/sub, it scales fine, and the entire message broker setup was just emotional support infrastructure
Much of the FUD around using LISTEN/NOTIFY in production comes from people who have never done so. It of course has its limits, and we should remain aware of them. Much like the limits of every piece of tech in our stacks.
Choose database queue technology https://news.ycombinator.com/item?id=37636841
Choose database queue technology https://news.ycombinator.com/item?id=37636841
Personally I love it for cache invalidation. I used it on an older especially on project originally built without caching in mind that then added caching for immutable data without caching for mutable data in mind. It fell to me to add caching of mutable data. I found it rather convenient to put NOTIFY triggers on cached tables and have a LISTENer that will delete the corresponding rows from memcached when it gets a notification. It’s basically impossible to out-scale it in that use case because the scale is intrinsically linked to number of writes the leader in your database processes.
and here was I thinking ( when creating same cache invalidation system ) that I was the only one that came up with this patch. LMAO
I had to use it in production and it abdolutely doesn't scale and actually looses messages if the load is high enough.
Is the optimization only possible using dbos? is not clear to me if this mean a way to tune normal PG
The core optimization is to buffer notifications in-memory and send them in a batch instead of sending them as part of every transaction. So that's a general-purpose optimization for Postgres apps using LISTEN/NOTIFY.
So this is not inside a trigger but on the app connected to pg?
Yes, per the article they “scaled” Postgres to meet their requirements by altering their usage pattern to avoid hitting the bottleneck.
If I understnad right, they are saying it scales (to theri needs) with a custom patch to pg changing their semantics, right?
It does seem interesting, and possibly welcome if there were a configuration option or even a way to set individual notifies as serialized or not.
It does seem interesting, and possibly welcome if there were a configuration option or even a way to set individual notifies as serialized or not.
To be clear, it's not a custom patch to pg itself, but an application-side buffering and batching optimization.
Ah, thanks. I am not following at all how an application can do this.
I'm reading the code for this and I'm like yeah it doesn't scale.
Like forget about debugging this code, does anybody even know what it is doing?
Something interesting is that postgres PostGIS index for location finding scaled well enough for Uber. They eventually switched to something else but I don't think it was due to scaling problems.
That makes one wonder, why is there only an option for globally ordered NOTIFYs?
I would expect to have a little more tuning knobs available:
- explicitly unordered
- ordered per table, but not globally
I would expect to have a little more tuning knobs available:
- explicitly unordered
- ordered per table, but not globally
The ceiling of LISTEN/NOTIFY is small enough that you need to pay attention, and I personally like to have at least an order of magnitude of slack left over even after my most pessimistic load numbers are accounted for, but it's still plenty for a lot of projects, and the integration with the rest of the DB, its availability, its not being another service you have to devops, it's definitely not something that should be simply dismissed out of hand as an option. Even the original 2K/s number they cite is a lot of messages for some systems that are more properly measured in seconds per message.