> My initial idea was that it would be possible to store data to be written at RAM at first, and periodically flush to hard drive / DB.
Sure, but you lose anything that isn't written to disk. IIRC you can set up Mongo and Redis to do this (I'm not familiar with many of the other options). At this point you're no longer an ACID database.
Most DBs will cache as much as they possibly can in memory. Often index's will take priority, but rows that are being accessed fairly frequently will also be in their cache. Obviously the details are different per db and configuration, but in general, it gets cached.
Also, when you fwrite your data actually sits in RAM for a bit before being written to disk. Databases and other applications that actually require data to be on the disk right now need to call fsync.
(Aside, even then you're disk can and will store writes in it's own internal buffer before it can be written to disk. The OS doesn't have much control over this.)
A lot of people will, to avoid having to spin up db replicas, cache rows in redis or memcache and use those for reads whenever possible. It has its advantages and disadvantages, but for pk-based reads you can decrease the number of operations your DB needs to do at once (allowing it, for example, to focus on writes.