What is the practical meaning of the 500 GiB limit today, if the the directly accessible stable memory is limited to 64 GiB, and that serialization/deserialization between heap memory and stable memory (via stable variables or preupgrade/postupgrade) is currently limited to about 2 GiB?
Is 500 GiB a future roadmap target, or is there already a way to utilize more than 64 GiB?
How should developers think about these limits when designing data-heavy canisters?
The 64 GiB number is wrong (i.e. old). 500 GiB is the stable memory limit. If you manage your stable memory directly, i.e. you use Regions or data structures built on top of Regions, then using all the 500 GiB is practical. You can do that regardless of whether your canister uses EOP or not.
For the heap memory you have two scenarios:
Your canister uses classical OP (not EOP): It uses serialization/deserialization between heap memory and stable memory. To be safe with the instruction limits during upgrades it is advisable to limit your heap memory usage to 2 GiB. Above that your may risk hitting limits, though you may get away with more. Those 2 GiB will be serialized to stable memory so they will be taken out of the 500 GiB where they co-exist with the Regions.
Your canister uses EOP: It does not use serialization/deserialization so the 2 GiB safety limit does not apply. It also uses wasm64 by necessity so the 4 GiB limit also doesn’t apply. You now have a temporary artificial 6 GiB heap limit which will probably be raised in the future.