Home / server / Questions / 82272
In Process
Jason S
Asked: 2009-11-07 09:07:27 +0800 CST

databases supporting MVCC (permanent history)

  • 772

I have an application which needs a database + would greatly benefit from a MVCC model. It would ideally support permanent history storage, access of the data at a particular point in time, and incremental backups.

I'm looking at the databases that might support this. I know CouchDB can (aside from the incremental backups, I'm not sure about that) but in this application I really need a relational database.

I'm surprised that MySQL lists MVCC support in Innodb / Falcon. I get the impression that maybe this is only for the database state within a transaction, and once all the transaction levels are committed, the history is lost.

What about PostgreSQL?

database

4 Answers

  1. MVCC isn't what you're looking for. It sounds like you want to be able to query data based on a "point in time" to results based on the state of the data at that specified point in time.

    That's a temporal database schema. You can design a relational database schema for temporal queries, but SQL itself makes some types of temporal queries very cumbersome. There is/was a temporal SQL dialect (the spec is here, though it appears to be the subject of research publications but was never standardized) that was supposed to make temporal queries less cumbersome while still "being SQL".

    You can find a lot of research and books on the subject of temporal data and the relational model if you search for it. Good data modeling can get you a lot of the way to where you want to go, though if you're going to use SQL as your query language you might not like how some of the queries end up looking.

    Edit: TimeDB is a front-end to Oracle that adds a temporal query dialect. The license this code is released under is completely unclear.

    • 4

  2. McObject announced in 11/09 that its eXtremeDB database has added an optional MVCC transaction manager:

    http://www.mcobject.com/november9/2009

    eXtremeDB, originally developed as an in-memory database system (IMDS), is now available in editions with hybrid (in-memory/on-disk) storage, High Availability, 64-bit support and more. Interfaces include SQL, a faster native API, and a Java Native Interface (JNI).

    • 0

  3. There's a good explanation of MVCC -- with diagrams -- and some performance numbers for eXtremeDB in this article, written by McObject's co-founder and CEO, in RTC Magazine:

    http://www.rtcmagazine.com/articles/view/101612

    Clearly MVCC is increasingly beneficial as an application scales to include many tasks executing on multiple CPU cores.

    • 0

  4. Also see this list on Wikipedia for other DBs that support MVCC https://en.wikipedia.org/wiki/List_of_databases_using_MVCC

    • 0