Well, my mods are designed to add stuff to be used/worn by the main character only. I don't think that what I would do would be too high res or detailed for the console, and beside, my Crimson Wire is high res by Morrowind standards, not skyrim standards. I won't be making models more detailed than what will be in Skyrim, that's for sure, so I'm pretty sure that my mods won't slow down the console, if I am able to make my future mods available for console players.
I'd say nearly every Oblivion mod user (even newbies) uses Oblivion Mod Manager. Or Wrye Bash. OBSE is even easier, as it's only "install-and-run". If people don't bother with them I'd say it's mostly because they wouldn't know they exist, not because "I know, but they're too difficult to use".
Well, if mods become available for consoles, I strongly think it will mostly be new content, as in new weapons and armors. If they are designed carefully, I honestly believe people wouldn't need mod manager tools, since these are relatively simple mods. It would only become a problem if Bethesda doesn't code Skyrim with that in mind. As I said, conflicts for simple mods were not really a problem for Morrowind, complex mods were more problematic.
Sure high res overhauls like Qarl's packs wouldn't work on consoles, but if it's new content, and if it's spread out in different/new areas, it should work. For instance Shivering Isles does not slow down Oblivion (as far as I know), but it adds plenty of new content.
I hope I didn't sound too harsh, that really wasn't my intention. My English is not perfect, and it shows at times like that, sorry :/
edit: also I agree with nightcobra's last post. Freedom of choice (choosing to make mods for consoles) is better than no choice at all.