NoSQL Databases Performance
100,000-ish records per second doesn't sound too bad as a headline number. Without context, however, it meaningless. What's the spec on the storage array? Are you getting even close to the manufacturer's IOPS and bandwidth numbers? How does it compare to a disk performance testing tool's reported values? When profiling the application under heavy load, where is the time taken? Is the system IO or CPU bound?

Some top-end servers do have ridiculous amounts of memory. 64TB is the largest I've seen. But you're right, holding absolutely everything in memory won't work in the general case. There is the concept of the working set. These are the rows that are being read and written by current users. The system holds these in memory and synchronizes them to disk from time to time. When users move on to new work the system makes room by purging the least recently used data from RAM.

For finding specific records you will need indexes. These trade additional disk and pre-processing time for reduced lookup time. There are many different indexing algorithms, each with benefits and drawbacks.

For large OLAP work it might be that you simply must read all of the data off disk to perform the query. It may be possible to pre-process to get summary values (cubes and similar).

This is a huge undertaking. Whole careers are spent trying to get this right. That you have a working product at all is impressive.