diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 0258391..d919818 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3387,8 +3387,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user's choosing. (Note, however, that if constraint - exclusion is unable to prune partitions effectively, query performance - will be very poor.) + exclusion is unable to prune child tables effectively, query performance + may be poor.) @@ -3410,18 +3410,18 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 We use the same measurement table we used - above. To implement it as a partitioned table using inheritance, use + above. To implement partitioning using inheritance, use the following steps: Create the master table, from which all of the - partitions will inherit. This table will contain no data. Do not + child tables will inherit. This table will contain no data. Do not define any check constraints on this table, unless you intend them - to be applied equally to all partitions. There is no point in + to be applied equally to all child tables. There is no point in defining any indexes or unique constraints on it, either. For our - example, master table is the measurement + example, the master table is the measurement table as originally defined. @@ -3431,7 +3431,7 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 Create several child tables that each inherit from the master table. Normally, these tables will not add any columns to the set inherited from the master. Just as with declarative - partitioning, these partitions are in every way normal + partitioning, these tables are in every way normal PostgreSQL tables (or foreign tables). @@ -3449,8 +3449,8 @@ CREATE TABLE measurement_y2008m01 () INHERITS (measurement); - Add non-overlapping table constraints to the partition tables to - define the allowed key values in each partition. + Add non-overlapping table constraints to the child tables to + define the allowed key values in each. @@ -3461,18 +3461,18 @@ CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 ) Ensure that the constraints guarantee that there is no overlap - between the key values permitted in different partitions. A common + between the key values permitted in different child tables. A common mistake is to set up range constraints like: CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 ) - This is wrong since it is not clear which partition the key value - 200 belongs in. + This is wrong since it is not clear into which child table the key + value 200 belongs. - It would be better to instead create partitions as follows: + It would be better to instead create child tables as follows: CREATE TABLE measurement_y2006m02 ( @@ -3501,7 +3501,7 @@ CREATE TABLE measurement_y2008m01 ( - For each partition, create an index on the key column(s), + For each child table, create an index on the key column(s), as well as any other indexes you might want. CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); @@ -3517,9 +3517,9 @@ CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate); We want our application to be able to say INSERT INTO measurement ... and have the data be redirected into the - appropriate partition table. We can arrange that by attaching + appropriate child table. We can arrange that by attaching a suitable trigger function to the master table. - If data will be added only to the latest partition, we can + If data will be added only to the latest child, we can use a very simple trigger function: @@ -3545,13 +3545,13 @@ CREATE TRIGGER insert_measurement_trigger We must redefine the trigger function each month so that it always - points to the current partition. The trigger definition does + points to the current child table. The trigger definition does not need to be updated, however. We might want to insert data and have the server automatically - locate the partition into which the row should be added. We + locate the child table into which the row should be added. We could do this with a more complex trigger function, for example: @@ -3579,7 +3579,7 @@ LANGUAGE plpgsql; The trigger definition is the same as before. Note that each IF test must exactly match the - CHECK constraint for its partition. + CHECK constraint for its child table. @@ -3590,8 +3590,8 @@ LANGUAGE plpgsql; - In practice it might be best to check the newest partition first, - if most inserts go into that partition. For simplicity we have + In practice, it might be best to check the newest child first, + if most inserts go into that child. For simplicity, we have shown the trigger's tests in the same order as in other parts of this example. @@ -3599,7 +3599,7 @@ LANGUAGE plpgsql; A different approach to redirecting inserts into the appropriate - partition table is to set up rules, instead of a trigger, on the + child table is to set up rules, instead of a trigger, on the master table. For example: @@ -3625,7 +3625,7 @@ DO INSTEAD Be aware that COPY ignores rules. If you want to use COPY to insert data, you'll need to copy into the - correct partition table rather than into the master. COPY + correct child table rather than directly into the master. COPY does fire triggers, so you can use it normally if you use the trigger approach. @@ -3641,25 +3641,25 @@ DO INSTEAD Ensure that the configuration parameter is not disabled in - postgresql.conf. - If it is, queries will not be optimized as desired. + postgresql.conf, otherwise + child tables may be accessed unnecessarily. - As we can see, a complex partitioning scheme could require a + As we can see, a complex table hierarchy could require a substantial amount of DDL. In the above example we would be creating - a new partition each month, so it might be wise to write a script that + a new child table each month, so it might be wise to write a script that generates the required DDL automatically. - Partition Maintenance + Maintenance for Inheritence Partitioning - To remove old data quickly, simply drop the partition that is no longer + To remove old data quickly, simply drop the child table that is no longer necessary: DROP TABLE measurement_y2006m02; @@ -3667,7 +3667,7 @@ DROP TABLE measurement_y2006m02; - To remove the partition from the partitioned table but retain access to + To remove the child table from the inheritence hierarchy table but retain access to it as a table in its own right: @@ -3676,8 +3676,8 @@ ALTER TABLE measurement_y2006m02 NO INHERIT measurement; - To add a new partition to handle new data, create an empty partition - just as the original partitions were created above: + To add a new child table to handle new data, create an empty child table + just as the original children were created above: CREATE TABLE measurement_y2008m02 ( @@ -3685,9 +3685,11 @@ CREATE TABLE measurement_y2008m02 ( ) INHERITS (measurement); - Alternatively, one may want to create the new table outside the partition - structure, and make it a partition after the data is loaded, checked, - and transformed. + Alternatively, one may want to create and populate the new child table + before adding it to the table hierarchy — this could allow data to be + loaded, checked, and transformed before being made visible to queries on the + parent table. + CREATE TABLE measurement_y2008m02 @@ -3705,7 +3707,7 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement; Caveats - The following caveats apply to partitioned tables implemented using + The following caveats apply to partitioning implemented using inheritance: @@ -3713,19 +3715,20 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement; There is no automatic way to verify that all of the CHECK constraints are mutually exclusive. It is safer to create code that generates - partitions and creates and/or modifies associated objects than + child tables and creates and/or modifies associated objects than to write each by hand. - The schemes shown here assume that the partition key column(s) - of a row never change, or at least do not change enough to require - it to move to another partition. An UPDATE that attempts + The schemes shown here assume that the values of a row's key column(s) + never change, or at least do not change enough to make it inconsistent + with the constraints on its table. + An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers - on the partition tables, but it makes management of the structure + on the child tables, but it makes management of the structure much more complicated. @@ -3734,7 +3737,7 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement; If you are using manual VACUUM or ANALYZE commands, don't forget that - you need to run them on each partition individually. A command like: + you need to run them on each child table individually. A command like: ANALYZE measurement; @@ -3754,7 +3757,7 @@ ANALYZE measurement; Triggers or rules will be needed to route rows to the desired - partition, unless the application is explicitly aware of the + child table, unless the application is explicitly aware of the partitioning scheme. Triggers may be complicated to write, and will be much slower than the tuple routing performed internally by declarative partitioning. @@ -3925,7 +3928,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; Constraint exclusion is a query optimization technique similar to partition pruning. While it is primarily used - for partitioned tables using the legacy inheritance method, it can be + for partitioning implemented using the legacy inheritance method, it can be used for other purposes, including with declarative partitioning. @@ -3933,7 +3936,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; Constraint exclusion works in a very similar way to partition pruning, except that it uses each table's CHECK constraints — which gives it its name — whereas partition - pruning uses the table's partition bounds, which exists only in the + pruning uses the table's partition bounds, which exist only in the case of declarative partitioning. Another difference is that constraint exclusion is only applied at plan time; there is no attempt to remove partitions at execution time. @@ -3943,9 +3946,9 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; The fact that constraint exclusion uses CHECK constraints, which makes it slow compared to partition pruning, can sometimes be used as an advantage: because constraints can be defined - even on declaratively-partitioned tables, in addition to the internal - partitioning constraints, and only constraint exclusion would be able - to elide certain partitions from the query plan using those. + even on declaratively-partitioned tables, in addition to their internal + partition bounds, constraint exclusion may be able + to elide additional partitions from the query plan. @@ -3976,7 +3979,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the - planner cannot know which partition the function's value might fall + planner cannot know which child table the function's value might fall into at run time. @@ -3984,7 +3987,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; Keep the partitioning constraints simple, else the planner may not be - able to prove that partitions don't need to be visited. Use simple + able to prove that child tables may not need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should @@ -3996,11 +3999,11 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; - All constraints on all partitions of the master table are examined - during constraint exclusion, so large numbers of partitions are likely + All constraints on all children of the parent table are examined + during constraint exclusion, so large numbers of children are likely to increase query planning time considerably. So the legacy inheritance based partitioning will work well with up to perhaps a - hundred partitions; don't try to use many thousands of partitions. + hundred child tables; don't try to use many thousands of children.