Uh, wow. You're in for a treat!
Freespace 2 looks like a modern game now... I'm not kidding.

It is insane what they have done to the engine, the modding community.
http://www.hard-light.netGet everything. My Freespace 2 directory is 16 gigs right now and growing. Yeah, 16 gigs. I don't even have everything. Be prepared to have your socks knocked off.
Oh, and as far as the best FS 2 mod... Wing Commander Saga is coming out soon... A Wing Commander 3 based-game (for free) from a different perspective.
www.wcsaga.netOh, and to get started with downloading all the neat mods...
http://www.hard-light.net/forums/index.php?topic=78710.0Read that, start installing. Have fun. If you have Track IR... even better. You can even use cockpit mods now... oh, and a guy is working on getting stuff like "godrays" and shadows into the engine now.
As for fun mods to start with? Hmmm... Blue Planet is incredible, sure to blow you away, along with the recent Wings of Dawn which is fun along with The Antagonist...
http://www.hard-light.net/forums/index.php/board,139.0.html (list of mods here, well, one of them, the main forums have more)