{"id":15326,"date":"2020-11-30T07:36:49","date_gmt":"2020-11-30T06:36:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/"},"modified":"2020-11-30T07:36:49","modified_gmt":"2020-11-30T06:36:49","slug":"postgresql-14-automatic-hash-and-list-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/","title":{"rendered":"PostgreSQL 14: Automatic hash and list partitioning?"},"content":{"rendered":"<p>Declarative partitioning was introduced in <a href=\"https:\/\/www.postgresql.org\/docs\/10\/ddl-partitioning.html\" target=\"_blank\" rel=\"noopener noreferrer\">PostgreSQL 10<\/a> and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature:<\/p>\n<ul>\n<li>You can partition by range, list and hash<\/li>\n<li>Attaching and detaching partitions<\/li>\n<li>Foreign keys<\/li>\n<li>Sub-partitioning<\/li>\n<li>Indexing and constrains on partitions<\/li>\n<li>Partition pruning<\/li>\n<\/ul>\n<p>What is missing, is the possibility to let PostgreSQL create partitions automatically. With <a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/7fec3abb-c663-c0d2-8452-a46141be6d4a%40postgrespro.ru\" target=\"_blank\" rel=\"noopener noreferrer\">this patch<\/a> this will finally be possible for hash and list partitioning, once it gets committed.<\/p>\n<p><!--more--><\/p>\n<p>Lets start with list partitioning: Looking at the patch, new syntax is introduced:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nCREATE TABLE tbl_list (i int) PARTITION BY LIST (i)\nCONFIGURATION (values in (1, 2), (3, 4) DEFAULT PARTITION tbl_default);\n<\/pre>\n<p>Taking that as an example we should see all partitions created automatically, if we create a partitioned table like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table tpart_list ( a text primary key, b int, c int )\n           partition by list(a)\n           configuration (values in ('a'),('b'),('c'),('d') default partition tpart_list_default);\nCREATE TABLE\n<\/pre>\n<p>That should have created 5 partitions automatically: a,b,c,d and the default partition:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# d+ tpart_list\n                           Partitioned table \"public.tpart_list\"\n Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+---------+-----------+----------+---------+----------+--------------+-------------\n a      | text    |           | not null |         | extended |              | \n b      | integer |           |          |         | plain    |              | \n c      | integer |           |          |         | plain    |              | \nPartition key: LIST (a)\nIndexes:\n    \"tpart_list_pkey\" PRIMARY KEY, btree (a)\nPartitions: tpart_list_0 FOR VALUES IN ('a'),\n            tpart_list_1 FOR VALUES IN ('b'),\n            tpart_list_2 FOR VALUES IN ('c'),\n            tpart_list_3 FOR VALUES IN ('d'),\n            tpart_list_default DEFAULT\n<\/pre>\n<p>Nice. The same works for hash partitioned tables but the syntax is slightly different:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nCREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)\nCONFIGURATION (modulus 3);\n<\/pre>\n<p>The idea is the same, of course: You need to specify the &#8220;configuration&#8221; and when you go for hash partitioning you need to provide the modulus:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table tpart_hash ( a int primary key, b text)\n           partition by hash (a)\n           configuration (modulus 5);\nCREATE TABLE\npostgres=# d+ tpart_hash\n                           Partitioned table \"public.tpart_hash\"\n Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+---------+-----------+----------+---------+----------+--------------+-------------\n a      | integer |           | not null |         | plain    |              | \n b      | text    |           |          |         | extended |              | \nPartition key: HASH (a)\nIndexes:\n    \"tpart_hash_pkey\" PRIMARY KEY, btree (a)\nPartitions: tpart_hash_0 FOR VALUES WITH (modulus 5, remainder 0),\n            tpart_hash_1 FOR VALUES WITH (modulus 5, remainder 1),\n            tpart_hash_2 FOR VALUES WITH (modulus 5, remainder 2),\n            tpart_hash_3 FOR VALUES WITH (modulus 5, remainder 3),\n            tpart_hash_4 FOR VALUES WITH (modulus 5, remainder 4)\n<\/pre>\n<p>Really nice, great work and thanks to all involved. I hope that the next steps will be:<\/p>\n<ul>\n<li>Support automatic partition creation for range partitioning<\/li>\n<li>Support automatic partition creation on the fly when data comes in, which requires a new partition. In the thread this is referenced as &#8220;dynamic&#8221; partitioning and what is implemented here is referenced as &#8220;static&#8221; partitioning<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Declarative partitioning was introduced in PostgreSQL 10 and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature: You can partition by range, list and hash Attaching and detaching partitions Foreign keys Sub-partitioning Indexing and constrains on partitions Partition pruning What is [&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":[77],"type_dbi":[],"class_list":["post-15326","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 14: Automatic hash and list 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\/postgresql-14-automatic-hash-and-list-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 14: Automatic hash and list partitioning?\" \/>\n<meta property=\"og:description\" content=\"Declarative partitioning was introduced in PostgreSQL 10 and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature: You can partition by range, list and hash Attaching and detaching partitions Foreign keys Sub-partitioning Indexing and constrains on partitions Partition pruning What is [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-30T06:36:49+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=\"2 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\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 14: Automatic hash and list partitioning?\",\"datePublished\":\"2020-11-30T06:36:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/\"},\"wordCount\":233,\"commentCount\":2,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/\",\"name\":\"PostgreSQL 14: Automatic hash and list partitioning? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-11-30T06:36:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-automatic-hash-and-list-partitioning\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 14: Automatic hash and list 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":"PostgreSQL 14: Automatic hash and list 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\/postgresql-14-automatic-hash-and-list-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 14: Automatic hash and list partitioning?","og_description":"Declarative partitioning was introduced in PostgreSQL 10 and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature: You can partition by range, list and hash Attaching and detaching partitions Foreign keys Sub-partitioning Indexing and constrains on partitions Partition pruning What is [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2020-11-30T06:36:49+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 14: Automatic hash and list partitioning?","datePublished":"2020-11-30T06:36:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/"},"wordCount":233,"commentCount":2,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/","name":"PostgreSQL 14: Automatic hash and list partitioning? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-11-30T06:36:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-automatic-hash-and-list-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 14: Automatic hash and list 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\/15326","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=15326"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15326\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15326"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}