I've written before that you can never have too many spells for roleplaying games like Pathfinder and Tales of the Valiant, and that's as true for the Game Master as it is for player characters. The problem isn't having too many spells to choose from, it's how to know what spells you have available.
When you have great books like Book of Lost Spells or Deep Magic, you have literally hundreds of spells you probably don't know you have. Well, you know you have them, but you don't know what they are. You can flip through a book, review a couple of spells, close the book, and then when it's time to choose a new spell, you forget everything you just read.
For players, it's frightfully easy to fall into the habit of just picking the same handful of spells from your game's standard rulebook.
If you've been playing for a while, those are the spells that are likely top of mind, after all.
And if you're playing with a particularly unimaginative strict Game Master, you might not have the option of using third party spells.
For Game Masters, you probably use spellcaster NPC stat blocks in combat, and they generally have standard spells listed. You get used to those spells, and so when you randomly decide that a player character has just found an ancient magic scroll, the only spell names that come to mind in the pressure of the moment are the same old standard spells.
Essentially, boring choices reinforce boring choices. The more you use a same-old-spell, the more it embeds itself in memory, making it easier for you to default to that same-old-spell the next time you need to choose a spell. Here are five ways that a proliferation of spells can help you be better at [pretend] magic.
The way to learn spells is to use spells. If you know the standard spells in your game's rulebook, you only know them because you've used them over and over again. The same is true for non-standard spells. If you want to have Shadow bite or Dark bolt or Brittling on the tip of your tongue, then you have to assign them to your player character or to an NPC.
Probably for an entire campaign.
I say "campaign" because when you just play through a short module, even if it takes multiple sessions, you may have only used some of your spells once or twice. Due to frequency, cantrips tend to be easiest to learn, so you can get familiar with those pretty quickly through even just a one-off adventure. But spells that cost a slot tend to get used only when necessary, and so it takes exploration and conflict for you to be pressed hard enough to rattle off a spell.
This does admittedly take time. If you only play once a week in a single campaign, then you probably only have 10 or 15 or even 25 spells by the end of it, and more than likely you've come to rely on just 6 or 8. When one spellbook has 500 spells in it, it's realistically going to take you 60 campaigns before you're familiar with all the spells in your book.
The good news is that you probably don't need to know all 500 spells. Books of spells are, in a way, overloaded. It's a matter of taste. A book of spells must cater to lots of different play styles. Some players want big powerful combat spells, while others want spells to manipulate the game world, and still others just want fun spells that create interesting situations.
I built and played a pure Illusionist for a game, and there literally weren't enough Illusion spells in the rulebook to even complete the build. But there were more in Deep Magic, so I was able to build the character I wanted, even though I admit that few were spells I'd have normally taken.
Spell books have to be loaded with more spells than you personally need, because a lot of other people out there are playing a different game than you. It's fine to focus in on the 20 or 50 spells out of a few hundred. The others are there to discover later, or when you feel like trying something different, or they're for somebody else that's not you.
There aren't enough high-level spells in most rulebooks. Not every game system handles its own high-level play particularly well, but even those that do tend to implicitly acknowledge that once your character is really really powerful, you're probably going to retire and start a new campaign. For that reason, game developers often don't put much effort into inventing high-level spells.
That's too bad because when you reach a high level, your character is still your character so it seems like you should have the same diversity of choice as you did at low levels. And that's where those extra spellbooks come in.
Take a look at Annihilate Soul from Frog God's Book of Lost Spells:
Target takes 8d6 + 50 necrotic damage, or half damage upon a successful Constitution saving throw. If the creature is reduced to 0 hit points after the failed save, it dies and its soul shatters into 2d6 spirit shards that takes up residence in random life forms in a 10-mile radius. [...] Target can’t be resurrected unless all bearers of shards are gathered into the same area before the resurrection magic is used...
It's a great 9th level spell. It's as powerful as you'd expect at that level, but just as importantly it creates a new story opportunity when it's successful. It's good for player characters and it's good for NPCs. Everybody's a winner. Well, except the person who got annihilated.
Tabletop RPG is a creative game. You can literally do whatever comes to mind. There are no limits, except fantasy physics, but you can get around that by using magic. And some magical spells get around the weirdest things.
Here's the 1st-level Assassin's Coin from Frog God's Book of Lost Spells:
A coin becomes imbued with a magical charm. Once the spell is cast, you must return the coin to the person or being who gave it to you. As an action, they can throw the coin into the air while speaking your name (or whatever name you gave them). The coin vanishes and reappears in your pocket, along with a mental image of whomever spoke your name. No message can be transferred along with the coin.
I don't know why that spell exists, but that's why the spell exists.
There are really useful spells out there, even though they're sometimes (to put it mildly) specific to a certain set of circumstances.
For instance, you can almost imagine Find Kin from Deep Magic having been written for an adventure someone was writing, in which the villain's evil twin was the real baddie, and the author needed to provide a way for the players to learn about that:
You touch one willing creature or make a melee spell attack against an unwilling creature, which is entitled to a Wisdom saving throw. On a failed save, or automatically if the target is willing, you learn the identity, appearance, and location of one randomly selected living relative of the target.
Then again, you can also imagine a situation where learning about the relatives of someone could be useful, whether your intent is to pass on some important message or warning, or to potentially coerce and threaten. What better way for an evil NPC to quickly learn an easy way to intimidate or extort one of the player characters?
Here's Mortal Insight from Kobold Press's Deep Magic:
A supernatural olfactory sense allows you to smell wounded living creatures. You can pinpoint a creature that doesn’t have all of its hit points within 30 feet of you, and you have advantage on checks to track it.
You can see lots of uses for this one, and in fact it's the details that make it so perfectly suited for certain character builds.
Sand Ship from Deep Magic might just change traversal for your Southlands campaign:
Transforms a sailing ship into a vessel capable of sailing on sand as easily as water. The vessel still needs a trained crew and relies on wind or oars for propulsion, but it moves at its normal speed across sand instead of water for the duration of the spell. It can sail only over sand, not soil or solid rock.
Some spells you can imagine immediate uses for. Some are almost oddly overly specific. For some players, the fun challenge is in figuring out how to use (or abuse?) those situational spells at unexpected times and places.
In Jack Vance-style magic, your player character forgets a spell after using it. In real life, the more you use a spell, the better you remember that it exists. So get those books off the shelf, and start choosing spells you don't know yet. It's the only way to learn them, and it's why you bought the books in the first place!