On 2017/02/28 17:25, Simon Riggs wrote:
> On 28 February 2017 at 08:14, Amit Langote
> <[email protected]> wrote:
>
>> OK. So, I will start writing the patch with above general skeleton and
>> hopefully post it within this week and you can improve it as fit.
>
> Will do, thanks.
Attached patch 0001 is what I managed so far. Please take a look and let
me know if there is more I can do. I guess you might want to expand the
parts related to UNION ALL views and BRIN indexes.
Also for consideration,
0002: some cosmetic fixes to create_table.sgml
0003: add clarification about NOT NULL constraint on partition columns in
alter_table.sgml
Thoughts?
Thanks,
Amit
>From 97c104054310c9361623c182497d708ed65bf65f Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Fri, 3 Mar 2017 16:39:24 +0900
Subject: [PATCH 1/3] Rewrite sections in ddl.sgml related to partitioning
Merge sections Partitioned Tables and Partitioning into one section
called Table Partitioning and Related Solutions.
---
doc/src/sgml/ddl.sgml | 1359 +++++++++++++++++++++++++------------------------
1 file changed, 707 insertions(+), 652 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 09b5b3ff70..a2dd39df54 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2772,14 +2772,181 @@ VALUES ('Albany', NULL, NULL, 'NY');
</sect2>
</sect1>
- <sect1 id="ddl-partitioned-tables">
- <title>Partitioned Tables</title>
+ <sect1 id="ddl-partitioning">
+ <title>Table Partitioning and Related Solutions</title>
+
+ <indexterm>
+ <primary>partitioning</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>table</primary>
+ <secondary>partitioning</secondary>
+ </indexterm>
<indexterm>
<primary>partitioned table</primary>
</indexterm>
<para>
+ <productname>PostgreSQL</productname> supports basic table
+ partitioning. This section describes why and how to implement
+ partitioning as part of your database design.
+ </para>
+
+ <sect2 id="ddl-partitioning-overview">
+ <title>Overview</title>
+
+ <para>
+ Partitioning refers to splitting what is logically one large table into
+ smaller physical pieces. Partitioning can provide several benefits:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Query performance can be improved dramatically in certain situations,
+ particularly when most of the heavily accessed rows of the table are in a
+ single partition or a small number of partitions. The partitioning
+ substitutes for leading columns of indexes, reducing index size and
+ making it more likely that the heavily-used parts of the indexes
+ fit in memory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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
+ index and random access reads scattered across the whole table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Bulk loads and deletes can be accomplished by adding or removing
+ partitions, if that requirement is planned into the partitioning design.
+ Doing <command>ALTER TABLE DETACH PARTITION</> followed by
+ <command>DROP TABLE</> is far faster than a bulk operation. These
+ commands also entirely avoid the <command>VACUUM</command> overhead
+ caused by a bulk <command>DELETE</>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Seldom-used data can be migrated to cheaper and slower storage media.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ The 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 physical
+ memory of the database server.
+ </para>
+
+ <para>
+ The following forms of partitioning can be implemented in
+ <productname>PostgreSQL</productname>:
+
+ <variablelist>
+ <varlistentry>
+ <term>Range Partitioning</term>
+
+ <listitem>
+ <para>
+ The table is partitioned into <quote>ranges</quote> defined
+ by a key column or set of columns, with no overlap between
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>List Partitioning</term>
+
+ <listitem>
+ <para>
+ The table is partitioned by explicitly listing which key values
+ appear in each partition.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ The following partitioning methods are currently supported:
+
+ <variablelist>
+ <varlistentry>
+ <term>Declarative Partitioning</term>
+
+ <listitem>
+ <para>
+ One creates a <firstterm>partitioned table</firstterm> by specifying
+ the partitioning method and a set of columns as the partition key.
+ <firstterm>Partitions</firstterm>, which contain actual data inserted
+ into the table, are created by specifying what subset of the data it
+ accepts.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>Using inheritance</term>
+
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>Using UNION ALL views</term>
+
+ <listitem>
+ <para>
+ One can define a <literal>UNION ALL</literal> view over
+ <literal>SELECT</literal> on individual tables, each of which
+ contains a partition of data. Partitions are added or removed
+ by updating the view definition.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>Accessing Tables using BRIN Indexes</term>
+
+ <listitem>
+ <para>
+ <acronym>BRIN</acronym>, which stands for Block Range Index is,
+ designed for handling very large tables in which certain columns
+ have some natural physical location within the table. Scanning
+ a large table using a <acronym>BRIN</acronym> index results in
+ reading only a portion of the table, which is often why partitioning
+ is implemented.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Each of the above mentioned methods is described below.
+ </para>
+ </sect2>
+
+ <sect2 id="ddl-partitioning-declarative">
+ <title>Declarative Partitioning</title>
+
+ <para>
PostgreSQL offers a way to specify how to divide a table into pieces
called partitions. The table that is divided is referred to as a
<firstterm>partitioned table</firstterm>. The specification consists
@@ -2790,25 +2957,29 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
All rows inserted into a partitioned table will be routed to one of the
<firstterm>partitions</firstterm> based on the value of the partition
- key. Each partition has a subset defined by its <firstterm>partition
- bounds</firstterm>. Currently supported partitioning methods include
- range and list, wherein each partition is assigned a range of keys or
- a list of keys, respectively.
+ key. Each partition has a subset of the data defined by its
+ <firstterm>partition bounds</firstterm>. Currently supported
+ partitioning methods include range and list, where each partition is
+ assigned a range of keys and a list of keys, respectively.
</para>
<para>
Partitions may have their own indexes, constraints and default values,
- distinct from other partitions. Partitions do not inherit indexes from
- the partitioned table.
+ distinct from other partitions. Partitions do not currently inherit
+ indexes from the partitioned table.
+ </para>
+
+ <para>
+ See <xref linkend="sql-createtable"> for more details creating partitioned
+ tables and partitions.
</para>
<para>
Partitions may themselves be defined as partitioned tables, referred to as
- <firstterm>sub-partitioning</firstterm>. See <xref linkend="sql-createtable">
- for more details creating partitioned tables and partitions. It is not
- currently possible to alter a regular table into a partitioned table or
- vice versa. However, it is possible to add a regular table containing
- data into a partition of a partitioned table, or remove a partition; see
+ <firstterm>sub-partitioning</firstterm>. It is not currently possible to
+ alter a regular table into a partitioned table or vice versa. However,
+ it is possible to add a regular or partitioned table containing data into
+ a partition of a partitioned table, or remove a partition; see
<xref linkend="sql-altertable"> to learn more about the
<command>ATTACH PARTITION</> and <command>DETACH PARTITION</> sub-commands.
</para>
@@ -2823,8 +2994,8 @@ VALUES ('Albany', NULL, NULL, 'NY');
partitioned table and partitions do not participate in inheritance with
regular tables. Since a partition hierarchy consisting of the
partitioned table and its partitions is still an inheritance hierarchy,
- all the normal rules of inheritance apply as described in the previous
- section (<xref linkend="ddl-inherit">) with some exceptions, most notably:
+ all the normal rules of inheritance apply as described in
+ <xref linkend="ddl-inherit"> with some exceptions, most notably:
<itemizedlist>
<listitem>
@@ -2840,13 +3011,11 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
The <literal>ONLY</literal> notation used to exclude child tables
would either cause error or will be ignored in some cases for
- partitioned tables. For example, specifying <literal>ONLY</literal>
- when querying data from a partitioned table would not make much sense,
- because all the data is contained in partitions, so this raises an
- error. Specifying <literal>ONLY</literal> when modifying schema is
- not desirable in certain cases with partitioned tables where it may be
- fine for regular inheritance parents (for example, dropping a column
- from only the parent); an error will be thrown in that case.
+ partitioned tables. Specifying <literal>ONLY</literal> when modifying
+ schema is not desirable in certain cases with partitioned tables
+ whereas it may be fine for regular inheritance parents (for example,
+ dropping a column from only the parent); an error will be thrown in
+ that case.
</para>
</listitem>
@@ -2855,9 +3024,9 @@ VALUES ('Albany', NULL, NULL, 'NY');
Partitions cannot have columns that are not present in the parent.
It is neither possible to specify columns when creating partitions
with <command>CREATE TABLE</> nor is it possible to add columns to
- partitions using <command>ALTER TABLE</>. Tables may be added with
- <command>ALTER TABLE ... ATTACH PARTITION</> if their columns exactly
- match the parent, including oids.
+ partitions using <command>ALTER TABLE</>. Tables may be added as a
+ partition with <command>ALTER TABLE ... ATTACH PARTITION</> only if
+ their columns exactly match the parent, including oids.
</para>
</listitem>
@@ -2871,199 +3040,353 @@ VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
- Partitions can also be foreign tables (see <xref linkend="ddl-foreign-data">),
+ Partitions can also be foreign tables (see <xref linkend="sql-createforeigntable">),
although certain limitations exist currently in their usage. For example,
- data inserted into the partitioned table cannot be routed to foreign table
- partitions.
+ data inserted into the partitioned table is currently not routed to foreign
+ table partitions.
</para>
+ <sect3 id="ddl-partitioning-declarative-example">
+ <title>Example</title>
+
<para>
- There are currently the following limitations of using partitioned tables:
- <itemizedlist>
+ Suppose we are constructing a database for a large ice cream company.
+ The company measures peak temperatures every day as well as ice cream
+ sales in each region. Conceptually, we want a table like:
+
+<programlisting>
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+);
+</programlisting>
+
+ We know that most queries will access just the last week's, month's or
+ 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
+ worth of data. At the beginning of each month we will remove the oldest
+ month's data. In this situation we can use partitioning to help us meet
+ all of our different requirements for the measurements table.
+ </para>
+
+ <para>
+ To use declarative partitioning in this case, use the following steps:
+
+ <orderedlist spacing="compact">
<listitem>
<para>
- It is currently not possible to add same set of indexes on all partitions
- automatically. Indexes must be added to each partition with separate
- commands.
+ Create <structname>measurement</structname> table as a partitioned
+ table by specifying the <literal>PARTITION BY</literal> clause, which
+ includes the partitioning method (<literal>RANGE</literal> or
+ <literal>LIST</literal>) and the list of column(s) to use as the
+ partition key.
+
+<programlisting>
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+</programlisting>
</para>
- </listitem>
- <listitem>
+ <note>
+ <para>
+ To decide when to use multiple columns in the partition key for range
+ partitioning, consider whether queries accessing the partitioned
+ in question will include conditions that involve multiple columns,
+ especially the columns being considered to be the partition key.
+ If so, the optimizer can create a plan that will scan fewer partitions
+ if a query's conditions are such that there is equality constraint on
+ leading partition key columns, because they limit the number of
+ partitions of interest. The first partition key column with
+ inequality constraint also further eliminates some partitions of
+ those chosen by equality constraints on earlier columns.
+ </para>
+ </note>
+
<para>
- It is currently not possible to define indexes on partitioned tables
- that include all rows from all partitions in one global index.
- Consequently, it is not possible to create constraints that are realized
- using an index such as <literal>UNIQUE</>.
+ To be able to insert data into this table, one must create partitions,
+ as described below.
</para>
</listitem>
<listitem>
<para>
- 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.
+ Create partitions. Each partition's definition must specify the bounds
+ that correspond to the partitioning method and partition key of the
+ 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 into the parent table that does
+ not map to one of the existing partitions will cause an error;
+ appropriate partition must be added manually.
+ </para>
+
+ <para>
+ Partitions thus created are in every way normal <productname>PostgreSQL</>
+ tables (or, possibly, foreign tables). It is possible, for example, to
+ specify tablespace, storage parameters for each partition separately.
+ </para>
+
+ <para>
+ It is not necessary to create table constraints describing partition
+ boundary condition for partitions. Instead, partition constraints are
+ generated implicitly from the partition bound specification whenever
+ there is need to refer to them. Also, since any data inserted into the
+ parent table is automatically inserted into the appropriate partition,
+ it is not necessary to create triggers for the same.
+
+<programlisting>
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
+
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
+
+...
+CREATE TABLE measurement_y2007m11 PARTITION OF measurement
+ FOR VALUES FROM ('2007-11-01') TO ('2007-12-01')
+
+CREATE TABLE measurement_y2007m12 PARTITION OF measurement
+ FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
+ TABLESPACE fasttablespace;
+
+CREATE TABLE measurement_y2008m01 PARTITION OF measurement
+ FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
+ TABLESPACE fasttablespace
+ WITH (parallel_workers = 4);
+</programlisting>
</para>
+
+ <note>
+ <para>
+ To implement sub-partitioning, specify the
+ <literal>PARTITION BY</literal> clause in the commands used to create
+ individual partitions, for example:
+
+<programlisting>
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
+ PARTITION BY RANGE (peaktemp);
+</programlisting>
+
+ After creating partitions of <structname>measurement_y2006m02</>,
+ any data inserted into <structname>measurement</> that is mapped to
+ <structname>measurement_y2006m02</> will be further redirected to one
+ of its partitions based on the <structfield>peaktemp</> column.
+ Partition key specified may overlap with the parent's partition key,
+ although care must be taken when specifying the bounds of
+ sub-partitions such that the accepted set of data constitutes a
+ subset of what a partition's own bounds allows; the system does not
+ try to check if that's really the case.
+ </para>
+ </note>
</listitem>
<listitem>
<para>
- Row triggers, if necessary, must be defined on individual partitions, not
- the partitioned table as it is currently not supported.
+ Create an index on the key column(s),
+ as well as any other indexes you might want for every partition.
+ Note that it is currently not supported to propagate index definition
+ from the master partitioned table to its partitions; in fact, it is
+ not possible to define indexes on partitioned tables in the first
+ place. This might change in future releases.
+
+<programlisting>
+CREATE INDEX ON measurement_y2006m02 (logdate);
+CREATE INDEX ON measurement_y2006m03 (logdate);
+...
+CREATE INDEX ON measurement_y2007m11 (logdate);
+CREATE INDEX ON measurement_y2007m12 (logdate);
+CREATE INDEX ON measurement_y2008m01 (logdate);
+</programlisting>
</para>
</listitem>
- </itemizedlist>
+
+ <listitem>
+ <para>
+ Ensure that the <xref linkend="guc-constraint-exclusion">
+ configuration parameter is not disabled in <filename>postgresql.conf</>.
+ If it is, queries will not be optimized as desired.
+ </para>
+ </listitem>
+ </orderedlist>
</para>
<para>
- A detailed example that shows how to use partitioned tables is discussed in
- the next chapter.
+ 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>
-
- </sect1>
+ </sect3>
- <sect1 id="ddl-partitioning">
- <title>Partitioning</title>
+ <sect3 id="ddl-partitioning-declarative-maintenance">
+ <title>Partition Maintenance</title>
- <indexterm>
- <primary>partitioning</primary>
- </indexterm>
+ <para>
+ Normally the set of partitions established when initially defining the
+ the table are not intended to remain static. It is common to want to
+ remove old partitions of data and periodically add new partitions 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, rather
+ than physically moving large amounts of data around.
+ </para>
- <indexterm>
- <primary>table</primary>
- <secondary>partitioning</secondary>
- </indexterm>
+ <para>
+ The simplest option for removing old data is simply to drop the partition
+ that is no longer necessary:
+<programlisting>
+DROP TABLE measurement_y2006m02;
+</programlisting>
+ This can very quickly delete millions of records because it doesn't have
+ to individually delete every record. Note however that the above command
+ requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the parent
+ table.
+ </para>
<para>
- <productname>PostgreSQL</productname> supports basic table
- partitioning. This section describes why and how to implement
- partitioning as part of your database design.
- </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:
- <sect2 id="ddl-partitioning-overview">
- <title>Overview</title>
+<programlisting>
+ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
+</programlisting>
+
+ 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
+ similar tools. It might also be a useful time to aggregate data
+ into smaller formats, perform other data manipulations, or run
+ reports.
+ </para>
<para>
- Partitioning refers to splitting what is logically one large table
- into smaller physical pieces.
- Partitioning can provide several benefits:
- <itemizedlist>
- <listitem>
- <para>
- Query performance can be improved dramatically in certain situations,
- particularly when most of the heavily accessed rows of the table are in a
- single partition or a small number of partitions. The partitioning
- substitutes for leading columns of indexes, reducing index size and
- making it more likely that the heavily-used parts of the indexes
- fit in memory.
- </para>
- </listitem>
+ Similarly we can add a new partition to handle new data. We can create an
+ empty partition in the partitioned table just as the original partitions
+ were created above:
- <listitem>
- <para>
- 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
- index and random access reads scattered across the whole table.
- </para>
- </listitem>
+<programlisting>
+CREATE TABLE measurement_y2008m02 PARTITION OF measurement
+ FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
+ TABLESPACE fasttablespace;
+</programlisting>
- <listitem>
- <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</> or <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</>.
- </para>
- </listitem>
+ 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:
- <listitem>
- <para>
- Seldom-used data can be migrated to cheaper and slower storage media.
- </para>
- </listitem>
- </itemizedlist>
+<programlisting>
+CREATE TABLE measurement_y2008m02
+ (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
+ TABLESPACE fasttablespace;
- The 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 physical
- memory of the database server.
- </para>
+ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
+ CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
- <para>
- Currently, <productname>PostgreSQL</productname> supports partitioning
- using two methods:
+\copy measurement_y2008m02 from 'measurement_y2008m02'
+-- possibly some other data preparation work
- <variablelist>
- <varlistentry>
- <term>Using Table Inheritance</term>
+ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
+ FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
+</programlisting>
+ </para>
- <listitem>
- <para>
- 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 with it. This was the only method to implement
- partitioning in older versions.
- </para>
- </listitem>
- </varlistentry>
+ <tip>
+ <para>
+ Before running the <command>ATTACH PARTITION</> command, it is
+ recommended to create a <literal>CHECK</> constraint on the table to
+ be attached describing the desired partition constraint. Using the
+ same, system is able to skip the scan to validate the implicit
+ partition constraint. Without such a constraint, the table will be
+ scanned to validate the partition constraint, while holding an
+ <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
+ One may want to drop the constraint after <command>ATTACH PARTITION</>
+ is finished, because it is no longer necessary.
+ </para>
+ </tip>
+ </sect3>
- <varlistentry>
- <term>Using Partitioned Tables</term>
+ <sect3 id="ddl-partitioning-declarative-limitations">
+ <title>Limitations</title>
- <listitem>
- <para>
- See last section for some general information:
- <xref linkend="ddl-partitioned-tables">
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
- </para>
+ <para>
+ There are currently the following limitations of using partitioned tables:
+ <itemizedlist>
+ <listitem>
+ <para>
+ It is currently not possible to add same set of indexes on all partitions
+ automatically. Indexes must be added to each partition with separate
+ commands.
+ </para>
+ </listitem>
- <para>
- The following forms of partitioning can be implemented in
- <productname>PostgreSQL</productname> using either of the above mentioned
- methods, although the latter provides dedicated syntax for each:
+ <listitem>
+ <para>
+ It is currently not possible to define indexes on partitioned tables
+ that include all rows from all partitions in one global index.
+ Consequently, it is not possible to create constraints that are realized
+ using an index such as <literal>UNIQUE</>.
+ </para>
+ </listitem>
- <variablelist>
- <varlistentry>
- <term>Range Partitioning</term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The table is partitioned into <quote>ranges</quote> defined
- by a key column or set of columns, with no overlap between
- 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.
- </para>
- </listitem>
- </varlistentry>
+ <listitem>
+ <para>
+ <command>INSERT</command> statements with <literal>ON CONFLICT</>
+ clause are currently not allowed on partitioned tables.
+ </para>
+ </listitem>
- <varlistentry>
- <term>List Partitioning</term>
+ <listitem>
+ <para>
+ An <command>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. This might
+ change in future releases.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The table is partitioned by explicitly listing which key values
- appear in each partition.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
- </para>
+ <listitem>
+ <para>
+ Row triggers, if necessary, must be defined on individual partitions, not
+ the partitioned table as it is currently not supported.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect3>
</sect2>
- <sect2 id="ddl-partitioning-implementation">
- <title>Implementing Partitioning</title>
+ <sect2 id="ddl-partitioning-implementation-inheritance">
+ <title>Implementation Using Inheritance</title>
+ <para>
+ In some cases, one may want to add columns to partitions that are not
+ present in the parent table which is not possible to do with the above
+ method. For such cases, partitioning can be implemented using
+ inheritance (see <xref linkend="ddl-inherit">).
+ </para>
+
+ <sect3 id="ddl-partitioning-inheritance-example">
+ <title>Example</title>
<para>
- To set up a partitioned table using inheritance, do the following:
+ We use the same <structname>measurement</structname> table we used
+ above. To implement it as a partitioned table using inheritance, do the
+ following:
<orderedlist spacing="compact">
<listitem>
<para>
@@ -3076,6 +3399,11 @@ VALUES ('Albany', NULL, NULL, 'NY');
be applied equally to all partitions. There is no point
in defining any indexes or unique constraints on it, either.
</para>
+
+ <para>
+ In case of our example, master table is the original
+ <structname>measurement</structname> as originally defined.
+ </para>
</listitem>
<listitem>
@@ -3090,12 +3418,27 @@ VALUES ('Albany', NULL, NULL, 'NY');
are in every way normal <productname>PostgreSQL</> tables
(or, possibly, foreign tables).
</para>
+
+ <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.
+<programlisting>
+CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
+CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
+...
+CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
+CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
+CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
+</programlisting>
+ </para>
</listitem>
<listitem>
<para>
- Add table constraints to the partition tables to define the
- allowed key values in each partition.
+ Add non-overlapping table constraints to the partition tables to
+ define the allowed key values in each partition.
</para>
<para>
@@ -3117,230 +3460,53 @@ CHECK ( outletID BETWEEN 200 AND 300 )
</para>
<para>
- Note that there is no difference in
- syntax between range and list partitioning; those terms are
- descriptive only.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For each partition, create an index on the key column(s),
- as well as any other indexes you might want. (The key index is
- not strictly necessary, but in most scenarios it is helpful.
- If you intend the key values to be unique then you should
- always create a unique or primary-key constraint for each
- partition.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- Optionally, define a trigger or rule to redirect data inserted into
- the master table to the appropriate partition.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Ensure that the <xref linkend="guc-constraint-exclusion">
- configuration parameter is not disabled in
- <filename>postgresql.conf</>.
- If it is, queries will not be optimized as desired.
- </para>
- </listitem>
-
- </orderedlist>
- </para>
-
- <para>
- To use partitioned tables, do the following:
- <orderedlist spacing="compact">
- <listitem>
- <para>
- Create <quote>master</quote> table as a partitioned table by
- specifying the <literal>PARTITION BY</literal> clause, which includes
- the partitioning method (<literal>RANGE</literal> or
- <literal>LIST</literal>) and the list of column(s) to use as the
- partition key. To be able to insert data into the table, one must
- create partitions, as described below.
- </para>
-
- <note>
- <para>
- To decide when to use multiple columns in the partition key for range
- partitioning, consider whether queries accessing the partitioned
- in question will include conditions that involve multiple columns,
- especially the columns being considered to be the partition key.
- If so, the optimizer can create a plan that will scan fewer partitions
- if a query's conditions are such that there is equality constraint on
- leading partition key columns, because they limit the number of
- partitions of interest. The first partition key column with
- inequality constraint also further eliminates some partitions of
- those chosen by equality constraints on earlier columns.
- </para>
- </note>
- </listitem>
-
- <listitem>
- <para>
- Create partitions of the master partitioned table, with the partition
- bounds specified for each partition matching the partitioning method
- and partition key of the master table. Note that specifying partition
- bounds such that the new partition's values will overlap with one or
- more existing partitions will cause an error. It is only after
- creating partitions that one is able to insert data into the master
- partitioned table, provided it maps to one of the existing partitions.
- If a data row does not map to any of the existing partitions, it will
- cause an error.
- </para>
-
- <para>
- Partitions thus created are also in every way normal
- <productname>PostgreSQL</> tables (or, possibly, foreign tables),
- whereas partitioned tables differ in a number of ways.
- </para>
-
- <para>
- It is not necessary to create table constraints for partitions.
- Instead, partition constraints are generated implicitly whenever
- there is a need to refer to them. Also, since any data inserted into
- the master partitioned table is automatically inserted into the
- appropriate partition, it is not necessary to create triggers for the
- same.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Just like with inheritance, create an index on the key column(s),
- as well as any other indexes you might want for every partition.
- Note that it is currently not supported to propagate index definition
- from the master partitioned table to its partitions; in fact, it is
- not possible to define indexes on partitioned tables in the first
- place. This might change in future releases.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Currently, partitioned tables also depend on constraint exclusion
- for query optimization, so ensure that the
- <xref linkend="guc-constraint-exclusion"> configuration parameter is
- not disabled in <filename>postgresql.conf</>. This might change in
- future releases.
- </para>
- </listitem>
-
- </orderedlist>
- </para>
-
- <para>
- For example, suppose we are constructing a database for a large
- ice cream company. The company measures peak temperatures every
- day as well as ice cream sales in each region. Conceptually,
- we want a table like:
-
-<programlisting>
-CREATE TABLE measurement (
- city_id int not null,
- logdate date not null,
- peaktemp int,
- unitsales int
-);
-</programlisting>
-
- We know that most queries will access just the last week's, month's or
- 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 worth of data. At the
- beginning of each month we will remove the oldest month's data.
- </para>
-
- <para>
- In this situation we can use partitioning to help us meet all of our
- different requirements for the measurements table. Following the
- steps outlined above for both methods, partitioning can be set up as
- follows:
- </para>
-
- <para>
- <orderedlist spacing="compact">
- <listitem>
- <para>
- The master table is the <structname>measurement</> table, declared
- exactly as above.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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_y2007m11 ( ) INHERITS (measurement);
-CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
-CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
-</programlisting>
-
- Each of the partitions are complete tables in their own right,
- but they inherit their definitions from the
- <structname>measurement</> table.
- </para>
-
- <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:
+ It would be better to instead create partitions as follows:
<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>
</para>
+
+ <para>
+ Note that there is no difference in syntax between range and list
+ partitioning; those terms are descriptive only.
+ </para>
</listitem>
<listitem>
<para>
- We probably need indexes on the key columns too:
-
+ For each partition, create an index on the key column(s),
+ as well as any other indexes you might want. (The key index is
+ not strictly necessary, but in most scenarios it is helpful.
+ If you intend the key values to be unique then you should
+ always create a unique or primary-key constraint for each
+ partition.)
<programlisting>
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (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);
</programlisting>
-
- We choose not to add further indexes at this time.
</para>
</listitem>
@@ -3363,7 +3529,9 @@ END;
$$
LANGUAGE plpgsql;
</programlisting>
+ </para>
+ <para>
After creating the function, we create a trigger which
calls the trigger function:
@@ -3425,151 +3593,88 @@ LANGUAGE plpgsql;
of this example.
</para>
</note>
- </listitem>
- </orderedlist>
- </para>
-
- <para>
- Steps when using a partitioned table are as follows:
- </para>
- <para>
- <orderedlist spacing="compact">
- <listitem>
<para>
- Create the <structname>measurement</> table as a partitioned table:
+ 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 TABLE measurement (
- city_id int not null,
- logdate date not null,
- peaktemp int,
- unitsales int
-) PARTITION BY RANGE (logdate);
+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>
- </listitem>
- <listitem>
<para>
- Then create partitions as follows:
+ 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>
-<programlisting>
-CREATE TABLE measurement_y2006m02 PARTITION OF measurement
- FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
-CREATE TABLE measurement_y2006m03 PARTITION OF measurement
- FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
-...
-CREATE TABLE measurement_y2007m11 PARTITION OF measurement
- FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
-CREATE TABLE measurement_y2007m12 PARTITION OF measurement
- FOR VALUES FROM ('2007-12-01') TO ('2008-01-01');
-CREATE TABLE measurement_y2008m01 PARTITION OF measurement
- FOR VALUES FROM ('2008-01-01') TO ('2008-02-01');
-</programlisting>
+ <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>
</listitem>
<listitem>
<para>
- Create indexes on the key columns just like in case of inheritance
- partitions.
+ Ensure that the <xref linkend="guc-constraint-exclusion">
+ configuration parameter is not disabled in
+ <filename>postgresql.conf</>.
+ If it is, queries will not be optimized as desired.
</para>
</listitem>
</orderedlist>
-
- <note>
- <para>
- To implement sub-partitioning, specify the
- <literal>PARTITION BY</literal> clause in the commands used to create
- individual partitions, for example:
-
-<programlisting>
-CREATE TABLE measurement_y2006m02 PARTITION OF measurement
- FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
- PARTITION BY RANGE (peaktemp);
-</programlisting>
-
- After creating partitions of <structname>measurement_y2006m02</>, any
- data inserted into <structname>measurement</> that is mapped to
- <structname>measurement_y2006m02</> will be further redirected to one
- of its partitions based on the <structfield>peaktemp</> column.
- Partition key specified may overlap with the parent's partition key,
- although care must be taken when specifying the bounds of sub-partitions
- such that the accepted set of data constitutes a subset of what a
- partition's own bounds allows; the system does not try to check if
- that's really the case.
- </para>
- </note>
</para>
<para>
- As we can see, a complex partitioning scheme could require a
- substantial amount of DDL, although significantly less when using
- partitioned tables. 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">
- <title>Managing Partitions</title>
-
- <para>
- Normally the set of partitions established when initially
- defining the table are not intended to remain static. It is
- common to want to remove old partitions of data and periodically
- add new partitions 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, rather than physically moving large
- amounts of data around.
- </para>
-
- <para>
- Both the inheritance-based and partitioned table methods allow this to
- be done, although the latter requires taking an <literal>ACCESS EXCLUSIVE</literal>
- lock on the master table for various commands mentioned below.
- </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>
+ </sect3>
- <para>
- The simplest option for removing old data is simply to drop the partition
- that is no longer necessary, which works using both methods of
- partitioning:
+ <sect3 id="ddl-partitioning-inheritance-maintenance">
+ <title>Partition Maintenance</title>
+ <para>
+ To remove old data quickly, simply to drop the partition that is no
+ longer necessary:
<programlisting>
DROP TABLE measurement_y2006m02;
</programlisting>
- This can very quickly delete millions of records because it doesn't have
- to individually delete every record.
- </para>
+ </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>
-
- When using a partitioned table:
+ To remove the partition from the partitioned table but retain access to
+ it as a table in its own right:
<programlisting>
-ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
+ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
</programlisting>
-
- 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
- similar tools. It might also be a useful time to aggregate data
- into smaller formats, perform other data manipulations, or run
- reports.
</para>
<para>
- Similarly we can add a new partition to handle new data. We can create an
- empty partition in the partitioned table just as the original partitions
- were created above:
+ To add a new partition to handle new data, create an empty partition just
+ as the original partitions were created above:
<programlisting>
CREATE TABLE measurement_y2008m02 (
@@ -3577,52 +3682,80 @@ CREATE TABLE measurement_y2008m02 (
) INHERITS (measurement);
</programlisting>
- When using a partitioned table:
-
-<programlisting>
-CREATE TABLE measurement_y2008m02 PARTITION OF measurement
- FOR VALUES FROM ('2008-02-01') TO ('2008-03-01');
-</programlisting>
-
- 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:
+ Alternatively, one may created the new table outside the partition
+ structure, and make it a partition after data is loaded, checked,
+ and transformed.
<programlisting>
CREATE TABLE measurement_y2008m02
(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'
-- possibly some other data preparation work
+
ALTER TABLE measurement_y2008m02 INHERIT measurement;
</programlisting>
+ </para>
+ </sect3>
+
+ <sect3 id="ddl-partitioning-inheritance-caveats">
+ <title>Caveats</title>
+
+ <para>
+ The following caveats apply to partitioned tables implemented using
+ inheritance:
+ <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>
- The last of the above commands when using a partitioned table would be:
+ <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.
+ </para>
+ </listitem>
+ <listitem>
+ <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:
<programlisting>
-ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
- FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
+ANALYZE measurement;
</programlisting>
- </para>
+ will only process the master table.
+ </para>
+ </listitem>
- <tip>
+ <listitem>
<para>
- Before running the <command>ATTACH PARTITION</> command, it is
- recommended to create a <literal>CHECK</> constraint on the table to
- be attached describing the desired partition constraint. Using the
- same, system is able to skip the scan to validate the implicit
- partition constraint. Without such a constraint, the table will be
- scanned to validate the partition constraint, while holding an
- <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
- One may want to drop the constraint after <command>ATTACH PARTITION</>
- is finished, because it is no longer necessary.
+ <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.
</para>
- </tip>
- </sect2>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect3>
+ </sect2>
- <sect2 id="ddl-partitioning-constraint-exclusion">
+ <sect2 id="ddl-partitioning-constraint-exclusion">
<title>Partitioning and Constraint Exclusion</title>
<indexterm>
@@ -3632,7 +3765,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
<para>
<firstterm>Constraint exclusion</> is a query optimization technique
that improves performance for partitioned tables defined in the
- fashion described above. As an example:
+ fashion described above (both declarative partitioned tables and those
+ implemented using inheritance). As an example:
<programlisting>
SET constraint_exclusion = on;
@@ -3715,153 +3849,6 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
are unlikely to benefit.
</para>
- <note>
- <para>
- Currently, constraint exclusion is also used for partitioned tables.
- However, we did not create any <literal>CHECK</literal> constraints
- for individual partitions as seen above. In this case, the optimizer
- uses internally generated constraint for every partition.
- </para>
- </note>
-
- </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 (unless it is a partitioned 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 using inheritance to implement partitioning:
- <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.
- </para>
- </listitem>
-
- <listitem>
- <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:
-<programlisting>
-ANALYZE measurement;
-</programlisting>
- will only process the master table.
- </para>
- </listitem>
-
- <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.
- </para>
- </listitem>
- </itemizedlist>
- </para>
-
- <para>
- The following caveats apply to partitioned tables created with the
- explicit syntax:
- <itemizedlist>
- <listitem>
- <para>
- An <command>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. This might
- change in future releases.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <command>INSERT</command> statements with <literal>ON CONFLICT</>
- clause are currently not allowed on partitioned tables.
- </para>
- </listitem>
-
- </itemizedlist>
- </para>
-
<para>
The following caveats apply to constraint exclusion, which is currently
used by both inheritance and partitioned tables:
@@ -3901,10 +3888,78 @@ ANALYZE measurement;
don't try to use many thousands of partitions.
</para>
</listitem>
-
</itemizedlist>
</para>
</sect2>
+
+ <sect2 id="ddl-partitioning-alternatives">
+ <title>Alternative Partitioning Methods</title>
+
+ <sect3 id="ddl-partitioning-alternatives-union-all">
+ <title>Using UNION ALL view</title>
+ <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>
+ </sect3>
+
+ <sect3 id="ddl-partitioning-alternatives-brin-index">
+ <title>Accessing Tables Using BRIN Index</title>
+ <para>
+ <acronym>BRIN</acronym>, which stands for Block Range Index, is
+ designed for handling very large tables in which certain columns
+ have some natural physical location within the table. For example,
+ in the <structname>measurement</structname> table, the entries for
+ earlier times (<structfield>logdate</structfield> column) will appear
+ earlier in the table most of the time. A table storing a ZIP code
+ column might have all codes for a city grouped together naturally.
+ </para>
+
+ <para>
+ In case of <structname>measurement</structname> table, one may consider
+ adding a minmax <acronym>BRIN</acronym> index on the
+ <structfield>logdate</structfield> column.
+
+<programlisting>
+CREATE INDEX ON measurement USING brin (logdate date_minmax_ops);
+</programlisting>
+
+ In this case, specifying <literal>date_minmax_ops</literal> is not
+ necessary; it is shown for clarity.
+ </para>
+
+ <para>
+ <acronym>BRIN</acronym> indexes leverage this locality of data and
+ store summary information for a range of consecutive pages and keep
+ it updated as the data is added or removed. Because a
+ <acronym>BRIN</acronym> index is very small, scanning the index adds
+ adds little overhead compared to a sequential scan, but may avoid
+ scanning large parts of the table that are known not to contain
+ matching tuples. That is often why table partitioning is used. Thus,
+ <acronym>BRIN</acronym> indexes provide a subset of benefits that
+ parttioning provides with much less upfront setup.
+ </para>
+
+ <para>
+ See <xref linkend="brin"> for more details.
+ </para>
+ </sect3>
+
+ </sect2>
</sect1>
<sect1 id="ddl-foreign-data">
--
2.11.0
>From 591fd2eb7a9089d68926bff1532879240be0aec6 Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Fri, 10 Feb 2017 15:03:45 +0900
Subject: [PATCH 2/3] Add a note about DROP NOT NULL and partitions
On the ALTER TABLE refpage, it seems better to mention how to drop
drop the not null constraint of a partition's column. Per suggestion
from Corey Huinker.
---
doc/src/sgml/ref/alter_table.sgml | 8 ++++++--
1 file changed, 6 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index da431f8369..be857882bb 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -172,9 +172,13 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</para>
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</>
+ If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
on a column if it is marked <literal>NOT NULL</literal> in the parent
- table.
+ table. To drop the <literal>NOT NULL</literal> constraint from all the
+ partitions, perform <literal>DROP NOT NULL</literal> on the parent
+ table. One might however want to set it for only some partitions,
+ which is possible by doing <literal>SET NOT NULL</literal> on individual
+ partitions.
</para>
</listitem>
</varlistentry>
--
2.11.0
>From 02c8753b3a81589c5f6679bcc4d50f452d0c658d Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Mon, 27 Feb 2017 19:00:08 +0900
Subject: [PATCH 3/3] Listify a note on the CREATE TABLE page
---
doc/src/sgml/ref/create_table.sgml | 82 ++++++++++++++++++++++----------------
1 file changed, 47 insertions(+), 35 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index bb081ff86f..161f7d1280 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -262,41 +262,53 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</para>
<note>
- <para>
- Each of the values specified in the partition bound specification is
- a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>.
- A literal is either a numeric constant or a string constant that is
- coercable to the corresponding partition key column's type.
- </para>
-
- <para>
- When creating a range partition, the lower bound specified with
- <literal>FROM</literal> is an inclusive bound, whereas the upper bound
- specified with <literal>TO</literal> is an exclusive bound. That is,
- the values specified in the <literal>FROM</literal> list are accepted
- values of the corresponding partition key columns in a given partition,
- whereas those in the <literal>TO</literal> list are not. To be precise,
- this applies only to the first of the partition key columns for which
- the corresponding values in the <literal>FROM</literal> and
- <literal>TO</literal> lists are not equal. All rows in a given
- partition contain the same values for all preceding columns, equal to
- those specified in <literal>FROM</literal> and <literal>TO</literal>
- lists. On the other hand, any subsequent columns are insignificant
- as far as implicit partition constraint is concerned.
-
- Specifying <literal>UNBOUNDED</literal> in <literal>FROM</literal>
- signifies <literal>-infinity</literal> as the lower bound of the
- corresponding column, whereas it signifies <literal>+infinity</literal>
- as the upper bound when specified in <literal>TO</literal>.
- </para>
-
- <para>
- When creating a list partition, <literal>NULL</literal> can be specified
- to signify that the partition allows the partition key column to be null.
- However, there cannot be more than one such list partitions for a given
- parent table. <literal>NULL</literal> cannot specified for range
- partitions.
- </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Each of the values specified in the partition bound specification is
+ a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>.
+ A literal is either a numeric constant or a string constant that is
+ coercable to the corresponding partition key column's type.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When creating a range partition, the lower bound specified with
+ <literal>FROM</literal> is an inclusive bound, whereas the upper
+ bound specified with <literal>TO</literal> is an exclusive bound.
+ That is, the values specified in the <literal>FROM</literal> list
+ are accepted values of the corresponding partition key columns in a
+ given partition, whereas those in the <literal>TO</literal> list are
+ not. To be precise, this applies only to the first of the partition
+ key columns for which the corresponding values in the <literal>FROM</literal>
+ and <literal>TO</literal> lists are not equal. All rows in a given
+ partition contain the same values for all preceding columns, equal to
+ those specified in <literal>FROM</literal> and <literal>TO</literal>
+ lists. On the other hand, any subsequent columns are insignificant
+ as far as implicit partition constraint is concerned.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Specifying <literal>UNBOUNDED</literal> in <literal>FROM</literal>
+ signifies <literal>-infinity</literal> as the lower bound of the
+ corresponding column, whereas it signifies <literal>+infinity</literal>
+ as the upper bound when specified in <literal>TO</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When creating a list partition, <literal>NULL</literal> can be
+ specified to signify that the partition allows the partition key
+ column to be null. However, there cannot be more than one such
+ list partitions for a given parent table. <literal>NULL</literal>
+ cannot specified for range partitions.
+ </para>
+ </listitem>
+ </itemizedlist>
</note>
<para>
--
2.11.0
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
