... I want spellcasters to feel different, and to sometimes have to make choices that don't involve the optimal choice. If the only spell you have memorized that's leftover is waterbreathing, how can you leverage it to help win a contest at the fair, or save the village from an invasion of wereboars?
To play devils advocate:
In that situation if the only spell leftover was waterbreathing, most players did not view that as the result of a choice "making the spellcasters feel different."
It was viewed as a wasted spell slot.
When WotC came around with their survey's players couldn't check the 'this is not fun!' box fast enough.
Components, having to learn all spells in-game, even the AD&D round segments with casting times leaving magic users open to interruption, were all used as checks to the magic users power.
They were all whittled away by WotC until by 5e none of those checks are there anymore. Generally by player demand.
Original D&D style Vancian casting has been seen more and more as limited and boring because it does not let players emulate the style of 'wizard' that is now commonly seen in media.
Part of this is limiting and constraining the effects each type of caster has available, but it's also about limiting which spells can be used when. A spell point system with free choice of any spell is another half of the generalist mage problem.
IMHO free spell choice is a problem with all magic systems that allow it.
The trick is that for early D&D when and what spells became "available" to learn was largely GM fiat.
Which was another reason players agitated for that restriction to be gone.
IMHO any system that makes it a requirement to learn spells in game would do well to have a table that is rolled on when the PC's do research, so as to maintain a sense of player-facing neutrality for the game.
Something else to consider is bookkeeping.
At higher levels, spell management in a Vancian system is fucking annoying. A mana pool/magic point system, or the spontaneous casting system, can be much less hassle.
IMHO, he trick is to make the hassle worth the payoff in play at the table, and if possible have it serve double duty as a check on the power level of the magic PC.
One Idea I am toying with was inspired by the stress track in Blades in the Dark:
So you roll to cast vs a DC.
But each spell costs a certain amount of 'stress' to cast. (call it strain, or whatever)
MU can cast as much as they want: (The conceit is that they are "channeling" the elemental powers of creation.)
But they always rack up 'stress'...
The Stress track comes into play when the PC's fail when the caster rolls a "1"...
Roll on a table where the PC rolls a 1d12 (or whatever) but the amount of stress accrued by the caster
is added to the roll.This table has various effects for how badly the caster has fucked themselves by racking up stress casting their spells.
Everything from causing themselves harm, losing control with varying effects, to outright uncontrolled channeling effects where the caster can go up like roman candle.
Essentially the idea is that magical casters are essentially time bombs waiting to go off if they get too profligate when channeling the elemental forces.
Casters would be somewhat powerful, but this is always tempered by the fact that at any given time there is a 1 in 20 chance that the caster will lose his shit to some degree.
So the more a caster pushes their luck before they have a chance to rest and reduce their stress; (a mechanic for that would be needed) The greater the chance that they will face harsh consequences when they pooch a casting or rather "channeling" roll.
Naturally this is a rough idea - refinement would be needed before a solid playtest.