It's true that Linux package managers used to be buggy and problematic in the 90s but those days have long since gone. And while I'm not discounting that a package upgrade could damage your system, the instances when they do are highly unusual rather than a typical problem users face with each and every upgrade. In fact Windows sysadmins have far more dread with running Windows updates than Linux admins do and yet Windows updates are only focused on Microsoft products rather than every piece of software on the system.
> It's all up to packagers to author their packages right so they don't leave garbage on your machine that you have to manually clean up (or give up and reformat).
Actually it's not. It's up to the application developers to do that. If you specify a package to install a file `x` to location `y` then the package manager will uninstall that file automatically too. You don't specifically need to tell the package manager to do that (or at least not with any of the packaging systems I've used). But if the application developer writes the application to spew out thousands of files into $HOME, that happens outside of the package manager. There isn't a whole lot you can do to stop that aside limit the directories which your application has permission to write to (either via chroot, containerisation, user/group permissions, SELinux, or other forms of ACL. There's actually plenty of tools on Linux / UNIX to handle that problem).