Answering my own question (after installing various OSX versions on different test partitions/drives) I found Photoshop CS4 to work fine (seemingly that is, I haven't done any extensive testing) in both 10.11 El Capitan and 10.12 Sierra. The same for Lightroom 6.14 which appeared to work fine.
I used the CS4 installer to do a clean installation in both cases. I also found
a site called Roaring apps which reports the same thing (and shows which apps work in which OSX versions).
I have yet to install Photoshop CS4 and Lightroom 6 within 10.13 High Sierra (which is also installed on one of my test partitions), but I'm leaning towards going for 10.11 El Capitan to replace my 10.9 Mavericks (for iOS 13 compatibility in iTunes) as that appears to be a leaner, less bloated OS than the later ones (failing 10.9 of course, which would be my choice before any of them).
UPDATE: I've now attempted to install my CS4 apps within 10.13 High Sierra and it simply didn't work. First, an alert about needing to update my 32-bit apps, secondly the (purchased and fully valid) serial number wasn't recognized by the Adobe installer. I've never experienced that before.
The Roaring Apps website (linked above) is quite misleading as it report about CS4 working fine (seemingly) for some people in High Sierra, while for most people (according to several web searches on the matter) it's a lost cause, and many people revert back to 10.12 Sierra or older OSX versions.
So for me I'm ruling out 10.13 High Sierra for sure and of course 10.14 Mojave, and most likely 10.12 Sierra as well -then go for 10.11 El Capitan.
Any final thoughts on that choice before I format the drive and spend time re-installing my apps and transferring my files/settings etc?