It appears that this is boiling down to the overhead of sustaining a persistent connection. MQTT is by design a light weight protocol and it does that very well and efficiently when exchanging data. However, if there is no data to exchange for long periods of time, i.e. days/weeks, then even a keepalive pings every minute, or whatever configured, IS creating wasteful network overhead. If you multiply that by perhaps 100 connections being maintained to a server, which I can think of a very real use case in the logistics industry, and those 100 connections are over a satellite link, or GSM network, or even a RF Lora type network, well now it really matters. In the global B2B domain, there remains significant network infrastructure in which ALL network traffic has a accumulating cost, and those costs can be significant at scale.
Indeed, MQTT is a publish/subscribe model. That doesn't necessarily mean that the publishing and subscribing functions must, or will, occur in real time. Hence why the retain flag exists at all. MQTT has been designed from the ground up with the idea of unreliable network connections. Again, this is why we have retain, qos, and lwt abilities.
In considering more use case scenarios, I think now there is also the need for not only an on demand, dynamic MQTT-IN type node that does the connect/subscribe/disconnect, but also an MQTT-OUT node to allow for event driven connect/publish/disconnect. Perhaps that can be accomplished within a single node structure (which I think has merit), but, again, I'm not a heavy duty coder so I can't really comment on the software engineering here.
I think node-red is a very, very important platform towards pushing the adoption of MQTT across multiple types of business domains. But, as MQTT is adopted, if node-red makes it too challenging or awkward or convoluted to perform a simple task, or doesn't provide enough flexibility to adopt to wide, unpredictable use cases, then designers will find other solutions.
It already has a beautifully, simplistic method to drop in a subscribe or publish node that seems to work flawlessly, but those create persistent connections and only connect on a reload/restart which is a no-go in a production, high availability deployment. Both of those are problematic in many use cases so, again, I'm just suggesting there is merit and value in having these nodes be able to be dynamically controlled via events.