Having no idea what they are doing sounds like dreaming the impossible dream!
Maybe newer ones are better as that was back in the 90s.
I'm currently in a 10 year old house based on old farm buildings and the walls here are even thicker (still sandstone).
I've no experience of those converted or purpose built long ago.
Soundproofing techniques are well-understood and not horrendously expensive (of course, many builders don't bother because it's not worth the cost, from their short-term perspective). And separate buildings in close proximity still allow plenty of opportunity for sound to carry—again, unless soundproofing has been performed.
You seem to be making the assumption that tiny houses will always be built well above minimum code requirements, while apartment buildings will not. If tiny houses really go mainstream, that will no longer be the case.
Why didn't you live in more efficient housing, they might ask.
Oh, because I was mildly inconvenienced by my neighbors.
Because I wanted a garden to grow my own food.
The insulation might not be perfect.
These things are not important or, in the case of the insulation, can be adjusted with aggressive regulation. We are going to force future generations to do it, because of our wasteful lifestyles. The least we can do is start working towards living like them, in honor of those whom we imprison in a hell of our making.
A small house is, for most, a better and far more efficient solution for a couple than the typical property available. As the kids move on we've no need of multiple bedrooms and bathrooms. Yet most of what gets built tries to squeeze more rooms and more bathrooms and toilets than people into the same square footage.
To me, that's what needs a rethink. You'll never get us into another apartment block though.
For the elderly they also seem like they could be an ideal solution where we once built sheltered flats. Cluster a group together with small garden space, and add a warden and panic alarms. More appealing than rattling around in the old family 4 bed home.
Even insulation benefits can't be relied on as shared walls are often uninsulated and overall insulation in apartment blocks often bare minimum to code rather than adequate.
Last, but not least, I would take a small garden over an apartment without that's twice or three times the size. You're even likely to see neighbours a lot more if you have a garden to potter in, and the dog has somewhere. :)