3 ms·

Correct me if I'm wrong, but if you tilt your head to the left by 90 degrees and look at a database sideways, don't all the rows turn into columns? Edit: But

Correct me if I'm wrong, but if you tilt your head to the left by 90 degrees and look at a database sideways, don't all the rows turn into columns? Edit: But seriously, does anyone know if this column-oriented stuff is really revolutionary, and if the article is correct in praising the technology?

mattculbreth 19y ago
For datawarehouse applications you're (obviously) doing many more reads than writes. The number of columns you return for any given ad hoc query is typically low. Take something like "give me the names and salaries of employees who missed more than 3 days last quarter and who also work in California". Since a single datamart might have 200 columns spread across dimension and fact tables, and since this query is only touching 5-6 of them, it pays to make it easy to get to individual columns, instead of reading entire rows. In a row-store database you'd have to read the main fact table rows and the Employee and Office dimension rows, and that might add up to 50 columns. If you can only touch the exact columns you need you're better off. All that said, I've never done it. I really need to get an open source column store and whack at it for a bit.
- Tichy 19y ago
  So "column based db" means just to denormalize the database schema?
  - brlewis 19y ago
    No, nothing about the schema changes. What changes is the physical storage of the data. Instead of a disk read picking up all the columns including unused ones, a given disk read gets many values for a single column.
ajju 19y ago
For applications that do not have frequent writes (or updates), the performance of column oriented databases is significantly better. Anecdotally, we recently started using Sybase IQ for an application of this type with a DB of the order of hundreds of Gigabytes and it is an order of magnitude faster than MySQL for the same data. Having said that, I wouldn't say Sybase IQ is as stable as MySQL and we have run into at least a couple of issues, for which patches aren't yet available, that cause intermittent crashes. http://en.wikipedia.org/wiki/Sybase_IQ http://en.wikipedia.org/wiki/Sybase_IQ has more details on Sybase IQ.

mattculbreth 19y ago

For datawarehouse applications you're (obviously) doing many more reads than writes. The number of columns you return for any given ad hoc query is typically low. Take something like "give me the names and salaries of employees who missed more than 3 days last quarter and who also work in California". Since a single datamart might have 200 columns spread across dimension and fact tables, and since this query is only touching 5-6 of them, it pays to make it easy to get to individual columns, instead of reading entire rows. In a row-store database you'd have to read the main fact table rows and the Employee and Office dimension rows, and that might add up to 50 columns. If you can only touch the exact columns you need you're better off. All that said, I've never done it. I really need to get an open source column store and whack at it for a bit.

Tichy 19y ago
So "column based db" means just to denormalize the database schema?
- brlewis 19y ago
  No, nothing about the schema changes. What changes is the physical storage of the data. Instead of a disk read picking up all the columns including unused ones, a given disk read gets many values for a single column.

ajju 19y ago

For applications that do not have frequent writes (or updates), the performance of column oriented databases is significantly better. Anecdotally, we recently started using Sybase IQ for an application of this type with a DB of the order of hundreds of Gigabytes and it is an order of magnitude faster than MySQL for the same data. Having said that, I wouldn't say Sybase IQ is as stable as MySQL and we have run into at least a couple of issues, for which patches aren't yet available, that cause intermittent crashes. http://en.wikipedia.org/wiki/Sybase_IQ http://en.wikipedia.org/wiki/Sybase_IQ has more details on Sybase IQ.