"It can work for a few hundred packages easily enough to do it the 'apt-get way'. It can scale upwards to several thousands. But to be on par with something like what Windows provides you have to scale to millions and you have a shitload of programs that nobody in the Debian project (or Fedora or Redhat or anybody else) will never be aware of, much less know enough to package and build them for end users.
There is just simply no chance in hell that a centrally managed, closed system like the current package management status quo can ever hope to scale and meet the diverse needs of the average population."
I don't buy that at all. The fact that there are "millions" of Windows programs available (assuming that is even true, which I doubt) just points out the inefficiency of closed-source development, where it is not possible to build on someone else's program without explicit licensing, fees, etc. The "millions" of programs are the problem with Windows, not an advantage of Windows. When I show people a Linux system, they invariably are more impressed by centralized package management than by any argument concerning freedom.
If you look at everything that is in Debian, it is hard to come up with a niche that isn't covered. Some of the OSS programs may lack important functionality, but in principle they could be improved to cover everything that their commercial counterparts provide.
I just don't see any need to provide "millions" of programs. If you have some need that isn't addressed by the ~25K packages in Debian, it probably requires a custom application anyway.