13 ms·

There is a paper on a same topic: Column-Stores vs. Row-Stores: How Different Are They Really? [http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf http

There is a paper on a same topic: Column-Stores vs. Row-Stores: How Different Are They Really? [http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf] This is a very interesting paper where the researchers have compared performance of Column Store databases with plain Row Store databases as well as Row Store databases emulating column stores by using vertical partitioning and other techniques. There is also A Comparison of C-Store and Row-Store in a Common Framework [http://pages.cs.wisc.edu/~alanh/tr.pdf http://pages.cs.wisc.edu/~alanh/tr.pdf] where researchers demonstrate that the gain that Column Store databases give can be easily achieve in row stores by but by other techniques.

siganakis 15y ago
Thanks for that, I'll check them out. From my work there are 2 key advantages that using a column oriented approach bring: 1. There is far less storage required to represent data in a column oriented database due to the ability to compress like data. This storage benefit is really important when you get to tables, as it means you can hold more of your data set in memory at any one time. 2. For tables that have a lot of columns, but queries that only touch a few at a time, it means less work for the query to do, since there is no jumping from row pointed to column pointer. Cheers
- ww520 15y ago
  For case 2, a good index in a normal RDBMS also gives the same benefit. If your query only touched the columns in the index, the query would be blasting fast. The full rows are not used at all. One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query.
  - siganakis 15y ago
    Yes but each additional index adds to the storage requirement of the data. With column oriented designs each column is often indexed with bitmap indexes which are very small (often the index + raw data is smaller than the raw data stored in an array thanks to compression). Index intersection is also much faster with bitmap indexes rather then b-trees.
  - matwood 15y ago
    One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query. MSSQL also allows you to create an index and include other columns with the index data while leaving them out of the index itself. I don't have time to check if this is standard SQL or an MSSQL extension. http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx
ww520 15y ago
From what I remember the advantage of Column-Oriented DB is its data locality - the data of a column are stored together so that more of them can be loaded into memory in one shot, less disk seeks. They are good when doing aggregation (sum,count,groupby) on a few columns out of a table with many columns since the rest of the columns are not touched at all. When the full rows have to be retrieved, there's no advantage or may be even slower than row-based db.
- AlisdairO 15y ago
  They also tend to have more effective compression. C-store (vertica) is also interesting because it takes advantage of compressed data inside the query engine to make queries faster, rather than taking the more traditional step of uncompressing the information and then processing it. One of the main disadvantages (particularly on more naive implementations) is that incremental inserts can be slow, due to the number of disk seeks required to insert a row. I do recommend the c-store paper, it's very well written: http://people.csail.mit.edu/tdanford/6830papers/stonebraker-cstore.pdf http://people.csail.mit.edu/tdanford/6830papers/stonebraker-...
- jondot 15y ago
  It can also be on the same _machine_, when you look at distributed data stores. One of the pains of "scanning" vertically is that you have to go through all of the machines in your cluster, where each takes a shard of your data. Cassandra's implementation provides many insights into this question. Not only data locality from the entity's POV, but also from the machine's POV. Cassandra also stores the data on disk in a forward-only data structure (to avoid disk seeks), which is periodically compacted (deletes, updates) and employs bloom filters to ease up on cache/disk hits and misses. I guess one more advantage of a practical column store like Cassandra is that you are being forced to shape your data to your queries (no relational calculus/algebra as in RDBMS) which requires much more thought.
  - sixbrx 15y ago
    > forced to shape your data to your queries This doesn't seem like an advantage to me at all. I'd like the system to be able to evolve regarding the sorts of queries that it answers to, and relational systems seem to be very good in this regard.
coolgeek 15y ago
Note that the first paper you cited is at least partially in response to the second paper you cited. "This simplistic view leads to the assumption that one can obtain the performance benefits of a column-store using a row-store: either by vertically partitioning the schema, or by indexing every column so that columns can be accessed independently. In this paper, we demonstrate that this assumption is false." ... "We conclude that while it is not impossible for a row-store to achieve some of the performance advantages of a column-store, changes must be made to both the storage layer and the query executor to fully obtain the benefits of a column-oriented approach."
- mayanksinghal 15y ago
  Yes, there are some obvious benefits of column stores and simple vertical partitioning cannot serve the purpose. Compression and cache locality would still require a lot of changes to be made on lower layers. I think there is ample scope for a DB Engine that allows some tables to be column stores when required. It is also noted in one of the papers (I forgot which one) that Column stores work better for read intensive use cases, probably because disk seeks would be less (appends/changes to one one section of a table and not 5 sections)

siganakis 15y ago

Thanks for that, I'll check them out. From my work there are 2 key advantages that using a column oriented approach bring: 1. There is far less storage required to represent data in a column oriented database due to the ability to compress like data. This storage benefit is really important when you get to tables, as it means you can hold more of your data set in memory at any one time. 2. For tables that have a lot of columns, but queries that only touch a few at a time, it means less work for the query to do, since there is no jumping from row pointed to column pointer. Cheers

ww520 15y ago
For case 2, a good index in a normal RDBMS also gives the same benefit. If your query only touched the columns in the index, the query would be blasting fast. The full rows are not used at all. One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query.
- siganakis 15y ago
  Yes but each additional index adds to the storage requirement of the data. With column oriented designs each column is often indexed with bitmap indexes which are very small (often the index + raw data is smaller than the raw data stored in an array thanks to compression). Index intersection is also much faster with bitmap indexes rather then b-trees.
- matwood 15y ago
  One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query. MSSQL also allows you to create an index and include other columns with the index data while leaving them out of the index itself. I don't have time to check if this is standard SQL or an MSSQL extension. http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx

ww520 15y ago

From what I remember the advantage of Column-Oriented DB is its data locality - the data of a column are stored together so that more of them can be loaded into memory in one shot, less disk seeks. They are good when doing aggregation (sum,count,groupby) on a few columns out of a table with many columns since the rest of the columns are not touched at all. When the full rows have to be retrieved, there's no advantage or may be even slower than row-based db.

AlisdairO 15y ago
They also tend to have more effective compression. C-store (vertica) is also interesting because it takes advantage of compressed data inside the query engine to make queries faster, rather than taking the more traditional step of uncompressing the information and then processing it. One of the main disadvantages (particularly on more naive implementations) is that incremental inserts can be slow, due to the number of disk seeks required to insert a row. I do recommend the c-store paper, it's very well written: http://people.csail.mit.edu/tdanford/6830papers/stonebraker-cstore.pdf http://people.csail.mit.edu/tdanford/6830papers/stonebraker-...
jondot 15y ago
It can also be on the same _machine_, when you look at distributed data stores. One of the pains of "scanning" vertically is that you have to go through all of the machines in your cluster, where each takes a shard of your data. Cassandra's implementation provides many insights into this question. Not only data locality from the entity's POV, but also from the machine's POV. Cassandra also stores the data on disk in a forward-only data structure (to avoid disk seeks), which is periodically compacted (deletes, updates) and employs bloom filters to ease up on cache/disk hits and misses. I guess one more advantage of a practical column store like Cassandra is that you are being forced to shape your data to your queries (no relational calculus/algebra as in RDBMS) which requires much more thought.
- sixbrx 15y ago
  > forced to shape your data to your queries This doesn't seem like an advantage to me at all. I'd like the system to be able to evolve regarding the sorts of queries that it answers to, and relational systems seem to be very good in this regard.

coolgeek 15y ago

Note that the first paper you cited is at least partially in response to the second paper you cited. "This simplistic view leads to the assumption that one can obtain the performance benefits of a column-store using a row-store: either by vertically partitioning the schema, or by indexing every column so that columns can be accessed independently. In this paper, we demonstrate that this assumption is false." ... "We conclude that while it is not impossible for a row-store to achieve some of the performance advantages of a column-store, changes must be made to both the storage layer and the query executor to fully obtain the benefits of a column-oriented approach."

mayanksinghal 15y ago
Yes, there are some obvious benefits of column stores and simple vertical partitioning cannot serve the purpose. Compression and cache locality would still require a lot of changes to be made on lower layers. I think there is ample scope for a DB Engine that allows some tables to be column stores when required. It is also noted in one of the papers (I forgot which one) that Column stores work better for read intensive use cases, probably because disk seeks would be less (appends/changes to one one section of a table and not 5 sections)