In the Ubuntu world we would go to an LTS release but on the RPM/Dnf world is there any other distro apart from CentOS Stream?
CentOS Stream is not a distro, it’s the carcass of the distro that Red Hat killed, CentOS. Stream is a beta testing program for RHEL, no more, no less. CentOS wasn’t even a Red Hat project originally, but Red Hat hired the maintainers of CentOS and gained control over it.
When Red Hat killed CentOS, going revising CentOS 8’s previous end of life from the end of May 2029 to the end of December 2021, one of the original founders of CentOS, Gregory Kurtzer, started Rocky Linux as a replacement for what CentOS was supposed to be, an open source, binary-compatible version of RHEL. Rocky Linux works well for this purpose. I’ve heard that Alma Linux does, as well, but I have never tried it.
I know that CentOS stream is more kind of a rolling release but… feels like an LTS distro in practice… or it is just me?
CentOS Stream should not be used for anything beyond hobby projects. It is, by nature, buggier than Rocky Linux or RHEL, and it was never intended to be stable. And there’s no reason to use it: If you want more stable versions than Fedora, Rocky Linux works just fine.