There are two parts to your concern about password length, the threat model and the rationale behind longer passwords. As you state, a lot of websites have protections against common attacks, and if the password database hasn't been leaked, your password isn't easily guessable, and online protections against brute-force protections are in place, longer passwords don't make much of a difference. When those assumptions fail, long passwords can make sure your password is too expensive to crack, as demonstrated in research. The more general case of this question, "Why have strong passwords at all?", is covered in this question.
The threat most password policies are concerned with is an attacker trying to crack the hash of the password which has been stolen or leaked from the system, also known as an offline attack. This usually has some of the strictest requirements. However, these offline attacks aren't the only threat model. You password could be compromised in other ways, like:
- Brute-force on the website itself, as you mentioned: Most secure websites have protections against this, which are further discussed below. Long passwords do help you from online brute-force attacks, although the cost/attempt is much higher than an offline password cracker. These kind of attacks are somewhat common.
- You reused a password and the password was compromised elsewhere: The only prevention here is to not reuse passwords. Password length won't help here and too stringent of password requirements might even discourage users from setting a different password for each site.
- A friend, colleague, or social engineer is trying to guess your password based on their knowledge of you: While password length helps here, you don't need very long passwords to stop this attack, and often services are rate limited to only a few attempts per hour per computer.
- There are also methods like phishing, exploiting the password reset, and stealing a plaintext database. In all of those threats it doesn't matter how complicated your password is; it's gone anyways.
Focusing just on online attacks and limiting log-in attempts is bad practice because password database leaks do happen. LinkedIn, MySpace, Adobe, Dropbox, and others have all had password hashes stolen. You can check if you appear on any public leaks here.
Don't Websites Block Online Brute-Force Attempts?
A major part of your question appears to be why password requirements are such a big deal if protections against brute-force attacks are in place. PCI compliance, for example, requires account lock-out for at least 30 minutes after 6 failed attempts. The biggest problem in preventing these attacks is working against botnets with thousands of computers without causing a denial of service. How do you prevent thousands of computers each trying to log in a few times without also locking out the user when they mistype their password while using a new computer, or using the same proxy as the botnet?
There are other ways other than lockouts to rate-limit attackers, like asking for CAPTCHA. However, there are some ways to work around CAPTCHA. This OWASP page discusses more of the best ways to block brute-force attacks.
Why Longer Passwords Help
A few decades ago when Unix was king, it was considered that five-character passwords were pretty good. Now the stakes are higher, advanced hardware is cheap, and hashes can be broken faster than ever so the requirements for good passwords are higher. This is part of the reason more companies are pushing for longer passwords.
Longer passwords help because the difficulty of password cracking goes up exponentially with password complexity. Just adding one random word to your password can make it thousands of times more expensive to guess- the difference between seconds and hours of cracking time. Adding another random word turns those hours into years.
Many people advocate for using symbols and creating passwords from larger and larger character sets to make passwords more difficult to crack. However, several examples in research show that requiring longer passwords instead of very complex passwords leads to harder-to-crack passwords (and is more user friendly). The longer passwords side was also famously supported by xkcd's "correct horse" method to generate passwords.
One caveat is that longer passwords aren't always more secure since extra characters can make a password more predictable. "orrect horse battery staple" is more secure than "correct horse battery staple", although the latter is much more memorable. Good password meters like Telepathwords or zxcvbn take this into account and will warn you when the extra characters are predictable.
How Long Should Passwords Be?
The specifics of how long your password has to be, and how hard it is to crack, can't be answered without knowing how your password is generated, how the password is hashed on the server, and the attacker's resources. For more details see this question.
I'll warn you, that password policy is a matter of opinion. I've tried to show the best solution based on research, but there are some experts who disagree with me.