> If you had some behemoth with 32TB ram and 1PB storage could all of motion fit?

Postgres has a bunch of low level locks and buffers (protected by locks) which are essentially single threaded. So even if you had a 500 CPU instance at some point you'd not be able to get more throughput out of it.

Of course it also depends on what you are doing. Large tables with high update rate are harder to handle than large tables which are insert only. My personal opinion is that Postgres tables with >100GB data (without indexes) are a starting to be a pain to work with, no matter how much RAM, CPU or IOPS you have.