I suppose the logical thing is to think of the password manager as a tool, like a car, or a washing machine. I know the theory behind how my car works - there is an engine, which takes in fuel and air, and puts out rotary motion, which drives the wheels around. However, I don't know the details - it's got some kind of electronic fuel injection, that lets it adjust how much fuel is in the engine on every stroke, which seems to be some kind of magic. This doesn't really matter, though, since I know the key function of my car, which is to get be from A to B. I can use it for getting from A to B, without needing to worry about the detailed "how".
In the case of passwords, I know a handful of mine. I can log into my password manager, and into a few other key places with long, memorised passwords - basically, I want to be able to bootstrap access to my accounts without the password manager. I don't know what my Paypal password, to take one example, is. Not the foggiest. It's just a long string of characters. However, just as with the electronic fuel injection on the car, it doesn't matter - I can quite happily get on with what I want to do (log into Paypal) without knowing the detail of how.
The benefit though, is that anyone attacking that account needs to work out something from effectively no knowledge. Doesn't matter if they know my favourite sports teams, TV shows or books. Doesn't matter if they happen to know another password of mine (perhaps they run some other service I use) - there isn't a pattern to spot.
Now, if they manage to break into my password safe, yes, they'd have access to everything. However, I have 2FA enabled for it, so they'd first need to have access to my token generator. It alerts me if there are new logins to it. My email provider alerts me to unusual login behaviour. I get a lot of indications that something is wrong, very soon after they get in. I also have a list of all the places I need to change a password for - the password manager itself. I don't need to worry about forgetting one site.
If you don't like the idea of a cloud service for passwords, which is understandable, you can backup the database file yourself, and remember a DB password, or keep it on a regularly updated drive stored in a safe. You could even print out the passwords and keep them in the safe - for most modern situations, the main method of getting attacked isn't someone sitting at your computer, but someone sitting at their computer, on the other side of the world. They're not likely to be able to get into your safe! (Obviously, if you're talking nation-state attackers, your precautions may be different, but in that case, you've got other things to worry about too.)