{"id":10124,"date":"2017-05-20T12:14:41","date_gmt":"2017-05-20T10:14:41","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/"},"modified":"2023-06-08T16:34:00","modified_gmt":"2023-06-08T14:34:00","slug":"postgresql-10-beta-1-native-table-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/","title":{"rendered":"PostgreSQL 10 Beta 1: Native Table Partitioning"},"content":{"rendered":"

By Mouhamadou Diaw<\/strong><\/p>\n

The Beta 1 version PostgreSQL 10 is now released with lot of new features. One of the most popular is the native support of table partitioning.
\nIn this blog we are going to see how we can implement partitioned table in PostgreSQL 10. Note that RANGE and LIST partition are the supported methods. In following example RANGE partition is used.
\nThe first step is to create the table to be partitioned, let\u2019s say for example table sales.
\n
\npostgres=# select version();
\nversion
\n------------------------------------------------------------------------------------------------------------
\nPostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
\n(1 row)
\n<\/code>
\n
\ncreate table sales (year date,
\nproduct varchar(10),
\ntotal int)
\npartition by range (year);
\n<\/code><\/p>\n


\npostgres=# d sales
\nTable \"public.sales\"
\nColumn | Type | Collation | Nullable | Default
\n---------+-----------------------+-----------+----------+---------
\nyear | date | | not null |
\nproduct | character varying(10) | | |
\ntotal | integer | | |
\nPartition key: RANGE (year)
\n<\/code><\/p>\n

After the table we have to create the corresponding partitions. Each partition’s definition must specify the bounds that correspond to the partitioning method and partition key of the parent
\n
\npostgres=# create table sales_2014 partition of sales for values from ('2014-01-01') to ('2014-12-31');
\nCREATE TABLE
\npostgres=# create table sales_2015 partition of sales for values from ('2015-01-01') to ('2015-12-31');
\nCREATE TABLE
\npostgres=# create table sales_2016 partition of sales for values from ('2016-01-01') to ('2016-12-31');
\nCREATE TABLE
\npostgres=# create table sales_2017 partition of sales for values from ('2017-01-01') to ('2017-12-31');
\nCREATE TABLE
\n<\/code>
\n
\npostgres=# d
\nList of relations
\nSchema | Name | Type | Owner
\n--------+------------+-------+----------
\npublic | sales | table | postgres
\npublic | sales_2014 | table | postgres
\npublic | sales_2015 | table | postgres
\npublic | sales_2016 | table | postgres
\npublic | sales_2017 | table | postgres
\n(5 rows)
\n<\/code><\/p>\n

Now let\u2019s insert some data into the base table sales.
\npostgres=# insert into sales values ('2014-05-23','art1',20000);
\nINSERT 0 1
\npostgres=# insert into sales values ('2014-12-12','art2',120000);
\nINSERT 0 1
\npostgres=# insert into sales values ('2014-05-07','art3',2050);
\nINSERT 0 1
\npostgres=# insert into sales values ('2015-10-23','art1',23000);
\nINSERT 0 1
\npostgres=# insert into sales values ('2015-10-03','art2',3000);
\nINSERT 0 1
\npostgres=# insert into sales values ('2015-01-02','art3',25400);
\nINSERT 0 1
\npostgres=# insert into sales values ('2016-03-04','art1',1256802);
\nINSERT 0 1
\npostgres=# insert into sales values ('2016-05-08','art2',320000);
\nINSERT 0 1
\npostgres=# insert into sales values ('2016-08-11','art3',220000);
\nINSERT 0 1
\npostgres=# insert into sales values ('2017-07-06','art1',320000);
\nINSERT 0 1
\npostgres=#
\n<\/code>
\nQuerying base table sales we can see inserted data.
\n
\npostgres=# table sales;
\nyear | product | total
\n------------+---------+---------
\n2014-05-23 | art1 | 20000
\n2014-12-12 | art2 | 120000
\n2014-05-07 | art3 | 2050
\n2015-10-23 | art1 | 23000
\n2015-10-03 | art2 | 3000
\n2015-01-02 | art3 | 25400
\n2016-03-04 | art1 | 1256802
\n2016-05-08 | art2 | 320000
\n2016-08-11 | art3 | 220000
\n2017-07-06 | art1 | 320000
\n(10 rows)
\n<\/code><\/p>\n

But what is important is that all data are stored in the partitions not in the base table. As we can see the query on the base table sales only, does not return any value.
\n
\npostgres=# select * from only sales;
\nyear | product | total
\n------+---------+-------
\n(0 rows)
\npostgres=#
\n<\/code><\/p>\n

This can be verified also in the execution plan of the query
\n
\npostgres=# explain select * from sales;
\nQUERY PLAN
\n---------------------------------------------------------------------
\nAppend (cost=0.00..84.00 rows=4400 width=46)
\n-> Seq Scan on sales_2014 (cost=0.00..21.00 rows=1100 width=46)
\n-> Seq Scan on sales_2015 (cost=0.00..21.00 rows=1100 width=46)
\n-> Seq Scan on sales_2016 (cost=0.00..21.00 rows=1100 width=46)
\n-> Seq Scan on sales_2017 (cost=0.00..21.00 rows=1100 width=46)
\n(5 rows)
\n<\/code><\/p>\n

We can also query corresponding partitions
\n
\npostgres=# table sales_2014;
\nyear | product | total
\n------------+---------+--------
\n2014-05-23 | art1 | 20000
\n2014-12-12 | art2 | 120000
\n2014-05-07 | art3 | 2050
\n(3 rows)
\npostgres=# table sales_2015;
\nyear | product | total
\n------------+---------+-------
\n2015-10-23 | art1 | 23000
\n2015-10-03 | art2 | 3000
\n2015-01-02 | art3 | 25400
\n(3 rows)
\npostgres=# table sales_2016;
\nyear | product | total
\n------------+---------+---------
\n2016-03-04 | art1 | 1256802
\n2016-05-08 | art2 | 320000
\n2016-08-11 | art3 | 220000
\n(3 rows)
\npostgres=# table sales_2017;
\nyear | product | total
\n------------+---------+--------
\n2017-07-06 | art1 | 320000
\n(1 row)
\npostgres=#
\n<\/code><\/p>\n

If I don\u2019t need values for the year 2014, the easiest way is just to drop the corresponding partition like
\nDROP TABLE sales_2014;<\/code>
\nAnother way is to simply detach the partition from the partitioned table but retain access to it as a table in its own right
\n
\npostgres=# ALTER TABLE sales DETACH PARTITION sales_2014;
\nALTER TABLE
\n<\/code><\/p>\n

We can see new data in sales table and that data of year 2014 are no longer in the table
\n
\npostgres=# table sales;
\nyear | product | total
\n------------+---------+---------
\n2015-10-23 | art1 | 23000
\n2015-10-03 | art2 | 3000
\n2015-01-02 | art3 | 25400
\n2016-03-04 | art1 | 1256802
\n2016-05-08 | art2 | 320000
\n2016-08-11 | art3 | 220000
\n2017-07-06 | art1 | 320000
\n(7 rows)
\n<\/code><\/p>\n

One advantage of detaching the partition is that we can move data in slower storage.
\nIf for any reason we need to add them again to the partitioned table sales we can just execute
\n
\npostgres=# ALTER TABLE sales ATTACH PARTITION sales_2014 for values from ('2014-01-01') to ('2014-12-31');
\nALTER TABLE
\npostgres=#
\n<\/code>
\n
\npostgres=# table sales;
\nyear | product | total
\n------------+---------+---------
\n2014-05-23 | art1 | 20000
\n2014-12-12 | art2 | 120000
\n2014-05-07 | art3 | 2050
\n2015-10-23 | art1 | 23000
\n2015-10-03 | art2 | 3000
\n2015-01-02 | art3 | 25400
\n2016-03-04 | art1 | 1256802
\n2016-05-08 | art2 | 320000
\n2016-08-11 | art3 | 220000
\n2017-07-06 | art1 | 320000
\n(10 rows)
\n<\/code><\/p>\n

In the documentation we can find following limitations apply to partitioned tables:<\/p>\n

There is no facility available to create the matching indexes on all partitions automatically. Indexes must be added to each partition with separate commands. This also means that there is no way to create a primary key, unique constraint, or exclusion constraint spanning all partitions; it is only possible to constrain each leaf partition individually.<\/p>\n

Since primary keys are not supported on partitioned tables, foreign keys referencing partitioned tables are not supported, nor are foreign key references from a partitioned table to some other table.<\/p>\n

Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.<\/p>\n

An UPDATE that causes a row to move from one partition to another fails, because the new value of the row fails to satisfy the implicit partition constraint of the original partition.<\/p>\n

Row triggers, if necessary, must be defined on individual partitions, not the partitioned table<\/p>\n","protected":false},"excerpt":{"rendered":"

By Mouhamadou Diaw The Beta 1 version PostgreSQL 10 is now released with lot of new features. One of the most popular is the native support of table partitioning. In this blog we are going to see how we can implement partitioned table in PostgreSQL 10. Note that RANGE and LIST partition are the supported […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1107,1108],"type_dbi":[],"class_list":["post-10124","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-partition-table","tag-postgresql-10"],"acf":[],"yoast_head":"\nPostgreSQL 10 Beta 1: Native Table Partitioning - dbi Blog<\/title>\n<meta name=\"description\" content=\"PostgreSQL 10, Partition tables\" \/>\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-10-beta-1-native-table-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 10 Beta 1: Native Table Partitioning\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL 10, Partition tables\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-20T10:14:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-08T14:34:00+00:00\" \/>\n<meta name=\"author\" content=\"Oracle 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=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 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-10-beta-1-native-table-partitioning\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"PostgreSQL 10 Beta 1: Native Table Partitioning\",\"datePublished\":\"2017-05-20T10:14:41+00:00\",\"dateModified\":\"2023-06-08T14:34:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/\"},\"wordCount\":466,\"commentCount\":0,\"keywords\":[\"Partition table\",\"PostgreSQL 10\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/\",\"name\":\"PostgreSQL 10 Beta 1: Native Table Partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-05-20T10:14:41+00:00\",\"dateModified\":\"2023-06-08T14:34:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"PostgreSQL 10, Partition tables\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 10 Beta 1: Native Table 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\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 10 Beta 1: Native Table Partitioning - dbi Blog","description":"PostgreSQL 10, Partition tables","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-10-beta-1-native-table-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 10 Beta 1: Native Table Partitioning","og_description":"PostgreSQL 10, Partition tables","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2017-05-20T10:14:41+00:00","article_modified_time":"2023-06-08T14:34:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"PostgreSQL 10 Beta 1: Native Table Partitioning","datePublished":"2017-05-20T10:14:41+00:00","dateModified":"2023-06-08T14:34:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/"},"wordCount":466,"commentCount":0,"keywords":["Partition table","PostgreSQL 10"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/","name":"PostgreSQL 10 Beta 1: Native Table Partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-05-20T10:14:41+00:00","dateModified":"2023-06-08T14:34:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"PostgreSQL 10, Partition tables","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-10-beta-1-native-table-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 10 Beta 1: Native Table 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\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10124","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\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10124"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10124\/revisions"}],"predecessor-version":[{"id":25697,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10124\/revisions\/25697"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10124"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10124"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10124"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10124"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}