In the case of websites, auto scaling is useful only if your application is not too dependent on DB but most web apps have DB as the bottle neck. The way auto scaling works is to launch new instances but that works only for application servers. You can't autoscale a DB by launching new instances.
So, even though this feature is useful it is not taking away the biggest headache in website scaling..i.e how to scale your DB.
Does anything prohibit someone from hacking together a way to scale their DB using this service? i.e. fire up instances that of a certain ilk? Not sure how detailed CloudWatch is, but you could conceivably automate anything that involves looking at numbers and taking steps to reduce load on your DB.
Hum, funny you say that, that's exactly what I'm working on: http://code.google.com/p/hotrepart/ The idea is to repartition a DB across multiple hosts while it's running. It's only a demonstrator project right now.