{"id":31728,"date":"2024-03-18T12:56:56","date_gmt":"2024-03-18T11:56:56","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=31728"},"modified":"2024-03-18T12:56:59","modified_gmt":"2024-03-18T11:56:59","slug":"getting-started-with-greenplum-6-why-greenplum","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/","title":{"rendered":"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?"},"content":{"rendered":"\n

Now that we have the basic knowledge for operating a Greenplum cluster we should talk about why Greenplum can be option and what are the benefits of such a system. Again, here are the previous posts: Getting started with Greenplum \u2013 1 \u2013 Installation<\/a>, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up the cluster<\/a>, Getting started with Greenplum \u2013 3 \u2013 Behind the scenes<\/a>, Getting started with Greenplum \u2013 4 \u2013 Backup & Restore \u2013 databases<\/a>, Getting started with Greenplum \u2013 5 – Recovering from failed segment nodes<\/a>.<\/p>\n\n\n\n

First of all, Greenplum is for data analytics and data warehousing. It is not meant as an OLTP system. Greenplum also describes itself as an MPP system, which means “Massively Parallel Processing”. The idea behind that is quite simple: Use as many hosts as you can, all of them with their own CPUs, memory disks, and operating system, process the required work in parallel on those nodes, combine the results and give it back to the client. This is also known as a “shared nothing” architecture.<\/p>\n\n\n\n

Coming back to the setup we’ve running now:<\/p>\n\n\n

\n                                        |-------------------|\n                             |------6000|primary---------   |\n                             |          |     Segment 1 |   |\n                             |      7000|mirror&lt;------| |   |\n                             |          |-------------------|\n                             |                        | |\n            |-------------------|                     | |\n            |                   |                     | |\n        5432|   Coordinator     |                     | |\n            |                   |                     | |\n            |-------------------|                     | |\n                             |                        | |\n                             |          |-------------------|\n                             |------6000|primary ------ |   |\n                                        |     Segment 2 |   |\n                                    7000|mirror&lt;--------|   |\n                                        |-------------------|\n<\/pre><\/div>\n\n\n
We have the coordinator node in front, this one receives all the client requests (the coordinator host can also be fault tolerant by adding a standby coordinator host). Work is then distributed to the amount of segment nodes you have available in the cluster. In my case this are just small VMs running on the same host so it will not give me any benefit performance wise. In a real setup all those nodes would run either on bare metal nodes or at least on different host nodes in a virtualized setup. The more segment nodes get added to the cluster, the more compute resources become available to be utilized. A critical part in such a setup is of course the networking. All the traffic goes through the network and the faster the network between the Greenplum nodes (which is called the interconnect) the better the whole cluster will perform.<\/p>\n\n\n\n
Let’s assume we got all these building blocks right, do we need to consider more points to get most out of such a setup? Yes, we do.<\/p>\n\n\n\n
In PostgreSQL all tables are heap organized. With Greenplum you can choose between heap oriented and append optimized tables. Heap oriented tables should be used when you expect frequent updates and deletes, append oriented tables should be used for initial load tables which only receive bulk inserts after loading.<\/p>\n\n\n\n
The following statements create two simple tables, one heap organized, the other one append optimized, both distributed by id:<\/p>\n\n\n
\npostgres=# create table t1 ( id int primary key\n                , dummy text \n                ) \n                using heap\n                distributed by (id);\n                \nCREATE TABLE\npostgres=# create table t2 ( id int primary key\n                , dummy text \n                ) \n                using ao_row\n                distributed by (id);\nCREATE TABLE\n<\/pre><\/div>\n\n\n
Populating both tables with the same amount of data and comparing the size of the tables give this:<\/p>\n\n\n
\npostgres=# \\timing\nTiming is on.\npostgres=# insert into t1 select i, i::text from generate_series(1,1000000) i;\nINSERT 0 1000000\nTime: 2639.981 ms (00:02.640)\npostgres=# insert into t2 select i, i::text from generate_series(1,1000000) i;\nINSERT 0 1000000\nTime: 2878.901 ms (00:02.879)\npostgres=# select pg_relation_size('t1');\n pg_relation_size \n------------------\n         44072960\n(1 row)\n\nTime: 1.394 ms\npostgres=# select pg_relation_size('t2');\n pg_relation_size \n------------------\n         25226336\n(1 row)\n\nTime: 2.035 ms\n<\/pre><\/div>\n\n\n
The append optimized table is much smaller than the traditional heap organized table even without compression. The reason is that the tuple headers are much smaller<\/a> for append optimized tables. This is also reason why they should not be used for frequent update and delete operations. Append only is only meant for bulk loading and bulk insert operations. <\/p>\n\n\n\n
Another option you have if you go for append optimized tables is columnar storage but again, consider when you to use them: Columnar storage is read optimized and is not mean for write heavy operations. If you only access a small number of columns this can reduce the required I\/O significantly. A table which is organized by columns is created like this:<\/p>\n\n\n
\npostgres=# create table t4 ( id int primary key\n                , dummy text \n                ) \n                using ao_row\n                distributed by (id);\nCREATE TABLE\n\n<\/pre><\/div>\n\n\n
On top of all that you can chose to go for compression and partitioning<\/a>, which gives you even more choice on how you want to layout your data. Connectors<\/a> are something you might want to look as well.<\/p>\n\n\n\n
Coming back to the initial question: Why Greenplum?<\/p>\n\n\n\n
If you have the requirement for massive parallel data processing and you want to have a system which very much feels like a standard PostgreSQL for the clients, Greenplum is a valid option. As there is an open source edition give it a try and explore the possibilities. There is a lot of choice for various use cases and access patterns.<\/p>\n","protected":false},"excerpt":{"rendered":"
Now that we have the basic knowledge for operating a Greenplum cluster we should talk about why Greenplum can be option and what are the benefits of such a system. Again, here are the previous posts: Getting started with Greenplum \u2013 1 \u2013 Installation, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[3276,77],"type_dbi":[],"class_list":["post-31728","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-greenplum","tag-postgresql"],"acf":[],"yoast_head":"\nGetting started with Greenplum \u2013 6 \u2013 Why Greenplum? - dbi Blog<\/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:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?\" \/>\n<meta property=\"og:description\" content=\"Now that we have the basic knowledge for operating a Greenplum cluster we should talk about why Greenplum can be option and what are the benefits of such a system. Again, here are the previous posts: Getting started with Greenplum \u2013 1 \u2013 Installation, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-18T11:56:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-18T11:56:59+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?\",\"datePublished\":\"2024-03-18T11:56:56+00:00\",\"dateModified\":\"2024-03-18T11:56:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\"},\"wordCount\":669,\"commentCount\":0,\"keywords\":[\"Greenplum\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\",\"name\":\"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2024-03-18T11:56:56+00:00\",\"dateModified\":\"2024-03-18T11:56:59+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum? - dbi Blog","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:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/","og_locale":"en_US","og_type":"article","og_title":"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?","og_description":"Now that we have the basic knowledge for operating a Greenplum cluster we should talk about why Greenplum can be option and what are the benefits of such a system. Again, here are the previous posts: Getting started with Greenplum \u2013 1 \u2013 Installation, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up […]","og_url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/","og_site_name":"dbi Blog","article_published_time":"2024-03-18T11:56:56+00:00","article_modified_time":"2024-03-18T11:56:59+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?","datePublished":"2024-03-18T11:56:56+00:00","dateModified":"2024-03-18T11:56:59+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/"},"wordCount":669,"commentCount":0,"keywords":["Greenplum","PostgreSQL"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/","url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/","name":"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-03-18T11:56:56+00:00","dateModified":"2024-03-18T11:56:59+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-6-why-greenplum\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Getting started with Greenplum \u2013 6 \u2013 Why Greenplum?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=31728"}],"version-history":[{"count":13,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31728\/revisions"}],"predecessor-version":[{"id":31917,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31728\/revisions\/31917"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=31728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=31728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=31728"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=31728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}