4 ms·

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

by 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.
- brazzy 15y ago
  Requiring much more thought to get the same result is not an advantage.
- MichaelSalib 15y ago
  you are being forced to shape your data to your queries (no relational calculus/algebra as in RDBMS) I think this is wrong twice over. First, there are commercial column store RDBMS systems already, starting with Vertica. And secondly, in any large RDBMS, you have to shape your data to your queries and your queries to your data. There's a reason that people with very large databases want very skilled DBAs and DB developers. The notion that row stores allow you to build enormous data warehouses without giving any thought to how you layout data on disk, what sort of indexing you use, how to normalize, etc is just absurd.

by ww520 15y ago

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.
brazzy 15y ago
Requiring much more thought to get the same result is not an advantage.
MichaelSalib 15y ago
you are being forced to shape your data to your queries (no relational calculus/algebra as in RDBMS) I think this is wrong twice over. First, there are commercial column store RDBMS systems already, starting with Vertica. And secondly, in any large RDBMS, you have to shape your data to your queries and your queries to your data. There's a reason that people with very large databases want very skilled DBAs and DB developers. The notion that row stores allow you to build enormous data warehouses without giving any thought to how you layout data on disk, what sort of indexing you use, how to normalize, etc is just absurd.