{"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":28,"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 v28.8 (Yoast SEO v28.8) - 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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}