4 ms·

The main benefit of a columnar representation in memory is it's more cache friendly for a typical analytical workload. For example, if I have a dataframe: (A

The main benefit of a columnar representation in memory is it's more cache friendly for a typical analytical workload. For example, if I have a dataframe: (A int, B int, C int, D int) And I write: A + B In a columnar representation, all the As are next to each other, and all the Bs are next to each other, so the process of (A and B in memory) => (A and B in CPU registers) => (addition) => (A + B result back to memory) will be a lot more efficient. In a row-oriented representation like protobuf, all your C and D values are going to get dragged into the CPU registers alongside the A and B values that you actually want. Column-oriented representation is also more friendly to SIMD CPU instructions. You can still use SIMD with a row-oriented representation, but you have to use gather-scatter operations which makes the whole thing less efficient.

jzer0cool 6y ago
Could you expand on this more columnar data and row data? I missed something here how the data is organized and what you mean by the C,D values getting dragged along.
- int_19h 6y ago
  Row data is an array of structs, with each struct representing a record. Columnar data is a struct of arrays, with each array representing a column.
- repsilat 6y ago
  To rephrase the sibling comment, if you had an array of four {ABCD} structs, there's basically two ways of storing them on disk: 1. AAAA BBBB CCCC DDDD 2. ABCD ABCD ABCD ABCD One major heuristic in how CPUs make your code fast is to assume that if you access some memory, you're probably interested in the memory nearby. So when you access the first "A" bit of memory (common to both sequences above), depending on the memory layout you use, the CPU might also be smart and load the next bits into memory too -- maybe the next "AA", maybe "BC". Depending on your workload, one or the other of those might be faster. If you're only interested in the first ABCD element because you're doing SELECT * FROM users WHERE id=$1 then you'll likely want "row-oriented" data -- the #2 scheme above. But if you're interested in all of the A values and none of the values from B/C/D because you're doing SELECT AVG(age) FROM users then you'll likely want something "column-oriented" -- the #1 scheme above.
gggtt 6y ago
It might be a stretch , but isn't it kind of the same idea as why ECS is faster than OO in video games development ?

jzer0cool 6y ago

Could you expand on this more columnar data and row data? I missed something here how the data is organized and what you mean by the C,D values getting dragged along.

int_19h 6y ago
Row data is an array of structs, with each struct representing a record. Columnar data is a struct of arrays, with each array representing a column.
repsilat 6y ago
To rephrase the sibling comment, if you had an array of four {ABCD} structs, there's basically two ways of storing them on disk: 1. AAAA BBBB CCCC DDDD 2. ABCD ABCD ABCD ABCD One major heuristic in how CPUs make your code fast is to assume that if you access some memory, you're probably interested in the memory nearby. So when you access the first "A" bit of memory (common to both sequences above), depending on the memory layout you use, the CPU might also be smart and load the next bits into memory too -- maybe the next "AA", maybe "BC". Depending on your workload, one or the other of those might be faster. If you're only interested in the first ABCD element because you're doing SELECT * FROM users WHERE id=$1 then you'll likely want "row-oriented" data -- the #2 scheme above. But if you're interested in all of the A values and none of the values from B/C/D because you're doing SELECT AVG(age) FROM users then you'll likely want something "column-oriented" -- the #1 scheme above.

gggtt 6y ago

It might be a stretch , but isn't it kind of the same idea as why ECS is faster than OO in video games development ?