I have recently upgraded from 2GB to 4GB on my Power Mac G5 as I found with all my usual applications open (MSN Messenger, Safari, Mail, iTunes, Address Book, iCal, Quicktime, Preview, BluePhoneElite etc etc), when I ran apps such as Aperture and Photoshop together, I was noticing a lot of page outs in the activity monitor, and a noticeable decrease in performance.
However, since upgrading to 4GB, I've noticed that the overall RAM usage is approx 3GB, so I still have some more in reserve just in case.
If you're goin to be keeping a lot of apps open at any one time and you're using quite resource hungry apps such as Photoshop/Aperture, then an upgrade to 3GB would be a worthy one, for most users however 2GB is more than enough.