Re:Postgres as Queue
by EvanAnderson on Saturday February 10, 2024 @07:42AM
(#39325768)
Came here to mention Service Broker. I've used it in production in multi-server configurations for a number of years. It works really well but it's terrible obscure. Nobody seems to know it's even there.
The learning curve is steep and there are some easy anti-patterns you can fall into. Once you grok it, though, it really is very good.
The LISTEN functionality is absolutely there. Your activation procedure is invoked by the server upon receipt of records into the queue. It's very slick. No polling at all.
Also Azure is adding SQL Server trigger support
https://learn.microsoft.com/en-us/azure/azure-functions/func...
https://learn.microsoft.com/en-us/sql/database-engine/config...
I haven’t had the opportunity to use it in production yet - but it’s worth keeping in mind.
I’ve helped fix poor attempts of “table as queue” before - once you get the locking hints right, polling performs well enough for small volumes - from your list above, the only thing I can’t recall there being in sql server is a LISTEN - but I’m not really an expert on it.