> That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users.

Once you have few million users then you can think about better solution.

You can fit a lot into server memory, and spilling out of RAM to NVMe isn't that bad either.