There are some specific cases where contracts should be upgradable. However, I think that for the most part, it's much better, and aligned with the ideals of the space, for contracts not to be upgradable.
The founding myth of Ethereum is that Vitalik was frustrated that a video game he way playing could change its rules. He had been leveling up some character, when the game manufacturer decided to change the way it allocates skills. And so he built Ethereum, where you can't change the rules of the game.
Obviously that's a myth and not the "real story" of why Ethereum exists, but I think that foundation myths are really important. As I tried to explain in my post about Facebook and the movie The Social Network, foundation myths illustrate what's psychologically for us to understand about platform, and can be powerful.
In Ethereum, the psychologically essential narrative is that there are games here where the rules can't change on you by some centralized party.
I think that protocols that abide by this will do well. Uniswap is a good example of a protocol that roles out in versions that don't upgrade. ENS is another example of this. Early Compound also couldn't upgrade, as well as many other great protocols.
Upgradability is essentially hack that comes from being able to delegate calls to an address saved in storage — where the storage is mutable.
It makes things very complicated, both for the end users and for the organization (company, DAO, etc.) that tries to organize upgrades.
There are some instances where it would be very difficult to move users or assets to a new version. Examples of this include smart contract wallet platforms like Dharma and Argent. Another example might be Compound, where improvements can be made to the cTokens, but we don't actually want to move our cTokens to new versions, because it might have tax implications or be otherwise troublesome.
So, clearly there are these cases where upgradability makes sense, in which we should think about the parts that are upgradable as versioned modules. Argent's style of having modules that are swappable makes sense to me.
I'm sad when I see a protocol like Foundation only provide a proxy that covers some mysterious upgradable black box. That seems the opposite of what we're trying to build in Ethereum in general. It means that you don't understand what's happening under the hood, and that whatever those rules are, they could just change on you.
I'm definitely a bit conservative here, and I'll continue to wish for more versions and fewer upgradable protocols. Versions are simpler for everyone, and it's actually more aligned with the core narrative of what web3 and Ethereum is about. I think we should strive to be less upgradable.