This could be grounds for a novel out of me.
I delved in Skyrim modding a lot back around 2015 or so (and a lot in Oblivion before that, and Fallout 3 as well). The 'best' I managed in terms of stability / mods compatibility / load order (part of stability) and amount of 'things' changed in the game by said mods was a total of about 65 mods. To have all of them installed, and figuring out how to configure _many_ of them individually, how to use and configure the modding tools used separately to start up the game (didn't start the game from Steam, had to use another tool separately) and how in the end I could start it up without the neighborhood exploding as a result took me a solid full week of reading, searching, posting, and watching videos and tutorials for many of those mods (not to mention that I had to clean up the base game files before doing any of the modding, which is another procedure in and of itself). At this point in time as I type this ALL of my mods have long been deleted, and my Skyrim (was the Legendary / Special 64-bit Edition when I played my modded playthrough) itself has been uninstalled for years.
But what I do recall was that I basically came up with two ways to play the game modded.
1) Play it with just a few select 'basic' mods just to have a slightly-better-than-vanilla experience, keep it simple and stable.
2) Play it with your true 'intended' mods but obviously take days, up to a week or more to understand everything and configure it all first before you arrive at the famous "start the game up modded for the first time after the past week of doing all of these things" and crossing your fingers it doesn't crash straight back to the Desktop without a single error window or error Log to check things out (which itself means a VERY long process of elimination in your attempt to troubleshoot what exactly might have caused a would-be crash on your first start up after modding for 5, 6 or 7 days straight).
Just going by heart here, and I know I'll miss a few mods; but the most "basic" way to play the game with a slight effort on your part to mod it (at a base level) and not spend a week or more modding it so you can start to play the darn thing involved just using the following mods:
- Unofficial Patch (100% required)
- SSE Engine Fixes (not required, needs manual adjustments in its own .INI file from what I recall, goes in hand well with the Unofficial Patch, one of the things it fixed that I found interesting is a 'detail' like water flow speed in rivers which normally is in sync with the actual in-game speed of time flow; so basically the day / night duration affects by default the speed of the water in how it flows... it's a weird engine-related issue that this mod can fix, among other things like that)
- SkyUI (couldn't stand the vanilla interface after using SkyUI, a major improvement)
- Quality World Map (adds a lot of details to the overview world map, especially for roads)
- Static Mesh Improvement Mod (significantly improves the various game world mesh models / higher polygon count on a lot of things, making the game look a bit less jarringly old)
- Immersive Citizens AI Overhaul (does what it says, it greatly improves the day / night 24h citizenry schedules of a lot of the NPCs in the game, actually making them do specific tasks at specific hours and reducing some useless flags and tasks in their scheduler, that's things I delved into myself back when I modded Oblivion on my own; in my opinion this mod is about as essential as the others I mentioned so far)
- Ordinator Perks (overhaul of the Perks system of the game from what I recall)
- Realistic Water (one of the only 'for appearance' visual changer mod I always came back to)
And that's about it. I might be forgetting like 2 or 3, maybe. But I do remember my "minimal" modded Skyrim having around 6 or 7 pluggins active in my load order.
Now, the IMPORTANT part for me at the time (when it came to making a basic modded Skyrim work well) was to make sure that I did not have more than just ONE mod doing one particular thing to the game at the same time. So, example would be... if I had Realistic Water (and I pretty much always had it) then I would NOT use any single other mod that would modify _anything_ ever about Water anywhere in the game. If I had a mod that changed the UI (and yes I did, always, with SkyUI) then I would NOT _ever_ use another mod that would directly change _anything_ anywhere on the screen with the interface windows (be it another overhaul of game systems or not). I also remember using a mod about Animations, although I don't recall the name, but it would allow more and new animations to be added to the game, especially with combat animations (it was something about finisher moves after a fight, like impaling your enemy with the sword in a cinematic way, it would require a mod for such animations to be possible); and if I did, I would make sure that no other mods would ever change animations in any way, shape or form.
All of that to ensure maximum stability and compatibility and to avoid mod conflicts.
AND, finally, BEFORE doing any of that I had to 'clean' the base game files because some mods were known (when used in high numbers mostly) to potentially create conflicts with "IDs" of... things... in the vanilla game, and to avoid that from potentially happening you'd have to remove unused / redundant ID entries from the base game files first and THEN you would use your modified (cleaned) versions of those base game files in sync with the rest of your mods and plugins.
I can point at this old but still very relevant video about this "cleaning" process just in case (it's the one I used myself all those years ago):
Side note: Gopher was known (still is I assume) at the time for making VERY well explained tutorial style videos like this for modding.
So anyway...
I'd recommend you just do something like this first, just play Skyrim with a basic level (and number) of mods for a start. There's tools that allow you to have two separate Skyrim mod installations at once, and the tool will 'switch' between essentially your Skyrim "builds" when starting up the game, etc. You can customize many things. Or just don't try to do 2 things like that at once and just have one main installation and just install like 5 or 6 mods and call it a night, play your game that way and it should be just fine. It does still require some configuration work on your part, but it won't take a week, maybe just one night at it and you'll be good to go.
And finally, THE #1 most important thing to do is to BACKUP your Master files AND your base .INI files as well, put those in a different location on your drive, you never know. And also, BEFORE you mod, and before you even clean up the Master files, right after you download and install the game, START it up ONE time, that's it. Just start the game "as is" after installing it, in order for the game to generate your basic .INI settings files one time. Then exit the game, and back up those .INI files along with your Master files. And THEN, after that is done, start up the steps to mod; and once all done, start up the game (which would be your 2nd startup only then).
Hope it helps (might be missing 1 or 2 things here, going by memory... it's been just about 5 or 6 years since I played modded Skyrim).
EDIT: Oh and, kinda forgot the main thread's question: can we get 'burnt' out by modding these games alone? (Skyrim and Co.), the answer is 500% yes.
I actually never finished Oblivion because of modding the game too much (I.E. spending way too much time on the whole modding thing). You can get burnt by it if you want to delve into it too much, because it's also addictive in a way. Once you realize you can get that one thing you don't like "as is" modified in a way that you'll like... you'll start doing just that for about everything. You have to force yourself to stop at some point and think "
Ok it's enough, I'd like more mods but these will be alright".