Ideal Civi Setup for big database
As others point out, there are no clear answers. The number of simultaneous users is also an important factor. But here are some tips from my administration of a similarly-sized database:

I run everything on a 4GB server.  This is usually sufficient, but when my client had a very high-traffic event, I saw some monitoring warnings (though I couldn't detect slowdown in manual testing).  If you're eligible for Microsoft Azure free hosting credits, you can get an 8GB server and not worry*.
Local vs. cloud doesn't affect performance.  If &quot;local&quot; implies &quot;not accessible to the Internet&quot; that will mean you lose some features though.
Install Redis.  I have it on my live site but not my dev site and the difference is noticeable.
Tweak your MySQL settings to give as much InnoDB cache as you can spare.

* As Alan points out in the comments, there are many political reasons to not host with Microsoft; data privacy issues, jurisdiction and a willingness of your host to fight for your rights are all important.