{"id":12738,"date":"2019-08-10T14:21:19","date_gmt":"2019-08-10T12:21:19","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/"},"modified":"2019-08-10T14:21:19","modified_gmt":"2019-08-10T12:21:19","slug":"edb-epas-12-comes-with-interval-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/","title":{"rendered":"EDB EPAS 12 comes with interval partitioning"},"content":{"rendered":"<p>While community PostgreSQL 12 is in beta quite some time now (currently in <a href=\"https:\/\/www.postgresql.org\/about\/news\/1960\/\" target=\"_blank\" rel=\"noopener noreferrer\">beta 3<\/a>) it usually takes some time until <a href=\"https:\/\/www.enterprisedb.com\/services-support\/edb-supported-products-and-platforms\" target=\"_blank\" rel=\"noopener noreferrer\">EDB EPAS<\/a> will be available on top of the next major PostgreSQL release. Yesterday EDB finally released a beta and you can register for access <a href=\"https:\/\/www.enterprisedb.com\/beta-programs\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>, release notes <a href=\"https:\/\/www.enterprisedb.com\/edb-docs\/static\/docs\/epas\/12\/EPAS_Release_Notes_v12_Beta.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>. One of the new features is interval partitioning which you already might know from Oracle. Basically you do not need to create range partitions in advance but the system will create the partitions for you once you add data that does not fit into any of the current partitions. Lets see how that works.<\/p>\n<p><!--more--><\/p>\n<p>Without interval partitioning you would need to create a range partitioned table like this (note that this is Oracle syntax which is supported by EPAS but not by community PostgreSQL):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\ncreate table my_part_tab ( id int\n                         , dummy text\n                         , created date\n                         )\n                         partition by range (created)\n                         ( partition my_part_tab_1 values less than (to_date('01.02.2019','DD.MM.YYYY'))\n                         );\n<\/pre>\n<p>The issue with that is, that once you want to add data that does not fit into any of the current partitions you will run into issues like that:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nedb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.01.2019','DD.MM.YYYY'));\nINSERT 0 1\nedb=# select * from my_part_tab;\n id | dummy |      created       \n----+-------+--------------------\n  1 | aaa   | 05-JAN-19 00:00:00\n(1 row)\n\nedb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));\npsql: ERROR:  no partition of relation \"my_part_tab\" found for row\nDETAIL:  Partition key of the failing row contains (created) = (05-FEB-19 00:00:00).\nedb=# \n<\/pre>\n<p>Only when you create the partition required manually you will be able to store the data (or it goes to a default partition, which comes with its own issues):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nedb=# alter table my_part_tab add partition my_part_tab_2 values less than (to_date('01.03.2019','DD.MM.YYYY'));\nALTER TABLE\nedb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));\nINSERT 0 1\nedb=# select * from my_part_tab;\n id | dummy |      created       \n----+-------+--------------------\n  1 | aaa   | 05-JAN-19 00:00:00\n  1 | aaa   | 05-FEB-19 00:00:00\n(2 rows)\nedb=# d+ my_part_tab\n                                     Partitioned table \"public.my_part_tab\"\n Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Descripti\n---------+-----------------------------+-----------+----------+---------+----------+--------------+----------\n id      | integer                     |           |          |         | plain    |              | \n dummy   | text                        |           |          |         | extended |              | \n created | timestamp without time zone |           |          |         | plain    |              | \nPartition key: RANGE (created) NULLS LAST\nPartitions: my_part_tab_my_part_tab_1 FOR VALUES FROM (MINVALUE) TO ('01-FEB-19 00:00:00'),\n            my_part_tab_my_part_tab_2 FOR VALUES FROM ('01-FEB-19 00:00:00') TO ('01-MAR-19 00:00:00')\n<\/pre>\n<p>Of course it is not a big deal to create the partitions for the next 20 years in advance but there is a more elegant way of doing this by using interval partitioning:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [7]\">\ndrop table my_part_tab;\ncreate table my_part_tab ( id int\n                         , dummy text\n                         , created date\n                         )\n                         partition by range (created)\n                         interval (numtoyminterval(1,'month'))\n                         ( partition my_part_tab_1 values less than (to_date('01.02.2019','DD.MM.YYYY'))\n                         );\n<\/pre>\n<p>Having the table partitioned like that new partitions will be created on the fly as required:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nedb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.01.2019','DD.MM.YYYY'));\nINSERT 0 1\nedb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));\nINSERT 0 1\nedb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.03.2019','DD.MM.YYYY'));\nINSERT 0 1\nedb=# d+ my_part_tab\n                                     Partitioned table \"public.my_part_tab\"\n Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Descripti\n---------+-----------------------------+-----------+----------+---------+----------+--------------+----------\n id      | integer                     |           |          |         | plain    |              | \n dummy   | text                        |           |          |         | extended |              | \n created | timestamp without time zone |           |          |         | plain    |              | \nPartition key: RANGE (created) INTERVAL ('1 mon'::interval)\nPartitions: my_part_tab_my_part_tab_1 FOR VALUES FROM (MINVALUE) TO ('01-FEB-19 00:00:00'),\n            my_part_tab_sys138880102 FOR VALUES FROM ('01-FEB-19 00:00:00') TO ('01-MAR-19 00:00:00'),\n            my_part_tab_sys138880103 FOR VALUES FROM ('01-MAR-19 00:00:00') TO ('01-APR-19 00:00:00')\n\nedb=# \n<\/pre>\n<p>A nice addition which is not (yet) available in community PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>While community PostgreSQL 12 is in beta quite some time now (currently in beta 3) it usually takes some time until EDB EPAS will be available on top of the next major PostgreSQL release. Yesterday EDB finally released a beta and you can register for access here, release notes here. One of the new features [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1004,1660,586],"type_dbi":[],"class_list":["post-12738","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-edb","tag-epas","tag-postegresql"],"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>EDB EPAS 12 comes with interval partitioning - 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\/edb-epas-12-comes-with-interval-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"EDB EPAS 12 comes with interval partitioning\" \/>\n<meta property=\"og:description\" content=\"While community PostgreSQL 12 is in beta quite some time now (currently in beta 3) it usually takes some time until EDB EPAS will be available on top of the next major PostgreSQL release. Yesterday EDB finally released a beta and you can register for access here, release notes here. One of the new features [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-08-10T12:21:19+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\/edb-epas-12-comes-with-interval-partitioning\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"EDB EPAS 12 comes with interval partitioning\",\"datePublished\":\"2019-08-10T12:21:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/\"},\"wordCount\":255,\"commentCount\":0,\"keywords\":[\"edb\",\"EPAS\",\"PostegreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/\",\"name\":\"EDB EPAS 12 comes with interval partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-08-10T12:21:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"EDB EPAS 12 comes with interval partitioning\"}]},{\"@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 &amp;\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":"EDB EPAS 12 comes with interval partitioning - 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\/edb-epas-12-comes-with-interval-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"EDB EPAS 12 comes with interval partitioning","og_description":"While community PostgreSQL 12 is in beta quite some time now (currently in beta 3) it usually takes some time until EDB EPAS will be available on top of the next major PostgreSQL release. Yesterday EDB finally released a beta and you can register for access here, release notes here. One of the new features [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2019-08-10T12:21:19+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\/edb-epas-12-comes-with-interval-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"EDB EPAS 12 comes with interval partitioning","datePublished":"2019-08-10T12:21:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/"},"wordCount":255,"commentCount":0,"keywords":["edb","EPAS","PostegreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/","name":"EDB EPAS 12 comes with interval partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-08-10T12:21:19+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/edb-epas-12-comes-with-interval-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"EDB EPAS 12 comes with interval partitioning"}]},{"@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 &amp;\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\/12738","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=12738"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12738\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12738"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12738"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12738"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12738"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}