But there's a lot of things that can be sensibly rate limited, such as logins attempts to a given account. Now that has its own considerations, too, if pushed to the limit... you'd prefer that an attacker can't lock down your service just by spuriously trying to log in to all your accounts 5 times every 5 minutes or something. But loud downtime (which you can then react to) may be preferable to getting your users silently hacked.
I definitely agree that they are less useful than they look at first glance, a great deal more complicated than you'd like, and more subtle than you'd think. But they can still be a useful tool.
A small site is unlikely to attract a proper DDoS, but will get hit by the usual misbehaving web scrapers, broken clients in a silly loop (I'm looking at you, iTunes) and keyboards with super sensitive F5 key. In those cases, serving an occasional 503 might work better than the alternatives.