Columnar Databases

I am evaluating Sybase IQ right now, Sybase IQ is columnar based and also claims 50X improvement

I will let you know the results after I test it out against a billion rows

Wow, interesting idea Paul. I worked with a columnar DB (we termed it columnbase but whatever) called Broadbase back in 2000-2004. Broadbase were a silicon valley startup from Menlo Park that thought they were onto something but eventually got swallowed up into Kana (http://www.kana.com/). I'm not sure where it is now - probably nowhere.

-Jamie

Paul,

would you mind adding this to the connect site as a SQL Server feature we want (you will get my vote)

If not I can add it, since it was your idea I did not feel like taking credit for it

Denis

I haven't had a chance to read up much on columnar dbs but can someone explain what the difference is between columnar storage & regular indexes? Indexes store any column or combination of columns together on storage so I'm wondering where the advantage in columnar storage is?

I think Paul's note is dead on.  In the column-based database I assume they keep some kind of header record so they know, e.g., "column 1's values start at sector 0001 and end at sector 0010", "column 2's values start at sector 0011 and end at sector 0100", etc.  So if you ask for column 2, it can do a single scan of exactly the right sectors from the disk, and there you are.

In a row-based DB, if you have more than one column in a given index, you need to read all of the data from every column in the index, then processes it to pick out only the data you actually need.

I guess it all comes down to what you're trying to do, and especially how often you actually need a large subset of the rows.  I assume a more sophisticated column-based setup would keep a row ID with each column value, so if you you had Name, Age, and City it would look like:

1.John 2.Steve 3.Bob, 1.34 2.20 3.53, 1.Denver 2.Boston 3.Melbourne

Then the data could be pre-sorted (not done here) to take advantage of range-based scans.  I can see how that would be quite advantageous if pulling down large chunks of data for OLAP queries... It also might help enable range scans for queries on composite indexes, something that SQL Server is certainly not good at doing today... Maybe a using a multidimensional tree to build the row-based indexes would better satisfy that requirement, though.

Having columnar based storage/engine(addition) with compression and in memory database/table options to the sql server relational engine would be a great addition to SQL Server as to further extend its feature leadership in the BI area. Then just as with OLAP storgare you could combine data stored in relational row storage, OLAP storage and column based storage. A unified interface over that would be a great advatage opposed to the niche players supplying only the column based engine. With an unified approache you could choose what storage you would give data in your dataware house deppending to type of queries being run to it, frequency of the data being queried and update-frequency.

Another factor is that a column is always one data type.  You then design compression algorithms for them and work with the compressed format instead of the original data.

To follow up on Jamie's comment above, the Broadbase server actually lives on in the open-source LucidDB project.

Very interesting.  One question....how would ETL jobs perform on such a database?  Since we are writing entire rows, wouldn't the ETL be incredibly slow?

Thanks,

SB

Another question:  what about building an index that has this columnar structure over a standard row-based table, instead of converting the table to column-based storage?  Is this possible?  Would this be a better idea?

Thanks,

SB

Any given DBMS could support both row and column based physical storage. It would be up to the DBA to decide which physical organisation is appropriate.

Data inversion was another technique for achieving a similar effect.

The existing logical access methods would apply.

We do not need new products just improvements to existing ones.

@Phil Runciman; I like your ideas. Have you ever read about the customized PostgreSQL from Yahoo? Yahoo took PostgreSQL made some considerable changes and uses this as a columnar db now (http://www.computerworld.com/s/article/9087918/Size_matters_Yahoo_claims_2_petabyte_database_is_world_s_biggest_busiest?taxonomyId=53&intsrc=kc_feat&taxonomyName=databases). It would be a matter of Yahoo making their code public adding some development to port the changes into the core of the Postgresql development and we would be 3 steps closer to your vision.

PingBack from http://datatechnology.wordpress.com/2009/09/18/columnar-databases-a-quick-start/

I currently work with Vertica, a columnar database. It is indeed blazingly fast.

This type of database isn't for every job, but for what it does it does really well. A hybrid option would be very interesting. But that would almost require a new product.

Hello Paul,

This seems to be a good working idea. My query is, How do we query this type of columnr database from Mysql. What differenrt settings would be requierd in the normal select query

Είναι πλέον διαθέσιμη η νέα έκδοση για τον SQL Server με την κωδική ονομασία Denali. Πρόκειται για την

This is an interesting article.  I would disagree that columnar databases are slow at reading all columns of a particular row.  Columnar databases allow, among other things, each value to be stored in a columnar index once, so initial searches on the where clause are considerably faster.  They also waste no space on null fields, so compression is a considerable attraction, especially when you'd prefer to have a significant portion of the base in memory.  The performance differential for OLTP is significant because it takes time to index a row into several columnar indices.  In general, columns are optimized for reads, tables are optimized for writes.

@Sam Bendayan

Great comments and questions.  Mature columnar implementations include bulkloaders which enable very quick writes.  Single transaction writes are relatively expensive, however some columnar DBMS employ a temporary row store which is visible to the SQL interface and is periodically inserted into the columnar store via a backend process.  I think you'll find that, if you play with some columnar engines, the write performance is decent.  I suspect that the general direction will be OLTP on traditional normalized table-based implementations, agile marts and warehouses increasingly on columnar databases with a ton of cache.  I can get more data into cache with a columnar store than I can in a row store, so retrieval of high value data from my mart is nigh instantaneous.