If want to use a version of postgres other than 10.x, you can either use a different version of Ubuntu or install a custom PPA.
Apt's target audience is systems administrators. Homebrew's target audience is independent developers who might need to have four different versions of Postgresql installed simultaneously on their laptop, because they maintain Rails/Django/Node apps for four different clients who are each unwilling to upgrade for whatever reason.
IMO homebrew is "messy" because it is trying to solve a harder problem. If there is such a thing as an average enterprise software developer, I would argue that homebrew is trying to solve problems that the enterprise developer does not have.
Mac OS itself doesn’t really have this problem since applications can bundle the exact version of their required frameworks, a bit like Ubuntu Snaps.
It's been a while since I used apt, but if I remember correctly you'd have the same problem the parent described, right?
-------------------
And regarding the 'name@version' criticism: If you want to stick to a version, how can you do it without specifying it?
Only if you are running Debian Sid or equivalent.