My preference is HD, 1920 x 1080, or 1280 x 720, at least. It just looks great on a big screen HD TV. Higher resolution is definitely worth it for big screens.
However, on a laptop for example, while higher resolution will still look better (sharper), it does not really matter that much.
Generally speaking, you want higher resolution for bigger screens, for the best viewing pleasure.
But that is not everything. Better or poorer picture can also be a result of the codec used and video compression rate.
Unfortunately, better picture comes with a price. Just like you noticed, Stewie, I think that the trend will be bigger and bigger files, and I'm okay with that. The good thing is that the external hard drives are getting bigger and bigger too, at more and more affordable prices.