4 ms·

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

by koolba 3y ago

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.

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

by 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.