10 ms·

PG works really well as a message queue and there's several excellent implementations on top of it. Most systems are still going to need Redis involved just as

PG works really well as a message queue and there's several excellent implementations on top of it. Most systems are still going to need Redis involved just as a coordinator for other pub/sub related work unless you're using a stack that can handle it some other way (looking at BEAM here). But there are always going to be scenarios as an application grows where you'll find a need to scale specific pieces. Otherwise though, PostgreSQL by itself can get you very, very far.

dfee 3y ago
Worth noting that Postgres has a pubsub implementation built in: listen/notify. https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html
- brightball 3y ago
  Oh yea, definitely aware of it. I believe many of the queuing solutions utilize it as well. I've ready a lot of reports (on here) that it comes with several unexpected footguns if you really lean on it though.
- cpursley 3y ago
  Yep, I should add that. One of the libraries in my list (that I maintain) is WalEx: https://github.com/cpursley/walex/issues https://github.com/cpursley/walex/issues It subscribes to the Postgres WAL and let you do the same sort of thing you can do with listen/notify, but without the drawbacks like need for triggers or character limits.
  - brightball 3y ago
    Going to add this to my research list.
    - cpursley 3y ago
      Ping me if you have any questions. Long time fan of your blog.
      - brightball 3y ago
        That is really cool to hear, thank you. And I should have mentioned it before, but we have an open call for speakers for the Carolina Code Conference. This would make for an interesting talk I think.
  - maxbond 3y ago
    What's the drawback to a trigger? I would think that any overhead you recouped by avoiding a trigger would be offset by the overhead of sending the entire WAL to your listener, rather than the minimized subset of events that listener is interested in. (To be clear I do see other downsides to listen/notify and I think WalEx makes a lot of sense, I just don't understand this particular example.)
    - cpursley 3y ago
      You don’t send the entire WAL, just what you subscribe to - and you can even filter via SQL: https://github.com/cpursley/walex?tab=readme-ov-file#publication https://github.com/cpursley/walex?tab=readme-ov-file#publica...
      - cpursley 3y ago
        This post describes some of the other issues with listen/notify trigger approach: https://news.ycombinator.com/item?id=36323698 https://news.ycombinator.com/item?id=36323698
stickfigure 3y ago
> PG works really well as a message queue It's also worth noting that by using PG as a message queue, you can do something that's nearly impossible with other queues - transactionally enqueue tasks with your database operations. This can dramatically simplify failure logic. On the other hand, it also means replacing your message queue with something more scalable is no longer a simple drop-in solution. But that's work you might never have to do.
- johnthescott 3y ago
  yep.

dfee 3y ago

Worth noting that Postgres has a pubsub implementation built in: listen/notify. https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html

brightball 3y ago
Oh yea, definitely aware of it. I believe many of the queuing solutions utilize it as well. I've ready a lot of reports (on here) that it comes with several unexpected footguns if you really lean on it though.
cpursley 3y ago
Yep, I should add that. One of the libraries in my list (that I maintain) is WalEx: https://github.com/cpursley/walex/issues https://github.com/cpursley/walex/issues It subscribes to the Postgres WAL and let you do the same sort of thing you can do with listen/notify, but without the drawbacks like need for triggers or character limits.
- brightball 3y ago
  Going to add this to my research list.
  - cpursley 3y ago
    Ping me if you have any questions. Long time fan of your blog.
    - brightball 3y ago
      That is really cool to hear, thank you. And I should have mentioned it before, but we have an open call for speakers for the Carolina Code Conference. This would make for an interesting talk I think.
- maxbond 3y ago
  What's the drawback to a trigger? I would think that any overhead you recouped by avoiding a trigger would be offset by the overhead of sending the entire WAL to your listener, rather than the minimized subset of events that listener is interested in. (To be clear I do see other downsides to listen/notify and I think WalEx makes a lot of sense, I just don't understand this particular example.)
  - cpursley 3y ago
    You don’t send the entire WAL, just what you subscribe to - and you can even filter via SQL: https://github.com/cpursley/walex?tab=readme-ov-file#publication https://github.com/cpursley/walex?tab=readme-ov-file#publica...
    - cpursley 3y ago
      This post describes some of the other issues with listen/notify trigger approach: https://news.ycombinator.com/item?id=36323698 https://news.ycombinator.com/item?id=36323698

stickfigure 3y ago

> PG works really well as a message queue It's also worth noting that by using PG as a message queue, you can do something that's nearly impossible with other queues - transactionally enqueue tasks with your database operations. This can dramatically simplify failure logic. On the other hand, it also means replacing your message queue with something more scalable is no longer a simple drop-in solution. But that's work you might never have to do.

johnthescott 3y ago
yep.