Origin - the short version
This 19th century expression isn't, as is often wrongly claimed, a reference to the tightness of the strings used to support mattresses. 'Tight' just means 'soundly/properly' and 'sleep tight' just means 'sleep soundly'. The word was probably chosen because of its rhyme with night, so people wished other 'good night, sleep tight'.
Origin - the full story
'Sleep tight' is a very well-used phrase in many parts of the English-speaking world. It's common at bedtime in the form of the rhyme "good night, sleep tight, don't let the bedbugs bite".
There are many meanings of the word 'tight' and it's no surprise that there are several theories going the rounds as to the origin of 'sleep tight'. One is that the phrase dates from the days when mattresses were supported by ropes which needed to be pulled tight to provide a well-sprung bed. This was the notion that was put forward on a 2008 BBC antiques show, when the presenter lay on an oak settle to demonstrate the support provided by the understringing and to confidently pronounce "hence the expression 'night, night, sleep tight'". This explanation seems unlikely, as it is the bed rather than its occupant that is tight and no one (in my experience) ever wishes furniture a good night's sleep. He would have had more luck had he opted to say that 'settle down to sleep' derives from 'settle' or 'seat' - which it does.
We may often have said 'sleep tight' - but why would 'tight' be a good thing?
The phrase 'sleep tight' itself was common in the late 20th century, and there could hardly have been a better way of cementing any phrase into the popular consciousness than by Lennon and McCartney using it in the lyrics of a song at the height of Beatlemania. That's where it found itself, in Good Night on the White Album in 1968:
Now it's time to say good night,
Good night. Sleep tight.
The 'don't let the bedbugs bite' part has prompted some to suggest that the 'tight' refers to the tightness of bedclothes, intended to keep bedbugs at bay. That's hardly likely, as bedbugs live in mattresses and wouldn't be avoided by tying bedclothes tightly. Also, '...bedbugs bite' is an extended version of the original 'sleep tight' bedtime message, which didn't start to be used until the mid-20th century - well after 'sleep tight' was first used.
'Sleep tight' didn't derive from either bedcoverings or ancient furniture and, in fact, isn't a very old expression at all. The first citation of it that I can find is from 1866. In her diary Through Some Eventful Years, Susan Bradford Eppes included:
"All is ready and we leave as soon as breakfast is over. Goodbye little Diary. ‘Sleep tight and wake bright,’ for I will need you when I return."
There aren't many other known citations until the early 20th century and the OED lists none until 1933, by which time the innerspring mattress had been invented and most mattresses were supported by metal straps or springs. This puts the phrase out of general circulation at the date that rope-strung beds were commonly used, which makes the rope-stringing origin unlikely at best.
Susan Eppes' line, with its clear link between 'sleep tight' and 'sleep well', leads us to the most probable explanation for the phrase. The word tightly, although not often used in this way now, means 'soundly, properly, well'. The earlier phrase 'tight asleep' derives from this meaning, as seen in this example from Marie Beauchamp's novel Elizabeth and her German Garden, 1898:
And once, when there was a storm in the night, she complained loudly, and wanted to know why lieber Gott didn't do the scolding in the daytime, as she had been so tight asleep.
'Tight asleep' just meant 'soundly asleep', or to put it another way 'fast asleep', and 'sleep tight' just means 'sleep soundly'.
See also: sleep like a top.