If it's not painfull you are not exerting enough effort at least that's the case in the gym. People who are refreshed and more energetic after going to the gym are the same people who won't improve beyond intermediate levels. The ones who let go of the any set at the first feelings of unease and never take a set close to failure.
It's actually fascinating how an ancient proverb could line up with modern science so perfectly.
It certainly does not need to be painful. I think most people will make a distinction between the burn of acidosis, or what you call unease, and actual pain indicating damage is occurring.
But yes, if you never train close to failure you will not grow, not past beginner gains, unless you take steroids.