How languages usually play out
The main problem with the language mechanics is that they usually clash with two unspoken rules of role play games - keep the plot moving (aka, don't grind the game to a halt because the players can't figure something or don't have something they need, the idea behind concepts like fail forward), and don't be exclusionary to the characters.
Here is how from my experience the language situation usually plays out in-game:
First option - everyone speaks some common language or use some sort of babel fish-like device. Everyone can understand everyone else, the language mechanics are ignored because nobody put their points into it. The plot must move forward, and this is the simplest way of doing it.
Second option - one character translates for everyone. This often ends up being like the first option, except with the added step of one character actually having the correct language skill. You don't want to exclude most of the party from some important NPC, plot or other things, but you also don't want to waste double the time of the linguist in question actually repeating everything being said for the sake of brevity.
Third option - an NPC conveniently knows your language. If the entire party ends up not knowing the local language, the GM will usually introduce an NPC that conveniently knows the party's language to soften the penalty. At best they have their own agenda and will twist the truth to suit their needs (the players won't have any meta knowledge of what the other NPCs would be saying anyway after all...), but at worst they act like an in-between for the party and we go back to option two and one...
Fourth option - language is a barrier. If you don't speak the local language, expect to have to think on your feet. At its worst, this can potentially derail an adventure if at least one PC decide they are bored trying to speak to the locals, start using their goblin brain and start some violence just for the fun of it. Barring that, the GM and the players would have to be clever in how they advance the plot with this approach.
No game is really stuck with one of these options permanently. The situation can shift from one town/country/planet to the next and change as the plot demands it.
All in all, it seems languages mostly exist to punish the players, rather than be a new cool tool for them to use. There might, however, be a few interesting ways to make the situation more interesting.
Interesting ideas for languages
Here are some interesting ideas for using the language mechanics that might spice up some games.
The first and perhaps simplest approach to languages would be to make them a soft punishment, rather than a hard punishment. Everyone could communicate and interact with NPCs just fine, but if you didn't have a given language skill, you would suffer some penalties to all of your social rolls. This is perhaps a simple mechanic, but at least it makes the language skill useful for those that want to take them, without making them a hard punishment to impose on everyone.
On a similar note, as discussed last time, in our game of Fellowship we had a player use the Angel playbook that created an air breathing mermaid problem for our game. That character had explicit powers for being able to be understood by every alien, animal and the like, which meant other characters did not have that universal ability to communicate as was the staple for all of our other games. This shifted our game from option one, to option two essentially, with the Angel being the translator for everyone. However, there is an interesting twist to this playbook - the Angel can only be understood by everyone, they themselves cannot by default understand everyone else. That is, without an extra piece of gear - the Ancient Dictionary. This lets them understand every language, but they can only use this gear if they have time to carefully consult it. This in turn can give the GM opportunities to add twists to the situations - the player can communicate freely when there is no danger, but as soon as there is a time limit and the action picks up, it changes the rules of engagement. Suddenly if you need to decipher some ancient ruins, you can't do it automatically. This perhaps is a neat way of transitioning between how big of an obstacle a language barrier can be.
Another way languages could be an interesting mechanics would be taking a page from Cultist Simulator and how it handled languages.
Cultist Simulator, where languages are a stepping stone to the dark arts
Cultist Simulator is a game about, among other things, learning the dark arts. Those unfortunately are not taught in a cultist school, so you have to consult the books. Old books, ancient books, foul books. The problem with these is that first you have to acquire them through potentially illegal means, and then they are often written in old and obscure languages. You have to translate those text before you can study them, and that takes language skills. The first basic ones like Latin and Greek you can pick up from some tutors and books from your local antiquarian, but eventually you stumble onto dead languages that you have to acquire by finding a Rosetta Stone of sorts (which would let you, say, learn Egyptian by knowing Greek already), and even further still you have to use those ancient languages to speak with spirits to learn even more primordial languages.
As such, the pursuit of languages itself is a project that you use to further your other knowledge. You could build entire campaigns around it. In more practical terms, those kinds of languages would be used as tools in character downtime, rather than being something active that comes up during a chat with an NPC. You could similarly use this during encounters - if characters find an old tome or stumble on an old library without the necessary linguistic knowledge to understand them, they couldn't use the knowledge right there and then, but they could either acquire a tome for later translation, or have to go back once they learn the languages themselves. Alternatively, they could get help from some other linguist, but the NPCs might start asking questions before long if the book they have to translate has a human face on it... Better learn those old tongues yourself and keep a lower profile!
If you want to further complicate the task of learning a language, take a book from Heaven's Vault and make the PCs have to acquire multiple manuscripts in order to even start learning the language ;).
Languages are often a binary system in RPGs - either the players are punished hard by not knowing them, or there is a way to avoid the issue of languages altogether. As simple as those options are, I unfortunately can't think of any system that has iterated much on this approach...