The best consumables are extremely narrow yet effective, the type of effect you'd never pick as a character skill but you'll be thankful to have it the one time it's a lifesaver. "See invisibility", "seal door", "put an ally in stasis for three turns", "flood room with water", effects like that.
Important note: it feels 1000 times better to use these if you bought them rather than getting them as random loot, because you feel like a smart planner.
The worst kind of consumables are small buffs, especially if they don't stack. Sure, when the big boss fight is approaching you'll be chugging all those Mega Ultra Titan Strength potions. But when the **** are you going to drink the "Gain +2 strength for ten turns" potion? By the time you determine that the fight is close enough to warrant a small buff, it's probably halfway through and spending a turn to drink it becomes a worse and worse value proposition. Looking at you, 3E potion of heroism (the 5E version is a huge improvement).
Healing potions are meh. If they're regular consumables to be drunk in combat, they're basically "insert coin to continue". Just make the enemies deal a little less damage and remove healing potions.
Out-of-combat or rechargeable healing potions have the potential to be much better if they integrate and support whatever your rest/resource management is. Though IMO 90% of RPG designers are **** at balance and shouldn't bother with it. First design your game by letting the player be at full strength at the start of each battle, and make a balanced and reasonably challenging game under those simplified rules. Then, once you can reliably hit that target, you can add resource management on top which massively complicates the game balance.
