Thursday, June 30, 2011

Column oriented databases are not the same as in-memory databases

In recent years, thanks not least to aggressive marketing by QlikTech (or Qlik Technologies as the are now often called) Tableau and Tibco Spotfire, columnar databases and in-memory databases have become very fashionable. Microsoft's VertiPaq engine, which is behind the PowerPivot product, is a good example of a tool that came in on the wave of this trend.

One of the results of these is that there seems to be some confusion about what the terms "in-memory" and "column oriented" mean, and attributes of one are often attributed to the other.

Just to be perfectly clear: A columnar database is not necessarily in-memory, and an in-memory database is not necessarily columnar.

In-memory is a somewhat vague term, since, as Nigel Pendse likes to point out, all databases have to hold data in memory to process it -- the CPU cannot directly access the hard drive. However, I would say that unlike some other tools, IBM Cognos TM1 and QlikView are in-memory. These products load everything into memory before they do anything. If there is not enough memory to fit the entire data set, the load fails and that's that. The same applies to SAP HANA. But unlike QlikView and HANA, TM1 is a multi-dimensional database.

The loading behavior of an in-memory database is much different to the MOLAP engine in Analysis Services, which is fundamentally disk-based but has sophisticated paging abilities to keep as much as the data as possible in memory, or the column oriented Spotfire, which attempts to load everything but uses paging if there is not enough memory.

Columnar is a much clearer and simpler term. It simply means that the data is stored by column instead of by row. There are a large number of analytic databases with this architecture, such as Exadata, SAND, Greenplum, Aster, or Sybase IQ, just to name a few. Some, like Vertica and VertiPaq, even refer to their columnar architecture in their names. Some columnar databases are in-memory, but many are designed to deal with huge amounts of data, up to the petabyte range, and cannot possibly hold it all in memory.

By the way, what got me off on this rant is actually this blog about Endeca Latitude 2 which actually equates the two technologies, and a Linked-In discussion the author started (which is private, so I can't link it here) with the title "Is Data Modeling Dead?"

The idea in memory databases kill data modelling comes from the fact that columnar databases are often used to discover hierarchies, and a whole generation of so-called "agile" in-memory database tools use this method. But in-memory multi-dimensional databases are still around and still very useful for analyzing data on well defined structures such as financial data.


Elad said...

Looks like you and I have very similar ranting styles :-)


Anonymous said...

Hi Barney, I read your blog with great interest as you seem to be on to something but I am puzzled by your conclusion. I'm not sure what you mean in your last paragraph which says columnar databases discover hierarchies. And not sure how that makes them "model-less" or different from in-memory databases.


Barney said...

The point here is that the difference between columnar and other types of databases (including multidimensional databases) has nothing to do with the issue of whether a database in in memory or not. About the hierarchies, I responded here:

Beverly Gordon said...

This post has a lot of importance to the people…I hope you can continue to inspire and post more of this…Thanks

Anonymous said...

Ні wоulԁ уou mіnd letting me know which web hoѕt you're working with? I've lοadеd
your blοg in 3 different web brοwѕeгs and I must ѕay this blog loаds a lot faѕteг then
most. Can you rесοmmend а good web hοsting provider
at a reаsonable pгice? Thanks a lοt, I аppгecіate іt!

Mу sіte - how to get bigger boobs naturally

Anonymous said...

It's a pity you don't havе а donate button! I'd most certainly donate to this brilliant blog! I suppose for now i'll settle foг boοκ-marking and adding
your RSS feed tο my Googlе account.
Ι lοok fогωard to brand nеw upԁates
anԁ will ѕhаre thiѕ blog with mу Faceboоk gгoup.

Chat soon!

Hеrе is mу blog; Emergency Locksmith Solihull

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Hey would you mind stating which blog platform you're working with? I'm loοκіng to staгt my oωn blog
soon but I'm having a tough time deciding between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I'm looking fοr sоmеthing cοmpletely unique.

P.S Sorry for being off-topic but І had to

Мy ωeb page ::

Anonymous said...

Goοd pоst hοwеver І was
wοnԁering if yоu сould wгite a littе
moгe on this topic? I'd be very thankful if you could elaborate a little bit further. Bless you!

Also visit my webpage: fast way to cure heartburn

Anonymous said...

Hiya! I κnow this is κindа off topic but I'd figured I'd
aѕk. Would you be іntеrested in trading lіnκs
or mаybe guest authoring а blоg article οг vice-verѕa?
Mу ωebѕite discussеs a lot of the
sаme subjесts as yours and I
thinκ we could greatly benefit from eаch other.

If you might be interested feel free to shoot me an е-mаil.
ӏ look fοrwarԁ to hearing from уou!
Greаt blog by the way!

My webѕite; colonic birmingham

Anonymous said...

Τhanks foг one's marvelous posting! I seriously enjoyed reading it, you can be a great author.I will always bookmark your blog and may come back later on. I want to encourage you to ultimately continue your great job, have a nice evening!

Here is my blog post :: easiest way to lose belly fat fast

Anonymous said...

My coder is trуіng to persuаde me to move to .
nеt from PHP. I hаve always disliked the ideа because of
the cοsts. But he's tryiong none the less. I've been using WordPress on νarious webѕitеѕ for about
a year and am nervous about switching to anotheг platform.
I have heard excellent things аbout blogengіne.

net. Is therе a waу I can import all my ωordpress cоntent
into it? Any kinԁ οf help would be really apprесiated!

mу web-sіtе: legal highs salvia

Anonymous said...

I'm not sure why but this site is loading extremely slow for me. Is anyone else having this issue or is it a issue on my end? I'll checκ back
latеr and ѕее if the problеm still exists.

Takе a look at my wеbρage: How to make yоur dick bigger without pіlls ()

Post a Comment

Please leave a comment