{"id":11941,"date":"2018-10-26T08:37:21","date_gmt":"2018-10-26T06:37:21","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/"},"modified":"2018-10-26T08:37:21","modified_gmt":"2018-10-26T06:37:21","slug":"deep-dive-postgres-at-the-pgconfeu-conference","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/","title":{"rendered":"Deep dive Postgres at the #pgconfeu conference"},"content":{"rendered":"<p>Today I followed many good technical sessions at the European Postgres conference. The Postgres conferences are  really technical oriented, you will find no marketing sessions there and you learn a lot of things.<br \/>\nAs promised yesterday, I wrote today my first blog about the new Postgres storage engine ZHEAP\/UNDO, which is a very interesting feature, with very interesting results.<br \/>\n<!--more--><br \/>\nBefore you continue to read this blog, if you didn&#8217;t read my blog from yesterday,read it first \ud83d\ude42 <a href=\"https:\/\/www.dbi-services.com\/blog\/my-first-day-at-the-pgconfeu-2018-in-lisbon\/\" title=\"link\" target=\"_blank\" rel=\"noopener noreferrer\">link<\/a><\/p>\n<h3>First test : table creation<\/h3>\n<p>We create 2 tables, one with the default Postgres storage engine HEAP, and one with the new storage enfine ZHEAP.<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nPSQL&gt; create table heap2 as select a.*, md5(a::varchar), now() from generate_series(1,5000000) a;\n \nSELECT 5000000\nTime: 12819.369 ms (00:12.819)\n\nPSQL&gt; create table zheap2  with (storage_engine='zheap') as select a.*, md5(a::varchar), now() from generate_series(1,5000000) a;\nSELECT 5000000\nTime: 19155.004 ms (00:19.155)\n<\/pre>\n<p>You noticed, that with Postgres you can choose your storage engine at table level :-). The table creation with ZHEAP is slower, but is is normal because now we have to create the UNDO segment also.<\/p>\n<h3>Second test : Size of the both tables<\/h3>\n<p>Before to start the tests we will check the size of the HEAP and ZHEAP tables, as announced yesterday the HEAP table should be smaller, because we have less header information.<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nPSQL&gt;  select pg_size_pretty(pg_relation_size('heap2'));\n pg_size_pretty \n----------------\n 365 MB\nPSQL&gt; select pg_size_pretty(pg_relation_size('zheap2'));\n pg_size_pretty \n----------------\n 289 MB\n<\/pre>\n<p>The ZHEAP tables is smaller, it exactly what Amit explain us yesterday, because the block header with ZHEAP is smaller. If you want to learn more read his presentation from yesterday. Again the link is on my blog from yesterday.<\/p>\n<h3>Third test : Update on the table<\/h3>\n<p>To get the bloat effect on the HEAP table, we will now update the full table and see what happen.<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nPSQL&gt; update heap2 set a=a+12222222;\nUPDATE 5000000\nTime: 19834.911 ms (00:19.835)\n\nPSQL&gt; update zheap2 set a=a+12222222;\nUPDATE 5000000\nTime: 26956.043 ms (00:26.956)\n\nPSQL&gt; select pg_size_pretty(pg_relation_size('zheap2'));\n pg_size_pretty \n----------------\n 289 MB\nPSQL&gt; vacuum heap2;\nPSQL&gt; select pg_size_pretty(pg_relation_size('heap2'));\n pg_size_pretty \n----------------\n 730 MB\n<\/pre>\n<p>The same as for the creation the update is a bit longer, but the update with ZHEAP write  many information into the log file.We should test again this update with disabling the writing of information into the log file about creating undo segment.<br \/>\nBut  as you can see, the most important information here is that the table don&#8217;t bloat as the HEAP table, now the HEAP table is 2 times bigger despite I executed a VACUUM.<\/p>\n<h3>Fourth test: test of the ROLLBACK<\/h3>\n<p>To test the ROLLBACK we have to open first a transaction with BEGIN;<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nPSQL&gt;  begin;\nBEGIN\nPSQL&gt;* update heap2 set a=a+12222222;\nUPDATE 5000000\nTime: 22071.462 ms (00:22.071)\nPSQL&gt; * rollback;\nROLLBACK\nTime: 1.437 ms\n\nPSQL&gt; begin;\nBEGIN\nPSQL&gt; * update zheap2 set a=a+12222222;\nUPDATE 5000000\nTime: 28210.845 ms (00:28.211)\nPSQL&gt; * rollback;\nROLLBACK\nTime: 0.567 ms\n<\/pre>\n<p>This is the part where I&#8217;m the most surprised, the ROLLBACK for ZHEAP is so fast as for HEAP, I can&#8217;t explain that. I will leave my colleague Daniel Westermann making deeper tests :-). Because with ZHEAP he has to apply the undo blocks, where HEAP tables only mark the transactions as aborted.<\/p>\n<h3>Fifth tests : Check of the query performances<\/h3>\n<p>For this test we have to first flush the filesystem cache and to restart the database, to be sure that nothing is cached.<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@dbi-pg-tun:\/home\/postgres\/zheap\/ [ZHEAP] pgstop \nwaiting for server to shut down.... done\nserver stopped\n\npostgres@dbi-pg-tun:\/home\/postgres\/ [ZHEAP] sudo sync\npostgres@dbi-pg-tun:\/home\/postgres\/ [ZHEAP] sudo echo 3 &gt; \/proc\/sys\/vm\/drop_caches\n\npostgres@dbi-pg-tun:\/home\/postgres\/zheap\/ [ZHEAP] pgstart\nwaiting for server to start.... done\nserver started\n<\/pre>\n<p>Now we are ready for the last test <\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@dbi-pg-tun:\/home\/postgres\/zheap\/ [ZHEAP] sqh\nPSQL&gt; select count(*) from heap2;\n  count  \n---------\n 5000000\nTime: 3444.869 ms (00:03.445)\n\nPSQL&gt; select count(*) from zheap2;\n  count  \n---------\n 5000000\nTime: 593.894 ms\n<\/pre>\n<p>As you can see the query performance are improved significantly for full table scan :-), because the table didn&#8217;t bloat as for the HEAP table. For you information I started additionally 2 times a full update before to restart the database and the HEAP table is now 3 times bigger.<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\nPSQL&gt; select pg_size_pretty(pg_relation_size('heap2'));\n pg_size_pretty \n----------------\n 1095 MB\n\nTime: 0.508 ms\nPSQL&gt; select pg_size_pretty(pg_relation_size('zheap2'));\n pg_size_pretty \n----------------\n 289 MB\n<\/pre>\n<h3>Conclusion of these tests<\/h3>\n<ul>\n<li>Postgres allow the usage or not of UNDO&#8217;s at the table level<\/li>\n<li>We are surprise how fast the ROLLBACK are, but this must be tested again, I don&#8217;t understand why<\/li>\n<li>Select performance are improved significantly for full table scan \ud83d\ude42<\/li>\n<li>The storage will not bloat anymore with ZHEAP<\/li>\n<li>Finally only the updates are a little bit slower<\/li>\n<\/ul>\n<p>It will be interesting to follow the discussions around this feature on the mailing list.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I followed many good technical sessions at the European Postgres conference. The Postgres conferences are really technical oriented, you will find no marketing sessions there and you learn a lot of things. As promised yesterday, I wrote today my first blog about the new Postgres storage engine ZHEAP\/UNDO, which is a very interesting feature, [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1473,77,1474,1475],"type_dbi":[],"class_list":["post-11941","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-mvcc","tag-postgresql","tag-undo","tag-zhead"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Deep dive Postgres at the #pgconfeu conference - 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\/deep-dive-postgres-at-the-pgconfeu-conference\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Deep dive Postgres at the #pgconfeu conference\" \/>\n<meta property=\"og:description\" content=\"Today I followed many good technical sessions at the European Postgres conference. The Postgres conferences are really technical oriented, you will find no marketing sessions there and you learn a lot of things. As promised yesterday, I wrote today my first blog about the new Postgres storage engine ZHEAP\/UNDO, which is a very interesting feature, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-26T06:37:21+00:00\" \/>\n<meta name=\"author\" content=\"Herv\u00e9 Schweitzer\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Herv\u00e9 Schweitzer\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 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\/deep-dive-postgres-at-the-pgconfeu-conference\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/\"},\"author\":{\"name\":\"Herv\u00e9 Schweitzer\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7\"},\"headline\":\"Deep dive Postgres at the #pgconfeu conference\",\"datePublished\":\"2018-10-26T06:37:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/\"},\"wordCount\":567,\"commentCount\":0,\"keywords\":[\"MVCC\",\"PostgreSQL\",\"Undo\",\"zHead\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/\",\"name\":\"Deep dive Postgres at the #pgconfeu conference - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-10-26T06:37:21+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Deep dive Postgres at the #pgconfeu conference\"}]},{\"@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\/7fb08fbefcb9b2c146ef4533cfee00c7\",\"name\":\"Herv\u00e9 Schweitzer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"caption\":\"Herv\u00e9 Schweitzer\"},\"description\":\"Herv\u00e9 Schweitzer has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies such as standardisation, Backup and Recovery, Tuning, and in High Availability solutions such as Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate, and Oracle Failsafe. Herv\u00e9 Schweitzer is \\\"Oracle Certified Master 11g (OCM 11g)\\\". Prior to joining dbi services, Herv\u00e9 Schweitzer was Senior Consultant at Trivadis in Basel. He also worked as an IT Administrator and Oracle DBA at Crossair in Basel. Herv\u00e9 Schweitzer holds a BTS degree in Information Systems from France. His branch-related experience covers Pharma, Transport and Logistics, Banking, Energy, Automotive etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/herve-schweitzer\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Deep dive Postgres at the #pgconfeu conference - 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\/deep-dive-postgres-at-the-pgconfeu-conference\/","og_locale":"en_US","og_type":"article","og_title":"Deep dive Postgres at the #pgconfeu conference","og_description":"Today I followed many good technical sessions at the European Postgres conference. The Postgres conferences are really technical oriented, you will find no marketing sessions there and you learn a lot of things. As promised yesterday, I wrote today my first blog about the new Postgres storage engine ZHEAP\/UNDO, which is a very interesting feature, [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/","og_site_name":"dbi Blog","article_published_time":"2018-10-26T06:37:21+00:00","author":"Herv\u00e9 Schweitzer","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Herv\u00e9 Schweitzer","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/"},"author":{"name":"Herv\u00e9 Schweitzer","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7"},"headline":"Deep dive Postgres at the #pgconfeu conference","datePublished":"2018-10-26T06:37:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/"},"wordCount":567,"commentCount":0,"keywords":["MVCC","PostgreSQL","Undo","zHead"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/","url":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/","name":"Deep dive Postgres at the #pgconfeu conference - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-10-26T06:37:21+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/deep-dive-postgres-at-the-pgconfeu-conference\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Deep dive Postgres at the #pgconfeu conference"}]},{"@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\/7fb08fbefcb9b2c146ef4533cfee00c7","name":"Herv\u00e9 Schweitzer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","caption":"Herv\u00e9 Schweitzer"},"description":"Herv\u00e9 Schweitzer has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies such as standardisation, Backup and Recovery, Tuning, and in High Availability solutions such as Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate, and Oracle Failsafe. Herv\u00e9 Schweitzer is \"Oracle Certified Master 11g (OCM 11g)\". Prior to joining dbi services, Herv\u00e9 Schweitzer was Senior Consultant at Trivadis in Basel. He also worked as an IT Administrator and Oracle DBA at Crossair in Basel. Herv\u00e9 Schweitzer holds a BTS degree in Information Systems from France. His branch-related experience covers Pharma, Transport and Logistics, Banking, Energy, Automotive etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/herve-schweitzer\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11941","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\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11941"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11941\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11941"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}