I have the 40 GB model, and the only noise I ever hear from it is the little beep when you turn it on/off. No fan noise, or noise from the drive, at all.

For me, 40 GB is plenty, since I don't load it up with music or movies. You do need a HD though, as some games install data from the DVD/Blu-Ray onto the HD for faster loading times (there is no option of "would you like to install YES/NO, so there is no way around it), and you will probably want to download game updates or additional content which will also be stored on the HD.

Most PSN games that I have downloaded are less than 500 MB each, and DVD/Blu-Ray disc games rarely install more than 1 GB each (allthough Fallout 3 installed over 3 GB I think... ). I still have plenty of room for mp3s for my custom soundtracks, game screenshots that I have taken, etc.

That said, you can get cheap-ish 500 GB replacement HDs and they are apparently very easy to install (no need to open up the console, etc), so if you regret it later you can always do something about it.