#columnar database

Difference between columnar and row-based databases.

There are many types of databases present and there is a lot of confusion(for beginners) to use which one in which cases. To add to the confusion there are columnar databases. In this article, we will look at what is the difference between row-based and columnar databases and where we can use columnar databases. Difference between these columnar and row-based databases The main difference…

Current technologies reflect the need of the business at certain times.  The need started with managing data effectively and Transactional and Relational databases responded.  Analytics were required and OLAP became mainstream.  Businesses balked at dealing with slow IT and behold Qlikview and Tableau.  Cell Phones, Social Media and the Internet made demands of data that were not thought of.  Volume and Velocity suddenly become important.  There is talk that the data warehouse days are numbered.  I dont believe that to be true.  They just are not set up to deal with the current need for getting data quickly to a point where analytics can take place.  Time to Analytics is key and the standard path looks like this> Data Source sends data to external source.  Data Integration takes place, business rules applied.  Aggregation happens in the OLAP component and then the business can look at it.  There is a production window for this that usually starts when a business ends for the night.  The data is offloaded and manipulated as discussed and then sent to the data warehouse and then aggregated in OLAP (not always but assume so for the lesson).  At the end its available for analysts to come in and slice and dice it.  This usually should happen in the production window before morning.  Companies usually underestimate the amount of data that needs to be managed daily.  

Assume that within the next few years the amount of data managed by these systems will increase to 30TB for midsized companies.  How do you manage that in the context of the current architecture?  

Welcome to the Big Data and the New Architectures.  Hadoop, Columnar databases and Correlational Databases.  A brief introduction will be given here.  Note these architectures are complements to the old not replacements.  However the lesson to remember is similar to what occurred in India with Cellular Infrastructures.  Most of the original hard wiring was so difficult to implement in areas where zoning was impossible to even understand that a lot of people skipped land lines and went directly to the new tech.  This is very similar to what Google, Amazon and FB are doing with technologies such as Hadoop which previously did not need to exist.  

Small and midsize companies can benefit from this technology and become extremely competitive in a short time.  The advantage the larger players will have will relate to their ability to manage their own internal knowledge. Knowledge management is a crucial value to companies that seek to stay ahead of smaller companies that can seemingly now come out of nowhere.  Lets look at some of that tech.

Hadoop was brought about by the need to look up information through massive libraries at a fast rate.  This can only be done by parceling out the work and letting the servers take on a piece of that work, the intelligence lies in knowing how to split the work across servers and you can maximize this by keeping servers close to each other (in the same rack etc) or understand what the split is of servers that are close and those that are far.  This is why the Cloud is not the best architecture for Hadoop since you remove a critical element of the original design which is knowing where the servers are in relation to each other.  

Columnar Databases are akin to keeping data in columns instead of tables.  The advantage would be that for the most part most sql statements on a relational database would need to go through the entire table to find everyone who lived on the same street.  Whereas in a columnar database if you were to keep street data in alphabetical order the search would know where to start and when it had read the last record so it might only need to read 100 records from a very large set.  This reduces the work significantly.  

Correlational databases are the minimalists of the database, think of it as storing something unique only once.  For instance if someone is called James then his name is stored in the name record.  Then he is stored in the table as 1.  If someone else is called James then you dont save his name again, you just save him as 2 in the table.  To illustrate this.  In relational you would have James stored twice as String type with a allocated size.  In Correlational you would have James, 1, 2.  The first as String the next two as integers (possibly a considerably smaller footprint to the original.  As the database increases in size the payoff in managing size becomes exponentially better.  

Columnar Databases

Ingres just published a TPC-H benchmark for VectorWise, an analytic database technology employing    1) SIMD processing (Intel SSE 4.2),   2) better memory optimizations to leverage on-chip cache,   3) compression,    4) Column-based storage.

Ingres originated as a research project at UC Berkeley (see Wikipedia) in the 1970s, and has since become a commercially supported, open source database system. Apparently, Ingres project people later founded Sybase. So Ingres in a sense, is the grandfather (or perhap grand-step-uncle?) to SQL Server. According to the Ingres whitepaper, VectorWise spun off from CWI in Amstersdam, with some roots in MonetDB? For the time being, Ingres VectorWise is not open-source. The interesting aspect of VectorWise among other column store database products is use of the SIMD, as in utilizing the special instructions and registers built into recent generation microprocessors. (In a previous blog, I proposed special SIMD instructions to improve the efficiency on accessing pages in traditional row-storage database engines.)

Below are TPC-H results at scale factor 100GB on HP ProLiant DL380 G7 with two Xeon 5680 six-core 3.33GHz processors for SQL Server 2008 R2 and Ingres VectorWise 1.5.

 PowerThroughputQphHSQL Server99,626.355,038.273,974.6VectorWise257,142.9246,101.7251,561.7

Overall, the VectorWise columnar database has about 2.5X higher score on Power, and 5X on Throughput. The glaring weak point of SQL Server is that the throughput is (almost 2X) lower than the power. The SQL Server parallel execution engine is very powerful running a single stream with no other concurrent queries competing for CPU. It does not have a good strategy for sharing compute resources with other concurrent long running queries.