Yes, existing techniques for full text search works or things like geolocation queries won't work but there are other techniques that work just as well; it's just not the sql way. Basically, support for multiple set membership queries against a list of tags stored with entities is extremely powerful and if you index properly, you can do a lot of cool things . Plus, you can do datastore queries in parallel , which means you don't have to denormalize as much as you think; just parallelize and memcache results; e.g for a complicated front page, you can fetch different types of content in parallel.
The local server behaves remarkably the same as the deployed server, it's quite rare I find a situation where something behaves differently in production. the entire datastore can be tested locally, including complicated schemas / indices / queries in fast running unit tests. This means when I do need to do something fancy with the datastore, I can fully test it with unit tests and be confident it will work when deployed.
Long running tasks can always be broken up using the task queue. the limit will soon be 10 minutes for individual tasks and cron jobs 
I agree that cold start is a huge issue, but looks like it is being addressed in the 1.4 release  where you can pay for 3 reserve instances at all ties. Lack of support for https on your domain definitely sucks too, but I don't see how he wouldn't have been aware of that before going with GAE.
Finally, there are a number of things that are a huge time / money savers:
- really easy deployment process including support for multiple versions. This let's you have staging instances and quickly roll back to a previous version if there are any problems
- a nice admin console with a number of tools, including comprehensive access to logs that are coherent across all instances
- some really nice libraries for examining performance of datastore queries and other api calls  and getting daily email reports of any exceptions . these are built using hooks available to you in case you want to build something similar (for instance I used hooks to have regression tests on the number of datastore queries each page requires).
- the services and apis made available are really nice. for instance, the image hosting infrastructure that provides fast access to different sizes for a stored picture based on a url is pretty slick; they basically opened up the same infrastructure that is used by picasaweb to app engine users
- virtually no hosting costs until you get a lot of traffic. thousands of daily visitors is still in the free range
That said, my biggest outstanding gripes:
- cold start problem (until 1.4 is out)
- datastore latency spikes sometimes. this has gotten a lot better in the past few weeks, but I'll still have this gripe until I see it more consistent for a couple months
- no support for incoming emails with attachments > 1mb (makes incoming photos from smart phones impossible since they are usually > 3Mp these days)
- no support for long polling (upcoming channel API seems to be more for chat rooms than for general purpose server push) 
Any links? I am saving both pics and thumbs and that info would help me a lot.