Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdl4Y-0006SS-PF for pgsql-docs@arkaria.postgresql.org; Sat, 14 Nov 2020 02:13:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdl4V-00058V-8g for pgsql-docs@arkaria.postgresql.org; Sat, 14 Nov 2020 02:13:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdl4U-00058O-N6 for pgsql-docs@lists.postgresql.org; Sat, 14 Nov 2020 02:13:46 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdl4O-0007gf-6d for pgsql-docs@lists.postgresql.org; Sat, 14 Nov 2020 02:13:44 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0AE2DYAZ414581; Fri, 13 Nov 2020 21:13:34 -0500 From: Tom Lane To: pgsql-docs@lists.postgresql.org cc: Edson Richter , "David G. Johnston" Subject: Partitioning docs (was Re: Range partitioning and overlap) In-reply-to: <398613.1605301084@sss.pgh.pa.us> References: , <398613.1605301084@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Fri, 13 Nov 2020 15:58:04 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <414538.1605319968.0@sss.pgh.pa.us> Date: Fri, 13 Nov 2020 21:13:34 -0500 Message-ID: <414580.1605320014@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <414538.1605319968.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable [ redirecting to pgsql-docs ] I wrote: > Edson Richter writes: >> Further on the documentation: "When creating a range partition, the low= er bound specified with FROM is an inclusive bound, whereas the upper boun= d specified with TO is an exclusive bound." >> I'm pretty sure I cannot find this statement in PostgreSQL 13 documenta= tion page about partitioning. May be this statement is in another page? > It's in the CREATE TABLE reference page. Seems like it would be a good > idea to have it also in ddl.sgml's discussion of partitioning, though. I went to do that, and soon decided that section 5.11 (Table Partitioning) really could stand a fair amount of editorial attention. There's a lot of less than pitch-perfect English, paragraphs that were evidently written with only minimal attention to nearby existing material, diving into the weedy details in even the earliest introductory paras, and so on. I propose the attached. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="improve-introductory-partitioning-docs.patch"; charset="us-ascii" Content-ID: <414538.1605319968.2@sss.pgh.pa.us> Content-Description: improve-introductory-partitioning-docs.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 872f7a7fac..25948a7316 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3553,8 +3553,8 @@ VALUES ('Albany', NULL, NULL, 'NY'); When queries or updates access a large percentage of a single - partition, performance can be improved by taking advantage - of sequential scan of that partition instead of using an + partition, performance can be improved by using a + sequential scan of that partition instead of using an index and random access reads scattered across the whole table. @@ -3577,7 +3577,7 @@ VALUES ('Albany', NULL, NULL, 'NY'); = - The benefits will normally be worthwhile only when a table would + These benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physic= al @@ -3599,6 +3599,13 @@ VALUES ('Albany', NULL, NULL, 'NY'); the ranges of values assigned to different partitions. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects. + Each range's bounds are understood as being inclusive at the + lower end and exclusive at the upper end. For example, if one + partition's range is from 1 + to 10, and the next one's range is + from 10 to 20, then + value 10 belongs to the second partition not + the first. @@ -3608,7 +3615,7 @@ VALUES ('Albany', NULL, NULL, 'NY'); = - The table is partitioned by explicitly listing which key values + The table is partitioned by explicitly listing which key value(s= ) appear in each partition. @@ -3640,25 +3647,34 @@ VALUES ('Albany', NULL, NULL, 'NY'); Declarative Partitioning = - PostgreSQL offers a way to specify how to - divide a table into pieces called partitions. The table that is divi= ded + PostgreSQL allows you to specify declarati= vely + that a table is divided into partitions. The table that is divided is referred to as a partitioned table. The specification consists of the partitioning method - and a list of columns or expressions to be used as the - partition key. + as described above, plus a list of columns or expressions to be used + as the partition key. = - All rows inserted into a partitioned table will be routed to one of t= he - partitions based on the value of the partition - key. Each partition has a subset of the data defined by its - partition bounds. The currently supported - partitioning methods are range, list, and hash. + The partitioned table itself is a virtual table having + no storage of its own. Instead, the storage belongs + to partitions, which are otherwise-ordinary + tables associated with the partitioned table. + Each partition stores a subset of the data as defined by its + partition bounds. + All rows inserted into a partitioned table will be routed to the + appropriate one of the partitions based on the values of the partitio= n + key column(s). + Updating the partition key of a row will cause it to be moved into a + different partition if it no longer satisfies the partition bounds + of its original partition. = - Partitions may themselves be defined as partitioned tables, using wha= t is - called sub-partitioning. Partitions may have = their + Partitions may themselves be defined as partitioned tables, resulting + in sub-partitioning. Although all partitions + must have the same columns as their partitioned parent, partitions ma= y + have their own indexes, constraints and default values, distinct from those of o= ther partitions. See for more details= on creating partitioned tables and partitions. @@ -3666,90 +3682,21 @@ VALUES ('Albany', NULL, NULL, 'NY'); = It is not possible to turn a regular table into a partitioned table o= r - vice versa. However, it is possible to add a regular or partitioned = table - containing data as a partition of a partitioned table, or remove a + vice versa. However, it is possible to add an existing regular or + partitioned table as a partition of a partitioned table, or remove a partition from a partitioned table turning it into a standalone table= ; - see to learn more about the + this can simplify and speed up many maintenance processes. + See to learn more about the ATTACH PARTITION and DETACH PARTITION sub-commands. = - - Individual partitions are linked to the partitioned table with inheri= tance - behind-the-scenes; however, it is not possible to use some of the - generic features of inheritance (discussed below) with declaratively - partitioned tables or their partitions. For example, a partition - cannot have any parents other than the partitioned table it is a - partition of, nor can a regular table inherit from a partitioned tabl= e - making the latter its parent. That means partitioned tables and thei= r - partitions do not participate in inheritance with regular tables. - Since a partition hierarchy consisting of the partitioned table and i= ts - partitions is still an inheritance hierarchy, all the normal rules of - inheritance apply as described in wit= h - some exceptions, most notably: - - - - - Both CHECK and NOT NULL - constraints of a partitioned table are always inherited by all its - partitions. CHECK constraints that are marked - NO INHERIT are not allowed to be created on - partitioned tables. - - - - - - Using ONLY to add or drop a constraint on only = the - partitioned table is supported as long as there are no partitions.= Once - partitions exist, using ONLY will result in an = error - as adding or dropping constraints on only the partitioned table, w= hen - partitions exist, is not supported. Instead, constraints on the - partitions themselves can be added and (if they are not present in= the - parent table) dropped. - - - - - - As a partitioned table does not have any data directly, attempts t= o use - TRUNCATE ONLY on a partition= ed - table will always return an error. - - - - - - Partitions cannot have columns that are not present in the parent.= It - is not possible to specify columns when creating partitions with - CREATE TABLE, nor is it possible to add columns= to - partitions after-the-fact using ALTER TABLE. T= ables may be - added as a partition with ALTER TABLE ... ATTACH PARTITIO= N - only if their columns exactly match the parent. - - - - - - You cannot drop the NOT NULL constraint on a - partition's column if the constraint is present in the parent tabl= e. - - - - - Partitions can also be foreign tables, although they have some limita= tions that normal tables do not; see for more information. = - - Updating the partition key of a row might cause it to be moved into a - different partition where this row satisfies the partition bounds. - - Example = @@ -3770,7 +3717,7 @@ CREATE TABLE measurement ( We know that most queries will access just the last week's, month's o= r quarter's data, since the main use of this table will be to prepare online reports for management. To reduce the amount of old data that - needs to be stored, we decide to only keep the most recent 3 years + needs to be stored, we decide to keep only the most recent 3 years worth of data. At the beginning of each month we will remove the olde= st month's data. In this situation we can use partitioning to help us m= eet all of our different requirements for the measurements table. @@ -3782,7 +3729,7 @@ CREATE TABLE measurement ( - Create measurement table as a partitioned + Create the measurement table as a partiti= oned table by specifying the PARTITION BY clause, wh= ich includes the partitioning method (RANGE in this case) and the list of column(s) to use as the partition key. @@ -3796,30 +3743,15 @@ CREATE TABLE measurement ( ) PARTITION BY RANGE (logdate); - - - You may decide to use multiple columns in the partition key for ra= nge - partitioning, if desired. Of course, this will often result in a = larger - number of partitions, each of which is individually smaller. On t= he - other hand, using fewer columns may lead to a coarser-grained - partitioning criteria with smaller number of partitions. A query - accessing the partitioned table will have to scan fewer partitions= if - the conditions involve some or all of these columns. - For example, consider a table range partitioned using columns - lastname and firstname (in that order) - as the partition key. - = - Create partitions. Each partition's definition must specify the b= ounds + Create partitions. Each partition's definition must specify bound= s that correspond to the partitioning method and partition key of th= e parent. Note that specifying bounds such that the new partition's - values will overlap with those in one or more existing partitions = will - cause an error. Inserting data into the parent table that does no= t map - to one of the existing partitions will cause an error; an appropri= ate - partition must be added manually. + values would overlap with those in one or more existing partitions= will + cause an error. = @@ -3827,13 +3759,6 @@ CREATE TABLE measurement ( PostgreSQL tables (or, possibly, foreign tables). It is possible to specify = a tablespace and storage parameters for each partition separately. - - - - It is not necessary to create table constraints describing partiti= on - boundary condition for partitions. Instead, partition constraints= are - generated implicitly from the partition bound specification whenev= er - there is need to refer to them. = CREATE TABLE measurement_y2006m02 PARTITION OF measurement @@ -3855,10 +3780,13 @@ CREATE TABLE measurement_y2008m01 PARTITION OF mea= surement WITH (parallel_workers =3D 4) TABLESPACE fasttablespace; + + (Recall that adjacent partitions can share a bound value, since + upper range bounds are treated as exclusive bounds.) = - To implement sub-partitioning, specify the + If you wish to implement sub-partitioning, again specify the PARTITION BY clause in the commands used to cre= ate individual partitions, for example: = @@ -3870,9 +3798,10 @@ CREATE TABLE measurement_y2006m02 PARTITION OF meas= urement = After creating partitions of measurement_y2006m02, any data inserted into measurement that i= s mapped to - measurement_y2006m02 (or data that is dir= ectly inserted - into measurement_y2006m02, provided it sa= tisfies its - partition constraint) will be further redirected to one of its + measurement_y2006m02 (or data that is + directly inserted into measurement_y2006m02, + which is allowed provided its partition constraint is satisfied) + will be further redirected to one of its partitions based on the peaktemp column= . The partition key specified may overlap with the parent's partition key, althoug= h care should be taken when specifying the bounds of a sub-partition @@ -3880,6 +3809,18 @@ CREATE TABLE measurement_y2006m02 PARTITION OF meas= urement the partition's own bounds allows; the system does not try to chec= k whether that's really the case. + + + Inserting data into the parent table that does not map + to one of the existing partitions will cause an error; an appropri= ate + partition must be added manually. + + + + It is not necessary to manually create table constraints describin= g + the partition boundary conditions for partitions. Such constraint= s + will be created automatically. + = @@ -3887,9 +3828,13 @@ CREATE TABLE measurement_y2006m02 PARTITION OF meas= urement Create an index on the key column(s), as well as any other indexes= you might want, on the partitioned table. (The key index is not strict= ly necessary, but in most scenarios it is helpful.) - This automatically creates - one index on each partition, and any partitions you create or atta= ch - later will also contain the index. + This automatically creates a matching index on each partition, and + any partitions you create or attach later will also have such an + index. + An index or unique constraint declared on a partitioned table + is virtual in the same way as the partitioned table + is: the actual data is in child indexes on the individual partitio= n + tables. = CREATE INDEX ON measurement (logdate); @@ -3920,7 +3865,7 @@ CREATE INDEX ON measurement (logdate); Normally the set of partitions established when initially defining = the table is not intended to remain static. It is common to want to - remove old partitions of data and periodically add new partitions f= or + remove partitions holding old data and periodically add new partiti= ons for new data. One of the most important advantages of partitioning is precisely that it allows this otherwise painful task to be executed nearly instantaneously by manipulating the partition structure, rat= her @@ -3969,8 +3914,10 @@ CREATE TABLE measurement_y2008m02 PARTITION OF meas= urement = As an alternative, it is sometimes more convenient to create the new table outside the partition structure, and make it a proper - partition later. This allows the data to be loaded, checked, and - transformed prior to it appearing in the partitioned table: + partition later. This allows new data to be loaded, checked, and + transformed prior to it appearing in the partitioned table. + The CREATE TABLE ... LIKE option is helpful + to avoid tediously repeating the parent table's definition: = CREATE TABLE measurement_y2008m02 @@ -3991,27 +3938,29 @@ ALTER TABLE measurement ATTACH PARTITION measureme= nt_y2008m02 Before running the ATTACH PARTITION command, it i= s recommended to create a CHECK constraint on the t= able to - be attached matching the desired partition constraint. That way, - the system will be able to skip the scan which is otherwise needed t= o validate the implicit + be attached that matches the expected partition constraint, as + illustrated above. That way, the system will be able to skip the sca= n + which is otherwise needed to validate the implicit partition constraint. Without the CHECK constrain= t, the table will be scanned to validate the partition constraint while - holding an ACCESS EXCLUSIVE lock on that partitio= n + holding both an ACCESS EXCLUSIVE lock on that par= tition and a SHARE UPDATE EXCLUSIVE lock on the parent t= able. - It may be desired to drop the redundant CHECK con= straint + It is recommended to drop the redundant CHECK con= straint after ATTACH PARTITION is finished. = As explained above, it is possible to create indexes on partitioned = tables - and they are applied automatically to the entire hierarchy. This is= very - convenient, as not only the existing partitions will become indexed,= but + so that they are applied automatically to the entire hierarchy. + This is very + convenient, as not only will the existing partitions become indexed,= but also any partitions that are created in the future will. One limita= tion is that it's not possible to use the CONCURRENTLY - qualifier when creating such a partitioned index. To overcome long = lock + qualifier when creating such a partitioned index. To avoid long loc= k times, it is possible to use CREATE INDEX ON ONLY the partitioned table; such an index is marked invalid, and the part= itions do not get the index applied automatically. The indexes on partitio= ns can - be created separately using CONCURRENTLY, and lat= er + be created individually using CONCURRENTLY, and t= hen attached to the index on the parent using ALTER INDEX .. ATTACH PARTITION. Once indexes fo= r all partitions are attached to the parent index, the parent index is mar= ked @@ -4048,18 +3997,22 @@ ALTER INDEX measurement_city_id_logdate_key - There is no way to create an - exclusion constraint spanning all partitions; it is only possible - to constrain each leaf partition individually. + Unique constraints (and hence primary keys) on partitioned tables = must + include all the partition key columns. This limitation exists bec= ause + the individual indexes making up the constraint can only directly + enforce uniqueness within their own partitions; therefore, the + partition structure itself must guarantee that there are not + duplicates in different partitions. = - Unique constraints (and hence primary keys) on partitioned tables = must - include all the partition key columns. This limitation exists bec= ause - PostgreSQL can only enforce - uniqueness in each partition individually. + There is no way to create an exclusion constraint spanning the + whole partitioned table. It is only possible to put such a + constraint on each leaf partition individually. Again, this + limitation stems from not being able to enforce cross-partition + restrictions. = @@ -4081,11 +4034,80 @@ ALTER INDEX measurement_city_id_logdate_key + + + Individual partitions are linked to their partitioned table using + inheritance behind-the-scenes. However, it is not possible to use + all of the generic features of inheritance with declaratively + partitioned tables or their partitions, as discussed below. Notably= , + a partition cannot have any parents other than the partitioned table + it is a partition of, nor can a table inherit from both a partitione= d + table and a regular table. That means partitioned tables and their + partitions never share an inheritance hierarchy with regular tables. + + + + Since a partition hierarchy consisting of the partitioned table and = its + partitions is still an inheritance hierarchy, all the normal rules o= f + inheritance apply as described in , w= ith + a few exceptions: + + + + + Both CHECK and NOT NULL + constraints of a partitioned table are always inherited by all it= s + partitions. CHECK constraints that are marked + NO INHERIT are not allowed to be created on + partitioned tables. + + + + + + Using ONLY to add or drop a constraint on only + the partitioned table is supported as long as there are no + partitions. Once partitions exist, using ONLY + will result in an error. Instead, constraints on the partitions + themselves can be added and (if they are not present in the paren= t + table) dropped. + + + + + + As a partitioned table does not have any data itself, attempts to= use + TRUNCATE ONLY on a partitio= ned + table will always return an error. + + + + + + Partitions cannot have columns that are not present in the parent= . It + is not possible to specify columns when creating partitions with + CREATE TABLE, nor is it possible to add column= s to + partitions after-the-fact using ALTER TABLE. = Tables may be + added as a partition with ALTER TABLE ... ATTACH PARTITI= ON + only if their columns exactly match the parent. + + + + + + You cannot drop a NOT NULL constraint on a + partition's column if the same constraint is present in the paren= t + table. + + + + = - - Implementation Using Inheritance + + Partitioning Using Inheritance + While the built-in declarative partitioning is suitable for most common use cases, there are some circumstances where a more flexible @@ -4135,8 +4157,8 @@ ALTER INDEX measurement_city_id_logdate_key Example = - We use the non-partitioned measurement - table above. To implement partitioning using inheritance, use + This example builds a partitioning structure equivalent to the + declarative partitioning example above. Use the following steps: = @@ -4148,7 +4170,16 @@ ALTER INDEX measurement_city_id_logdate_key to be applied equally to all child tables. There is no point in defining any indexes or unique constraints on it, either. For o= ur example, the root table is the measurement - table as originally defined. + table as originally defined: + + +CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int +); + = @@ -4195,10 +4226,7 @@ CHECK ( outletID BETWEEN 200 AND 300 ) This is wrong since it is not clear which child table the key value 200 belongs in. - - - - It would be better to instead create child tables as follows: + Instead, ranges should be defined in this style: = CREATE TABLE measurement_y2006m02 ( @@ -4271,7 +4299,7 @@ CREATE TRIGGER insert_measurement_trigger = We must redefine the trigger function each month so that it alwa= ys - points to the current child table. The trigger definition does + inserts into the current child table. The trigger definition do= es not need to be updated, however. = @@ -4736,7 +4764,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logda= te >=3D DATE '2008-01-01'; = - Declarative Partitioning Best Practices + Best Practices for Declarative Partitioning = The choice of how to partition a table should be made carefully as th= e @@ -4767,7 +4795,8 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logda= te >=3D DATE '2008-01-01'; which could result in low cache hit ratios. However, dividing the ta= ble into too many partitions can also cause issues. Too many partitions = can mean longer query planning times and higher memory consumption during= both - query planning and execution. When choosing how to partition your ta= ble, + query planning and execution, as further described below. + When choosing how to partition your table, it's also important to consider what changes may occur in the future.= For example, if you choose to have one partition per customer and you currently have a small number of large customers, consider the @@ -4781,13 +4810,15 @@ EXPLAIN SELECT count(*) FROM measurement WHERE log= date >=3D DATE '2008-01-01'; = Sub-partitioning can be useful to further divide partitions that are - expected to become larger than other partitions, although excessive - sub-partitioning can easily lead to large numbers of partitions and c= an - cause the same problems mentioned in the preceding paragraph. + expected to become larger than other partitions. + Another option is to use range partitioning with multiple columns in + the partition key. + Either of these can easily lead to excessive numbers of partitions, + so restraint is advisable. = - It is also important to consider the overhead of partitioning during + It is important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few thousand partitions fai= rly well, provided that typical queries allow the query planner to prune = all @@ -4810,8 +4841,8 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logda= te >=3D DATE '2008-01-01'; either of these two types of workload, it is important to make the ri= ght decisions early, as re-partitioning large quantities of data can be painfully slow. Simulations of the intended workload are often benef= icial - for optimizing the partitioning strategy. Never assume that more - partitions are better than fewer partitions and vice-versa. + for optimizing the partitioning strategy. Never just assume that mor= e + partitions are better than fewer partitions, nor vice-versa. = ------- =_aaaaaaaaaa0--