I've built some of "social" sites, so this is pretty much familiar for me.
For these type of sites, I usually split the database.
It's not recommended by MODX's core team, because xPDO doesn't do that.
I found the trick of joining query of foreign database and MODX.
By finding this, I could split the user's information, including their usergroups to all database.
It was meant to centralized which database was subject of database replication.
That said, MODX was only used for registering/login and blogging.
At any time the client wanted to switch CMS/Framework/Platform (for any reason), the developer would just need to convert blog contents, without worrying to deal with user's database.
Well, of course it's the scenario of keeping the database in MySQL.
On sidenote, beyond the MODX topic, you might want to read more about
graph database.
It's built specifically to deal with horizontal relation data.
For this database, I don't have experience, yet
, so I can only tell you that.
For hardware, I recommend you to try one of the Cloud services, because handling the anger of half a million users will be pain in the ass if your site is down even just in 10 seconds.
[ed. note: goldsky last edited this post 8 years, 1 month ago.]