0009-Update-DDL-Partitioning-chapter-to-reflect-new-devel-10.patch
text/x-diff
Filename: 0009-Update-DDL-Partitioning-chapter-to-reflect-new-devel-10.patch
Type: text/x-diff
Part: 8
Patch
Format: unified
Series: patch 0009
| File | + | − |
|---|---|---|
| doc/src/sgml/ddl.sgml | 83 | 319 |
>From 1a12495e49c35b99d07f9a5a7b5997a3c8948916 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Thu, 28 Jul 2016 13:40:02 +0900
Subject: [PATCH 9/9] Update DDL Partitioning chapter to reflect new developments.
---
doc/src/sgml/ddl.sgml | 402 ++++++++++---------------------------------------
1 files changed, 83 insertions(+), 319 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 157512c..fe76ab0 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2771,7 +2771,7 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning design.
- <command>ALTER TABLE NO INHERIT</> and <command>DROP TABLE</> are
+ <command>ALTER TABLE DETACH PARTITION</> and <command>DROP TABLE</> are
both far faster than a bulk operation.
These commands also entirely avoid the <command>VACUUM</command>
overhead caused by a bulk <command>DELETE</>.
@@ -2793,12 +2793,15 @@ VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
- Currently, <productname>PostgreSQL</productname> supports partitioning
- via table inheritance. Each partition must be created as a child
- table of a single parent table. The parent table itself is normally
- empty; it exists just to represent the entire data set. You should be
- familiar with inheritance (see <xref linkend="ddl-inherit">) before
- attempting to set up partitioning.
+ Currently, <productname>PostgreSQL</productname> provides a way to
+ specify the partition key of table along with two methods of partitioning
+ to choose from. Individual partitions of a partitioned table are created
+ using separate <literal>CREATE TABLE</> commands where you must specify
+ the partition bound such that it does not overlap with any existing
+ partitions of the parent table. The parent table itself is empty;
+ it exists just to represent the entire data set. See <xref
+ linkend="sql-createtable"> and <xref linkend="sql-createforeigntable">
+ for more details on the exact syntax to use for above mentioned commands.
</para>
<para>
@@ -2842,59 +2845,22 @@ VALUES ('Albany', NULL, NULL, 'NY');
<orderedlist spacing="compact">
<listitem>
<para>
- Create the <quote>master</quote> table, from which all of the
- partitions will inherit.
+ Create the <quote>partitioned</quote> table.
</para>
<para>
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 defining any indexes or unique constraints on it, either.
+ in defining any indexes or unique constraints on it, either,
+ since the notion of global uniqueness is not yet implemented.
</para>
</listitem>
<listitem>
<para>
- 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.
- </para>
-
- <para>
- We will refer to the child tables as partitions, though they
- are in every way normal <productname>PostgreSQL</> tables
- (or, possibly, foreign tables).
- </para>
- </listitem>
-
- <listitem>
- <para>
- Add table constraints to the partition tables to define the
- allowed key values in each partition.
- </para>
-
- <para>
- Typical examples would be:
-<programlisting>
-CHECK ( x = 1 )
-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
- 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.
- </para>
-
- <para>
- Note that there is no difference in
- syntax between range and list partitioning; those terms are
- descriptive only.
+ Create several <quote>partitions</quote> of the above created
+ partitioned table. Partitions are in every way normal
+ <productname>PostgreSQL</> tables (or, possibly, foreign tables).
</para>
</listitem>
@@ -2911,8 +2877,10 @@ CHECK ( outletID BETWEEN 200 AND 300 )
<listitem>
<para>
- Optionally, define a trigger or rule to redirect data inserted into
- the master table to the appropriate partition.
+ Note that a data row inserted into the master table will be mapped
+ to and stored in the appropriate partition. If some row does not
+ fall within any of existing partitions, an error will be thrown.
+ You must create the missing partition explicitly.
</para>
</listitem>
@@ -2940,7 +2908,7 @@ CREATE TABLE measurement (
logdate date not null,
peaktemp int,
unitsales int
-);
+) PARTITION BY RANGE (logdate);
</programlisting>
We know that most queries will access just the last week's, month's or
@@ -2971,12 +2939,12 @@ CREATE TABLE measurement (
Next we create one partition for each active month:
<programlisting>
-CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
-CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2016m07 PARTITION OF measurement FOR VALUES START ('2016-07-01') END ('2016-08-01');
+CREATE TABLE measurement_y2016m08 PARTITION OF measurement FOR VALUES START ('2016-08-01') END ('2016-09-01');
...
-CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
-CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
-CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2017m04 PARTITION OF measurement FOR VALUES START ('2017-04-01') END ('2017-05-01');
+CREATE TABLE measurement_y2017m05 PARTITION OF measurement FOR VALUES START ('2017-05-01') END ('2017-06-01');
+CREATE TABLE measurement_y2017m06 PARTITION OF measurement FOR VALUES START ('2017-06-01') END ('2017-07-01');
</programlisting>
Each of the partitions are complete tables in their own right,
@@ -2986,36 +2954,9 @@ CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
<para>
This solves one of our problems: deleting old data. Each
- month, all we will need to do is perform a <command>DROP
- TABLE</command> on the oldest child table and create a new
- child table for the new month's data.
- </para>
- </listitem>
-
- <listitem>
- <para>
- We must provide non-overlapping table constraints. Rather than
- just creating the partition tables as above, the table creation
- script should really be:
-
-<programlisting>
-CREATE TABLE measurement_y2006m02 (
- CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
-) INHERITS (measurement);
-CREATE TABLE measurement_y2006m03 (
- CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
-) INHERITS (measurement);
-...
-CREATE TABLE measurement_y2007m11 (
- CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
-) INHERITS (measurement);
-CREATE TABLE measurement_y2007m12 (
- CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
-) INHERITS (measurement);
-CREATE TABLE measurement_y2008m01 (
- CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
-) INHERITS (measurement);
-</programlisting>
+ month, all we will need to do is perform a <command>ALTER TABLE
+ measurement DETACH PARTITION</command> on the oldest child table
+ and create a new partition for the new month's data.
</para>
</listitem>
@@ -3024,110 +2965,19 @@ CREATE TABLE measurement_y2008m01 (
We probably need indexes on the key columns too:
<programlisting>
-CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
-CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
+CREATE INDEX measurement_y2016m07_logdate ON measurement_y2016m07 (logdate);
+CREATE INDEX measurement_y2016m08_logdate ON measurement_y2016m08 (logdate);
...
-CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
-CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
-CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
+CREATE INDEX measurement_y2017m04_logdate ON measurement_y2017m04 (logdate);
+CREATE INDEX measurement_y2017m05_logdate ON measurement_y2017m05 (logdate);
+CREATE INDEX measurement_y2017m06_logdate ON measurement_y2017m06 (logdate);
</programlisting>
We choose not to add further indexes at this time.
</para>
</listitem>
-
- <listitem>
- <para>
- We want our application to be able to say <literal>INSERT INTO
- measurement ...</> and have the data be redirected into the
- appropriate partition 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
- use a very simple trigger function:
-
-<programlisting>
-CREATE OR REPLACE FUNCTION measurement_insert_trigger()
-RETURNS TRIGGER AS $$
-BEGIN
- INSERT INTO measurement_y2008m01 VALUES (NEW.*);
- RETURN NULL;
-END;
-$$
-LANGUAGE plpgsql;
-</programlisting>
-
- After creating the function, we create a trigger which
- calls the trigger function:
-
-<programlisting>
-CREATE TRIGGER insert_measurement_trigger
- BEFORE INSERT ON measurement
- FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
-</programlisting>
-
- We must redefine the trigger function each month so that it always
- points to the current partition. 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
- could do this with a more complex trigger function, for example:
-
-<programlisting>
-CREATE OR REPLACE FUNCTION measurement_insert_trigger()
-RETURNS TRIGGER AS $$
-BEGIN
- IF ( NEW.logdate >= DATE '2006-02-01' AND
- NEW.logdate < DATE '2006-03-01' ) THEN
- INSERT INTO measurement_y2006m02 VALUES (NEW.*);
- ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
- NEW.logdate < DATE '2006-04-01' ) THEN
- INSERT INTO measurement_y2006m03 VALUES (NEW.*);
- ...
- ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
- NEW.logdate < DATE '2008-02-01' ) THEN
- INSERT INTO measurement_y2008m01 VALUES (NEW.*);
- ELSE
- RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
- END IF;
- RETURN NULL;
-END;
-$$
-LANGUAGE plpgsql;
-</programlisting>
-
- 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.
- </para>
-
- <para>
- While this function is more complex than the single-month case,
- it doesn't need to be updated as often, since branches can be
- added in advance of being needed.
- </para>
-
- <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
- shown the trigger's tests in the same order as in other parts
- of this example.
- </para>
- </note>
- </listitem>
</orderedlist>
</para>
-
- <para>
- As we can see, a complex partitioning scheme 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 generates the required DDL automatically.
- </para>
-
</sect2>
<sect2 id="ddl-partitioning-managing-partitions">
@@ -3145,22 +2995,17 @@ LANGUAGE plpgsql;
</para>
<para>
- The simplest option for removing old data is simply to drop the partition
+ The simplest option for removing old data is simply detach the partition
that is no longer necessary:
<programlisting>
-DROP TABLE measurement_y2006m02;
+ALTER TABLE measurement DETACH PARTITION measurement_y2016m07;
</programlisting>
+
This can very quickly delete millions of records because it doesn't have
to individually delete every record.
- </para>
- <para>
- Another option that is often preferable is to remove the partition from
- the partitioned table but retain access to it as a table in its own
- right:
-<programlisting>
-ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
-</programlisting>
+ The detached partition continues to exist as a regular table, which if
+ necessary can be dropped using regular <command>DROP TABLE</> command.
This allows further operations to be performed on the data before
it is dropped. For example, this is often a useful time to back up
the data using <command>COPY</>, <application>pg_dump</>, or
@@ -3175,9 +3020,7 @@ ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
were created above:
<programlisting>
-CREATE TABLE measurement_y2008m02 (
- CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
-) INHERITS (measurement);
+CREATE TABLE measurement_y2017m07 PARTITION OF measurement FOR VALUES START ('2017-07-01') END ('2017-08-01');
</programlisting>
As an alternative, it is sometimes more convenient to create the
@@ -3186,13 +3029,15 @@ CREATE TABLE measurement_y2008m02 (
transformed prior to it appearing in the partitioned table:
<programlisting>
-CREATE TABLE measurement_y2008m02
+CREATE TABLE measurement_y2017m07
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
- CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
-\copy measurement_y2008m02 from 'measurement_y2008m02'
+ALTER TABLE measurement_y2017m07 ADD CONSTRAINT y2017m07
+ CHECK ( logdate >= DATE '2017-07-01' AND logdate < DATE '2017-08-01' );
+\copy measurement_y2017m07 from 'measurement_y2017m07'
+ALTER TABLE measurement_y2017m07 DROP CONSTRAINT y2017m07;
-- possibly some other data preparation work
-ALTER TABLE measurement_y2008m02 INHERIT measurement;
+ALTER TABLE measurement
+ ATTACH PARTITION measurement_y2017m07 FOR VALUES START ('2017-07-01') END ('2017-08-01');
</programlisting>
</para>
</sect2>
@@ -3211,7 +3056,7 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
<programlisting>
SET constraint_exclusion = on;
-SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+SELECT count(*) FROM measurement WHERE logdate >= DATE '2017-01-01';
</programlisting>
Without constraint exclusion, the above query would scan each of
@@ -3220,7 +3065,9 @@ SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
partition and try to prove that the partition need not
be scanned because it could not contain any rows meeting the query's
<literal>WHERE</> clause. When the planner can prove this, it
- excludes the partition from the query plan.
+ excludes the partition from the query plan. Note that the aforementioned
+ constraints need not be explicitly created; they are internally derived
+ from the partition bound metadata.
</para>
<para>
@@ -3230,23 +3077,23 @@ SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<programlisting>
SET constraint_exclusion = off;
-EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
-
- QUERY PLAN
------------------------------------------------------------------------------------------------
- Aggregate (cost=158.66..158.68 rows=1 width=0)
- -> Append (cost=0.00..151.88 rows=2715 width=0)
- -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
- -> Seq Scan on measurement_y2006m02 measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
- -> Seq Scan on measurement_y2006m03 measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
+EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2018-07-01';
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Aggregate (cost=866.69..866.70 rows=1 width=8)
+ -> Append (cost=0.00..828.12 rows=15426 width=0)
+ -> Seq Scan on measurement (cost=0.00..0.00 rows=1 width=0)
+ Filter: (logdate >= '2017-01-01'::date)
+ -> Seq Scan on measurement_y2016m07 (cost=0.00..33.12 rows=617 width=0)
+ Filter: (logdate >= '2017-01-01'::date)
+ -> Seq Scan on measurement_y2016m08 (cost=0.00..33.12 rows=617 width=0)
+ Filter: (logdate >= '2017-01-01'::date)
...
- -> Seq Scan on measurement_y2007m12 measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
- -> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
+ -> Seq Scan on measurement_y2018m06 (cost=0.00..33.12 rows=617 width=0)
+ Filter: (logdate >= '2017-01-01'::date)
+ -> Seq Scan on measurement_y2018m07 (cost=0.00..33.12 rows=617 width=0)
+ Filter: (logdate >= '2017-01-01'::date)
</programlisting>
Some or all of the partitions might use index scans instead of
@@ -3257,15 +3104,15 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<programlisting>
SET constraint_exclusion = on;
-EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
- QUERY PLAN
------------------------------------------------------------------------------------------------
- Aggregate (cost=63.47..63.48 rows=1 width=0)
- -> Append (cost=0.00..60.75 rows=1086 width=0)
- -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
- -> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)
- Filter: (logdate >= '2008-01-01'::date)
+EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2018-07-01';
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Aggregate (cost=34.67..34.68 rows=1 width=8)
+ -> Append (cost=0.00..33.12 rows=618 width=0)
+ -> Seq Scan on measurement (cost=0.00..0.00 rows=1 width=0)
+ Filter: (logdate >= '2018-07-01'::date)
+ -> Seq Scan on measurement_y2018m07 (cost=0.00..33.12 rows=617 width=0)
+ Filter: (logdate >= '2018-07-01'::date)
</programlisting>
</para>
@@ -3292,93 +3139,22 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
</sect2>
- <sect2 id="ddl-partitioning-alternatives">
- <title>Alternative Partitioning Methods</title>
-
- <para>
- A different approach to redirecting inserts into the appropriate
- partition table is to set up rules, instead of a trigger, on the
- master table. For example:
-
-<programlisting>
-CREATE RULE measurement_insert_y2006m02 AS
-ON INSERT TO measurement WHERE
- ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
-DO INSTEAD
- INSERT INTO measurement_y2006m02 VALUES (NEW.*);
-...
-CREATE RULE measurement_insert_y2008m01 AS
-ON INSERT TO measurement WHERE
- ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
-DO INSTEAD
- INSERT INTO measurement_y2008m01 VALUES (NEW.*);
-</programlisting>
-
- A rule has significantly more overhead than a trigger, but the overhead
- is paid once per query rather than once per row, so this method might be
- advantageous for bulk-insert situations. In most cases, however, the
- trigger method will offer better performance.
- </para>
-
- <para>
- Be aware that <command>COPY</> ignores rules. If you want to
- use <command>COPY</> to insert data, you'll need to copy into the correct
- partition table rather than into the master. <command>COPY</> does fire
- triggers, so you can use it normally if you use the trigger approach.
- </para>
-
- <para>
- Another disadvantage of the rule approach is that there is no simple
- way to force an error if the set of rules doesn't cover the insertion
- date; the data will silently go into the master table instead.
- </para>
-
- <para>
- Partitioning can also be arranged using a <literal>UNION ALL</literal>
- view, instead of table inheritance. For example,
-
-<programlisting>
-CREATE VIEW measurement AS
- SELECT * FROM measurement_y2006m02
-UNION ALL SELECT * FROM measurement_y2006m03
-...
-UNION ALL SELECT * FROM measurement_y2007m11
-UNION ALL SELECT * FROM measurement_y2007m12
-UNION ALL SELECT * FROM measurement_y2008m01;
-</programlisting>
-
- However, the need to recreate the view adds an extra step to adding and
- dropping individual partitions of the data set. In practice this
- method has little to recommend it compared to using inheritance.
- </para>
-
- </sect2>
-
<sect2 id="ddl-partitioning-caveats">
<title>Caveats</title>
<para>
The following caveats apply to partitioned tables:
<itemizedlist>
- <listitem>
- <para>
- 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
- 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</> that attempts
- to do that will fail because of the <literal>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
- much more complicated.
+ to do that will fail because of applying internally created <literal>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 much more complicated.
</para>
</listitem>
@@ -3397,9 +3173,9 @@ ANALYZE measurement;
<listitem>
<para>
<command>INSERT</command> statements with <literal>ON CONFLICT</>
- clauses are unlikely to work as expected, as the <literal>ON CONFLICT</>
- action is only taken in case of unique violations on the specified
- target relation, not its child relations.
+ clauses are currently unsupported on partitioned tables as there is
+ currently no reliable way to check global uniqueness across all the
+ partitions.
</para>
</listitem>
@@ -3423,18 +3199,6 @@ ANALYZE measurement;
<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
- 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
- contain only comparisons of the partitioning column(s) to constants
- using B-tree-indexable operators.
- </para>
- </listitem>
-
- <listitem>
- <para>
All constraints on all partitions of the master table are examined
during constraint exclusion, so large numbers of partitions are likely
to increase query planning time considerably. Partitioning using
--
1.7.1