{"id":16,"date":"2014-11-27T17:02:00","date_gmt":"2014-11-27T17:02:00","guid":{"rendered":""},"modified":"2024-09-13T14:25:59","modified_gmt":"2024-09-13T14:25:59","slug":"columnar-database","status":"publish","type":"post","link":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/","title":{"rendered":"Columnar Database"},"content":{"rendered":"<p>I recently read about an interesting database concept that uses a <a href=\"http:\/\/www.columnardatabase.org\/\" target=\"_blank\" rel=\"noopener\">columnar database<\/a>, which is where data is stored in columns instead of rows. <br \/>\nApparently this approach has several advantages over a row based database. A columnar database has better performance when a large number of queries are run at the same time. Joins and aggregation are faster as aggregate function results are incrementally calculated. Columnar databases can be compressed which eliminates the storage of multiple indexes, views and aggregation. Columnar databases also load data quickly. Each column is built in one pass and stored separately, so multiple threads can load different columns at the same time. Also, since columns are stored separately, data can be added and removed without updating the system after each change. <br \/>\nThere are disadvantages to a columnar database though. It doesn&#8217;t work for every case. Converting a row based database into a columnar database can be slow for large datasets. Incremental loads can be slow. Uncompressing data for reading can be slow. There are structural limitations, such as some columnar databases require the same primary key on all tables. These imitations may make it difficult to expand the system in the future. Columnar databases are also meant for large datasets and may not be suitable for small datasets. <\/p>\n<p><i>This post was brought to you by your friends at MemSQL.<\/i><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently read about an interesting database concept that uses a columnar database, which is where data is stored in columns instead of rows. Apparently this approach has several advantages over a row based database. A columnar database has better performance when a large number of queries are run at the same time. Joins and [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14,15],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-columnar-database","category-memsql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Columnar Database - Zofxare Blog Home<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Columnar Database - Zofxare Blog Home\" \/>\n<meta property=\"og:description\" content=\"I recently read about an interesting database concept that uses a columnar database, which is where data is stored in columns instead of rows. Apparently this approach has several advantages over a row based database. A columnar database has better performance when a large number of queries are run at the same time. Joins and [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Zofxare Blog Home\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-27T17:02:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-09-13T14:25:59+00:00\" \/>\n<meta name=\"author\" content=\"zofxare\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"zofxare\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/\"},\"author\":{\"name\":\"zofxare\",\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/#\\\/schema\\\/person\\\/094f5dde70100b9f8fbc63174a2a40ce\"},\"headline\":\"Columnar Database\",\"datePublished\":\"2014-11-27T17:02:00+00:00\",\"dateModified\":\"2024-09-13T14:25:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/\"},\"wordCount\":227,\"commentCount\":0,\"articleSection\":[\"Columnar Database\",\"MemSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/\",\"url\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/\",\"name\":\"Columnar Database - Zofxare Blog Home\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/#website\"},\"datePublished\":\"2014-11-27T17:02:00+00:00\",\"dateModified\":\"2024-09-13T14:25:59+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/#\\\/schema\\\/person\\\/094f5dde70100b9f8fbc63174a2a40ce\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/2014\\\/11\\\/27\\\/columnar-database\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Columnar Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/\",\"name\":\"Zofxare Blog Home\",\"description\":\"The Zofxare blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/#\\\/schema\\\/person\\\/094f5dde70100b9f8fbc63174a2a40ce\",\"name\":\"zofxare\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/?s=96&d=retro&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/?s=96&d=retro&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/?s=96&d=retro&r=g\",\"caption\":\"zofxare\"},\"url\":\"https:\\\/\\\/zofxare.com\\\/zofxare\\\/blog\\\/author\\\/zofxare\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Columnar Database - Zofxare Blog Home","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/","og_locale":"en_US","og_type":"article","og_title":"Columnar Database - Zofxare Blog Home","og_description":"I recently read about an interesting database concept that uses a columnar database, which is where data is stored in columns instead of rows. Apparently this approach has several advantages over a row based database. A columnar database has better performance when a large number of queries are run at the same time. Joins and [&hellip;]","og_url":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/","og_site_name":"Zofxare Blog Home","article_published_time":"2014-11-27T17:02:00+00:00","article_modified_time":"2024-09-13T14:25:59+00:00","author":"zofxare","twitter_card":"summary_large_image","twitter_misc":{"Written by":"zofxare","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/#article","isPartOf":{"@id":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/"},"author":{"name":"zofxare","@id":"https:\/\/zofxare.com\/zofxare\/blog\/#\/schema\/person\/094f5dde70100b9f8fbc63174a2a40ce"},"headline":"Columnar Database","datePublished":"2014-11-27T17:02:00+00:00","dateModified":"2024-09-13T14:25:59+00:00","mainEntityOfPage":{"@id":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/"},"wordCount":227,"commentCount":0,"articleSection":["Columnar Database","MemSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/","url":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/","name":"Columnar Database - Zofxare Blog Home","isPartOf":{"@id":"https:\/\/zofxare.com\/zofxare\/blog\/#website"},"datePublished":"2014-11-27T17:02:00+00:00","dateModified":"2024-09-13T14:25:59+00:00","author":{"@id":"https:\/\/zofxare.com\/zofxare\/blog\/#\/schema\/person\/094f5dde70100b9f8fbc63174a2a40ce"},"breadcrumb":{"@id":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/zofxare.com\/zofxare\/blog\/2014\/11\/27\/columnar-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zofxare.com\/zofxare\/blog\/"},{"@type":"ListItem","position":2,"name":"Columnar Database"}]},{"@type":"WebSite","@id":"https:\/\/zofxare.com\/zofxare\/blog\/#website","url":"https:\/\/zofxare.com\/zofxare\/blog\/","name":"Zofxare Blog Home","description":"The Zofxare blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zofxare.com\/zofxare\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zofxare.com\/zofxare\/blog\/#\/schema\/person\/094f5dde70100b9f8fbc63174a2a40ce","name":"zofxare","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=retro&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=retro&r=g","caption":"zofxare"},"url":"https:\/\/zofxare.com\/zofxare\/blog\/author\/zofxare\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":178,"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/posts\/16\/revisions\/178"}],"wp:attachment":[{"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zofxare.com\/zofxare\/blog\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}