How do I get decent textures in a game?

As I understand, Textures in a game should be fairly small for performance. My problem is that when I have a large UV layout with many small details then it comes out very pixely and ugly looking in Unity (Or any 3d application for that matter). So how to get I these textures to look good?

Sure, textures should be as small as possible and as big as needed. Small is not always the best solution. It's often better to make one big texture and unwrap multiply objects on that texture. That way you can use big areas on the texture for detailed things and somewhere in a corner you can place some unimportant assets.

I'm just a programmer so i never have unwrapped anything :D. I just relay what we are doing in our project. We have a lot of weapons and armor parts and we try to put all different armors on one or two textures(1024).

Hope that helps a bit. In general it depends on your platform you want to develop for. You always have to find a way between super sized monster textures and the right look and feel.

It depends somewhat on what you are doing and what platform. I try to keep all my textures no larger than 1024 on either edge, as some video cards can't handle more.

If you really need all that res, and the object is that much bigger, you'd have to break it up into multiple meshes, each with it's own texture.

But if it's something like a large floor or wall, I use a smaller texture which tiles nice (bricks/grass/dirt/etc.) and then add small planes here and there with details like rocks/cracks/windows/etc. They need to float just slightly above the bigger surface.

I've never used Decals, but that may be another option, if not now, if/when it becomes available.