Technically you get quite a bit less than the 256 TB theoretical in practice.
It is a realistic concern, I’ve lived it for more than a decade across many orgs, though I shared your opinion at one point. Storage density is massively important for both workload scalability and economic efficiency. Low storage density means buying a ton of server hardware that sits idle under max load and vastly larger clusters than would otherwise be necessary, which have their own costs.
When your database is sufficiently large, backup and restore often isn’t even a technical possibility so that requirement is a red herring. The kinds of workloads that can be recovered from backup at that scale on a single server, and some can, benefit massively from the economics of running it on a single server. A solution that has 10x the AWS bill for the same workload performance doesn’t get chosen.
At scale, hardware footprint economics is one of the central business decision drivers. Data isn’t getting smaller. It is increasingly ordinary for innocuous organizations to have a single table with a trillion records in it.
For better or worse, the market increasingly drives my technical design decisions to optimize for hardware/cloud costs above all else, and dense storage is a huge win for that.