I don’t really think of MQTT as a metric system, but a messaging system.
Throwing out some thoughts:
Retain has issues due to the lack of ability to clean things up in a easy way - who wants to get a bunch of old unused data when they subscribe to #? It would be nice if they can “expire”, but that isn’t part of the standard as far as I know. On Mosquitto, I don’t have a persistence database setup, so restarting Mosquitto clears the cruft.
If you think of MQTT as strictly a messaging protocol and ignore the storage/retention aspect, then you will come to the conclusion that the application needs a way to request updated data when it first subscribes. I set my clients to subscribe to a command that another subscriber can publish to that tells them to publish all their data. Unfortunately I can’t control 3rd party clients, so those I have to just wait for them to publish. Fortunately those clients so far have been pretty dumb and just publish on an interval.
Sparkplug has the primary app that accomplishes the above and gives the ability to know all the nodes that are connected, but, as I think you are pointing out, and as far as I know, it is not suitable for multiple (later) subscribers. I don’t know Sparkplug well enough to know if there is a suitable way to accomplish this,
I just use plain MQTT for now and deal with it at the application level (through Node-Red and Ignition).