My views on the suitability of CentOS Stream
I honestly believe that CentOS Stream will be suitable for the majority of CentOS Linux users, and a huge improvement for some. [...]
At one level, I agree with Ben Cotton on this. There's every indication that CentOS Stream won't be worse than plain CentOS 7 as far as bugs and security issues go; while it will now be getting (some) package versions before RHEL does instead of afterward, Red Hat has also apparently drastically increased its pre-release testing of packages. The move from CentOS 8 to CentOS Stream does cost you an extra five years of package updates, but I also feel that you shouldn't run ancient Linux distribution versions so you probably shouldn't be running most CentOS installs for longer than five years anyway.
(I measure these five years from the release of RHEL 8, since what matters is increasingly ancient software versions. And since RHEL freezes package versions well in advance of the actual release, that means that by the end of five years after release the packages are often six or more years out of date. A lot changes in six years.)
So at that level, if you're already running CentOS 8 as a general OS I believe that CentOS Stream will be perfectly fine replacement for it for you and I don't see a strong reason to, say, migrate your existing systems to Ubuntu LTS. There's good indication that CentOS Stream will not create more bugs and instability, while migrating to Ubuntu LTS is both a bunch of work and won't get you much longer of a support period (20.04 LTS support will run out in early 2025, while I believe that CentOS Stream for 8 support will end in late 2024).
Unfortunately, that's only at one level, the level that ignores the risks now in the future. The blunt fact of the matter is that the IBM-ized Red Hat has now shown us that they are willing to drastically change the support period for an existing CentOS product with basically no notice. We have only Red Hat's word that CentOS Stream for 8 support will continue through end of full maintenance for RHEL 8 in late 2024, or actually we don't even have that; Red Hat has made no promises to not change things around again, for example when RHEL 9 is released. Red Hat has made it clear that they decide how this goes and what the CentOS board feels doesn't really matter; the board can at best mitigate the damage (as they apparently did this time around, including getting Red Hat to allow CentOS Stream for 8 to continue longer than Red Hat wanted).
(Red Hat has also made it relatively clear that their only interest in CentOS today is as a way to give people a free preview of what will be in the current RHEL in the future. This neither requires nor rewards supporting and funding CentOS Stream for RHEL 8 after RHEL 9 comes out. It also implicitly encourages things that get in the way of using CentOS Stream as a substitute for RHEL.)
Any commercial company can change direction at the drop of a hat, so Canonical (or SUSE) could also decide to make similar abrupt changes with their Linux distributions (yes, Ubuntu is Canonical's thing, not a community thing, but that's another entry). However, Canonical has not done this so far (instead they've delivered a very consistent experience for over a decade), while Red Hat just has. There's a bigger difference in practice between 'never' and 'once' than there is between 'once' and 'several'.
If I had a CentOS based environment that I had to plan the next iteration of (for example CentOS 7 and I was considering what next), I'm not sure I would build the next iteration on CentOS Stream. It might well be time to start considering alternatives, ones with a longer record of stability in what had been promised and delivered to people. Certainly at this point Ubuntu LTS has a more than a decade record of basically running like clockwork; there are LTS releases every other April, and they get supported for five years from release. There are real limits on the 'support' you get (see also), but at least you know what you're getting and it seems very likely that there won't be abrupt changes in the future.
(Debian doesn't have Canonical's clockwork precision but may give you more or less the same support period and release frequency, but see also. I don't know enough about SUSE to say anything there, but it does use RPM instead of .debs and I like RPMs better. The Debian community is probably the most stable and predictable one; Debian is extremely unlikely to change its fundamental nature after all this time.)