I can't comment on what having a high refresh rate monitor is like without G-Sync (or Freesync), but with G-Sync it's amazing. You can gain and drop frames as much as you like with no tearing at all, and it really is how gaming should be for everyone. I paid £300 for my 24" 1080p AOC G2460PG and it was worth every penny.
As for how things are at 30/60/120/144, in GTAV my personal minimum framerate is 60fps. If it starts dropping below that I'll turn down settings. It's the same with anything else where I can control it. It's made it so Dark Souls at 30fps is nearly unplayable. You obviously do adjust, same as you always do and will, but it's literally a quarter of the smoothness if you're forced to play one of the crappy ports that limit you to 30.
In my experience, it's not AS noticeable in games as it is in just normal usage. Simply using the mouse is a joy on a 144hz screen.
I feel the opposite. I can really notice it in games when I go above 60, but for regular PC stuff it's not quite as noticeable. The exception to that is minimise/maximise animations. They take on a whole new smoothness, and it's great.
Yes it's pretty noticeable but it's not like when you go from 60 to 30 and want it all to end.
Load up something that gives you quick access to an option and go from 144fps down to 30. It's something else.
Definitely not as noticeable as 30->60 but it is noticeable. I just picked up a 1080p 144hz g-sync monitor yesterday and it's awesome for fast-paced FPS games.
That's what I've got. Fast paced stuff is obviously where it's best, and FPSs are included in that. It seriously makes me play the Half-Life games differently. I play much faster at 144fps than I do at 60, I guess because I'm more in control.
I can definitely tell the difference between 30FPS versus 60FPS, but when it goes to around 90FPS or higher I have a really hard time detecting the difference, It eventually just all blurs together.
There are definitely diminishing returns, and yeah over 90 it gets harder and harder to tell. It's easier to tell if you're going fast in something like Sleeping Dogs.
Please note:
I don't want you to think that every game will let you play at 144fps. A lot of games, especially console ports, are locked at 60. For FPS you're generally fine (Source engine games are unlocked, and Unreal Engine 3 games are locked at 90, apparently), and open world games (Sleeping Dogs, Just Cause 2, GTAV) are over 60, but for a lot of games (depending on your library) will lock you to 60. Burnout Paradise for example, a game that would benefit from 144fps, is locked to 60. Some games let you play at above 60fps, but have engine issues that appear above a certain point. I've read of Fallout New Vegas having physics problems (some physics engines start to crack, like Dark Souls') and throwing you across a room when you enter at high enough rates. I gave Skyrim a whirl for 10 minutes yesterday and you get a couple slight graphical glitches. Nothing serious or gameplay affecting, just a little noticeable thing here and there that lasts for 1/4 second).
For a general guide of games that do and don't support it, I found this the other day:
http://120hz.net/showthread.php?3258-New-120Hz-Games-Report&