{"id":12479,"date":"2019-06-02T06:00:23","date_gmt":"2019-06-02T04:00:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/"},"modified":"2019-06-02T06:00:23","modified_gmt":"2019-06-02T04:00:23","slug":"postgresql-12-new-partition-reporting-functions","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/","title":{"rendered":"PostgreSQL 12: New partition reporting functions"},"content":{"rendered":"<p>PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (<a href=\"https:\/\/www.dbi-services.com\/blog\/updating-the-partition-key-now-works-in-postgresql-11\/\" target=\"_blank\" rel=\"noopener noreferrer\">Updating the partition key now works in PostgreSQL 11<\/a>, <a href=\"https:\/\/www.dbi-services.com\/blog\/insert-on-conflict-with-partitions-finally-works-in-postgresql-11\/Local partitioned indexes in PostgreSQL 11\" target=\"_blank\" rel=\"noopener noreferrer\">Insert\u2026on conflict with partitions finally works in PostgreSQL 11<\/a>, <a href=\"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/\" target=\"_blank\" rel=\"noopener noreferrer\">Local partitioned indexes in PostgreSQL 11<\/a>, <a href=\"https:\/\/www.dbi-services.com\/blog\/hash-partitioning-in-postgresql-11\/\" target=\"_blank\" rel=\"noopener noreferrer\">Hash Partitioning in PostgreSQL 11<\/a>) and PostgreSQL 12 goes even further. Beside that foreign keys can now reference partitioned tables three new functions made it into PostgreSQL 12 that will give you information about your partitioned tables. <\/p>\n<p><!--more--><\/p>\n<p>Our little demo setup consist of a list partitioned table with three partitions:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table animals ( id int generated always as identity ( cache 10 ),\npostgres(#                        name text unique,\npostgres(#                        primary key(id,name)\npostgres(#                      ) \npostgres-#                      partition by list (name);\nCREATE TABLE\npostgres=# create table animals_elephants\npostgres-#   partition of animals\npostgres-#   for values in ('elephant');\nCREATE TABLE\npostgres=# create table animals_cats\npostgres-#   partition of animals\npostgres-#   for values in ('cats');\nCREATE TABLE\npostgres=# create table animals_dogs\npostgres-#   partition of animals\npostgres-#   for values in ('dogs');\nCREATE TABLE\n<\/pre>\n<p>What already worked in PostgreSQL 11 is that <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-psql.html\" target=\"_blank\" rel=\"noopener noreferrer\">psql<\/a> will give you information about your partitions:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# d animals\n                   Partitioned table \"public.animals\"\n Column |  Type   | Collation | Nullable |           Default            \n--------+---------+-----------+----------+------------------------------\n id     | integer |           | not null | generated always as identity\n name   | text    |           | not null | \nPartition key: LIST (name)\nIndexes:\n    \"animals_pkey\" PRIMARY KEY, btree (id, name)\n    \"animals_name_key\" UNIQUE CONSTRAINT, btree (name)\nNumber of partitions: 3 (Use d+ to list them.)\n<\/pre>\n<p>Using &#8220;d+&#8221; will even show you more information:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# d+ animals\n                                       Partitioned table \"public.animals\"\n Column |  Type   | Collation | Nullable |           Default            | Storage  | Stats target | Description \n--------+---------+-----------+----------+------------------------------+----------+--------------+-------------\n id     | integer |           | not null | generated always as identity | plain    |              | \n name   | text    |           | not null |                              | extended |              | \nPartition key: LIST (name)\nIndexes:\n    \"animals_pkey\" PRIMARY KEY, btree (id, name)\n    \"animals_name_key\" UNIQUE CONSTRAINT, btree (name)\n    \"animals_i1\" btree (name)\nPartitions: animals_cats FOR VALUES IN ('cats'),\n            animals_dogs FOR VALUES IN ('dogs'),\n            animals_elephants FOR VALUES IN ('elephant')\n<\/pre>\n<p>Now with PostgreSQL 12 there are three new functions which you can use get information about your partitioned tables and the partitions itself. The first one will give you the partition tree:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select pg_partition_tree('animals');\n        pg_partition_tree        \n---------------------------------\n (animals,,f,0)\n (animals_elephants,animals,t,1)\n (animals_dogs,animals,t,1)\n (animals_cats,animals,t,1)\n(4 rows)\n<\/pre>\n<p>The second one will give you the ancestor relations of the given partition:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select pg_partition_ancestors('animals_dogs');\n pg_partition_ancestors \n------------------------\n animals_dogs\n animals\n(2 rows)\n<\/pre>\n<p>The third one will give you the root for a given partition:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select pg_partition_root('animals_cats');\n pg_partition_root \n-------------------\n animals\n(1 row)\n<\/pre>\n<p>All of them do also work for partitioned indexes:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create index animals_i1 on animals (name);\nCREATE INDEX\npostgres=# select pg_partition_tree('animals_i1');\n              pg_partition_tree              \n---------------------------------------------\n (animals_i1,,f,0)\n (animals_cats_name_idx,animals_i1,t,1)\n (animals_dogs_name_idx,animals_i1,t,1)\n (animals_elephants_name_idx,animals_i1,t,1)\n(4 rows)\n<\/pre>\n<p>Nice.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (Updating the partition key now works in PostgreSQL 11, Insert\u2026on conflict with partitions finally works in PostgreSQL 11, Local partitioned indexes in PostgreSQL 11, Hash Partitioning in PostgreSQL 11) and PostgreSQL 12 goes even further. Beside that foreign keys can now [&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-12479","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.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 12: New partition reporting functions - 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-12-new-partition-reporting-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 12: New partition reporting functions\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (Updating the partition key now works in PostgreSQL 11, Insert\u2026on conflict with partitions finally works in PostgreSQL 11, Local partitioned indexes in PostgreSQL 11, Hash Partitioning in PostgreSQL 11) and PostgreSQL 12 goes even further. Beside that foreign keys can now [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-02T04:00:23+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-12-new-partition-reporting-functions\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 12: New partition reporting functions\",\"datePublished\":\"2019-06-02T04:00:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/\"},\"wordCount\":175,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/\",\"name\":\"PostgreSQL 12: New partition reporting functions - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-06-02T04:00:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 12: New partition reporting functions\"}]},{\"@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 12: New partition reporting functions - 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-12-new-partition-reporting-functions\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 12: New partition reporting functions","og_description":"PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (Updating the partition key now works in PostgreSQL 11, Insert\u2026on conflict with partitions finally works in PostgreSQL 11, Local partitioned indexes in PostgreSQL 11, Hash Partitioning in PostgreSQL 11) and PostgreSQL 12 goes even further. Beside that foreign keys can now [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/","og_site_name":"dbi Blog","article_published_time":"2019-06-02T04:00:23+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-12-new-partition-reporting-functions\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 12: New partition reporting functions","datePublished":"2019-06-02T04:00:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/"},"wordCount":175,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/","name":"PostgreSQL 12: New partition reporting functions - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-02T04:00:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-new-partition-reporting-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 12: New partition reporting functions"}]},{"@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\/12479","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=12479"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12479\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12479"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12479"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12479"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}