4 ms·

Column store is really just making sure values for a column are continuous in memory, instead of rows as it is done usually. Basic premise is that modern CPU's

Column store is really just making sure values for a column are continuous in memory, instead of rows as it is done usually. Basic premise is that modern CPU's can chew through continuous memory locations at light speed, with single core being capable of scanning a gigabyte of RAM in few milliseconds[0]. This is extremely beneficial when your tables have large number of columns, but you only want to do computations on a few of them. Columnar stores usually implemented using dictionaries which allow for efficient duplicate handling and blazing fast lookups. Often those dictionaries also contain run length optimizations, so values 1 through 100 are only stored using two values start-range 1 and end-range 100. All in all, column stores are efficient at analytic workload, but struggle when a lot of inserts and updates need to happen. [0] Using prefetch, properly aligned data, and likely other optimizations I am not thinking of.

seiji 12y ago
but struggle when a lot of inserts and updates need to happen. I ran across this with Redis recently. Redis has an internal data structure called ziplist that's a doubly linked list with no pointers. It just uses byte offsets to the next element, so all elements are in one contiguous block of memory. It's great because you can store a lot of values compactly without needing 1 to 5 pointers (8 to 40 bytes) overhead per element in your data structure. But, a problem shows up when you want to insert or delete items. Every insert or delete requires expanding or shrinking the entire solid-chunk-of-memory allocation, which isn't the fastest thing in the world when your allocation is large. Also, inserting into the HEAD requires copying the _entire_ ziplist up exactly one element position because the block of your memory layout just changed. So, obviously these things are useful, but their usefulness degrades as your solid blocks of memory hit cache limits. Everything is super fast when your entire ziplist fits in L1, still about the same in L2, worse in L3, and horrible if your ziplist grows any larger. But, what can we do? We can fix the entire problem by creating a traditional linked lists of solid memory block ziplists. Now, each ziplist can remain small (8 KB to 16 KB), and when we grow bigger, we just cut a new ziplist, pointer-attach it to the previous ziplist, and now we have a minimal-pointer, high-locality data structure with unlimited growth potential without performance defects. It doesn't buckle under inserting or updating arbitrary elements since each memory block is isolated to a maximum ~8 KB size (reminder: your L1 cache is 32 KB to 64 KB depending on architecture) and now your memory usage due to pointer overhead is now also _greatly_ reduced (assuming your data was small and dominated or matched by pointer overhead size in the first place). As a double bonus, now when your data structure is a solid block of memory, you can compress individual contiguous memory blocks (usually with great compression ratios) because your contents will tend to be homogeneous in shape inside the same container. Result: huge reduction in pointer overhead + sequential access + compression = best of all possible worlds. Other details/comparisons at https://matt.sh/redis-quicklist-visions https://matt.sh/redis-quicklist-visions
- hyc_symas 12y ago
  This isn't news. Arrays are fast until you need to insert/delete in them, then they get horrible. This is why we have B-trees. Every page in a B-tree is essentially a sorted array - you can find items in it quickly using binary search, and you can insert/delete in it relatively quickly because it has a small bounded size. When you need to grow beyond a single page you do a page-split and add a parent above it, etc. B-trees are the optimal implementation of dynamic arrays, end of story.
  - seiji 12y ago
    This reply is golden. It starts off completely dismissive with "this isn't news" and it ends completely dismissive with "end of story." I am quite sorry my free internet post failed to meet your quality guidelines. A wise man once said: Things have now gotten to the stage where I flinch slightly as I click on the "comments" link, bracing myself for the dismissive comment I know will be waiting for me at the top of the page.
    - tough-crowd 12y ago
      For what it is worth I enjoyed your parent post.
    - hyc_symas 12y ago
      If you honestly believe your linked list of blocks is actually superior in efficiency to a B-tree, you're delusional.
  - crucini 12y ago
    >B-trees are the optimal implementation of dynamic arrays Then why don't the popular scripting languages use B-trees for their sequence containers? Both Perl and Python use arrays for this. Both pay the price of O(n) inserts mid-container.
    - TJSomething 12y ago
      I would be unsurprised if it was for legacy reasons. Both of those programming languages were written over twenty years ago. Furthermore, arrays play nicely with C extensions.

seiji 12y ago

but struggle when a lot of inserts and updates need to happen. I ran across this with Redis recently. Redis has an internal data structure called ziplist that's a doubly linked list with no pointers. It just uses byte offsets to the next element, so all elements are in one contiguous block of memory. It's great because you can store a lot of values compactly without needing 1 to 5 pointers (8 to 40 bytes) overhead per element in your data structure. But, a problem shows up when you want to insert or delete items. Every insert or delete requires expanding or shrinking the entire solid-chunk-of-memory allocation, which isn't the fastest thing in the world when your allocation is large. Also, inserting into the HEAD requires copying the _entire_ ziplist up exactly one element position because the block of your memory layout just changed. So, obviously these things are useful, but their usefulness degrades as your solid blocks of memory hit cache limits. Everything is super fast when your entire ziplist fits in L1, still about the same in L2, worse in L3, and horrible if your ziplist grows any larger. But, what can we do? We can fix the entire problem by creating a traditional linked lists of solid memory block ziplists. Now, each ziplist can remain small (8 KB to 16 KB), and when we grow bigger, we just cut a new ziplist, pointer-attach it to the previous ziplist, and now we have a minimal-pointer, high-locality data structure with unlimited growth potential without performance defects. It doesn't buckle under inserting or updating arbitrary elements since each memory block is isolated to a maximum ~8 KB size (reminder: your L1 cache is 32 KB to 64 KB depending on architecture) and now your memory usage due to pointer overhead is now also _greatly_ reduced (assuming your data was small and dominated or matched by pointer overhead size in the first place). As a double bonus, now when your data structure is a solid block of memory, you can compress individual contiguous memory blocks (usually with great compression ratios) because your contents will tend to be homogeneous in shape inside the same container. Result: huge reduction in pointer overhead + sequential access + compression = best of all possible worlds. Other details/comparisons at https://matt.sh/redis-quicklist-visions https://matt.sh/redis-quicklist-visions

hyc_symas 12y ago
This isn't news. Arrays are fast until you need to insert/delete in them, then they get horrible. This is why we have B-trees. Every page in a B-tree is essentially a sorted array - you can find items in it quickly using binary search, and you can insert/delete in it relatively quickly because it has a small bounded size. When you need to grow beyond a single page you do a page-split and add a parent above it, etc. B-trees are the optimal implementation of dynamic arrays, end of story.
- seiji 12y ago
  This reply is golden. It starts off completely dismissive with "this isn't news" and it ends completely dismissive with "end of story." I am quite sorry my free internet post failed to meet your quality guidelines. A wise man once said: Things have now gotten to the stage where I flinch slightly as I click on the "comments" link, bracing myself for the dismissive comment I know will be waiting for me at the top of the page.
  - tough-crowd 12y ago
    For what it is worth I enjoyed your parent post.
  - hyc_symas 12y ago
    If you honestly believe your linked list of blocks is actually superior in efficiency to a B-tree, you're delusional.
- crucini 12y ago
  >B-trees are the optimal implementation of dynamic arrays Then why don't the popular scripting languages use B-trees for their sequence containers? Both Perl and Python use arrays for this. Both pay the price of O(n) inserts mid-container.
  - TJSomething 12y ago
    I would be unsurprised if it was for legacy reasons. Both of those programming languages were written over twenty years ago. Furthermore, arrays play nicely with C extensions.