If you don't want to get into dependency hell, just stick to apt and what is in the repositories for the release you installed. Package maintainers did that work for you. To make a package for Debian implies you have full knowledge about all the Debian policies, etc, and to learn it means a lot of work.
I also think things are getting worse. The reason are all those pesky new/better/wonderful ways to install software... that are full of shit. They don't want to learn how to create debian packages, or think they will make some money going that way, or fame/glory/whatever, so they have to invent the wheel again and again. But, while apt is almost the perfect wheel, those new wheels, well, not so much.
AppImage can work. I don't like it however. Docker could be useful if you have servers and want/need to migrate not just the software, but also the contents, from one server to another. What I can't swallow is pip. Use pip to install any python thing, and odds are things will be messed up sooner rather than later, because you have now python things installed, unknown to apt. Pip would like to be half the good apt is. And you'll probably will forget you installed that thing with pip. Then a rough ride will begin.
tar.gz packages? To me, that means source code to be built. If you do so, you'll need to learn previously about the dependencies, and build that dependencies first. And the dependencies of the dependencies. And so on an on... until you have built all what's needed. All should go into a separate partition. There used to be a /local directory just for that partition to be mounted there. That way you can have different versions of libraries, etc, needed to do the work, and those binaries can be easily preserved if you want to upgrade the rest of the system. I used to do that only for versions of software with new features/fixes that I needed, and I get it back to be installed with apt as soon as the corresponding debian package for "stable" is released.
But, often, the main reason is just user ignorance and unwillingness to read any documentation. Debian has quite good guides, and anyone that did read about the sources.list file, would know that metrologist, in that other thread, is getting messages about hits from two different repositories, one of them for bookworm, the other one for jessie. So, no wonder he got a dependency hell. He just messed things, first, choosing to do an upgrade when the easiest way, not having anything functional on that system, would have been to go for a clean install. Then he modified /etc/apt/sources.list without knowing what he was doing (because he couldn't be bothered to read the guide). Then he complains "Linux sucks". LOL.
Easiest solution: clean install with just the defaults. After that, use just APT to install any packages. If some software couldn't be installed with APT, it just means that software isn't mature enough, and it should be avoided like the plague, until you know what you are doing.
Edit: if you are going to do a Debian install downloading the packages from Internet, just use an ethernet cable. Wifi won't work... because the firmware for the wifi hardware isn't free. You'll have to install that firmware with apt after install is done. It's just the Debian way, and Canonical profiteered from that greatly. Again, anyone that did read the guide would know it... he would even know that an image including non-free firmware, with kernel modules for wifi hardware, is also available, but very few people seems to be willing to read anything nowadays