I played vanilla on PC for a long time, then I added some mods that enriched the experience, then I got the PS3 version and have been playing it recently.
I think the vanilla game is great without mods. Sure, there are a few things here or there that you wish you could change (and you can!), but the core experience is still really good without mods.
That said, mods can greatly add to your gameplay experience. I tend to avoid big overhauls (I still like the original game, don't need to change it into another game, thank you) and lore-unfriendly mods, I like to play with mods that make subtle changes on gameplay -- a mod to denock arrows, another to be able to use alchemy apparatuses without having them on the inventory, among others -- and some cosmetic mods -- better skin textures, different weathers, illumination for windows at night. I try not to go overboard with mods and sometimes it is an exercise in restraint.