Its hard to say how much improvement is necessary before "it becomes worth it" for someone else.
You didn't say, but the following assumes you are refering to Adobe Photoshop and not to any of the other individual CSn applications or complete bundles.
Not too long ago, I upgraded my personal "antique" Photoshop CS to CS4. I not use PS/CS4 at home and still use PS/CS at work. There are some nice improvements in PS/CS4, but nothing that makes the pictures any better or significantly improves my workflow other than ACR. There are some nice UI improvements involving Adjustment Layers that are nice, given my work habits.
The big improvement centers on ACR. If you shoot RAW, the upgrade may well be worth it for that alone.
The biggest motivator for me was that CS4 is the last version that would allow an upgrade from CS. You CS2 will qualify for upgrade to the current CS4 and CS5, whenever that appears. Unless Adobe changes their upgrade policy, CS5 will be the last that allows upgrade from CS2.