Nowadays, most people use password managers (hopefully). However, there are still some passwords that you need to memorize, like master password (for a password manager), phone lock, wifi password, etc.
Security wise, can passphrase reach the strength of a good password without getting so long that it defeats the purpose of even using it?
If your services are storing passwords properly with a salt, dictionary attacks (including rainbow tables) are just as time-consuming to perform, since the salt renders each password hash unique; even for the same passwords.
So the same principle still stands; the longer your password, the longer to guess - as long as the encryption-at-rest is done correctly.