I upgraded straight from SL to Mavericks recently with very little grief. It was free for me. I did it on a quiet Friday afternoon though so I had the weekend to fix anything that went wrong!
In the end I had 2 minor issues which I uncovered by looking at the error messages in the Console – the Calendar and some Adobe installation app both kept trying (and failing) to do something every few seconds which obviously slowed my iMac down to a crawl. I googled the errors and it turned out they were fairly common and solved with a few commands in Terminal. After that the computer was much faster than before.
CS4 should be fine… Photoshop CS4 works for me, for Illustrator I have CS6 though as that is what I use mostly. Office 2008 works fine also.