I think that all comes down to how the travel, visual appeal, and POIs are handled. As well as a personal interest in the gameplay loop. The following are my general opinions on a few games for why I think they do or do not work.

Daggerfall would be way too big, because the POIs are few and far between and there is no visual interest between, but it worked because it had fast travel.

Each of the successive TES games had more visual interest to them and wel spaced POIs and I spent a lot of time walking on first playthroughs without fast traveling anywhere.

Similarly No Man's Sky could seem too big at first blush, but if you like the gameplay loop it's infinitely fascinating. For anyone wanting to move further in it's also helpful that there are gates to help make large jumps, without them being a requirement to enjoy things.

Cyberpunk 2077 was very visually interesting and had a ton of POIs and was fun to traverse on foot and in a vehicle. I thought the size was fantastic on my first two playthroughs. The third time the badlands areas got a little frustrating though.

Stalker and Stalker 2, are very fun to traverse by foot for me despite being very large. They are visually very interesting, especially 2. There are plenty of things you can stumble on and explore. In fact on my first playthrough of Stalker 2, I didn't even realize it had a fast travel option for over 60 hours because I didn't feel the need to look for one to use. Loved the huge size of those.

WoW was horribly oversized, as are many MMOs. WoW was(and imo still is despite many upgrades since I played, just not a fan of toony looking games) completely uninteresting visually, had no "on the way" POIs and had no motivation to look around. Long travel was a chore on top of a burdensome gameplay loop. I hated WoWs size. It felt big just because it would take people longer to play. I can't express how fucking boring it was to me. And exploring had zero reward. I remember wandering into the water and swimming for like 30 minites to get behind some massive tree or something (all I remember was it was a brown gradient that's how dull the visuals were) and I get behind it and there was fuckall. That was the last time I played I think. More brown gradient and uninteresting light blue water gradient stretched off into a foggy white gradient. Fucking hated WoW but especially its size. MMOs like that are the equivalent of having a rail shooter that's more train ride simulator than shooter. It works for other people, I just couldn't stand it.

Outward is a fantastic game but it's world feels a little too big sometimes. I don't really enjoy wandering it that much even though I enjoyed the game on the whole. Just felt I got to the point of sprinting from one objective to the next because I was tired of traversing the map.

So it's really game dependant imo. If they nail some key aspects, size doesn't seem to matter.