Feel free to use it until it is no longer supported (which no one knows how long that will be), or no longer useful to you. Right now 10.5.x is pretty much the oldest version of OS X that is still usable, although some people are still holding onto 10.4, but certain browsers/programs drop support for older versions of OS X after awhile.
Does anyone have any idea how well the older OS'es hold up after the newer ones are released?
Very well in fact. Many people still use Tiger as it was arguably the best for older G4s. Apple even released a version of Safari for it in November 2010, years after Tiger was discontinued.
Your biggest concern will be lack of support from third party software developers, but considering everything written for Snow Leopard requires Intel code it is unlikely anything developed for Snow will become obsolete any time soon. All software will be distributed through the App Store but Snow Leopard already has that.
Right now I don't know of anything that requires Lion, other than certain Apple features like AirDrop.
Lion and its eventual descendants will only improve but that's no reason you have to jump in right now. Lion is a major release, arguably the biggest change in OS X since its inception. Waiting is OK.
They hold up, for some definition of "up". I probably don't recommend it for someone who doesn't have reasonable control of their machine, i.e., how to care and feed it over the years. You won't get any new updates of any kind from apple and some third party apps will be left behind. If you are afraid of things like trojans and not prepared to deal with them if they occur then perhpas it is not for you.
FWIW, I use SL 10.6.5 so some stuff is already leaving me behind like Lion-only apps and security updates but I am willing to accept this.
My perspective on Lion (only 6 months on my MBP, but you did ask for opinions ...)
You can always load Lion (or beyond) on a separate partition and let it grow on you, or simply "grow up" over the next couple of years(depending on your perspective).
But I too am still at SL, even though I bought Lion to learn it well enough to help support family members.
I'm seriously thinking about forgetting the whole Lion/Mountain Lion idea
FWIW, I think you're putting the cart before the horses. If you purchased a Mac to be productive at whatever work you're doing, then it's your work which should be the deciding factor.
I'm lazy, and upgrading is a major headache. AFAIC, I upgrade only when I have no other alternative -- and even then, only kicking and screaming.
You guys are great! Thank you so much for the speedy responses!
I guess my main concern is keeping a stable OS, which means I'm naturally worried about security holes and bugfixes. It's mostly that I'm very relaxed and comfortable with SL, Lion's new features don't interest me, and my current applications run really well.
I'd really rather not change what's working well, unless I absolutely have to. Maybe I'm lazy, too haha... mostly, though, I'm afraid that all of the changes with Lion/Mountain Lion will hamper my workflow instead of helping it. I've heard of users having trouble with wifi after updating, as well as shortened battery life, and other odd bugs and glitches. I don't need those kinds of problems right now. My Macs work great, and I don't want to ruin that.
I do worry about malware and trojans, though... good point.
I'd really rather not change what's working well, unless I absolutely have to.
Good reason to stick with SL. I generally upgrade when it's too uncomfortable not to.
I love Lion and never want to go back to anything earlier, but upgrading was not entirely painless. Having a bootable SL clone eased the transition.
Stay with Snow Leopard until it is no longer supported.
Which will most likely be only until a bit after 10.8 is released this summer, fully a year ahead of what "normally" should be happening. It's never good to be running an unsupported OS, but this is making things even worse than usual, if you don't want to lose all your PPC apps and turn your computer into a giant iPad.
With Apple going to yearly OS releases, you'll have to jump to the crack of the whip every year, if you want to stay supported.
Apple says, "screw you."
My complaints all had to do with having to cope with the loss of Rosetta - Power PC apps that ran in emulation under Snow Leopard are completely gone with Lion. I knew that beforehand, so I kept a bootable Snow Leopard volume on an external drive.
The particular software that I wanted to run was Microsoft Office, the same version I had been running since 2001. I think I paid $9 for it back then. You can buy MS Office for Lion but I refuse to buy anything Microsoft any more. Instead I bought Pages, and also use OpenOffice on occasion. NeoOffice is another good alternative but I have not tried it yet.
I also had vexing Wi-Fi reconnection problems after waking from sleep along with a few million other people. That problem was addressed in an OS X update and seems to be gone for good.
That's pretty much it.