10.6.8 and all in all for my needs, I'm happy enough. However as time goes by I do wonder if it would it be worth upgrading my OS, as in to Lion, Mountain Lion, Mavericks
Absolutely....
Everything from 10.0 to 10.7 was a work in progress..... perfectly good, but with improvements being made all the time.
10.8 and 10.9 are however a different kettle of fish. Much more refined, more optimised .... great iterations of OS X indeed.
Up until recently, I was running 10.8 on a 3.06 Ghz 2009 vintage MacBook Pro and it worked perfectly well (well, as well as you could expect on a vintage machine..... but it was not particularly slow .. intact, if anything, upgrading to 10.8 actually made a positive difference). But do check the tech requirements against your particular machine.
Yes, there are incompatibilities with some older old apps (Apple don't make a fetish of backwards compatibility)
Actually they do ! Apple gradually phase out APIs rather than just suddenly removing them. Its either lazy developers failing to keep up or lazy users who can't be bothered to update who are the problem.