The tightness has nothing to do with the body of the individual. And it doesn’t mean holding on to someone sleeping next to you tightly!
“Tight” here refers to the tightness of the bed. Nowadays of course, most beds are made of either wood or steel. In the old days, it was only the frame of the bed that was made of wood. The middle portion, the portion on which people actually slept on consisted of strings. There were strings running from one end of the frame to the other. If these strings were not tightly strung, the bed sagged, making it very uncomfortable for the person lying down. Result? The individual did not get much sleep. If the strings were tightly strung, then the bed was firm and the person sleeping on it got a good night’s sleep. So when you say sleep tight, what you are actually saying is, I hope your cot is tightly strung.