One of my favorite games of all time is DayZ. Its at times described as a walking simulator, and for good reason, as you do in fact spend a lot of time walking and running around with nothing much happening.
And part of the gameplay loop is looting; you need to get stuff to survive and stuff to help you get other stuff you need to survive, and all of it has limited usage, meaning you can't just get one and call it a day.
So you're always running around, looting stuff, and repeating constantly, forever.
But that isn't all there is to the game and how it plays out, because between the atmosphere of the game and the simple fragility of life, those long bouts of basically nothing contribute to the relatively short bouts of a whole of something being incredibly intense.
Running into another Survivor in DayZ, or triggering a zombie horde or a pack of wolves, is no laughing matter, even when you're fully geared. In fact, even a person whose fully decked out in military gear has to be wary of approaching unknown bambis, players who just spawned, because all they strictly need to end your life is a can of beans to beat you to death with.
In that game, resource management largely serves the purpose of filling out the gameplay inbetween these moments of intense violence and terror, and it goes fairly deep; you even have to upkeep your shoes, less you have to run barefoot, putting you at risk of cutting yourself and bleeding out.
In TTRPGs, specifically of the DND variety, the equivalent of those intense moments happen much more frequently in most games, even ones that attempt a more gritty take. As a result, the amount of resource management thats appropriate needs to be balanced with that frequency in mind, otherwise its just tedium.
But the other issue tends to be that of bloat that drags the game out, separately from tedious elements.
Counting arrows for instance tends to be well balanced for this in say early 5E. But as you get to later levels, and enemy HP starts to baloon, counting the arrows starts to get tedious because the arrows basically stay the same and your damage, if it goes up at all on average, doesn't always keep up with the amount of damage that needs to be put out.
In DayZ, this isn't much of a problem. Some guns are super weak and basically worthless outside of your first hour or so alive, but most of them are relatively equally lethal against all targets. Player skill tends to matter more than having bigger numbers.
So back in DND land, while you can push the game to be more player skill oriented and address the issue that way, but I think you can also address it going the other way. Using the arrow example, you could set it up to where arrows as a resource have more depth. Different types, effects, and quantities that one can count on having. These arrows would be differentiated from standard ones, and counted. Whereas standard arrows, might be effectively infinite; especially in 5e where there is no real crafting for them and the game leans too heavily into the power fantasy for standard arrows to be that big of a concern.
But, if you also address those issues, and introduce gathering and crafting mechanics that don't disrupt the gameplay loop (and ideally integrate seamlessly with it), and perhaps draw down the power fantasy elements to make arrows of all kinds, including standard ones, more individually valuable as resources (rather than just flavor or a mechanical ribbon), then you could have resource management actually be fun for a player.
And of course, as others mentioned, it still does come down to preference. These ideas mainly go towards providing the better experience for the player that enjoys having resource management as part of the game; they may or may not do diddly for someone who doesn't like that kind of gameplay.