I have tons of CC, and although my Mods folder might cause some lag, I very rarely have any trouble with the cc itself. I mostly have BB and CAS stuff, not so much Mods. Whenever there is a patch causing mods trouble to many smmers, I have only one file I need to replace (and that creator is on the ball immediately, so I don't need to wait playing my game).
My biggest warning re loads of cc is that the game's catalog system gets harder to navigate. The search tool gets useless because you will see only the first few matches, and often not he one you are looking for. I waste so much of my simming time scrolling the BB catalog, quite often I find what I looked for in a section I already scrolled twice. I have plenty of items that I probably never used in my game, or added in one room and then forgot at I never played that household again.
My tip would be to add only CC that radically offer some action or style that you do not have already. Adding 10 versions of "pile of paper" for variety (I probably have 20...) has no real effect as your game would be just as fine if using just one or two of them. Depending on your style, avoid CC that is not Maxis match. The exceptions might be those offering unique gameplay/features, but in my game I use the "wrong looking items" very rarely, so I could just as well get rid of them.
Some technical consideration. Learn to adjust cc settings in SimsStudio. CC often come with not so good categories, or CAS items might be tagged for random use by NPC. I also remove anything related to situational outfits. Be aware that the cc filename can cause lag if it contains special characters. You would definitely want to change " supercute dress&scarf (version 2)" to "Creator_supercute-dress-scarf-version-2". _ and - are fine while most other special stuff is harder to read by the game. Having one cc with such a bad file name is not a problem, but I noticed lag when there were 20. And you will quickly get there if you dl many files from a creator who is not aware of this.
From time to time you will need to look up a file in the Mods folder. If you have more than say 50 files, it's smart to create sub categories like CAS, BUILD, BUY, MOS etc. You can have sub categories too, but I guess fewer levels makes a happier game :)