In practice however, I rarely encounter issues, except for closed-source programs that don't ship their libraries. That, I think, is mostly the fault of the vendor and not the Linux system. Of course, as a user it doesn't really matter whose fault it is if your application doesn't work because you just want the damn thing to work. It does mean the problem (and solution) is mostly an educational one, rather than a technical one. You certainly can ship binary programs that should work for decades to come: the two core components (Linux kernel and GNU libc) take backwards compatibility pretty serious, more or less on equal level with Windows.
You don't even need flatpack. A wrapper script with "LD_LIBRARY_PATH=. ./binary" gets you a long way (and still provides people the ability to use a system library if they want, so sort-of the best of both worlds).
The key difference is that each app bundles its DLLs for its own private use. From the user's perspective, it's essentially the same as static linking, if you treat the entire folder as "the app".
In MS speak, it is xcopy install, based on how MS-DOS applicatons used to be distributed, even when static linking was the only option (with overlays).
See that's the thing, everything on linux is designed to work by sideloading as much as possible, depending on thirty thousand packages that must be all installed to the perfect version by apt or else nothing works. A good system if you need to get a fully featured OS running on 100 MB of disk space, which tbf is linux's niche, but it's absolute horseshit to maintain.
Windows on the other hand tends to have flatpak-style monolithic executables, with the odd .NET framework or cpp resdistrubutable here and there, but it's the rare exception. Things tend to actually work when they bundle their dependencies. Hell, the average Java app ships the JRE along with it because nothing works if the wrong version is installed globally. Linux just takes that problem as a fact of life and tells you to fuck off.