On Fri, Jun 01, 2018 at 03:00:10PM -0400, Alvaro Herrera wrote:
> On 2018-May-23, Justin Pryzby wrote:
>
> > There's two other, wider changes to consider:
...
>
> > - should we find a unified term for "inheritence-based partitioning" and
> > avoid
> > using the word "partitioning" in that context? For example:
> > "Partitioning
> > can be implemented using table inheritance[...]". One possible phrase
> > currently begin used is: "legacy inheritance method".
>
> Yeah, maybe it'd be a good time to do that. In particular I wondered
> whether the section title "Partitioning and Constraint Exclusion" should
> be changed somehow to note the fact that it's mostly for the legacy
> method.
I made changes to avoid "partition" (which I think should mean a child of
relkind='p', and itself of relkind='r') and "partitioned" (meaning relkind='p'
itself) but left alone most instances of "partitioning".
There's two issues. One is the unfortunately-named, recommended setting of
constraint_exclusion='partition' :(
And one is this, which I think should be disambiguated from native
list/range/hash partition bounds:
Use simple equality conditions for list partitioning, or simple range
tests for range partitioning, as illustrated in the preceding examples.
I'm short on words so maybe someone else can recommend language.
On Fri, Jun 01, 2018 at 02:57:22PM -0400, Alvaro Herrera wrote:
> Pushed. I made a couple of minor changes, in particular I added the
It looks like you also fixed a double negative - thanks.
Justin
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.)
</para>
</listitem>
@@ -3410,18 +3410,18 @@ ALTER TABLE measurement ATTACH PARTITION
measurement_y2008m02
<para>
We use the same <structname>measurement</structname> table we used
- above. To implement it as a partitioned table using inheritance, use
+ above. To implement partitioning using inheritance, use
the following steps:
<orderedlist spacing="compact">
<listitem>
<para>
Create the <quote>master</quote> table, from which all of the
- partitions will inherit. This table will contain no data. Do not
+ <quote>child</quote> 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 <structname>measurement</structname>
+ example, the master table is the <structname>measurement</structname>
table as originally defined.
</para>
</listitem>
@@ -3431,7 +3431,7 @@ ALTER TABLE measurement ATTACH PARTITION
measurement_y2008m02
Create several <quote>child</quote> 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
<productname>PostgreSQL</productname> tables (or foreign tables).
</para>
@@ -3449,8 +3449,8 @@ CREATE TABLE measurement_y2008m01 () INHERITS
(measurement);
<listitem>
<para>
- 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.
</para>
<para>
@@ -3461,18 +3461,18 @@ CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire',
'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
</programlisting>
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:
<programlisting>
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
</programlisting>
- 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.
</para>
<para>
- It would be better to instead create partitions as follows:
+ It would be better to instead create child tables as follows:
<programlisting>
CREATE TABLE measurement_y2006m02 (
@@ -3501,7 +3501,7 @@ CREATE TABLE measurement_y2008m01 (
<listitem>
<para>
- 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.
<programlisting>
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
@@ -3517,9 +3517,9 @@ CREATE INDEX measurement_y2008m01_logdate ON
measurement_y2008m01 (logdate);
<para>
We want our application to be able to say <literal>INSERT INTO
measurement ...</literal> 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:
<programlisting>
@@ -3545,13 +3545,13 @@ CREATE TRIGGER insert_measurement_trigger
</programlisting>
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.
</para>
<para>
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:
<programlisting>
@@ -3579,7 +3579,7 @@ LANGUAGE plpgsql;
The trigger definition is the same as before.
Note that each <literal>IF</literal> test must exactly match the
- <literal>CHECK</literal> constraint for its partition.
+ <literal>CHECK</literal> constraint for its child table.
</para>
<para>
@@ -3590,8 +3590,8 @@ LANGUAGE plpgsql;
<note>
<para>
- 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.
</para>
@@ -3599,7 +3599,7 @@ LANGUAGE plpgsql;
<para>
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:
<programlisting>
@@ -3625,7 +3625,7 @@ DO INSTEAD
<para>
Be aware that <command>COPY</command> ignores rules. If you want to
use <command>COPY</command> to insert data, you'll need to copy into
the
- correct partition table rather than into the master.
<command>COPY</command>
+ correct child table rather than directly into the master.
<command>COPY</command>
does fire triggers, so you can use it normally if you use the trigger
approach.
</para>
@@ -3641,25 +3641,25 @@ DO INSTEAD
<para>
Ensure that the <xref linkend="guc-constraint-exclusion"/>
configuration parameter is not disabled in
- <filename>postgresql.conf</filename>.
- If it is, queries will not be optimized as desired.
+ <filename>postgresql.conf</filename>, otherwise
+ child tables may be accessed unnecessarily.
</para>
</listitem>
</orderedlist>
</para>
<para>
- 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.
</para>
</sect3>
<sect3 id="ddl-partitioning-inheritance-maintenance">
- <title>Partition Maintenance</title>
+ <title>Maintenance for Inheritence Partitioning</title>
<para>
- 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:
<programlisting>
DROP TABLE measurement_y2006m02;
@@ -3667,7 +3667,7 @@ DROP TABLE measurement_y2006m02;
</para>
<para>
- 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:
<programlisting>
@@ -3676,8 +3676,8 @@ ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
</para>
<para>
- 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:
<programlisting>
CREATE TABLE measurement_y2008m02 (
@@ -3685,9 +3685,11 @@ CREATE TABLE measurement_y2008m02 (
) INHERITS (measurement);
</programlisting>
- 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.
+
<programlisting>
CREATE TABLE measurement_y2008m02
@@ -3705,7 +3707,7 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
<title>Caveats</title>
<para>
- The following caveats apply to partitioned tables implemented using
+ The following caveats apply to partitioning implemented using
inheritance:
<itemizedlist>
<listitem>
@@ -3713,19 +3715,20 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
There is no automatic way to verify that all of the
<literal>CHECK</literal> 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.
</para>
</listitem>
<listitem>
<para>
- 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 <command>UPDATE</command> 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 <command>UPDATE</command> that attempts
to do that will fail because of the <literal>CHECK</literal>
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.
</para>
</listitem>
@@ -3734,7 +3737,7 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
<para>
If you are using manual <command>VACUUM</command> or
<command>ANALYZE</command> 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:
<programlisting>
ANALYZE measurement;
</programlisting>
@@ -3754,7 +3757,7 @@ ANALYZE measurement;
<listitem>
<para>
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';
<para>
<firstterm>Constraint exclusion</firstterm> 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.
</para>
@@ -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 <literal>CHECK</literal>
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 <literal>CHECK</literal>
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.
</para>
<para>
@@ -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
<function>CURRENT_TIMESTAMP</function> 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.
</para>
</listitem>
@@ -3984,7 +3987,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate
>= DATE '2008-01-01';
<listitem>
<para>
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';
<listitem>
<para>
- 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.
</para>
</listitem>
