Когда я должен рассмотреть использование в базе данных памяти и что проблему должны высматривать?

The trend seems to be to cache aggressively and use the database to populate the cache. Regardless of where the database lives, joins are still expensive so the preference seems to be to do the join once and cache the result in something like Memcached or Velocity.

There are still in-memory databases around and they are used, but it depends upon the context you want to use them. SQLite for example is often used as an in-memory database when testing data layers.

Well, in-memory databases generally lack the D (durability) in ACID (atomicity, consistency, isolation, durability) by their very nature. This can be overcome to an extent with "hybrid" approaches, however, at some point something (either the data itself, or a transaction log) has to be persisted somewhere to provide the durability aspect. This can generally slow down performance or introduce other non-desirable properties to an in-memory database solution

In contrast, most of todays RDBMS's have the full complement of ACID, as well as having many decades of development behind them. This has resulted in disk-based database systems that are very performant, especially with the many years of improvements and optimisations that modern day RDBMS system have seen (your BTree example being just one of many).

Another factor is our ability as application developers to reduce the load on the database by such mechanisms as caching, thereby squeezing much more perceived performance from the data layer of an application. Indeed, caching itself has seen extensive developments in recent years with distributed caching being common nowadays (just look at the number of users of memcached, for example).

Ironically, the modern day caching systems are, in many ways, slowly transmogrifying into something akin to a true in-memory database system. In-memory databases, like object-oriented databases, are very much the "new kids on the block", so it will be interesting to see where all of this goes in time. Oracle has now acquired TimesTen, and, according to this wikipedia article, Microsoft are looking at getting into the in-memory database market quite soon. That's two modern day "big players" in the traditional RDBMS field that are taking in-memory database systems seriously.