I know how tedious it is to maintain decades old enterprise Java software, but from a cost perspective, it makes much more sense to keep those rather than constantly refactor to chase the newest trend.
As an example, if you had a software that was written 20 years ago to store data in a relational DB, updating it to work with current versions of that database system won't be much work (if any). If you rely on managed services, I wouldn't be too sure that you get away that easy.
This very much depends. In the example you gave, nothing changes if you used managed services or not.
But you could argue that the 20yo software is technical debt preventing the upgrade of the database due to the source code being lost, the library used to connect to the latest version of the database doesn't exist requiring a rewrite in a moderm language or framework. Etc.
Technical debt really is about code that cannot easily be modified to adapt to the requirements of a business.
If you wrote some code, and it was trash, made no sense, in an obscure language that few people know, with no comments. Yet it ran for 10 years flawlessly with everyone too scared to look at it, but made the business money. It's not technical debt until it needs to be changed/modified.
If you have infra skills then absolutely, it's way simpler to manage. But infra people like don't really fit in "small shops" because the price tag for one of us is (depending on the cost of living) anywhere between a quarter and half a mil total comp. And if you ever want them to take a vacation not on-call you'll need at least three. I say this against my own interest, just go with the managed services and consider it "good problems to have" when you feel the itch to hire some infra person to clean up the debt.
And yes, you'll need three people, but not full time. From experience I can say that even with a hundred servers, it tends to be a small chunk of each of their time. And if you have a redundant system and don't deploy on Fridays, the chance that someone has to respond to a call on the weekend is pretty much 0.
Managing your own infrastructure (with dedicated servers, so no hardware management) isn't too hard, even if you're a small shop. And managing a fleet of AWS services isn't necessarily less work.
Maybe there's a reason all ads for cloud tend to compare it to running your own data center. Because once you get rid of hardware management, it's not really that much easier being in the cloud, at the risk of lock-in and huge surprise bills.