When I learned that frozen meat had ice physics and I could glue a pile of the stuff to the bottom of a plank with a fan on it to make an all-terrain sled I knew it was the greatest mechanic ever added to a video game
XP-based progression isn't always padding. It definitely isn't hard to find examples where it is, but it's also a pretty good solution to a common problem: you want the game to present a hero's journey, where you start out weak and eventually become powerful, but you want a generic way to handle the players' progress.
It's really the same as the debate in TTRPGs like D&D, where the DM could either reward levels based on XP earned from killing monsters, or could forego that altogether and award levels at set points in the story. In a video game setting where you intend things to be really open ended / the player should have a lot of freedom about what tasks they do and in what order, it's hard to handcraft exactly what each player's adventure and progression should look like, so an XP system is a really simple way to generalize it for everyone.
It's only padding if it requires you to engage with a lot of content that you otherwise wouldn't want to do, before you can progress the story you're actually interested in. But that's not the fault of the system itself, it's in how the designers chose to use it.
How about when the number goes up and has a real-time effect on the world you play in, showing you the fruit of your labour growing as you progress further and further?
I mean that sounds nice, but honestly I don't "progress" nearly as much as I "faff about" so stuff like XP lets me have the illusion of progress while I spend 30 hours roaming around the starting area looking for collectibles. I'm not sure what a real-time effect on that would look like.
Really depends. Exp points range from a mere UI feature for skill progress (you've picked a lock you're this much closer to getting better at picking locks), over fungible skill progress (you've picked 100 locks so you get stronger and can spend a skill point on archery), to pay2win madness.
Structurally exp points come into play each time any progression in a game is not immediate -- "defeat the guardian at the gate, now you can go through the gate" has a 1:1 relationship between things-you-do to more-access-to-things, if you have to collect ten fox skins to gift to the guardian to let you through that's a 10:1 relationship. Doesn't sound like exp but in the raw game mechanics those things are isomorphic.
...to bring that later point a bit into perspective: Imagine a card game where you have five stacks of ten cards. You draw cards from the first stack (not just the top card) until you get a certain card that's guaranteed to be in there (say the ace of spades), once you have it you can continue to draw from that stack, or move on to the second stack. Once you've drawn the special card from the last stack the game is presumed over though you're free to both draw from any stack that still has cards on it, as well as sit around on the table doing nothing.
Doesn't sound like a game? Uninteresting? It depends: It's the mechanics of your usual walking simulator and they can tell very good stories. It's progression by (semi-)random n:1 actions. If the environmental storytelling is good, if the setting is engaging, if the mystery is enticing, then time will pass like nothing. If you're doing it with actual cards yes it's pure grind.
tl;dr: It's (modulo pay2win bullshit) not about the raw game mechanics, but how they're dressed up, that make things grindy or not.
Really depends on the genre but especially AAA have definitely over done it.
Personally I love terraria and forager and those games are grindy like that but it fits the game. As opposed to the loot/crafting system in Control, that game really didn't need it and would have been more enjoyable without it (tho I still really enjoyed the game due to the story and telekinesis combat)
Yeah, Terraria is a great example of a game where the grind is integral to the game. Needing to get 20 drops from a particular creature encourages you to explore the specific zone deeply enough to really enjoy it. There's no story to progress through, it's just exploration and grinding to get different materials.
Similarly for Minecraft. Is it "grinding" to mine diamonds at z-level 11, or is that the game?
JRPGs and MMOs are the ones who generally don't respect your time with their XP grind systems.