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.