I 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.

• jelder

  today at 1:08 PM

  "use Postgres for everything" is certainly wrong, eventually. It's still the second-best choice for every new project, and most products will never see the traffic levels that justify using something more specialized. Obviously, recall.ai hit the level of traffic where Postgres was no longer ideal. I bet they don't regret it for the other parts of their product.

  • closeparen

    today at 4:03 PM

    They aren't even questioning its use as a database, just as an event bus.

• riedel

  today at 8:56 AM

  Actually LISTEN/NOTIFY does also not scale the other way. Immich also moved to that pg for everything mentality (trying to remove redis dependencies). The problem: postgres needs a WAL flush for all notifications. I ran immich on my HDD-NAS. The result was constant noise because the pg backed socket.io backend issues constant keep alive messages.

• dathinab

  today at 2:16 PM

  Honestly whatever kind of DB you are speaking about always be wary of "niche/side features" which don't fit it's core design goals, they tend to have unexpected limitations.

  listen/notify isn't necessary a replacement for redis or other pub/sub systems, redis pub/sub and similar isn't necessary a replacement for idk. Kafka or similar queue/messaging system

  but a lot of companies have (for modern standards) surprisingly small amounts of data, very even a increase by 2,3,4x still isn't that big. In that case listen/notify and similar might just work fine :shrug:

  also same is true the other way around, depending on you application you can go redis only, as long as you data volume stays small enough and needs for transactional/sync are reasonable simple enough (with watch+exec, NX,XX options etc. and maybe some redis side lua scripts you can do quite a lot for data synchronization). Issue with that is that stylistically redis data sync/transaction code is often much more similar to writing atomic data-structures then to SQL transactions, and even for SQL transactions there is a trend of devs severely overestimating what they provide, so often you are better of not touching on it when you can avoid it, also BTW. redis has something very similar to sqlite or Notify where "basically" (oversimplified by a lot) there is only one set of writes done at a time ;) (and then afterwards distributed to replicas), just that outside of some micro lua scripts you don't really run much logic outside of some NX, XX checks etc. so it's not blocking much and it's "more or less" all just in memory not touching a WAL (again oversimplified).

• ownagefool

  today at 8:04 AM

  Largely agree. Functionality wise if you don't have many jobs, using the database as the queue is fine.

  However, I've been in several situations where scaling the queue brings down the database, and therefore the app, and am thus of the opinion you probably shouldn't couple these systems too tightly.

  There are pros and cons, of course.

  • mike_hearn

    today at 8:36 AM

    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 can have financial impact.

    This seems like another case where Postgres gets free marketing due to companies hitting its technical limits. I get why they choose to make lemonade in these cases with an eng blog post, but this is a way too common pattern on HN. Some startup builds on Postgres then spends half their eng budget at the most critical growth time firefighting around its limits instead of scaling their business. OpenAI had a similar blog post a couple of months ago where they revealed they were probably spending more than quarter of a million a month on an Azure managed Postgres, and it had stopped scaling so they were having to slowly abandon it, where I made the same comment [1].

    Postgres is a great DB for what you pay, but IMHO well capitalized blitzscaling startups shouldn't be using it. If you buy a database - and realistically most Postgres users do anyway as they're paying for a cloud managed db - then you might as well just buy a commercial DB with an integrated queue engine. I have a financial COI because I have a part time job there in the research division (on non-DB stuff), so keep that in mind, but they should just migrate to an Oracle Database. It has a queue engine called TxEQ which is implemented on top of database tables with some C code for efficient blocking polls. It scales horizontally by just adding database nodes whilst retaining ACID transactions, and you can get hosted versions of them in all the major clouds. I'm using it in a project at the moment and it's been working well. In particular the ability to dequeue a message into the same transaction that does other database writes is very useful, as is the exposed lock manager.

    Beyond scaling horizontally the nice thing about TxEQ/AQ is that it's a full message queue broker with all the normal features you'd expect. Delayed messages, exception queues, queue browsing, multi-consumer etc. LISTEN/NOTIFY is barely a queue at all, really.

    For startups like this, the amount of time, money and morale they are losing with all these constant stories of firefights just doesn't make sense to me. It doesn't have to be Oracle, there are other DBs that can do this too. But "We discovered X about Postgres" is a eng blog cliché by this point. You're paying $$$ to a cloud and GPU vendor anyway, just buy a database and get back to work!

    [1] https://news.ycombinator.com/item?id=44074506

    • jumski

      today at 12:01 PM

      Using queues in atomic, transactional way was a core principle for building https://pgflow.dev - having whole workflow state transactionally updated alongside the work on the in db queue really simplifies a lot of things: debugging is easier, audit log is easy, reporting, stats etc are one SQL query away.

      • mike_hearn

        today at 12:14 PM

        Nice! I'm also using queues as part of a workflow engine.

        • jumski

          today at 12:33 PM

          Oh really? Would love to check it out and borrow some ideas! :)

      • pbronez

        today at 12:56 PM

        Looks interesting- but why the Supabase dependency? That’s a much tighter requirement than a vanilla PostgreSQL extension or something like PostgREST

        • jumski

          today at 4:57 PM

          Valid point!

          So pgflow is really agnostic and Postgres is it's fundamental dependency. All components are modular and ready to be adapted to other runtimes.

          It's just that Supabase is what I use and I figured out this will be my first platform, but the abstraction to port to others is there!

    • osigurdson

      today at 5:52 PM

      >> but they should just migrate to an Oracle Database

      No big tech companies or unicorn type startups are using Oracle. Is your claim that they are all wrong?

      >> Some startup builds on Postgres then spends half their eng budget at the most critical growth time firefighting around its limits instead of scaling their business

      This is why I suggest starting with some kind of normal queue / stream mechanism and columnar DB if needed. It isn't even harder than using one DB, particularly if you are using niche features.

    • ownagefool

      today at 12:29 PM

      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 ).

      • mike_hearn

        today at 3:29 PM

        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.

    • sgarland

      today at 1:43 PM

      > "We discovered X about Postgres" is a eng blog cliché by this point.

      It really is, and it’s often surprising to me how basic some of the issues are being discovered. Like Figma, when they waited a shocking amount of time add [0] PgBouncer and read replicas. This is such a well-trod path that it’s baffling to me why you wouldn’t add it once it’s clear you have a winning product. At the very least, PgBouncer (or PgCat, or any other connection pooler / proxying service) - it adds negligible cost per month (in comparison to DB read replicas) to run a couple of containers with a load balancer.

      Re: Oracle, as much as I despise the company for its litigious practices, I’ll hand it to you that the features your DB has are astonishing. RAC is absolutely incredible (on paper - I’ve never used it).

      [0]: https://www.figma.com/blog/how-figma-scaled-to-multiple-data...

    • dathinab

      today at 2:49 PM

      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 message" becomes a directly coupled with DB transactions you have created something very brittle and cumbersome.

      To be fair that might still be the best solution for some situations.

      But the better solution is to make sure you treat a queue as message passing system and handle messages as messages with the appropriate delivery semantics. And if you can't because idk. idempotency logic is supper unreliable then there indeed is a problem, but its not in the missing cross transactions but how you write that logic (missing ?_tooling_, strict code guidelines people actually comply with, interface regression checks, tests (including prop/fuzz tests, regression tests, integration/e2e tests etc., not just "dump" unit test)).

      > just migrate to an Oracle Database.

      In my experience while Oracle DB is very powerful but also very cumbersome in a lot of ways and if you need thing only they can provide you most likely already fucked up big time somewhere else in your design/architecture. Sure if you are at that point Oracle can lightly be the cheaper solution. But still preferable you never endup there.

      As a side note, there are also a lot of decent plugins which can provide similar capabilities to PG, but they tend to have the issue that they aren't part of managed PG solutions and self managing PG (or most other reasonable powerful DB) can be a huge pain, and then yes Oracle can be a solution.

      Still the amount of startups which had a overall good experience are in my experience overall non existing in my experience. (But there are some pretty big companies/projects I know of which have a overall good experience with Oracle.)

      > constant stories of firefights

      If you mean stories on HN, than that isn't a meaningful metric, you will only hear about the "interesting" stories which mostly are about fire fighting or "using pg for everything is grate" but rarely the majority of in-between stories and boring silent successes. If it's about stories from you carriers and asking dev friends you have what their experience is then it is more meaningful. But in a bubble (like this answer of mine is, without question, in a bubble).

      Generally I think people really overestimate how representative HN is, idk. about the US but outside of it _huge_ parts of the IT industry are not represented by HN in any meaningful way. I would say in my country HN is _at most_ representative for 1/4 of the industry, but that 1/4 also contains many of the very very motivated software developers. But also very few of the "that my work but not my calling", "bread and butter" work software devs, which are often 1/3 to over 1/2 of devs in most countries as far as I can tell.

      • osigurdson

        today at 6:11 PM

        >> To be fair that might still be the best solution for some situations.

        It is arguable. Let's say your team knows Postgres well from a relational standpoint. Now they need to do something messages and require some type of notification / messaging. There is a learning curve here anyway. I'd argue they should spend it on more standard approaches which are not harder to start with. Of course, if you know that your site / service will only be used by yourself and your grandmother do whatever you want (just use a text file or better yet just call her instead).

      • imtringued

        today at 3:38 PM

        >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 storing the last successfully processed message ID in the database and updating it in the same transaction that has processed the message. If an error occurs you roll the transaction back, which also rolls back the last message ID. The consumer will automatically re-request the failed message on the next attempt, giving you out of the box idempotency for at least once messaging.

        • tracker1

          today at 9:34 PM

          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.

• KronisLV

  today at 11:04 AM

  > Postgres/ClickHouse/NATS

  Maybe throw in a dedicated key-value store like Redis or Valkey.

  Oh and maybe something S3 compatible like MinIO, Garage or SeaweedFS for storing bunches of binary data.

  With all of that, honestly it should cover most of the common workloads out there! Of course, depends on how specialized vs generic you like your software to be.

  • whaleofatw2022

    today at 11:16 AM

    NATS does KV pretty well now (didn't have expiration till earlier this year)

    • indeyets

      today at 1:55 PM

      Nats is getting there, but not yet.

      Redis is still much more powerful: lists, sorted sets and bazillion of other data structures

• goodkiwi

  today at 3:44 AM

  I’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

    today at 4:24 AM

    I'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.

    • FZambia

      today at 12:48 PM

      Client SDKs are often a major challenge in systems like these. In my experience, building SDKs on top of asynchronous protocols is particularly tricky. It's generally much easier to make the server-side part reliable. The complexity arises because SDKs must account for a wide range of usage patterns - and you are not controlling the usage.

      Asynchronous protocols frequently result in callback-based or generator-style APIs on the client side, which are hard to implement safely and intuitively. For example, consider building a real-time SDK for something like NATS. Once a message arrives, you need to invoke a user-defined callback to handle it. At that point, you're faced with a design decision: either call the callback synchronously (which risks blocking the socket reading loop), or do it asynchronously (which raises issues like backpressure handling).

      Also, SDKs are often developed by different people, each with their own design philosophy and coding style, leading to inconsistency and subtle bugs.

      So this isn't only about NATS. Just last week, we ran into two critical bugs in two separate Kafka SDKs at work.

    • PaoloBarbolini

      today at 8:57 AM

      I've had the same experience and I fixed part of the problem by writing my own Rust client, Watermelon. It's still missing a lot of features but at least I'm not blocked by weird decisions taken by upstream.

    • chatmasta

      today at 6:23 AM

      Are 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.

  • osigurdson

    today at 6:07 AM

    NATS gives you regular pub/sub but also streams as well (similar to Kafka along with strong durability guarantees, etc).

• cryptonector

  today at 3:07 PM

  I think PG could relax the ordering thing with NOTIFYs since... it seems a bit silly, but NOTIFYs already are unsafe to use because there is no authorization around channel access, so one might as well use change data capture (logical replication, basically) instead.

• fathomdeez

  today at 3:47 AM

  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.

  • tsimionescu

    today at 6:53 AM

    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, this may change, and even require data migrations.

    None of this means you have to or even should use stored procedures, triggers, or listen/notify. I'm just making the point that there is no clean separation between "data" and "business logic".

    • ehansdais

      today at 8:06 AM

      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.

      • brightball

        today at 3:19 PM

        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.

  • chatmasta

    today at 6:22 AM

    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.

    • dathinab

      today at 3:01 PM

      I think, It's because the locking is part of the transaction commit locking, but yes it should be mentioned.

      But it's oversimplified a case of "high queue load f* up the availability/timings for other DB operations" (and themself).

      And thats a generic problem you have, even if just due to "generic CPU/WAL/disk load" if you put your queue into your DB even iff that specific lock would be somehow solved with some atomic concurrent algorithms or similar (not sure if that even is possible).

      So in general make your storage db, and queue a different service (and you cache too), even if it uses the same kind of storage. (Through technically there are clever in-between solutions which run their own queue service but still use you DB for final storage but have a ton of caching, in memory locking etc. to remove a huge part of the load from the DB. )

    • perlgeek

      today at 6:44 AM

      I really hope somebody reading this article (or HN thread) writes a doc patch to mention that.

      I'm unlikely to get it myself today, and by tomorrow I've probably already forgotten it :-(

      • Cthulhu_

        today at 8:20 AM

        > and by tomorrow I've probably already forgotten it :-(

        You're self-aware and are writing about it, why not maintain and add it to your todo list if this is a recurring issue?

    • cryptonector

      today at 3:20 PM

      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).

  • physix

    today at 5:08 AM

    That may hold to a certain extent for relational databases where your business model doesn't align well with physical model (tables). Although you might wonder why stored procedures and triggers were invented.

    In databases where your domain is also your physical data model, coupling business logic to the database can work quite well, if the DBMS supports that.

    https://medium.com/@paul_42036/entity-workflows-for-event-dr...

  • bevr1337

    today at 3:52 AM

    > the data does not get to decide what happens next based on itself.

    Then why bother with a relational database? Relations and schemas are business logic, and I'll take all the data integrity I can get.

    • jl6

      today at 6:42 AM

      I think an argument can be made that relations, schemas and constraints encode a kind of business logic that is intrinsic to the definition and integrity of the data, while other types of business logic represent processes that may hinge on data but aren’t as tightly coupled to it. Similar to the difference between a primitive type and a function.

      I guess some will argue that their business logic is special and really is so tightly coupled to the data definition that it belongs in the database, and I’m not going to claim those use cases don’t exist, but I’ve seen over-coupling far more often than under-coupling.

      This is why I say: Applications come and go, but data is forever.

    • Jailbird

      today at 3:59 AM

      I've seen both of these philosophies. I liken them to religions, the believers are devout. Code is King vs the DB is King.

      I'm personally Code is King, and I have my reasons (like everyone else)

      • sgarland

        today at 2:22 PM

        Every company I’ve been at that relied on application code to handle referential integrity had orphaned rows, and incidents related to data errors or the absurd pipelines they had built to recreate what FK constraints and triggers already do.

        RDBMS are extremely well-tested pieces of software that do their job incredibly well. To think that you could do better, or even equally as well, is hubris. If you want to trade those guarantees for “velocity” go right ahead, but you also need to take into account the inevitable incidents and recoveries that will occur.

      • whstl

        today at 11:42 AM

        And both of those philosophies will lead to bad engineering.

        There are things that work better, are safer and simpler to do on the database, and things that work better, are safer and simpler in code. And those things might change depending on context, technology, requirements, size of project, experience of contributors, etc.

        Forcing round pegs into square holes will always lead to brittle code and brittle products, often for more cost (mental and financial!) than actually using each tool correctly.

      • ako

        today at 10:12 AM

        It’s really not about code is better or database it better, it’s mostly about locality: if you want to update thousands of records, you can’t pull those records into a separate process, update them there and then write back. So you put your code next to the data in the database. Stored procedures are just code deployed to a database container…

        • 0xFEE1DEAD

          today at 12:12 PM

          Sure you can, I've done it plenty of times. I'm genuinely curious why you think it's not possible.

          The only reasons I can think of:

          - you're rewriting a legacy system and migrate parts incrementally

          - data compliance

          - you're running a dangerous database setup

          I try my best to avoid putting any business logic inside databases and see stored procedures only as a temporary solution.

          • bevr1337

            today at 2:14 PM

            Although I'm partial to a SPROC, I do not deploy them because I understand my colleagues might throw me from a window. But without going full tilt DB-as-the-application,

            The DB can make much stronger guarantees about transactions and updates the closer that logic happens to itself. In the world of cloud computing, this can be a cost savings for ingress/egress too.

      • IgorPartola

        today at 6:05 AM

        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 single set of database models. This would mean that critical constraints on the shape of the data could be defined and respected at all times vs some developer on my team forgetting to include some critical check in their data update routine.

        • sgarland

          today at 2:25 PM

          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.

          • IgorPartola

            today at 10:33 PM

            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.

      • dotancohen

        today at 12:54 PM

        I believe that both code and data are kings, under different realms. Code is king of the "what we're doing today" realm. Data is king of the "what's possible tomorrow" realm.

        Both have their place in business.

  • platzhirsch

    today at 4:08 AM

    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.

    • IgorPartola

      today at 6:01 AM

      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.

      • cryptonector

        today at 3:22 PM

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

        • IgorPartola

          today at 10:35 PM

          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.

        • tracker1

          today at 9:49 PM

          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.

  • whstl

    today at 11:37 AM

    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 things: data validation and queue atomicity.

    On the other hand, lots of potential issues can also arise by putting other parts of business logic to the database, for example, calling HTTPS endpoints from inside the DB itself is highly problematic.

    The reality is that the world is not black and white, and being an engineer is about navigating this grey area.

    • cryptonector

      today at 3:23 PM

      Thank you for bringing some sanity into this discussion.

  • panzi

    today at 4:04 AM

    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).

    • fathomdeez

      today at 4:17 AM

      I don't think of those as business logic, per se. They're just validity checks on what the data should look like before it's written to disk - they're not actionable in the way L/N is. That being said, constraints usually end up being duplicated outside the db anyway, but having them where the data rests (so you don't have to assume every client is using the correct constraint code) makes sense.

      • panzi

        today at 8:34 PM

        I see. Further I have used triggers to automatically populate log tables or aggregate statistics on write. Why do I need fast statistics? For API limits. Customers have N free operations per months and such, so I have to query that on every operation. Do you consider these things as business logic that don't belong in the database?

    • Lio

      today at 7:20 AM

      Rails fully supports constraints and encourages you to use them.

      You can either execute SQL in your migration or use add_check_constraint.

      • panzi

        today at 8:28 PM

        Back when I used Rails the sentiment was: You don't need foreign keys, this is all handled by ActiveRecord.

    • Footkerchief

      today at 5:37 AM

      You still use constraints even if you put all your business logic in stored procedures.

    • parthdesai

      today at 12:33 PM

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

      • sgarland

        today at 2:41 PM

        FK Constraints on partitioned tables has been a solved problem for years for Postgres. MySQL still doesn’t support them, unfortunately.

        For sharding, Vitess kind of supports them; Citus fully supports them.

        You’re correct that they do impact performance to an extent, but as a counter argument, if your data is incorrect, it doesn’t matter how quickly you wrote it.

      • cryptonector

        today at 3:28 PM

        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.

  • cryptonector

    today at 3:18 PM

    You're reaching the wrong conclusion, probably because of confirmation bias. Certainly this LISTEN/NOTIFY problem does not lead to your conclusion, nor does it support it. After all if you were relying on LISTEN/NOTIFY you could instead rely on logical replication decoding / CDC instead. And heck, you could even have a client connected to the same database that uses logical decoding to pick up events worth NOTIFYing about and then does just that, but without burdening any other transactions.

  • KronisLV

    today at 11:07 AM

    > That's what application code is for.

    I've seen people who disagree with that statement and say that having a separate back end component often leads to overfetching and in-database processing is better. I've worked on some systems where the back end is essentially just passing data to and from stored procedures.

    It was blazing fast, but working with it absolutely sucked - though for whatever reason the people who believe that seem to hold those views quite strongly.

  • sgarland

    today at 2:17 PM

    Disagree; these issues come up when people use more advanced features of DBs without having the requisite DB expertise on staff. I’ll give OP that Postgres’ docs do not mention this gotcha (and props to them for drilling down to source code!), but by and large, these issues are from people operating via tech blogs.

    The DB is - or should be - the source of truth for your application. Also, since practically everyone is using cloud RDBMS with (usually) networked storage, the latency is atrocious. Given those, it seems silly to rely on an application to react to and direct changes to related data.

    For example, if you want to soft-delete customer data while maintaining the ability to hard-delete, then instead of having an is_deleted and/or deleted_at column, have a duplicate table or tables, and an AFTER DELETE trigger on the originals that move the tuples to the other tables.

    Or if you want to have get_or_create without multiple round trips (and you don’t have Postgres’ MERGE … RETURNING), you can easily accomplish this with a stored procedure.

    Using database features shouldn’t be seen as verboten or outdated. What should be discouraged is not treating things like stored procedures and triggers as code. They absolutely should be in VCS, should go the same review process as anything else, and should be well-documented.

  • Cthulhu_

    today at 8:19 AM

    It really depends, but it's also a factor of time, that is, "back in the day", databases were designed to serve many different clients, nowadays a common practice is to have a 1:1 relationship between a database and a client application.

    Of course, this is sometimes abused and taken to extremes in a microservices architecture where each service has their own database and you end up with nastiness like data duplication and distributed locking.

    • sgarland

      today at 2:46 PM

      > Of course, this is sometimes abused and taken to extremes in a microservices architecture where each service has their own database and you end up with nastiness like data duplication and distributed locking.

      Not to mention the difficulty in maintaining referential integrity with all of that duplicated data. There are various workarounds, but at that point it feels very much like we’re recreating a shared DB, but shittily, and netting zero benefits.

  • djfivyvusn

    today at 9:21 AM

    That's purely because nobody knows how to write SQL let alone stored procedures. If stored procedures had better devex they'd be used for most of your app.

    • sgarland

      today at 2:32 PM

      Postgres lets you write stored procedures out of the box in pgSQL, C, Tcl, Perl, and Python. There are also 3rd party extensions for most languages you might want, including Rust and JS.

      More broadly, not knowing how to write SQL is a very solvable problem, and frankly anyone accessing an RDBMS as a regular part of their job should know it. Even if you’re always using an ORM, you should understand what it’s doing so you can understand the EXPLAIN output you’ll probably be looking at eventually.

      • v5v3

        today at 2:37 PM

        >... 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

        • sgarland

          today at 2:55 PM

          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.

          • v5v3

            today at 3:35 PM

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

• today at 8:27 AM

• v5v3

  today at 8:28 AM

  Isn't Kafka the Postgresql of pub/sub

  I.e. use Kafka unless you have a explicit reason not to?

  So why Nats?

  • evnix

    today at 8:46 AM

    After working with NATS, I wouldn't want to touch Kafka even with a long stick. Its just too complex and a memory hog for no good reason. It doesn't have all the features that NATS supports as well.

    • v5v3

      today at 9:50 AM

      What about the Kafka V2, Pulsar?

  • the_duke

    today at 10:00 AM

    Kafka is far from trivial to operate, for one thing, even post zookeeper.

    • ahoka

      today at 12:07 PM

      And it's kinda wrong to use as a queue (in most cases), being a log stream you can seek in.

• j45

  today at 12:11 PM

  There’s no reason this article and start with Postgres for everything can’t be true.

  In the beginning having fewer parts to connect and maintain lets the needs and bottlenecks of the actual application emerge.

  If it was listen/notify in such a scenario at some volume where optimizing it isn’t in the cards… so be it. It would be some time down the road before sharding a function into a specific subsystem like what you described.

  Appreciate learning about the Postgres/Clickhouse/nats combo. If there might be an article if the three together that you liked would be happy to read and learn.

• riku_iki

  today at 5:28 PM

  > However, it does help to contrast the much more common, "use Postgres for everything" type sentiment.

  I think sentiment is to use "for everything in 99% business cases", which involves few 100GB of data with some thousands QPS, and could be handled by PG very well.

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 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

No proposals yet on what to do upstream, just trying to gather interest and surface the performance cliff. Feedback welcome.

• cryptonector

  today at 3:36 PM

  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

  • maxbond

    today at 10:08 PM

    > 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.)

Postgres 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.

• nightpool

  today at 6:41 PM

  What about Heroku made Erlang clustering difficult? It's had the same DNS clustering feature that Fly has, and they've had it since 2017: https://devcenter.heroku.com/articles/dyno-dns-service-disco....

  • sorentwo

    today at 6:55 PM

    The problem was with restrictive connections, not DNS based discovery for clustering. It wasn't possible (as far as I'm aware) to connect directly from one dyno to another through tcp/udp.

    • nightpool

      today at 7:16 PM

      That is not an issue when using Private Spaces, which have been available since 2015

• cpursley

  yesterday at 9:09 PM

  How did you resolve this? Did you consider listening to the WAL?

  • sorentwo

    yesterday at 9:21 PM

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

    • MuffinFlavored

      today at 12:16 AM

      > Another important change was removing insert triggers, partially for the exact reasons mentioned in this post.

      What did you replace them with instead?

      • sorentwo

        today at 1:28 AM

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

  • parthdesai

    yesterday at 9:30 PM

    Distributed Erlang if application is clustered, redis if it is not.

    Source: Dev at one of the companies that hit this issue with Oban

• alberth

  yesterday at 10:00 PM

  I didn’t realize Oban didn’t use Mnesia (Erlang built-in).

  • sorentwo

    yesterday at 10:17 PM

    Very very few applications use mnsesia. There’s absolutely no way I would recommend it over Postgres.

    • arcanemachiner

      yesterday at 11:08 PM

      I have heard the mnesia is very unreliable, which is a damn shame.

      I wonder if that is fixable, or just inherent to its design.

      • sb8244

        today at 12:04 AM

        My understanding is that mnesia is sort of a relic. Really hard to work with and lots of edge / failure cases.

        I'm not sure if it should be salvaged?

    • tecleandor

      today at 2:53 AM

      I think RabbitMQ still uses by default for its metadata storage. Is it problematic?

      • schaum

        today at 4:18 AM

        They are in the process of migrating away from it https://www.rabbitmq.com/docs/metadata-store

    • asg0451

      today at 1:46 AM

      can you explain why?

      • spooneybarger

        today at 2:56 AM

        Mnesia along with clustering was a recipe for split brain disasters a few years ago I assume that hasn't been addressed.

      • ahoka

        today at 12:10 PM

        I have only worked with a product that used it, so no direct experience, but one problem that was often mentioned is split-brains happening very frequently.

Many 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 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.

I'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). 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

• qianli_cs

  today at 1:03 AM

  My colleague did some internal benchmarking and found that LISTEN/NOTIFY performs well under low to moderate load, but doesn't scale well with a large number of listeners. Our findings were pretty consistent with this blog post.

  (Shameless plug [1]) I'm working on DBOS, where we implemented durable workflows and queues on top of Postgres. For queues, we use FOR UPDATE SKIP LOCKED for task dispatch, combined with exponential backoff and jitter to reduce contention under high load when many workers are polling the same table.

  Would love to hear feedback from you and others building similar systems.

  [1] https://github.com/dbos-inc/dbos-transact-py

  • mind-blight

    today at 2:22 AM

    Nice! I'm using DBOS and am a little active on the discord. I was just wondering how y'all handled this under the hood. Glad to hear I don't have to worry much about this issue

  • eatonphil

    today at 10:57 AM

    Why not read the WAL?

    • qianli_cs

      today at 4:54 PM

      We considered using WAL for change tracking in DBOS, but it requires careful setup and maintenance of replication slots, which may lead to unbounded disk growth if misconfigured. Since DBOS is designed to bolt onto users' existing Postgres instances (we don't manage their data), we chose a simpler, less intrusive approach that doesn't require a replication setup.

      Plus, for queues, it's so much easier to leverage database constraints and transactions to implement global concurrency limit, rate limit, and deduplication.

• singron

  yesterday at 9:33 PM

  Polling is the way to go, but it's also very tricky to get right. In particular, it's non-trivial to make a reliable queue that's also fast when transactions are held open and vacuum isn't able to clean tuples. E.g. "get the first available tuple" might have to skip over 1000s of dead tuples.

  Holding transactions open is an anti-pattern for sure, but it's occasionally useful. E.g. pg_repack keeps a transaction open while it runs, and I believe vacuum also holds an open transaction part of the time too. It's also nice if your database doesn't melt whenever this happens on accident.

  • time0ut

    today at 12:22 AM

    An approach that has worked for me is to hash partition the table and have each worker look for work in one partition at a time. There are a number of strategies depending on how you manage workers. This allows you to only consider 1/Nth of the dead tuples, where N is the number of partitions, when looking for work. It does come at the cost of strict ordering, but there are many use cases where strict ordering is not required. The largest scale implementation of this strategy that I have done had 128 partitions with a worker per partition pumping through ~100 million tasks per day.

    I also found LISTEN/NOTIFY to not work well at this scale and used a polling based approach with a back off when no work was found.

    Quite an interesting problem and a bit challenging to get right at scale.

    • j16sdiz

      today at 1:13 AM

      Can't change the number of partition dynamically.

      Additional challenge if jobs comes in funny sizes

      • AlisdairO

        today at 4:01 AM

        Depending on exactly what you need, you can often fake this with a functional index on mod(queue_value_id, 5000). You then query for mod(queue_value_id,5000) between m and n. You can then dynamically adjust the gap between m and n based on how many partitions you want

    • dfsegoat

      today at 12:36 AM

      If there were a toy or other public implementation of this, I would love to see it.

    • CBLT

      today at 2:10 AM

      This is how Kafka does it. Kafka has spent years working on the rough edges (e.g. partition resizing), haven't used it recently though.

  • atombender

    today at 9:59 AM

    Dead tuples is a real and significant problem, not just because it has to skip the tuples, but because the statistics that drive the planner don't account for them.

    I found this out the hard way when I had a simple query that suddenly got very, very slow on a table where the application would constantly do a `SELECT ... FOR UPDATE SKIP LOCKED` and then immediately delete the rows after a tiny bit of processing.

    It turned out that with a nearly empty table of about 10-20k dead tuples, the planner switched to using a different index scan, and would overfetch tons of pages just to discard them, as they only contained dead tuples. What I didn't realize is that the planner statistics doesn't care about dead tuples, and ANALYZE doesn't take them into account. So the planner started to think the table was much bigger than it actually was.

    It's really important for these uses cases to tweak the autovacuum settings (which can be set on a per-table basis) to be much more aggressive, so that under high load, the vacuum runs pretty much continuously.

    Another option is to avoid deleting rows, but instead use a column to mark rows as complete, which together with a partial index can avoid dead tuples. There are both pros and cons; it requires doing the cleanup (and VACUUM) as a separate job.

    • singron

      today at 7:10 PM

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

      • atombender

        today at 7:14 PM

        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.

        • singron

          today at 9:10 PM

          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.

          • atombender

            today at 9:39 PM

            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.

            • menthe

              today at 10:30 PM

              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.

              • atombender

                today at 10:34 PM

                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.

  • leontrolski

    today at 4:36 AM

    > also fast when transactions are held open

    In my linked example, on getting the item from the queue, you immediately set the status to something that you're not polling for - does Postgres still have to skip past these tuples (even in an index) until they're vacuumed up?

• broken_broken_

  today at 5:33 AM

  I have implemented polling against a cluster of mixed mariadb/mysql databases which do not offer listen/notify. It was a pain in the neck to get right.

  - The batch size needs to be adaptative for performance, latency, and recovering smoothly after downtime.

  - The polling timeouts, frequency etc the same.

  - You need to avoid hysteresis.

  - You want to be super careful about not disturbing the main application by placing heavy load on the database or accidentally locking tables/rows

  - You likely want multiple distributed workers in case of a network partition to keep handling events

  It’s hard to get right especially when the databases at the time did not support SKIP LOCKED.

  In retrospect I wish I had listened to the WAL. Much easier.

• cpursley

  yesterday at 9:10 PM

  Have you played with pgmq? It's pretty neat: https://github.com/pgmq/pgmq

  • edoceo

    yesterday at 10:29 PM

    Another thing for @leontrolski to add to the benchmarks - which I cannot wait to read.

    • cpursley

      today at 12:19 AM

      There's a pretty cool solution built on pgmq called pgflow:

      https://www.pgflow.dev/concepts/how-pgflow-works

• cryptonector

  today at 3:38 PM

  Instead of LISTEN/NOTIFY you could listen to the wal / logical replication stream.

  Or you could have a worker whose only job is to listen to the wal / logical replication stream and then NOTIFY. Being the only one to do so would not burden other transactions.

  Or you could have a worker whose only job is to listen to the wal / logical replication stream and then publish on some non-PG pubsub system.

• RedShift1

  yesterday at 9:29 PM

  I use polling with back off up to one minute. So when a workload is done, it immediately polls for more work. If nothing found, wait for 5 seconds, still nothing 10 seconds, ... until one minute and from then on it polls every minute until it finds work again and the back off timer resets to 0 again.

• TkTech

  today at 12:42 AM

  With that experience behind you, would you have feedback for Chancy[1]? It aims to be a batteries-included offering for postgres+python, aiming for hundreds of millions of jobs a day, not massive horizontal worker scaling.

  It both polls (configurable per queue) and supports listen/notify simply to inform workers that it can wake up early to trigger polling, and this can be turned off globally with a notifications=false flag.

  [1]: https://github.com/tktech/chancy

• aurumque

  yesterday at 9:03 PM

  I'll take the shameless plug. Thank you for putting this together! Very helpful overview of pg locks.

  • notarobot123

    today at 6:08 PM

    It'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.

• sorentwo

  today at 2:06 AM

  Ping requires something persistent to check. That requires creating tuples, and most likely deleting them after they’ve been consumed. That puts pressure on the database and requires vacuuming in ways that pubsub doesn’t because it’s entirely ephemeral.

  Not to mention that pubsub allows multiple consumers for a single message, whereas FOR UPDATE is single consumer by design.

Right, 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 (there's a few useful links in here)

• williamdclt

  yesterday at 9:20 PM

  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 if there’s gotchas

  Edit: found it, it’s pg_logical_emit_message

  • gunnarmorling

    today at 6:34 AM

    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 they monitor [3].

    [1] https://speakerdeck.com/gunnarmorling/ins-and-outs-of-the-ou...

    [2] https://www.infoq.com/articles/wonders-of-postgres-logical-d...

    [3] https://www.morling.dev/blog/mastering-postgres-replication-...

    • brightball

      today at 3:22 PM

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

    • williamdclt

      today at 8:58 AM

      Ha, your [2] is how I learnt about it! Thanks :)

  • cryptonector

    today at 3:42 PM

    `pg_logical_emit_message()` is great and better than `NOTIFY` in terms of how it works, but...

    `pg_logical_emit_message()` perpetuates/continues the lack of authz around `NOTIFY`.

    • williamdclt

      today at 3:45 PM

      What do you mean by this? What authz would you expect/like?

      • cryptonector

        today at 7:11 PM

        I'd like to say that only some roles can NOTIFY to some channels. Similarly for alternatives to LISTEN/NOTIFY.

  • cyberax

    yesterday at 9:50 PM

    One annoying thing is that there is no counterpart for an operation to wait and read data from WAL. You can poll it using pg_logical_slot_get_binary_changes, but it returns immediately.

    It'd be nice to have a method that would block for N seconds waiting for a new entry.

    You can also use a streaming replication connection, but it often is not enabled by default.

    • williamdclt

      yesterday at 10:13 PM

      I think replication is the way to go, it’s kinda what it’s for.

      Might be a bit tricky to get debezium to decode the logical event, not sure

      • gunnarmorling

        today at 6:36 AM

        Debezium handles logical decoding messages OOTB. There's also an SMT (single message transform) for decoding the binary payload: https://debezium.io/documentation/reference/stable/transform....

      • cyberax

        today at 1:10 AM

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

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

• denysonique

  yesterday at 10:21 PM

  For node.js users there is postgres.js that can listen to the Postgres WAL and emit node events that can be handled by application code.

• meesles

  yesterday at 10:23 PM

  Yeah until vendors butcher Postgres replication behaviors and prevent common paths of integrating these capabilities into other tools. Looking at you AWS

I 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

  today at 1:02 AM

  Sounds like ChatGPT appreciated the post

  • acdha

    today at 1:49 AM

    If you think they’re a bot, flag and move on. No need for a derail about writing style.

  • yrds96

    today at 12:54 PM

    I'm ESL, so I often check my grammar on ChatGPT, and 99% of the time it includes em dashes in the corrected sentences, which I remove or just replace with commas or hyphens to sound more natural. So maybe this was not entirely written but just revised by ChatGPT.

  • CaliforniaKarl

    today at 1:31 PM

    I did not use ChatGPT—nor any AI—in writing the post. I'm curious, would you mind emailing—or replying—with what made you think that it was written by AI? Or why you do not believe my statement?

  • jjgreen

    today at 8:34 AM

    Just for the em-dashes? Some humans also use them.

    • Gigachad

      today at 8:36 AM

      It’s also the fact it’s just a summary of the post content without anything extra or any opinions.

      • jjgreen

        today at 8:41 AM

        Fair point

    • TrackerFF

      today at 9:18 AM

      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.

      • cryptonector

        today at 3:50 PM

        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 —.

        • tracker1

          today at 10:18 PM

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

Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?

• nick_

  yesterday at 8:47 PM

  My 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?

  • zbentley

    yesterday at 10:32 PM

    This 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.

  • foota

    yesterday at 10:28 PM

    Wouldn't you need to then commit to remove the entries from the temp table?

    • zbentley

      yesterday at 10:33 PM

      No, 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.

• parthdesai

  yesterday at 9:48 PM

  You lose transactional guarantees if you notify outside of the transaction though

  • hombre_fatal

    yesterday at 10:02 PM

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

    • parthdesai

      yesterday at 10:35 PM

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

      • saltcured

        yesterday at 11:04 PM

        For 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.

        • valenterry

          today at 3:29 AM

          > you can make the recipient poll the table(s) of record for relevant state

          That is tricky due to transactions and visibility. How do you write the poller to not miss events that were written by a long/blocked transaction? You'd have to set the poller scan to a long time (e.g. "process events that were written since now minus 5minutes") and then make sure transactions are cancelled hard before those 5minutes.

        • parthdesai

          today at 12:21 AM

          fwiw - 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.

      • Groxx

        today at 1:18 AM

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

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

        • cheesekunator

          today at 2:56 AM

          98% of developers can't see it

  • gwbas1c

    today at 5:15 PM

    ... And working outside of the guarantee is harder, especially if you're in a "move fast and break things because we can fix it later" mode.

    Anyway, the article indicates that the fix was very simple and primarily in the application layer. Makes me wonder if someone was getting "creative" when they used LISTEN/NOTIFY.

• soursoup

  yesterday at 8:40 PM

  Isn’t it standard practice to have a separate TCP stream for NOTIFY or am I mistaken

  • remram

    yesterday at 8:57 PM

    You mean for LISTEN?

• zerd

  today at 4:57 AM

  That would make the locked time shorter, but it would still contend on the global lock, right?

My kneejerk reaction to the headline is ‘why would it?’.

It’s unsurprising to me that an AI company appears to have chosen exactly the wrong tool for the job.

• kristianc

  today at 12:34 AM

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

  • const_cast

    today at 1:26 AM

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

    • physix

      today at 5:32 AM

      "Sprawling distributed systems".

      I like that. Sounds like a synonym for "Platform Engineering". :-)

      I remember being amazed that lambda architecture was considered a kind of reference, when it looked to me more like a workaround.

      We like to build IT cathedrals, until we have to run them.

      • const_cast

        today at 8:25 AM

        If there's one thing I took away from school, it's that distributed systems are hard. More failure points and much more communication hops. Serialization into deserialization into serialization again over network hops.

  • v5v3

    today at 8:31 AM

    Better to be successful with simple tech and have a minor 'blow up', then over engineer and go bust.

  • oulipo

    today at 7:53 AM

    Not sure I get it... how would you replicate this functionality with Kafka? You'd still need to have the database LISTEN to changes and push it to Kafka no?

  • j16sdiz

    today at 1:19 AM

    Kafka head of line blocking sucks.

    • LgWoodenBadger

      today at 5:26 PM

      Isn't this one of the things partitioning is meant to ameliorate? Either through partitions themselves, or through an appropriate partitioning strategy?

    • chrnola

      today at 2:37 AM

      Guaranteeing 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...

• bravesoul2

  yesterday at 11:19 PM

  Yeah 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?

• TheTaytay

  today at 2:53 AM

  Because documentation doesn’t warn about this well-loved feature effectively ruins the ability to perform parallel writes, and because everything else in Postgres scales well.

  I think it’s a reasonable assumption. Based on the second half of your comment, you clearly don’t think highly of “AI companies,” but I think that’s a separate issue.

For real-time notifications, I believe Nats (https://nats.io) or Centrifugo (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

  today at 3:53 PM

  And Debezium.

The 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."

If 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

  today at 12:02 PM

  Why doesn't LISTEN/NOTIFY work with connection poolers?

  • cryptonector

    today at 3:52 PM

    Because 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.

The 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.

Wouldn’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?

Facebook’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.

Postgres 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

• ryanjshaw

  today at 5:30 AM

  IMO 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).

• baristaGeek

  today at 3:33 AM

  Very good article! Succinct, and very informative.

If 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

  yesterday at 9:18 PM

  possibly, 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).

Clarification 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?

Rls and triggers dont scale either

• shivasaxena

  yesterday at 9:12 PM

  Yeah, 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.

  • lelanthran

    yesterday at 11:13 PM

    > Yeah, I'm going to remove triggers in next deploy of a POS system since they are adding 10-50ms to each insert.

    Do you expect it to be faster to do the trigger logic in the application? Wouldn't be slower to execute two statements from the application (even if they are in a transaction) than to rely on triggers?

  • candiddevmike

    yesterday at 10:58 PM

    How do you handle trigger logic that compares old/new without having a round trip back to the application?

    • SoftTalker

      yesterday at 11:58 PM

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

      • candiddevmike

        today at 12:17 AM

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

        • const_cast

          today at 1:27 AM

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

          • shivasaxena

            today at 5:48 PM

            Yes, we already have all of our business logic in postgres functions(create_order, create_partial_payment etc).

            Doing the extra work in stored procedures is noticeably faster than relying on triggers.

  • nine_k

    yesterday at 11:48 PM

    Hmm, imho, triggers do scale, they are just slow. But as you add more connections, partitionss, and CPUs, the slowness per operation remains constant.

    • ants_a

      today at 10:20 AM

      Triggers are not even particularly slow. They just hide the extra work that is being done and thus sometimes come back to bite programmers by adding a ton of work to statements that look like they should be quick.

  • brikym

    yesterday at 10:48 PM

    Have you tried deferring them?

  • GuinansEyebrows

    yesterday at 9:20 PM

    that, and keeping your business logic in the database makes everything more opaque!

    • lelanthran

      yesterday at 11:12 PM

      > 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

        yesterday at 11:38 PM

        I 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.

      • GuinansEyebrows

        today at 3:04 PM

        you make good points; i'm overcorrecting from past trigger abuses :)

• Spivak

  yesterday at 9:26 PM

  Neither do foreign keys the moment you need to shard. Turns out that there's no free lunch when you ask your database to do "secret extra work" that's supposed to be transparent-ish to the user.

  • mulmen

    yesterday at 10:49 PM

    Does that only apply when you need to shard within tenants?

    If each tenant gets an instance I would call that a “shard” but in that pattern there’s no need for cross-shard references.

    Maybe in the analytics stack but that can be async and eventually consistent.

Out 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`

• hans_castorp

  today at 5:27 AM

  > 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

  today at 7:29 AM

  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-c...

• franckpachot

  yesterday at 9:26 PM

  Can 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

This 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.

Seriously 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

  today at 1:47 AM

  Amen! NATS is how we do AI streaming! JetStream subject per thread with an ordered consumer on the client.

I wrapped together a simple yet powerful queue system:

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.

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

• nightfly

  today at 1:26 AM

  > 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

• nhumrich

  today at 4:43 AM

  You 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.

• valenterry

  today at 3:31 AM

  How 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.

You 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

  yesterday at 8:52 PM

  it 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).

> our Postgres database

> tens of thousands of simultaneous writers

I'm surprised they aren't sharding at this scale. I wonder why?

They're the same company that ran into this, at least they're learning! > How WebSockets cost us $1M on our AWS bill

There’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

  yesterday at 9:17 PM

  Agreed, 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

    yesterday at 9:45 PM

    Agreed.

    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.

• thom

  yesterday at 10:06 PM

  Yeah, 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.

• tomrod

  yesterday at 9:24 PM

  Assuming you skip select transaction, or require logging on it because your regulated industry had bad auditors, then every transaction changes something.

Postgres 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.

My 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.

LISTEN/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

  today at 12:04 AM

  It'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?

I didn't see it in the article, can some tell me what is the scale of " many writers."?

Nice to know about this, good article.

Am 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 %?

It does scale. Just not to recall levels of traffic. Come on guys let's not rewrite everything in cassandra and rust now.

They have a history of not prioritising performance.

RBDMS 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.

• const_cast

  today at 1:31 AM

  People 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.

  • 0xbadcafebee

    today at 2:31 AM

    That is the same logic that led every failed design I've seen in my career to take months (if not years) and tons of money to fix. "YOLO engineering" is simple at first and a huge pain in the ass later. Whereas actually correct engineering is slightly painful at first and saves your ass later.

    The people who design it walk away after a few years, so they don't give a crap what happens. The rest of us have to struggle to support or try to replace whatever the lumbering monstrosity is.

  • today at 2:16 AM

• hombre_fatal

  yesterday at 10:14 PM

  But 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

    today at 2:25 AM

    There 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

  yesterday at 10:23 PM

  There are OLTP and OLAP RDBMSes. Only OLAP ones are designed for read-heavy analyses.

Got 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.

• kccqzy

  yesterday at 10:21 PM

  I 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

  today at 7:03 AM

  If “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.

• whatevaa

  today at 5:50 AM

  You 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.

Funny, I got to their homepage and get 504'd

Sounds like one centralized Postgres instance, am I understanding that correctly? Wouldn’t meeting bots be very easy to parallelize across single-tenant instances?

What a discovery , even Postgres itself doesn’t scale easy. There are so many solutions that are dedicated and cost you less.

Transactional 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

  yesterday at 9:39 PM

  Because transactional databases are perfectly fine for this type of thing when you have 0 to 100k users.

  • 0xbadcafebee

    today at 2:09 AM

    The 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.

• Kwpolska

  yesterday at 9:43 PM

  [citaiton needed]

Re: Postgres LISTEN/NOTIFY and PgQueuer, which is built on LISTEN/NOTIFY: https://news.ycombinator.com/item?id=41284703#41285614

I’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.

Honestly 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

  today at 2:08 AM

  I 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.

wow thanks for the heads up! no idea this was a thing.

• wordofx

  yesterday at 10:15 PM

  It’s not a thing.

  • randall

    today at 1:22 AM

    i don’t understand. is the serialized write global lock a thing or no?

was hoping the solution was: we forked postgres.

cool writeup!

• threecheese

  yesterday at 10:32 PM

  I had a similar thought, as I was clicking through to TFA; “NOTIFY does not scale, but our new Widget can! Just five bucks”

[dead]

[dead]

hi

> 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

  today at 7:12 AM

  [flagged]

  • ilitirit

    today at 8:54 AM

    What 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

    • ants_a

      today at 10:43 AM

      In 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.

      • ilitirit

        today at 1:06 PM

        > In that snippet are links to Postgres docs and two blog posts

        Yes, that's what a snippet generally is. The generated document from my very basic research prompt is over 300k in length. There are also sources from the official mailing lists, graphile, and various community discussions.

        I'm not going to post the entire outout because it is completely beside the point. In my original post, I expliclity asked "What is the qualitative and quantitative nature of relevant workloads?" exactly because it's not clear from the blog post. If, for example, they only started hitting these issues with 10k simultaneous reads/writes, then it's reasonable to assume that many people who don't have such high work loads won't really care.

        The ChatGPT snippet was included to show that that's what ChatGPT research told me. Nothing more. I basically typed a 2-line prompt and asked it to include the original article. Anyone who thinks that what I posted is authoritative in any way shouldn't be considering doing this type of work.