An upgradeable contract is a contract for which the logic can be changed while keeping the same storage (data). For example you can add new functionality to an upgradeable contract without losing any of its data.
From a developer point of view it works by using two contracts: a proxy contract and a logic (implementation) contract. Everyone interacts with the proxy contract, which simply relays the transactions onwards to the logic contract. And the logic contract can be switched to be a new contract simply by updating the contract reference (address) in the proxy. All data is stored in the proxy contract.
From user perspective there is not much difference. They just interact with the proxy contract (which displays the functionality of the implementation contract) and they probably don't even notice if the underlying logic contract is switched (upgraded).
The reason why some consider upgradeability bad is that some entity has the power to change the logic. In theory they can rugpull the project by switching to a contract which sends them all of its assets. If there is no upgradeability, this attack vector doesn't exist.