Honestly, I'm still not sure, why would I use something like RocksDB instead or in addition to plain PostgreSQL/MongoDB/Redis instances.
I don't work with a lot of data, but typically my decisions base on basic factors and purpose:
PostgreSQL - SQL, structured data, cannot scale horizontally
MongoDB - NoSQL, unstructured data
Redis - key-value, distributed cache
I get it that you can replace storage engine and you can theoretically get more performance, but in practice compatibility and standardization is more important, because a lot of products (including third-party) will already use PostgreSQL/MongoDB/Redis, so it's no-brainer to use it as well for your solution.
However for me to pick RocksDB or some other, new, shining database/storage engine, there would have to be more compelling reasons.
What's also interesting is the trend of newer distributed "database systems" like Vitess[0] or SpiceDB[1] that forego embedded KV stores and instead reuse existing SQL databases as their "embedded database". Vitess leverages MySQL and SpiceDB leverages MySQL, PostgreSQL, CockroachDB, or Spanner. Systems built this way get to leverage many high-level features from existing databases systems such that they can focus on innovating in even higher-level functionality. In the case of Vitess, it's scaling, distributing, and schema management of MySQL. In the case of SpiceDB, it's building a database specifically optimized for querying access control data in a way that can coordinate with causality across multiple services.