Hi,
Welcome to the Apple Forums. Keeping software/operating systems up to date can have some big advantages especially as improvements are constantly being made & any issues that are discovered are ironed out. Deciding when to upgrade can be a personal preference depending on how much you use your Mac & whether the versions of software you are running are meeting your needs or not.
Whenever you perform an upgrade (especially a major one), it's worth checking that any 3rd party software that you use will be compatible once you have upgraded.
Backing up your data before an upgrade is very important...if you have an external Hard Drive, you can copy your files/photos/videos etc etc on to it should you encounter any problems. Alternatively, making CD's/DVD's of important stuff will work equally. You can also make a clone of your computers Hard Drive. Even if you aren't upgrading, keeping copies of files etc is very important in this digital age.
I recently upgraded to Snow Leopard & can well recommend making the move. For more info on some of the new features & improvements, check out
http://www.apple.com/macosx
Good luck.
Ben