4 ms·

Can somebody explain in the most basic way possible what column oriented databases can do for me and some example use cases?

ako 3y ago
Instead of storing your data grouped by record, column databases store data grouped by column. This means that if your query is only interested in one or a few columns but from a large number of records , e.g. to aggregate it to calculate an average, it needs to read less database blocks from disk, so lower io costs. Same for in memory or cached, the data your interested in is easier accessible for lower performance cost. This is especially useful for reporting/analytical purposes where you want to aggregate over data from many records.
- cpursley 3y ago
  Thanks. So if I understand correctly, this is basically like ignoring all the other columns (that you're not interested in)? And do you run them in conjunction with your existing Postgres, install it as a plugin, or switch your entire db to something like Hydra?
  - ako 3y ago
    Usually you choose the type of database based on your use case. Reporting databases like data warehouses, benefit from using a column oriented database, whereas transactional use cases use a row oriented database. Modern databases allow you to choose per table.
  - beagle3 3y ago
    Yes, the downside being that if you need all the columns of one specific record (row), it will be spread over many column blocks. As a rule of the thumb, if most of your queries are aggregations over few columns of many records (OLAP), column based would be a better choice - whereas if you need to access every column/field of a small number of records (OLTP), record/row based would be a better choice.
  - adammarples 3y ago
    And in addition, each column, or even partition of a column, can store statistics about itself like min max avg count etc. That way if you look up max(column) you only have to read the meta data on the partitions on that column, it's very fast. And for analytics this is pretty common.
- onlyrealcuzzo 3y ago
  Not an expert on the traditional database. Do they also have a benefit similar to the compression that comes from record re-ordering in column databases? It's not just that you need to read a smaller section of disk to scan the same amount of interesting data - it's that that data also takes up considerably less space than it otherwise would.
  - coatue 3y ago
    Hi, hydra cofounder here. catching up on the thread since we were submitted by the community! Yes, it depends on the data, but we've observed customers with a 10X data compression over Postgres (heap) tables although ~5-7X is more common.
koolba 3y ago
When data is stored in columns, you can add it up very fast. The traditional use case is analytical reporting.
- cpursley 3y ago
  I'm not sure I even understand what "data stored in columns" actually means. Can you explain it to me like I'm 6?
  - samwillis 3y ago
    Each cell in the column is store one after another in memory, you have continuous memory of one column. Effectively think of having text file for each column, each cell having a line, to reconstruct a row you have to get its line from every column file. To sum a column you only have to scan the one file for that column. In row oriented, each cell for each row is next to each other in memory. Think traditional CSV files, each row is a line in the file. To sum a column you have scan the whole file and all data for all columns.
  - koolba 3y ago
    Assuming you knew C and CPU arch terms like SIMD at age 6, imagine an array of complex structs compared to an int[]. The latter can be read directly from disk and aggregated (ie summed) very fast via SIMD instructions. Whereas a dynamically sized structure for rows (like how traditional databases store them) requires a lot of branch and dereference logic.
  - jiggawatts 3y ago
    Database tables are stored as an "array of structures". If a row has someone's ID, name, and age, then on disk it'll be laid out like so: [#001][John Smith][25];[#002][Jane Doe][32];[#003][Kane Citizen][62];[... Columnar databases store the data as a "structure of arrays", keeping each type of value together: [#001][#002][#003][...;[John Smith][Jane Doe][Kane Citizen][...;[25][32][62][... With row-based storage, the database can look up everything it needs to show a form to the user with a single disk I/O operation: just read a block of data starting at the location where the record row is. With column-based storage, the database has to do an I/O per column, which seems worse... but these days on SSD storage the overhead is negligible when displaying one form at a time. Where columnar wins massively is reporting workloads. If you want to know the average age of employees, then with row storage you pretty much have to read everything in, including their names and ID numbers, even though you don't need that data. This is because all disk storage comes as "block devices" that read a minimum of 512 bytes, but typically more like 64KB at a time. You get more than you want, and then you have to throw it away. With column storage, you can read just the age data to compute the average age. With column storage there are also compression techniques that work a lot better than with row storage. Run-length encoding, lookup tables, etc... provide higher compression ratios because the data is much more uniform for much larger chunks. The typical end-result is 100x less I/O to run the same query.
quadrature 3y ago
In row oriented DBs data is laid out row by row. In order to read a column of data the DB has to read through the entire row to get to the next one. When computing an analytical query like the sum of a single column, the DB wastes cycles skipping through all the columns in the row. In columnar databases a batch of column data is stored sequentially followed by the next column and so on. So if you need to sum a column you can read it sequentially very quickly and sum it up without needing to touch the data of any other column. Columnar is also better at filtering and grouping for similar reasons. As a general rule if your workload looks like “select sum(x) where y group by X” a columnar db will be a great fit. The trade off is that columnar databases don’t like being updated and work best when you only insert data. This is because when you update existing data you now need to rewrite several pages on disk because the columns are all over the place. Because of this there’s very few areas where a pure columnar DB makes sense. They’re great for timeseries metrics like sensor data , click event logs etc. places where you are just recording a fact and don’t need to update past events.
- cpursley 3y ago
  Thanks. So they are specifically for aggregate queries?
  - quadrature 3y ago
    Pretty much. Analytical aggregates are where they shine.
brightball 3y ago
My understanding has always been: 1. No indexes or primary keys 2. Insert and delete only, no updates 3. Automatically normalized behind the scenes leading to data compression in the 10x-100x range 4. Very fast analytical queries BigQuery, RedShift, etc are columnar databases (for example).
- jiggawatts 3y ago
  Microsoft SQL Server ColumnStore and SAP HANA both support indexes, point updates, etc... You can convert a table in SQL Server to "Clustered ColumnStore" storage, it'll become compressed, but everything will mostly work the same. The implementation in SQL Server uses a row "delta store" to keep recent changes. Once a certain threshold is reached, the delta store is converted to columnar format and merged with the underlying table.
smallerfish 3y ago
Say you have a billion records in your table. If you want to filter the records on a column which has values that are on average 30 bytes, then an index on that column would be 30GB (uncompressed). It becomes extremely impractical to hold those indices in memory if you have several / dozens / hundreds. The problem with row based database structures is that the data between any two rows can vary in length, so if scanning across rows on disk for a column's value, you need metadata that points at the start of each column. That means the process of scanning across the data on disk involves a _lot_ of jumping around; less of a problem on SSDs than it was on spinning platters, but still work. The alternate approach is to store columns together on disk. That means that when performing a query that filters based on values in a given column, you can scan sequential areas on disk, which is much much faster. Of course, if you now want to assemble rows based on your filter, you need to use the metadata about where related values are in each other column in order to assemble those rows, but hopefully you have a much smaller set of records that you need to do this over. (It's cheaper to assemble 10k matching rows across a set of columns than it is to jump across a billion unaligned columns to filter them in the first place.)
- ollien 3y ago
  One thing I've seen about other columnar databases (e.g. Clickhouse) is that they're bad at k/v style lookups. Why is that? Your explanation would make me think otherwise, as we would be able to quickly scan the columns using our sequential reads, ideally.
  - smallerfish 3y ago
    1) If they're only using on disk indices, it'll be slower to look up any one record than it would be if the index was in memory (particularly true if a good chunk of the data was in memory also) 2) The operation of retrieving a full row of data is more expensive in a column based db than in a row based db, because in row based the data for the row is contiguous on disk. So row based is likely optimal if your data size works with that architecture. (And, to be clear, row based _can_ work with billions of rows, if you're thoughtful about what kind of querying you'll be doing against such tables, and how you maintain them.)
dboreham 3y ago
> most basic way possible It's like a database engine that uses grep under the hood. Data is in flat files. Queries are executed by scanning those files (as quickly as possible). For aggregate queries (which is what these things are used for), substitute awk for grep.
didip 3y ago
The keyword you want to look for is: SIMD (Single Instruction Multiple Data) optimization at hardware level. This is how many aggregate functions can run real fast. To take advantage of that, data has to be structured a certain way: In a columnar way (which is then be vectorized and pushed to SIMD).

ako 3y ago

Instead of storing your data grouped by record, column databases store data grouped by column. This means that if your query is only interested in one or a few columns but from a large number of records , e.g. to aggregate it to calculate an average, it needs to read less database blocks from disk, so lower io costs. Same for in memory or cached, the data your interested in is easier accessible for lower performance cost. This is especially useful for reporting/analytical purposes where you want to aggregate over data from many records.

cpursley 3y ago
Thanks. So if I understand correctly, this is basically like ignoring all the other columns (that you're not interested in)? And do you run them in conjunction with your existing Postgres, install it as a plugin, or switch your entire db to something like Hydra?
- ako 3y ago
  Usually you choose the type of database based on your use case. Reporting databases like data warehouses, benefit from using a column oriented database, whereas transactional use cases use a row oriented database. Modern databases allow you to choose per table.
- beagle3 3y ago
  Yes, the downside being that if you need all the columns of one specific record (row), it will be spread over many column blocks. As a rule of the thumb, if most of your queries are aggregations over few columns of many records (OLAP), column based would be a better choice - whereas if you need to access every column/field of a small number of records (OLTP), record/row based would be a better choice.
- adammarples 3y ago
  And in addition, each column, or even partition of a column, can store statistics about itself like min max avg count etc. That way if you look up max(column) you only have to read the meta data on the partitions on that column, it's very fast. And for analytics this is pretty common.
onlyrealcuzzo 3y ago
Not an expert on the traditional database. Do they also have a benefit similar to the compression that comes from record re-ordering in column databases? It's not just that you need to read a smaller section of disk to scan the same amount of interesting data - it's that that data also takes up considerably less space than it otherwise would.
- coatue 3y ago
  Hi, hydra cofounder here. catching up on the thread since we were submitted by the community! Yes, it depends on the data, but we've observed customers with a 10X data compression over Postgres (heap) tables although ~5-7X is more common.

koolba 3y ago

When data is stored in columns, you can add it up very fast. The traditional use case is analytical reporting.

cpursley 3y ago
I'm not sure I even understand what "data stored in columns" actually means. Can you explain it to me like I'm 6?
- samwillis 3y ago
  Each cell in the column is store one after another in memory, you have continuous memory of one column. Effectively think of having text file for each column, each cell having a line, to reconstruct a row you have to get its line from every column file. To sum a column you only have to scan the one file for that column. In row oriented, each cell for each row is next to each other in memory. Think traditional CSV files, each row is a line in the file. To sum a column you have scan the whole file and all data for all columns.
- koolba 3y ago
  Assuming you knew C and CPU arch terms like SIMD at age 6, imagine an array of complex structs compared to an int[]. The latter can be read directly from disk and aggregated (ie summed) very fast via SIMD instructions. Whereas a dynamically sized structure for rows (like how traditional databases store them) requires a lot of branch and dereference logic.
- jiggawatts 3y ago
  Database tables are stored as an "array of structures". If a row has someone's ID, name, and age, then on disk it'll be laid out like so: [#001][John Smith][25];[#002][Jane Doe][32];[#003][Kane Citizen][62];[... Columnar databases store the data as a "structure of arrays", keeping each type of value together: [#001][#002][#003][...;[John Smith][Jane Doe][Kane Citizen][...;[25][32][62][... With row-based storage, the database can look up everything it needs to show a form to the user with a single disk I/O operation: just read a block of data starting at the location where the record row is. With column-based storage, the database has to do an I/O per column, which seems worse... but these days on SSD storage the overhead is negligible when displaying one form at a time. Where columnar wins massively is reporting workloads. If you want to know the average age of employees, then with row storage you pretty much have to read everything in, including their names and ID numbers, even though you don't need that data. This is because all disk storage comes as "block devices" that read a minimum of 512 bytes, but typically more like 64KB at a time. You get more than you want, and then you have to throw it away. With column storage, you can read just the age data to compute the average age. With column storage there are also compression techniques that work a lot better than with row storage. Run-length encoding, lookup tables, etc... provide higher compression ratios because the data is much more uniform for much larger chunks. The typical end-result is 100x less I/O to run the same query.

quadrature 3y ago

In row oriented DBs data is laid out row by row. In order to read a column of data the DB has to read through the entire row to get to the next one. When computing an analytical query like the sum of a single column, the DB wastes cycles skipping through all the columns in the row. In columnar databases a batch of column data is stored sequentially followed by the next column and so on. So if you need to sum a column you can read it sequentially very quickly and sum it up without needing to touch the data of any other column. Columnar is also better at filtering and grouping for similar reasons. As a general rule if your workload looks like “select sum(x) where y group by X” a columnar db will be a great fit. The trade off is that columnar databases don’t like being updated and work best when you only insert data. This is because when you update existing data you now need to rewrite several pages on disk because the columns are all over the place. Because of this there’s very few areas where a pure columnar DB makes sense. They’re great for timeseries metrics like sensor data , click event logs etc. places where you are just recording a fact and don’t need to update past events.

cpursley 3y ago
Thanks. So they are specifically for aggregate queries?
- quadrature 3y ago
  Pretty much. Analytical aggregates are where they shine.

brightball 3y ago

My understanding has always been: 1. No indexes or primary keys 2. Insert and delete only, no updates 3. Automatically normalized behind the scenes leading to data compression in the 10x-100x range 4. Very fast analytical queries BigQuery, RedShift, etc are columnar databases (for example).

jiggawatts 3y ago
Microsoft SQL Server ColumnStore and SAP HANA both support indexes, point updates, etc... You can convert a table in SQL Server to "Clustered ColumnStore" storage, it'll become compressed, but everything will mostly work the same. The implementation in SQL Server uses a row "delta store" to keep recent changes. Once a certain threshold is reached, the delta store is converted to columnar format and merged with the underlying table.

smallerfish 3y ago

Say you have a billion records in your table. If you want to filter the records on a column which has values that are on average 30 bytes, then an index on that column would be 30GB (uncompressed). It becomes extremely impractical to hold those indices in memory if you have several / dozens / hundreds. The problem with row based database structures is that the data between any two rows can vary in length, so if scanning across rows on disk for a column's value, you need metadata that points at the start of each column. That means the process of scanning across the data on disk involves a _lot_ of jumping around; less of a problem on SSDs than it was on spinning platters, but still work. The alternate approach is to store columns together on disk. That means that when performing a query that filters based on values in a given column, you can scan sequential areas on disk, which is much much faster. Of course, if you now want to assemble rows based on your filter, you need to use the metadata about where related values are in each other column in order to assemble those rows, but hopefully you have a much smaller set of records that you need to do this over. (It's cheaper to assemble 10k matching rows across a set of columns than it is to jump across a billion unaligned columns to filter them in the first place.)

ollien 3y ago
One thing I've seen about other columnar databases (e.g. Clickhouse) is that they're bad at k/v style lookups. Why is that? Your explanation would make me think otherwise, as we would be able to quickly scan the columns using our sequential reads, ideally.
- smallerfish 3y ago
  1) If they're only using on disk indices, it'll be slower to look up any one record than it would be if the index was in memory (particularly true if a good chunk of the data was in memory also) 2) The operation of retrieving a full row of data is more expensive in a column based db than in a row based db, because in row based the data for the row is contiguous on disk. So row based is likely optimal if your data size works with that architecture. (And, to be clear, row based _can_ work with billions of rows, if you're thoughtful about what kind of querying you'll be doing against such tables, and how you maintain them.)

dboreham 3y ago

> most basic way possible It's like a database engine that uses grep under the hood. Data is in flat files. Queries are executed by scanning those files (as quickly as possible). For aggregate queries (which is what these things are used for), substitute awk for grep.

didip 3y ago

The keyword you want to look for is: SIMD (Single Instruction Multiple Data) optimization at hardware level. This is how many aggregate functions can run real fast. To take advantage of that, data has to be structured a certain way: In a columnar way (which is then be vectorized and pushed to SIMD).