No body piercings are completely pain free. The pain involved ranges from the slight sting of an earlobe or eyebrow piercing to the more significant hurt caused by piercing tough cartilage tissue and muscles such as your tongue. None of this pain, however, is so bad as to dissuade people from having it done. The pain you feel can easily be predicted according to the feel of the tissue: if it feels soft, such as your ear lobe, then the pain will be slight, but if it feels hard, such as the cartilage of the hard parts of your ears, then it will likely hurt more. It also follows that the more sensitive areas of your body, such as labial or tongue piercings, will be more painful due to the higher concentration of nerves in these particular parts of your body.
Your pain tolerance might be higher or lower, but you can make that choice yourself and avoid piercing the more sensitive parts of your body. Generally, though, the actual pain experienced during the procedure itself is short-lived and believed by most to be worth it. It is the afterward that you must take care of, and ensure that you avoid post-piercing infection. Do that and your piercing will be successful with the minimum of personal pain and stress.