We use PG Notify at work extensively and it's the source of a lot of pain and suffering. Not because of the functionality itself, more so because what we did to ourselves by using it in the way we did.

I think this could be great for certain projects, but there is a lot room to put yourself into a situation that's hard to maintain if you/you're team doesn't possess the right amount of discipline around documentation, developer tooling, observability etc..