We had a waterbed for 30+ years. It was wonderful, and if I slept away from home, I had really bad issues with back pain, regardless of the quality of the mattress I slept on. Unfortunately (or maybe not?) DH developed back issues of his own that were NOT helped by the waterbed, so we acknowledged that if we were ever going to sleep together again, we needed to get a new bed. I can't tell you how anxious I was about that.
In our search, we did try the Sleep Number Bed, and I have to agree with mrblanche. It didn't impress me at all, especially at the prices they were going for!
We tried memory foam, and found it OK once you got settled and agreed with it where the indents should go, but disconcerting when you needed to change position, and overall not very satisfactory, again, especially at the prices they were going for.
Of the conventional mattresses -- and we tried all the names you see advertised -- the one that begged to be taken home was Kingsdown. It's not unlike all the other pocket coil sytems with thick pillow top, but they got the balance right. That was only last February, but there was no "getting used to it" period. It was a perfect sleep from the word go. Having had such a bad time for years on any conventional mattress, I cannot tell you how much of a relief it was to find one that worked that well.