When should I consider using a in memory database and what are the issue to look out for?

Question

I was just think that now it is common to have enough RAM on your database server to cache your complete database why are the specialist in memory database (e.g TimesTen, see also Wikipedia page) that were all the rage a few years ago not being used more?

It seems to be that as time go on, none disk based databases are being used less, e.g most applications are now built on conventional rational databases. I would have expected the opposite as RAM is getting close to being free for a lot of servers.

I am asking this, as I just read up on the stack-overflow-architecture and the page says

This is significant because Stack Overflow's database is almost completely in RAM and the joins still exact too high a cost.

But I don’t think this would be a problem if “pointers” and “collections” were used instead of the normal btree. Btree are a very clever to get round limits on disk access speed, e.g they trade CPU useage to reduce disk usage. However we now have so match ram.

But we still need database, as doing your own

• Locking
• Deadlock detection
• Transaction logging
• Recovering
• Etc

Is very hard.

@S.Lott, Given we all spend so long choosing indexes, avoiding joins and investigating database performance problems. There must be a better way. A few years ago we were told the “in memory databases” was the better way. So before I jump into using one etc, I wish to know why other people are not using them more.

(I am unlikely to use TimesTen myself, as it is high priced ($41,500.00 / Processor) and I don’t like talking to Oracle sales people - I rather spend my time writing code.)

See also:

• Alternative to the TimesTen in memory database
• Has anyone published a detailed comparison between different in-memory RDBMSs?

Answer 1

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.

Answer 2

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.

Answer 3

Most probably there are just no mature products of memory databases which could be used as a full replacement for a classic database.

Relational database are a very old concept. Although there where many approaches to move forward and develop new technologies, eg. object oriented databases, the relational databases didn't really change their concepts. Don't expect things to change too fast, since databases didn't change much in the last ten or fifteen years or even longer.

I think, development of technologies is not so fast as one might believe. It takes decades for new concepts to be matured and established. First of all in database technologies, where maturity is much more important then anything else.

In ten or twenty years, databases are probably not the same anymore as they are today. If in-memory databases is the future - nobody can tell this today - they just need some more time to develop.