Being completely and utterly insignificant in the grand scheme of things is neither.
So, I have a friend who is a classicist, and he pointed out to me that there's an element that I think people often miss about Lovecraft's writing that kind of helps to make sense of it, and that is the obvious familiarity with the Bacchae. Like, there are passages in Lovecraft's work which are almost direct quotes from the Bacchae.
The Bacchae was not well known until the late 19th century, because it didn't really fit in with the Victorian idea of ancient Greece as the rational birthplace of western civilization. By Lovecraft's time, it was starting to be performed again. The Bacchae is the story of the god Dionysus returning to the city of his birth and inflicting this horrific series of punishments on its ruling family (for denying his divinity). A recurring theme is that Dionysus' presence drives people, especially women, into ecstatic madness which they cannot control. The climax of the play is the king of the city being torn limb from limb by his own mother while he pleads with her to recognize him. At the very end, the chorus explains that the gods often bring misfortune, and that it is impossible to predict their actions.
The point is that lovecraft isn't really about literally being afraid of space, or of monsters with big tentacles. It's about the fear that your life is not under your own control, that everything you think you are is just an illusion that can be taken away. It's about the fear that there is something incomprehensible and alien
inside you that your conscious mind is incapable of understanding. The monsters in lovecraft aren't just big gribbly squids, they're things that are so outside your understanding that merely trying to wrap your head around them changes you, it takes away your capacity for reason, it makes you less human.
You're correct that the abstract concept of "being insignificant in the grand scheme of things" isn't very scary, but what Lovecraft definately finds extremely scary is the idea of having no control over your own existence, of being not just insignificant but so powerless that you are entirely at the mercy of a universe that does not care about you. Of having no stable foundation, of never being able to know who you are because
you are nothing.
The thing is Lovecraft was a weird person. He was incredibly neurotic, and had an overpowering fear that there was something wrong with him (because his father was sent to a psychiatric hospital). I think one thing about his work that can be alienating (and that I certainly find alienating) is that "otherness" is always evil (and also heavily racialized but that's a whole other fucking discussion). To me, I feel like a lot of modern "Lovecraftian" media is doing Lovecraft better than Lovecraft himself, because they don't embrace his weird fixation on purity and order as good and chaos and otherness as evil.
Another thing my classicist friend pointed out is that while Lovecraft views cultists as these wretched, subhuman creatures (basically, the way he saw anyone who wasn't white) the cultists generally seem to be doing okay. Like, maybe it's not such a bad thing to be a bit mad in a mad universe. Maybe you should go a bit easy on yourself and just accept that the universe is big and scary and outside of your control. See, the Bacchae isn't a play about how evil Dionysus is, it's a play performed in honour of Dionysus. For the Greeks, Dionysus represented something real about the universe and human nature, something that they found scary and troubling but which nonetheless existed and needed to be acknowledged. The Greeks didn't see people as fully in control of their own lives, they saw themselves as playthings of amoral forces that didn't really care about them, and they were fine with that.
