First: Everything I write here is only for Oblivion. I haven't really bothered with modding Skyrim or others.
There is this tool called Ordenador that can optimize textures all on its own. I strongly suggest checking it out, if you just want to optimize existing mods. The guide below is mainly meant for optimizing stuff you create and might want to release (or not).
Lately I've been checking my mod files to see if I can optimize some. Some of the outfit and landscape mods I use ended up having huge textures - often for unimportant things like a bit of metal or straw. That's stuff I often resize for myself.
What's worse, sometimes the textures are saved in an unnecessarily large format. An alpha channel doubles the file size so it should be left out unless it is needed. On color maps, the alpha channel is used for transparency, on normal maps for glossiness. If the texture has no transparency or the normal map has no glossiness saving them as DXT1 (no alpha) instead of DXT5 instantly saves 50% of the file size for no loss.
This is a great guide I've always used - because I can never remember which parts of the alpha should be black and which should be white and the table is pretty easy to read. It also tells when to generate mipmaps (always on object textures, never on UI elements). There's just one problem: Unlike the comment at the bottom, it is possible to save normal maps without specular shine as DXT1 without alpha. I am relatively sure that was a bug with the gimp dds plugin that might or might not still exist - if you open your saved file again and it did not magically gain a white alpha channel, you should be fine saving it as DXT1.
This is a more recent guide that explains a bit more about the format and when to use what.
Here's how I use the different settings:
DXT1 no alpha
Textures without transparency
Normal maps without shine
All glow maps
DXT3
Menu icons / UI elements - no mipmaps
DXT5
Textures with transparency
Normal maps with shine
8.8.8.(8.) (A)RBG
Those work, but result in huge file sizes because they are uncompressed.
You know how sometimes people report that their icons or textures become a rainbow pixel mess? That is related to gimp saving no mipmap textures wrong - it writes in the header that the image has 1 mipmap, but it has none and the game gets confused. I never used gimp and I can't find where I read about this right now, if I'll do, I'll update this post.
Edit 2020-03-03: Link to Ordenator, removed some unneeded fluff.