In real-world use memory needs will vary by use like anything else, but are entirely reasonable. I have an old box with 4 gigs of RAM and about 20T disk that performs just fine for modest file-serving needs. If I had more than a few users for that system, it would need more at some point, but mostly for client access software, not the filesystem.
ZFS will use as much memory as you want, and benefits from lots of it in many use cases. But it doesn't require it.
I find it to be the current sweet-spot between useful features, performance, and stability - snapshots, trivial filesystem serialization; not the fastest, but acceptable; and rock solid.
Anyone approaching it from scratch, I highly recommend thoroughly going through the operations one does without your irreplaceable data on it. Everyone, including me, ignores this recommendation. So at the very least, allow me to suggest that you think very long and hard before running that -f (force) command the first time you replace a disk on the array with the baby pics.
[1] Maybe on a system with a lot of ram but constrained storage; I haven't encountered those, but I'm sure they're out there somewhere.
The high memory usage profile definitely isn't from an extra 4 bytes on a pointer, but from design and features of the filesystem.
So 2^64 * block size = 2417851639229258349412352 bytes
Or 2147483648 PB. Sure there might be distributed systems that are mind boggling large, but those aren't in a single filesystem attached to a single node.
ZFS would be a better filesystem with "only" 64 bit blocks.
Today's higher density nodes are something like 48 disks * 12TB, so approximately 2 per PB. 12 per rack would be 6PB. So 178,956,970 racks consuming 300 times the annual production on earth and you'd start wishing for that 65th bit. All in a single OS connecting to a single storage system.
Or you could assume that in the next few decades you'd be batshit crazy to want to install that much storage on a single zfs system and half the overhead of every pointer into the filesystem.