You just described Shadow of the Colossis.

Perhaps Outer Wilds? You zoom around in a small space ship in a solar system and when you approach different phenomenas and see how big they get it gets pretty amazing.

Kingdom Come: Deliverence. Heard so much good about this game. Open world, you need to train to actually become decent or good at anything at all. Might be a perfect fit.

You perfectly describe Rain World!

You’re a small and insignificant creature in a dangerous and unforgiving ecosystem. You’re not saving the world. Even the enemies are playing by the rules. Evading enemies is often the safest option, but sometimes you must do a risky confrontation. It’s rare you even take down an enemy at all. Deaths are punishing. It’s open world, but the random placement of enemies often dictate the path you’re taking.

It has also one of the most intricate AI systems in any game. There’s YouTube videos describing it in detail, but I recommend to not watch them and go into the game clean.

Sounds like you'd enjoy a good play through of Outer Wilds.

X3: Terran Conflict - Ignoring the story plot and just playing sandbox ("custom game") makes you nothing more than a citizen in a universe that doesn't care if you succeed and success is a long, hard road to the top of whatever avenue you pursue. One of the best space games there is. It's also moddable and there are some awesome mods out there to make the game even better.

Mount & Blade: Bannerlord - Basically the same as above; but in a medieval wargame/RPG... It's pretty unique in gameplay so I'm not sure what to really call it. You start off as just a dude and can work your way up to becoming a king and conquering the entire country. The combat is part large-scale strategy, part 4X and part action sim as you move units around a world map for positioning and getting to cities and outposts, and battles put you in control of your singular dude swinging your weapon with some nice mouse controls, while also able to command your literal thousands of men in moment to moment tactical decisions.

Dwarf Fortress - Specifically Adventure mode in the pre-Steam version (since Adventure mode is not yet in the Steam version). You're literally whatever character you create living in a fantasy world. It's a simulation more than any other kind of genre. You can basically do whatever you can think of. You can be whatever you want up to and including a literal god if you work hard enough. There is no story other than the history of the world, which the game records and even after you die and make a new character in the same world, any mark that previous character left continues to exist and can affect others. Like say you steal from a guy, get into a fight and end up killing him. His son might seek revenge on your character, succeed and then fall into a depression. It's kind of a mad lib of sorts so you have to have some imagination, but it's the most complex game that exists right now.

Kenshi - Nobody likes you, you own nothing, you're weak as shit, the world is massive, and you can also basically do anything if you work at it. Command huge armies. Become a robot and forego the need to eat. Build cities. Conquer the planet. Not only is the game play incredibly fun and rewarding, it has a super interesting world with plenty of cool lore. Plays kind of like an ARTS and has complexity that comes close to Dwarf Fortress's.

How has Kenshi not been mentioned

Subnautica!

Outward is really good for this. You aren't the chosen one, you aren't special, you're just a person trying to get by and the game really makes sure you know it haha

Permanently Deleted

World of Warcraft Classic. You start as a no one. Everybody around you seems to know what to do. Your items are shit. You look like a peasant. The silliest enemies force you to rest and are able to kill you.

It‘s challenging but so rewarding. It makes you feel grateful for every single lvlup, item and skill you can get.

Even after spending literal days of your life, there always will be people who are better than you. Everything is said and done. Still this game manages to pull me back in from time to time. It’s so much fun.

Rain World. You are a little slugcat in a hostile ecosystem. You can fight but the predators will brutally kill you most of the time so evading combat is often better. The locations you visit are beautiful and it's easy to get lost, and the other creatures keep interacting even if you aren't there

Another different: NaissanceE. You explore incredible vast locations in a lonely monochromatic world. It's a mix of puzzle with platformer that will make you feel really small with structures that appear to repeat to the infinity. It's free on Steam.

Well I feel like have to kick this off with

Rain World

Space Engine. It's not quite a game, but rather an accurate simulation of the known universe. Anything beyond what we know is procedurally generated. The first time I played it it made me feel so small.

I started at Earth and flew around the solar system, then picked a star and flew towards it. You have to increase your speed by multiples of the speed if light to get them to move. The stars started moving and then moving past, me, but the star I chose wasn't moving. I realised it was actually different galaxy, so increased my speed by many many multiples of the speed of light until eventually it dwarves to move. I flee over to it, and then slowed and explored a few star systems there, I found a binary star system, that was really cool.

Then I had a realisation. If I didn't use the search function, and I just flew around trying to find my way back to Earth, I just never would. I could play it for the rest of my life and be certain that I wouldn't find it. The odds are that small. That thought scared the shit our of me and I closed the game and couldn't okay it again for a few days.

Don't know if that's what you're looking for but I do recommend the experience!

Disco Elysium (currently on HB Choice), you're a drunk cop trying to solve a case while trying to keep yourself together

• The Longing: a really subversive experience
• Kingdom Come Deliverance: open world medieval game with a moderate emphasis on realism. You start out as a peasant who knows how to use a sword and finish the game being not much more than that.
• Dark Souls 1, 2, 3: these games make you feel pretty unimportant, unlike other FromSoft games like Bloodborne or Elden Ring. The bosses and environments will certainly make you feel challenged and powerless all the time.

I think you'd like the Souls series by FromSoft. I only have played Elden Ring, but I think it fits your description really well. And from what I've heard, the rest of the Souls games would fit

Arguably, Spec Ops: The Line.

It’s a game where you’re a protagonist, but whether you’re a hero or not is a different story - and I can’t say any more without major, major spoilers.