{"id":11040,"date":"2018-03-22T07:23:40","date_gmt":"2018-03-22T06:23:40","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/"},"modified":"2018-03-22T07:23:40","modified_gmt":"2018-03-22T06:23:40","slug":"local-partitioned-indexes-in-postgresql-11","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/","title":{"rendered":"Local partitioned indexes in PostgreSQL 11"},"content":{"rendered":"<p>When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=8b08f7d4820fd7a8ef6152a9dd8c6e3cb01e5f99\" target=\"_blank\" rel=\"noopener\">local partitioned indexes<\/a>. To make it easier to understand lets start with an example in PostgreSQL 10.<\/p>\n<p><!--more--><\/p>\n<p>A very simple list partitioned table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                                          version                                                           \n----------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit\n(1 row)\n\npostgres=# create table part ( a int, list varchar(5) ) partition by list (list);\nCREATE TABLE\npostgres=# create table part_1 partition of part for values in ('beer');\nCREATE TABLE\npostgres=# create table part_2 partition of part for values in ('wine');\nCREATE TABLE\npostgres=# d+ part\n                                          Table \"public.part\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           |          |         | plain    |              | \n list   | character varying(5) |           |          |         | extended |              | \nPartition key: LIST (list)\nPartitions: part_1 FOR VALUES IN ('beer'),\n            part_2 FOR VALUES IN ('wine')\n<\/pre>\n<p>In PostgreSQL 10 what happens when we create an index on the partitioned table?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create index i_test on part (a);\nERROR:  cannot create index on partitioned table \"part\"\n<\/pre>\n<p>You just can not do it. But you can create indexes on the partitions directly:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create index i_test_1 on part_1 (a);\nCREATE INDEX\npostgres=# create index i_test_2 on part_2 (a);\nCREATE INDEX\n<\/pre>\n<p>Lets do the same test with PostgreSQL 11:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                                  version                                                   \n------------------------------------------------------------------------------------------------------------\n PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit\n(1 row)\n\npostgres=# create table part ( a int, list varchar(5) ) partition by list (list);\nCREATE TABLE\npostgres=# create table part_1 partition of part for values in ('beer');\nCREATE TABLE\npostgres=# create table part_2 partition of part for values in ('wine');\nCREATE TABLE\npostgres=# d+ part\n                                          Table \"public.part\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           |          |         | plain    |              | \n list   | character varying(5) |           |          |         | extended |              | \nPartition key: LIST (list)\nPartitions: part_1 FOR VALUES IN ('beer'),\n            part_2 FOR VALUES IN ('wine')\n<\/pre>\n<p>Try to create the index on the partitioned table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create index i_test on part (a);\nCREATE INDEX\npostgres=# d+ part\n                                          Table \"public.part\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           |          |         | plain    |              | \n list   | character varying(5) |           |          |         | extended |              | \nPartition key: LIST (list)\nIndexes:\n    \"i_test\" btree (a)\nPartitions: part_1 FOR VALUES IN ('beer'),\n            part_2 FOR VALUES IN ('wine')\n\npostgres=# d+ part_1\n                                         Table \"public.part_1\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           |          |         | plain    |              | \n list   | character varying(5) |           |          |         | extended |              | \nPartition of: part FOR VALUES IN ('beer')\nPartition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))\nIndexes:\n    \"part_1_a_idx\" btree (a)\n\npostgres=# d+ part_2\n                                         Table \"public.part_2\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           |          |         | plain    |              | \n list   | character varying(5) |           |          |         | extended |              | \nPartition of: part FOR VALUES IN ('wine')\nPartition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))\nIndexes:\n    \"part_2_a_idx\" btree (a)\n<\/pre>\n<p>The index is cascaded down to all the partitions in PostgreSQL 11 which is really nice. As a side effect of this, when you try this in PostgreSQL 10:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table part add constraint part_pk primary key(a,list);\nERROR:  primary key constraints are not supported on partitioned tables\nLINE 1: alter table part add constraint part_pk primary key(a,list);\n                             ^\n<\/pre>\n<p>&#8230; you will get an error message telling you that primary keys are not supported on partitioned tables. The same applies here, you can do that on the partitions directly:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table part_1 add constraint part1_pk primary key(a,list);\nALTER TABLE\npostgres=# alter table part_2 add constraint part2_pk primary key(a,list);\nALTER TABLE\n<\/pre>\n<p>Now in PostgreSQL 11 this works as well:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table part add constraint part_pk primary key(a,list);\nALTER TABLE\npostgres=# d+ part\n                                          Table \"public.part\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           | not null |         | plain    |              | \n list   | character varying(5) |           | not null |         | extended |              | \nPartition key: LIST (list)\nIndexes:\n    \"part_pk\" PRIMARY KEY, btree (a, list)\n    \"i_test\" btree (a)\nPartitions: part_1 FOR VALUES IN ('beer'),\n            part_2 FOR VALUES IN ('wine')\n\npostgres=# d+ part_1\n                                         Table \"public.part_1\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           | not null |         | plain    |              | \n list   | character varying(5) |           | not null |         | extended |              | \nPartition of: part FOR VALUES IN ('beer')\nPartition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))\nIndexes:\n    \"part_1_pkey\" PRIMARY KEY, btree (a, list)\n    \"part_1_a_idx\" btree (a)\n\npostgres=# d+ part_2\n                                         Table \"public.part_2\"\n Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer              |           | not null |         | plain    |              | \n list   | character varying(5) |           | not null |         | extended |              | \nPartition of: part FOR VALUES IN ('wine')\nPartition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))\nIndexes:\n    \"part_2_pkey\" PRIMARY KEY, btree (a, list)\n    \"part_2_a_idx\" btree (a)\n<\/pre>\n<p>Quite some improvements to show up in PostgreSQL 11.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in [&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-11040","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>Local partitioned indexes in PostgreSQL 11 - 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\/local-partitioned-indexes-in-postgresql-11\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Local partitioned indexes in PostgreSQL 11\" \/>\n<meta property=\"og:description\" content=\"When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-22T06:23:40+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=\"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\\\/local-partitioned-indexes-in-postgresql-11\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Local partitioned indexes in PostgreSQL 11\",\"datePublished\":\"2018-03-22T06:23:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/\"},\"wordCount\":179,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/\",\"name\":\"Local partitioned indexes in PostgreSQL 11 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-03-22T06:23:40+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/local-partitioned-indexes-in-postgresql-11\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Local partitioned indexes in PostgreSQL 11\"}]},{\"@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":"Local partitioned indexes in PostgreSQL 11 - 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\/local-partitioned-indexes-in-postgresql-11\/","og_locale":"en_US","og_type":"article","og_title":"Local partitioned indexes in PostgreSQL 11","og_description":"When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/","og_site_name":"dbi Blog","article_published_time":"2018-03-22T06:23:40+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Local partitioned indexes in PostgreSQL 11","datePublished":"2018-03-22T06:23:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/"},"wordCount":179,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/","url":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/","name":"Local partitioned indexes in PostgreSQL 11 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-03-22T06:23:40+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/local-partitioned-indexes-in-postgresql-11\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Local partitioned indexes in PostgreSQL 11"}]},{"@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\/11040","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=11040"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11040\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11040"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11040"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11040"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11040"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}