I'm looking for games that make me feel small and insignificant
Pretty much, I'm looking for a game where I'm not the hero, I'm not the protagonist, I'm just a pawn in the big picture.
Preferably a game with a big open world too, one that feels real and isn't just a predetermined path for my player.
I also like games where you aren't clearly stronger than all your opponents like you're some sort of God so they actually feel intimidating and there is a fear of dying
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.
Seconding the Souls games. Even though you’re on a quest to do something important and big, the game does absolutely everything it can to make you feel worthless. I saw a good quote, something around the lines of “most games ask you to beat them. They’re designed to be beaten. Dark Souls tells you it’s impossible and dares you to try” which sounds like what OP is wanting
That just paints DS as dishonest, since it's designed to be beaten too. There are entire genres of games far less fair and forgiving than DS, such as roguelikes, where death means starting over from scratch and bad RNG can screw you over in a heartbeat no matter how well you play.
Yet that doesn't matter much as the two endings of the game are that you either refuse to link the fire and walk away, leaving the world to die, or you do link it to keep it going for a little bit longer but in the end it won't matter much as it will die down eventually.
You could say that about any other story where you save the world. You only ever save it from the present danger, you never make it safe for all time. Otherwise there could be no sequels (of which DS has received a couple). "Happily ever after" is figurative.