バージョンごとのドキュメント一覧

5.12. テーブルのパーティショニング #

<title>Table Partitioning</title>

<productname>PostgreSQL</productname> supports basic table partitioning. This section describes why and how to implement partitioning as part of your database design. PostgreSQLは基本的なテーブルのパーティショニング(分割)をサポートしています。 この節では、データベース設計において、なぜそしてどのようにしてパーティショニングを実装するのかを解説します。

5.12.1. 概要 #

<title>Overview</title>

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits: パーティショニングとは、論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分割することを指します。 パーティショニングによって得られる利点は以下のようにいくつかあります。

  • 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. Partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit in memory. 特定の条件下で問い合わせのパフォーマンスが劇的に向上することがあります。 特にテーブル内のアクセスが集中する行のほとんどが単一または少数のパーティションに存在している場合がそうです。 パーティショニングは実質的にインデックスの上位木レベルの代わりになり、インデックスの頻繁に使われる部分がメモリに収まりやすくなるようにします。

  • When queries or updates access a large percentage of a single partition, performance can be improved by using a sequential scan of that partition instead of using an index, which would require random-access reads scattered across the whole table. 問い合わせや更新が一つのパーティションの大部分にアクセスする場合、インデックスを使用してテーブル全体にまたがるランダムアクセス読み取りをする代わりに、そのパーティションへの順次アクセスをすることでパフォーマンスを向上させることができます。

  • Bulk loads and deletes can be accomplished by adding or removing partitions, if the usage pattern is accounted for in the partitioning design. Dropping an individual partition using <command>DROP TABLE</command>, or doing <command>ALTER TABLE DETACH PARTITION</command>, is far faster than a bulk operation. These commands also entirely avoid the <command>VACUUM</command> overhead caused by a bulk <command>DELETE</command>. 一括挿入や削除について、その使い方のパターンをパーティショニングの設計に組み込んでいれば、それをパーティションの追加や削除で実現することが可能です。 個々のパーティションをDROP TABLEで削除する、あるいはALTER TABLE DETACH PARTITIONを実行することにより、一括の操作をするよりも遥かに高速です。 これらのコマンドはまた、一括のDELETEで引き起こされるVACUUMのオーバーヘッドを完全に回避できます。

  • Seldom-used data can be migrated to cheaper and slower storage media. めったに使用されないデータを安価で低速なストレージメディアに移行できます。

These benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server. これらの利益は通常、そうしなければテーブルが非常に大きくなる場合にのみ価値があります。 テーブルがパーティショニングから利益を得られるかどうかの正確な分岐点はアプリケーションに依存しますが、重要なことはテーブルのサイズがデータベースサーバの物理メモリより大きいことです。

<productname>PostgreSQL</productname> offers built-in support for the following forms of partitioning: PostgreSQLにはパーティショニングについて以下の形式の組み込み機能があります。

範囲パーティショニング #

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. Each range's bounds are understood as being inclusive at the lower end and exclusive at the upper end. For example, if one partition's range is from <literal>1</literal> to <literal>10</literal>, and the next one's range is from <literal>10</literal> to <literal>20</literal>, then value <literal>10</literal> belongs to the second partition not the first. テーブルはキー列またはキー列の集合で定義される範囲にパーティション分割され、異なるパーティションに割り当てられる値の範囲に重なりがないようになります。 例えば、日付の範囲によってパーティション分割することもあるでしょうし、特定のビジネスオブジェクトの識別子の範囲によって分割することもあるでしょう。 個々の範囲の境界は、下限は境界値を含み、上限は境界値を含まないと理解されています。 たとえば、あるパーティションの境界が1から10で、次の範囲が10から20なら、値10は最初ではなく、二番目のパーティションに所属します。

リストパーティショニング #

The table is partitioned by explicitly listing which key value(s) appear in each partition. 各パーティションに現れるキーの値を明示的に列挙することでテーブルをパーティションに分割します。

ハッシュパーティショニング #

The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder. 各パーティションに対して法と剰余を指定することでテーブルをパーティションに分割します。 各パーティションは、パーティションキーのハッシュ値を指定された法で割った際に指定された剰余となる行を保持します。

If your application needs to use other forms of partitioning not listed above, alternative methods such as inheritance and <literal>UNION ALL</literal> views can be used instead. Such methods offer flexibility but do not have some of the performance benefits of built-in declarative partitioning. アプリケーションで上記に列挙されていない他の形式のパーティショニングを使用する必要がある場合は、継承やUNION ALLなどの代替方式を代わりに使うことができます。 そのような方式は柔軟性がありますが、組み込みの宣言的パーティショニングによるパフォーマンス上の利益の一部を享受できません。

5.12.2. 宣言的パーティショニング #

<title>Declarative Partitioning</title>

<productname>PostgreSQL</productname> allows you to declare that a table is divided into partitions. The table that is divided is referred to as a <firstterm>partitioned table</firstterm>. The declaration includes the <firstterm>partitioning method</firstterm> as described above, plus a list of columns or expressions to be used as the <firstterm>partition key</firstterm>. PostgreSQLではテーブルをパーティションに分割すると宣言できます。 分割されたテーブルはパーティションテーブルと呼ばれます。 この宣言は上で述べたパーティショニング方式を含んでおり、加えてパーティションキーとして使用される列あるいは式のリストからなります。

The partitioned table itself is a <quote>virtual</quote> table having no storage of its own. Instead, the storage belongs to <firstterm>partitions</firstterm>, which are otherwise-ordinary tables associated with the partitioned table. Each partition stores a subset of the data as defined by its <firstterm>partition bounds</firstterm>. All rows inserted into a partitioned table will be routed to the appropriate one of the partitions based on the values of the partition key column(s). Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition. パーティションテーブル自身はストレージを持たない仮想テーブルです。 その代わり、ストレージはパーティションテーブルに関連付けられた通常のテーブルであるpartitionsに所属します。 個々のパーティションはパーティション境界によって定義されるデータのサブセットです。 パーティションテーブルに挿入されるすべての行は、パーティションキーの列の値に基づいてパーティションの一つに振り向けられます。 行のパーティションキーを更新し、それが元のパーティション境界を満たさなくなった場合、その行は異なるパーティションに移動されます。

Partitions may themselves be defined as partitioned tables, resulting in <firstterm>sub-partitioning</firstterm>. Although all partitions must have the same columns as their partitioned parent, partitions may have their own indexes, constraints and default values, distinct from those of other partitions. See <xref linkend="sql-createtable"/> for more details on creating partitioned tables and partitions. パーティションは自身をパーティション化テーブルであると定義することができ、その結果サブパーティショニングとなります。 すべてのパーティションは親のパーティションと同じ列を持たなければなりませんが、パーティションは他のパーティションとは別の独自のインデックス、制約、デフォルト値を持つことができます。 パーティションテーブルおよびパーティションの作成についてのさらなる詳細についてはCREATE TABLEを参照してください。

It is not possible to turn a regular table into a partitioned table or vice versa. However, it is possible to add an existing regular or partitioned table as a partition of a partitioned table, or remove a partition from a partitioned table turning it into a standalone table; this can simplify and speed up many maintenance processes. See <xref linkend="sql-altertable"/> to learn more about the <command>ATTACH PARTITION</command> and <command>DETACH PARTITION</command> sub-commands. 通常のテーブルをパーティションテーブルに変更する、およびその逆はできません。 しかし、既存の通常のテーブルやパーティションテーブルをパーティションテーブルのパーティションとして追加する、あるいはパーティションテーブルからパーティションを削除し、それを独立したテーブルにすることは可能です。 これにより多くの保守プロセスを単純化して効率化できます。 ATTACH PARTITIONおよびDETACH PARTITIONのサブコマンドについての詳細はALTER TABLEを参照してください。

Partitions can also be <link linkend="ddl-foreign-data">foreign tables</link>, although considerable care is needed because it is then the user's responsibility that the contents of the foreign table satisfy the partitioning rule. There are some other restrictions as well. See <xref linkend="sql-createforeigntable"/> for more information. 外部テーブルの内容がパーティション化のルールを満たすようにするのはユーザの責任なので、入念な考慮が必要ではあるものの、パーティションを外部テーブルとすることができます。 他にもいくつか制限事項があります。 詳細はCREATE FOREIGN TABLEを参照してください。

5.12.2.1. 例 #

<title>Example</title>

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: 大きなアイスクリーム会社のデータベースを構築している場合を考えましょう。 その会社は毎日の最高気温、および各地域でのアイスクリームの売上を計測します。 概念的には次のようなテーブルが必要です。

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

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 keep only 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. このテーブルの主な利用目的は経営層向けにオンラインの報告書を作成することであるため、ほとんどの問い合わせは単に直前の週、月、四半期のデータにアクセスするだけであることがわかっています。 保存すべき古いデータの量を削減するため、最近3年分のデータのみを残すことに決めました。 各月のはじめに、最も古い月のデータを削除します。 この場合、計測テーブルについての様々な要求のすべてを、パーティショニングを使って満たすことができます。

To use declarative partitioning in this case, use the following steps: この場合に宣言的パーティショニングを使うには、以下の手順に従います。

  1. Create the <structname>measurement</structname> table as a partitioned table by specifying the <literal>PARTITION BY</literal> clause, which includes the partitioning method (<literal>RANGE</literal> in this case) and the list of column(s) to use as the partition key. PARTITION BY句を指定して、measurementテーブルをパーティションテーブルとして作成します。 PARTITION BY句にはパーティショニング方式(この場合はRANGE)とパーティションキーとして使う列のリストを記述します。

    CREATE TABLE measurement (
        city_id         int not null,
        logdate         date not null,
        peaktemp        int,
        unitsales       int
    ) PARTITION BY RANGE (logdate);
    

  2. Create partitions. Each partition's definition must specify bounds that correspond to the partitioning method and partition key of the parent. Note that specifying bounds such that the new partition's values would overlap with those in one or more existing partitions will cause an error. パーティションを作成します。 各パーティションの定義では、親のパーティショニング方式およびパーティションキーに対応する境界を指定しなければなりません。 新しいパーティションの値が一つ以上の既存のパーティションの値と重なるような境界を指定するとエラーになることに注意してください。 既存のおよびパーティションのどれにも当てはまらないデータを親テーブルに挿入するとエラーになります。 この場合、適切なパーティションを手作業で追加しなければなりません。

    Partitions thus created are in every way normal <productname>PostgreSQL</productname> tables (or, possibly, foreign tables). It is possible to specify a tablespace and storage parameters for each partition separately. こうして作成されたパーティションは、すべての点においてPostgreSQLの通常のテーブル(あるいは場合によっては外部テーブル)と同じです。 各パーティション毎に別々にテーブル空間や格納パラメータを指定することもできます。

    For our example, each partition should hold one month's worth of data, to match the requirement of deleting one month's data at a time. So the commands might look like: この例では、個々のパーティションは一月分のデータを保持し、一度に一月分のデータを削除するという要件を満たしています。 ですからコマンドは以下のようになるかもしれません。

    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')
        WITH (parallel_workers = 4)
        TABLESPACE fasttablespace;
    

    (Recall that adjacent partitions can share a bound value, since range upper bounds are treated as exclusive bounds.) (境界の上限は境界値を含まないので、隣接したパーティションは境界値を共有できることを思い出してください。)

    If you wish to implement sub-partitioning, again specify the <literal>PARTITION BY</literal> clause in the commands used to create individual partitions, for example: サブパーティショニングの実装が希望なら、同じように以下のように、個々のパーティションを作成するコマンドでPARTITION BY句を指定してください。

    CREATE TABLE measurement_y2006m02 PARTITION OF measurement
        FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
        PARTITION BY RANGE (peaktemp);
    

    After creating partitions of <structname>measurement_y2006m02</structname>, any data inserted into <structname>measurement</structname> that is mapped to <structname>measurement_y2006m02</structname> (or data that is directly inserted into <structname>measurement_y2006m02</structname>, which is allowed provided its partition constraint is satisfied) will be further redirected to one of its partitions based on the <structfield>peaktemp</structfield> column. The partition key specified may overlap with the parent's partition key, although care should be taken when specifying the bounds of a sub-partition such that the set of data it accepts constitutes a subset of what the partition's own bounds allow; the system does not try to check whether that's really the case. measurement_y2006m02のパーティションの作成後、measurementに挿入されるデータでmeasurement_y2006m02に振り向けられるもの(あるいはmeasurement_y2006m02に直接挿入されるデータでそのパーティション制約を満たしているもの)はすべて、peaktemp列に基いてさらにその下のパーティションの一つにリダイレクトされます。 指定するパーティションキーは親のパーティションキーと重なっても構いませんが、サブパーティションの境界を指定するときは、それが受け付けるデータの集合がパーティション自体の境界でできるものの部分集合を構成するように注意してください。 システムは本当にそのようになっているかどうか、検査しようとしません。

    Inserting data into the parent table that does not map to one of the existing partitions will cause an error; an appropriate partition must be added manually. 既存のパーティションにマップされない親テーブルにデータを挿入しようとするとエラーになります。 手動で適切なパーティションを追加しなければなりません。

    It is not necessary to manually create table constraints describing the partition boundary conditions for partitions. Such constraints will be created automatically. パーティションのパーティション境界条件を記述するテーブル制約を手動で作る必要はありません。 そのような制約は自動的に作られます。

  3. Create an index on the key column(s), as well as any other indexes you might want, on the partitioned table. (The key index is not strictly necessary, but in most scenarios it is helpful.) This automatically creates a matching index on each partition, and any partitions you create or attach later will also have such an index. An index or unique constraint declared on a partitioned table is <quote>virtual</quote> in the same way that the partitioned table is: the actual data is in child indexes on the individual partition tables. キー列にインデックスを作成し、またその他のインデックスも必要に応じてパーティションテーブル上に作成します。 (厳密に言えば、キー列のインデックスが必要なわけではありませんが、ほとんどの場合に役に立つでしょう。) これは個々のパーティションに対応するインデックスを自動的に作るので、作成したすべてのパーティション、あるいは後でアタッチしたパーティションもそのようなインデックスを持ちます。 パーティションテーブルのインデックスあるいは一意制約はパーティションテーブルがそうであるのと同様、仮想です。 実際のデータは個々のパーティションテーブル上の子インデックスにあります。

    CREATE INDEX ON measurement (logdate);
    

  4. Ensure that the <xref linkend="guc-enable-partition-pruning"/> configuration parameter is not disabled in <filename>postgresql.conf</filename>. If it is, queries will not be optimized as desired. postgresql.confで設定パラメータenable_partition_pruningが無効になっていないことを確認します。 これが無効になっていると、問い合わせが期待通りには最適化されません。

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. 上記の例では、毎月、新しいパーティションを作ることになりますから、必要なDDLを自動的に生成するスクリプトを作るのが賢明かもしれません。

5.12.2.2. パーティションの保守 #

<title>Partition Maintenance</title>

Normally the set of partitions established when initially defining the table is not intended to remain static. It is common to want to remove partitions holding old 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. 最初にテーブルを定義した時に作成したパーティションの集合は、通常はそのまま静的に残ることを意図したものではありません。 古いデータを持つパーティションを削除し、新しいデータの入った新しいパーティションを定期的に作成したいというのが普通です。 パーティショニングのもっとも重要な利点の一つは、パーティショニングがなければ大変なことになるであろうこの作業を、大量のデータを物理的に動かすのではなく、パーティション構造を操作することにより、ほとんど一瞬にして実行できるという、まさにそのことなのです。

The simplest option for removing old data is to drop the partition that is no longer necessary: 古いデータを削除する最も単純な方法は、次のように、不要になったパーティションを削除することです。

DROP TABLE measurement_y2006m02;

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. これはすべてのレコードを個別に削除する必要がないため、数百万行のレコードを非常に高速に削除できます。 ただし、上記のコマンドは親テーブルについてACCESS EXCLUSIVEロックを取得する必要があることに注意してください。

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. This has two forms: 別の方法で多くの場合に望ましいのは、パーティションテーブルからパーティションを削除する一方で、パーティションそれ自体はテーブルとしてアクセス可能なまま残すことです。 これには2つの形式があります。

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

These allow 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</command>, <application>pg_dump</application>, or similar tools. It might also be a useful time to aggregate data into smaller formats, perform other data manipulations, or run reports. The first form of the command requires an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table. Adding the <literal>CONCURRENTLY</literal> qualifier as in the second form allows the detach operation to require only <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the parent table, but see <link linkend="sql-altertable-detach-partition"><literal>ALTER TABLE ... DETACH PARTITION</literal></link> for details on the restrictions. こうすると、データを削除する前に、そのデータについて追加の操作が実行できます。 例えば、COPYpg_dumpや類似のツールを使ってデータのバックアップをする好機となることが多いでしょう。 また、データを集計してより小さな形式にする、その他のデータ操作を実行する、レポート作成を実行するなどのための好機となるかもしれません。 コマンドの最初の形式はACCESS EXCLUSIVEロックを親テーブルに必要とします。 二番目の形式のようにCONCURRENTLY修飾子を追加すると、デタッチ操作の際にSHARE UPDATE EXCLUSIVEを親テーブルにかけるだけで済みますが、制限の詳細についてはALTER TABLE ... DETACH PARTITIONを参照してください。

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: 同様に、新しいデータを扱うために新しいパーティションを追加できます。 上で元のパーティションを作ったのと全く同じように、パーティションテーブル内に空のパーティションを以下のように作成できます。

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

As an alternative to creating a new partition, it is sometimes more convenient to create a new table separate from the partition structure and attach it as a partition later. This allows new data to be loaded, checked, and transformed prior to it appearing in the partitioned table. Moreover, the <literal>ATTACH PARTITION</literal> operation requires only a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the partitioned table rather than the <literal>ACCESS EXCLUSIVE</literal> lock required by <command>CREATE TABLE ... PARTITION OF</command>, so it is more friendly to concurrent operations on the partitioned table; see <link linkend="sql-altertable-attach-partition"><literal>ALTER TABLE ... ATTACH PARTITION</literal></link> for additional details. The <link linkend="sql-createtable-parms-like"><literal>CREATE TABLE ... LIKE</literal></link> option can be helpful to avoid tediously repeating the parent table's definition; for example: 《マッチ度[66.033755]》別の方法として、パーティション構造の外で新しいテーブルを作成し、後でパーティションとしてアタッチする方が便利なことがあります。 これにより、パーティションテーブルに現れる前に新しいデータをロードし、チェックし、変換することができます。 さらに、ATTACH PARTITION操作は、CREATE TABLE ... PARTITION OFで必要とされるACCESS EXCLUSIVEロックとは対照的に、パーティションテーブルに対してはSHARE UPDATE EXCLUSIVEロックを必要とするので、パーティションテーブルに対する並行操作に対してよりフレンドリーです。 CREATE TABLE ... LIKEオプションは、親テーブルの定義を繰り返し書くのを避けるのに役立ちます。 《機械翻訳》新しいパーティションを作成する代わりに、パーティション構造とは別に新しいテーブルを作成し、後でパーティションとしてアタッチする方が便利な場合があります。 これにより、分割されたデータに表示される前に、新しいテーブルをロード、チェック、変換できるようになります。 さらに、ATTACH PARTITION操作は、CREATE TABLE ... PARTITION OFで必要とされるACCESS EXCLUSIVEロックとは異なり、パーティションテーブルに対してはSHARE UPDATE EXCLUSIVEロックのみが必要です。 ALTER TABLE ... ATTACH PARTITIONを参照してください。 CREATE TABLE ... LIKEオプションは、親テーブルの定義をうんざりするほど繰り返さないようにするのに役立ちます。 例の場合:。

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

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'

&#45;- possibly some other data preparation work

-- その他のデータ準備操作を行うこともあります。

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

Note that when running the <command>ATTACH PARTITION</command> command, the table will be scanned to validate the partition constraint while holding an <literal>ACCESS EXCLUSIVE</literal> lock on that partition. As shown above, it is recommended to avoid this scan by creating a <literal>CHECK</literal> constraint matching the expected partition constraint on the table prior to attaching it. Once the <command>ATTACH PARTITION</command> is complete, it is recommended to drop the now-redundant <literal>CHECK</literal> constraint. If the table being attached is itself a partitioned table, then each of its sub-partitions will be recursively locked and scanned until either a suitable <literal>CHECK</literal> constraint is encountered or the leaf partitions are reached. 《機械翻訳》ノートは、ATTACH PARTITIONコマンドを実行する場合、テーブルはパーティション制約を検証するためにスキャンされ、そのパーティションでACCESS EXCLUSIVEロックを保持します。 上記のように、このスキャンを回避するために、CHECK制約マッチングを作成することをお勧めします。 テーブルをアタッチする前に、予想されるパーティション制約を作成します。 ATTACH PARTITIONが完成したら、冗長になったCHECK制約を削除することをお勧めします。 アタッチされているテーブルが分割された自分自身のテーブルである場合、そのサブパーティションのそれぞれは、適切なCHECK制約に遭遇するか、リーフパーティションに到達するまで、再帰的にロックされ、スキャンされる。

Similarly, if the partitioned table has a <literal>DEFAULT</literal> partition, it is recommended to create a <literal>CHECK</literal> constraint which excludes the to-be-attached partition's constraint. If this is not done, the <literal>DEFAULT</literal> partition will be scanned to verify that it contains no records which should be located in the partition being attached. This operation will be performed whilst holding an <literal>ACCESS EXCLUSIVE</literal> lock on the <literal> DEFAULT</literal> partition. If the <literal>DEFAULT</literal> partition is itself a partitioned table, then each of its partitions will be recursively checked in the same way as the table being attached, as mentioned above. 《マッチ度[91.126761]》同様に、そのパーティションがDEFAULTパーティションを持っているなら、アタッチ予定のパーティションの制約を含まないCHECK制約を作成することをお勧めします。 これをしておかないと、アタッチ予定のパーティション中にレコードがないことを確認するためにDEFAULTパーティションが走査されます。 この操作の間、ACCESS EXCLUSIVEロックがDEFAULTパーティションに保持されます。 DEFAULTパーティション自体がパーティションテーブルなら、個々のパーティションは、上で述べたようにアタッチ予定のテーブルと同じ方法で走査されます。

As mentioned earlier, it is possible to create indexes on partitioned tables so that they are applied automatically to the entire hierarchy. This can be very convenient as not only will all existing partitions be indexed, but any future partitions will be as well. However, one limitation when creating new indexes on partitioned tables is that it is not possible to use the <literal>CONCURRENTLY</literal> qualifier, which could lead to long lock times. To avoid this, you can use <command>CREATE INDEX ON ONLY</command> the partitioned table, which creates the new index marked as invalid, preventing automatic application to existing partitions. Instead, indexes can then be created individually on each partition using <literal>CONCURRENTLY</literal> and <firstterm>attached</firstterm> to the partitioned index on the parent using <command>ALTER INDEX ... ATTACH PARTITION</command>. Once indexes for all the partitions are attached to the parent index, the parent index will be marked valid automatically. Example: 《マッチ度[64.509804]》前述のとおり、階層全体に自動で適用されるようにパーティションテーブル上にインデックスを作成することが可能です。 既存のパーティションだけではなく将来作成されるパーティションもインデックス付けされるため、これはとても便利です。 一つの制限は、そのようなパーティションのインデックスを作成する場合CONCURRENTLY句を使うことができません。 長いロック時間を避けるためには、パーティションテーブルにCREATE INDEX ON ONLYを使うことが可能です。 そのようなインデックスは無効とマークされ、パーティションは適用するインデックスを自動で取得しません。 パーティション上のインデックスはCONCURRENTLYを使用して個々に作成することができ、後からALTER INDEX .. ATTACH PARTITIONを使用して親のインデックスにattachedできます。 全てのパーティションに対してインデックスがアタッチされた時点で、親のインデックスは、自動で有効とマークされます。 例を示します。 《機械翻訳》前述したように、テーブルパーティションにインデックスを作成して、階層全体に自動的に適用することができます。 これは、既存のすべてのパーティションにインデックスが作成されるだけでなく、将来のパーティションにもインデックスが作成されるため、非常に便利です。 ただし、テーブルパーティションで新しいインデックスを作成する場合の1つの制限は、CONCURRENTLY修飾子を使用できないことです。 これにより、ロック時間が長くなる可能性があります。 これを回避するには、CREATEインデックスON ONLYを使用して分割されたテーブルを作成します。 これにより、無効とマークされた新しいインデックスが作成され、既存の分割への自動アプリケーションが防止されます。 代わりに、CONCURRENTLYを使用して各パーティションに個別にインデックスを作成し、ALTER INDEX ... ATTACH PARTITIONを使用して親の分割されたインデックスにアタッチすることができます。 すべてのパーティションのインデックスが親インデックスにアタッチされると、親インデックスは自動的に有効とマークされます。 例:。

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX CONCURRENTLY measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
...

This technique can be used with <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints too; the indexes are created implicitly when the constraint is created. Example: この手法は、UNIQUEPRIMARY KEY制約でも使用できます。 制約が作成された際にインデックスは暗黙的に作成されます。 例を示します。

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...

5.12.2.3. 制限事項 #

<title>Limitations</title>

The following limitations apply to partitioned tables: パーティションテーブルには以下の制限事項があります。

  • To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions. パーティション化テーブルに一意性制約または主キー制約を作成するには、パーティションキーに式または関数呼び出しが含まれないようにし、制約の列にすべてのパーティションキー列が含まれている必要があります。 この制限が存在するのは、制約を構成する個々の索引が、独自のパーティション内でのみ一意性を直接強制できるためです。 したがって、パーティション構造自体が異なるパーティションに重複がないことを保証する必要があります。

  • Similarly an exclusion constraint must include all the partition key columns. Furthermore the constraint must compare those columns for equality (not e.g. <literal>&amp;&amp;</literal>). Again, this limitation stems from not being able to enforce cross-partition restrictions. The constraint may include additional columns that aren't part of the partition key, and it may compare those with any operators you like. 《機械翻訳》同様に、排他制約はすべてのパーティションキー列をincludeする必要があります。 さらに、制約はそれらの列が等しいかどうかを比較する必要があります(&&などではありません)。 繰り返しますが、この制限はクロスとパーティションの制限を強制できないことに起因しています。 制約はパーティションキーのパートではない追加の列をincludeすることがあり、それらを任意の演算子と比較することがあります。

  • <literal>BEFORE ROW</literal> triggers on <literal>INSERT</literal> cannot change which partition is the final destination for a new row. INSERTBEFORE ROWトリガーは、どのパーティションが新しい行の最終目的地であるかを変更することはできません。

  • Mixing temporary and permanent relations in the same partition tree is not allowed. Hence, if the partitioned table is permanent, so must be its partitions and likewise if the partitioned table is temporary. When using temporary relations, all members of the partition tree have to be from the same session. 一時リレーションと永続的リレーションを同じパーティションツリーに混合することはできません。 ですから、パーティション化されたテーブルが永続的なら、パーティションも永続的でなければなりません。 同様にパーティション化されたテーブルが一時的なら、パーティションも一時的でなければなりません。 一時リレーションを使う場合は、パーティションツリーのすべてのメンバは同じセッションに由来しなければなりません。

Individual partitions are linked to their partitioned table using inheritance behind-the-scenes. However, it is not possible to use all of the generic features of inheritance with declaratively partitioned tables or their partitions, as discussed below. Notably, a partition cannot have any parents other than the partitioned table it is a partition of, nor can a table inherit from both a partitioned table and a regular table. That means partitioned tables and their partitions never share an inheritance hierarchy with regular tables. 個々のパーティションは継承を背景にパーティションテーブルに紐付けられています。 しかし、宣言的パーティションテーブルもしくはそれらのパーティションでは継承の一般的な機能の一部(後述)を使用することはできません。 例えば、パーティションテーブルのパーティションは、そのパーティションテーブル以外の親を持つことができませんし、また一般のテーブルはパーティションテーブルをその親にしてパーティションテーブルから継承することはできません。 これはつまり、パーティションテーブルおよびそれらのパーティションは一般のテーブルと継承によって繋がることができないということです。

Since a partition hierarchy consisting of the partitioned table and its partitions is still an inheritance hierarchy, <structfield>tableoid</structfield> and all the normal rules of inheritance apply as described in <xref linkend="ddl-inherit"/>, with a few exceptions: パーティションテーブルとそのパーティションを構成するパーティションの階層は継承の階層でもあるので、tableoidと継承におけるすべての通常の規則が5.11で説明したとおりに適用されますが、いくつか例外があります。 最も重要な例外を以下に示します。

  • Partitions cannot have columns that are not present in the parent. It is not possible to specify columns when creating partitions with <command>CREATE TABLE</command>, nor is it possible to add columns to partitions after-the-fact using <command>ALTER TABLE</command>. Tables may be added as a partition with <command>ALTER TABLE ... ATTACH PARTITION</command> only if their columns exactly match the parent. パーティションは親に存在しない列を持つことができません。 パーティションをCREATE TABLEで作成する時に列を指定することはできませんし、作成後にALTER TABLEでパーティションに列を追加することもできません。 テーブルをALTER TABLE ... ATTACH PARTITIONでパーティションとして追加できるのは、その列が完全に親と一致している場合のみです。

  • Both <literal>CHECK</literal> and <literal>NOT NULL</literal> constraints of a partitioned table are always inherited by all its partitions. <literal>CHECK</literal> constraints that are marked <literal>NO INHERIT</literal> are not allowed to be created on partitioned tables. You cannot drop a <literal>NOT NULL</literal> constraint on a partition's column if the same constraint is present in the parent table. パーティションテーブルのCHECK制約とNOT NULL制約はいずれも必ずすべてのパーティションに継承されます。 パーティションテーブルでNO INHERITの印を付けたCHECK制約を作ることはできません。 同じ制約が親テーブルに存在する場合、親テーブルの列に存在するNOT NULL制約をパーティションの列から削除することはできません。

  • Using <literal>ONLY</literal> to add or drop a constraint on only the partitioned table is supported as long as there are no partitions. Once partitions exist, using <literal>ONLY</literal> will result in an error for any constraints other than <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>. Instead, constraints on the partitions themselves can be added and (if they are not present in the parent table) dropped. パーティションテーブルに対してのみ制約を追加または削除する場合、パーティションが存在しない限り、ONLYを使用することがサポートされています。 ひとたびパーティションが存在すれば、ONLYを使用すると、UNIQUEおよびPRIMARY KEY以外の制約に対してエラーが発生します。 その代わりに、パーティション自身の制約を追加することや(親テーブルに存在しない場合)削除することが可能です。

  • As a partitioned table does not have any data itself, attempts to use <command>TRUNCATE</command> <literal>ONLY</literal> on a partitioned table will always return an error. パーティションテーブルは直接データを所有することはないため、TRUNCATE ONLYをパーティションテーブルに対して使用しようとすると、必ずエラーが返されます。

5.12.3. 継承を用いたパーティショニング #

<title>Partitioning Using Inheritance</title>

While the built-in declarative partitioning is suitable for most common use cases, there are some circumstances where a more flexible approach may be useful. Partitioning can be implemented using table inheritance, which allows for several features not supported by declarative partitioning, such as: 組み込みの宣言的パーティショニングは、ほとんどの一般的な利用例に適合しますが、もっと柔軟な方式が便利な状況もあります。 パーティショニングはテーブルの継承を使用して実装することも可能で、これは宣言的パーティショニングではサポートされない以下のような機能が利用できます。

  • For declarative partitioning, partitions must have exactly the same set of columns as the partitioned table, whereas with table inheritance, child tables may have extra columns not present in the parent. 宣言的パーティショニングの場合、パーティションは正確にパーティションテーブルと同じ列の集合を持たなければなりません。 一方テーブルの継承では、子テーブルは親テーブルに存在しない追加の列を持つかもしれません。

  • Table inheritance allows for multiple inheritance. テーブルの継承では、複数の継承が可能です。

  • 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 child tables effectively, query performance might be poor.) 宣言的パーティショニングではリストパーティショニング、範囲パーティショニングとハッシュパーティショニングしかサポートされませんが、テーブルの継承ではユーザが選択した方法に従ってデータを分割できます。 (ただし、制約による除外が子テーブルを効果的に分離できない場合、問い合わせのパフォーマンスが悪くなるかもしれないことに注意してください。)

5.12.3.1. 例 #

<title>Example</title>

This example builds a partitioning structure equivalent to the declarative partitioning example above. Use the following steps: この例は、上の宣言的パーティショニングの例と等価な構造のパーティショニングを作成しています。 以下の手順に従います。

  1. Create the <quote>root</quote> table, from which all of the <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 child tables. There is no point in defining any indexes or unique constraints on it, either. For our example, the root table is the <structname>measurement</structname> table as originally defined: rootテーブルを作成します。 すべてのテーブルはこれを継承します。 このテーブルにデータは含まれません。 子テーブルに同じように適用されるのでなければ、このテーブルにチェック制約を定義しないでください。 このテーブル上にインデックスや一意制約を定義することにも意味はありません。 以下の例では、rootテーブルは最初に定義したのと同じmeasurementテーブルです。

    CREATE TABLE measurement (
        city_id         int not null,
        logdate         date not null,
        peaktemp        int,
        unitsales       int
    );
    

  2. Create several <quote>child</quote> tables that each inherit from the root table. Normally, these tables will not add any columns to the set inherited from the root. Just as with declarative partitioning, these tables are in every way normal <productname>PostgreSQL</productname> tables (or foreign tables). いくつかのテーブルを作成し、それぞれrootテーブルを継承するものにします。 通常、これらのテーブルはrootから継承したものに列を追加しません。 宣言的パーティショニングの場合と同じく、これらのテーブルはすべての点で普通のPostgreSQLのテーブル(あるいは外部テーブル)と同じです。

    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);
    

  3. Add non-overlapping table constraints to the child tables to define the allowed key values in each. 子テーブルに、重なり合わないテーブル制約を追加し、各テーブルに許されるキー値を定義します。

    Typical examples would be: 典型的な例は次のようなものです。

    CHECK ( x = 1 )
    CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
    CHECK ( outletID >= 100 AND outletID < 200 )
    

    Ensure that the constraints guarantee that there is no overlap between the key values permitted in different child tables. A common mistake is to set up range constraints like: 制約により、異なる子テーブルで許されるキー値に重なりがないと保証されるようにします。 よくある誤りは、次のような範囲制約を設定することです。

    CHECK ( outletID BETWEEN 100 AND 200 )
    CHECK ( outletID BETWEEN 200 AND 300 )
    

    This is wrong since it is not clear which child table the key value 200 belongs in. Instead, ranges should be defined in this style: キー値200がどちらの子テーブルに属するか明らかではないため、これは誤っています。 代わりにこの方法で範囲を定義すべきです。

    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);
    

  4. For each child table, create an index on the key column(s), as well as any other indexes you might want. 各子テーブルについて、キー列にインデックスを作成し、またその他のインデックスも必要に応じて作成します。

    CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
    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);
    

  5. We want our application to be able to say <literal>INSERT INTO measurement ...</literal> and have the data be redirected into the appropriate child table. We can arrange that by attaching a suitable trigger function to the root table. If data will be added only to the latest child, we can use a very simple trigger function: アプリケーションでINSERT INTO measurement ...を実行することができ、そのときにデータが適切な子テーブルにリダイレクトされることが望ましいです。 rootテーブルに適当なトリガー関数を追加することでそのような設定にできます。 データが最後の子テーブルにしか追加されないなら、次のような非常に単純なトリガー関数を使うことができます。

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    

    After creating the function, we create a trigger which calls the trigger function: 関数を作成した後で、このトリガ関数を呼ぶトリガを作成します。

    CREATE TRIGGER insert_measurement_trigger
        BEFORE INSERT ON measurement
        FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
    

    We must redefine the trigger function each month so that it always inserts into the current child table. The trigger definition does not need to be updated, however. 常に現在の子テーブルに挿入するようにするためには、毎月、トリガ関数を再定義しなくてはいけません。 しかし、トリガ定義を更新する必要はありません。

    We might want to insert data and have the server automatically locate the child table into which the row should be added. We could do this with a more complex trigger function, for example: データを挿入したら、サーバが行を追加すべき子テーブルを自動的に決定するようにしたいかもしれません。 これは以下のようなもっと複雑なトリガ関数を作成することにより可能です。

    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;
    

    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 child table. トリガ定義は前と同じです。 それぞれのIFテストを子テーブルのCHECK制約と正確に一致させなければならないことに注意してください。

    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. この関数は単一月の場合より複雑になりますが、頻繁に更新する必要はありません。なぜなら条件分岐を前もって追加しておくことが可能だからです。

    注記

    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. 実際には、ほとんどの挿入が一番新しい子テーブルに入る場合は、その子を最初に検査することが最善です。 簡単にするため、この例でのほかの部分と同じ順番でのトリガのテストを示しました。

    A different approach to redirecting inserts into the appropriate child table is to set up rules, instead of a trigger, on the root table. For example: 挿入を適切な子テーブルにリダイレクトする別の方法は、rootテーブルにトリガーではなくルールを設定することです。 例えば次のようにします。

    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.*);
    

    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. ルールはトリガーに比べるとかなり大きなオーバーヘッドがありますが、このオーバーヘッドは一つの問い合わせに対して一度だけで行ごとではないので、この方法にも一括挿入の状況では利点があります。 ただし、ほとんどの場合はトリガーを使う方法の方が良いパフォーマンスを得られます。

    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 child table rather than directly into the root. <command>COPY</command> does fire triggers, so you can use it normally if you use the trigger approach. COPYはルールを無視することに注意してください。 データの挿入にCOPYを使いたい場合は、rootではなく正しい子テーブルにコピーする必要があります。 トリガーであればCOPYでも起動されるので、トリガーを使う方法であれば通常通りに使用できます。

    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 root table instead. ルールを使う方法のもう一つの欠点は、ルールの集合が挿入日付に対応しきれていない場合に、強制的にエラーにする簡単な方法がないことです。 この場合、データは警告などを出すことなくrootテーブルに入ります。

  6. Ensure that the <xref linkend="guc-constraint-exclusion"/> configuration parameter is not disabled in <filename>postgresql.conf</filename>; otherwise child tables may be accessed unnecessarily. 設定パラメータconstraint_exclusionpostgresql.confで無効にされないようにしてください。 他の子テーブルが不要にアクセスされるかもしれません。

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 child table each month, so it might be wise to write a script that generates the required DDL automatically. 以上のように、複雑なテーブルの階層はたくさんのDDLが必要となります。 上記の例では、毎月新しい子テーブルを作成することになりますが、必要となるDDLを自動的に生成するスクリプトを書くのが賢明です。

5.12.3.2. 継承パーティショニングの保守 #

<title>Maintenance for Inheritance Partitioning</title>

To remove old data quickly, simply drop the child table that is no longer necessary: 古いデータを高速に削除するには、不要になった子テーブルを単に削除します。

DROP TABLE measurement_y2006m02;

To remove the child table from the inheritance hierarchy table but retain access to it as a table in its own right: 子テーブルを継承階層テーブルから削除するものの、それ自体をテーブルとしてアクセスできるようにするには、次のようにします。

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

To add a new child table to handle new data, create an empty child table just as the original children were created above: 新しいデータを扱う新しい子テーブルを追加するには、上で最初の子テーブルを作成したときと同じように空の子テーブルを作成します。

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

Alternatively, one may want to create and populate the new child table before adding it to the table hierarchy. This could allow data to be loaded, checked, and transformed before being made visible to queries on the parent table. あるいは、新たな子テーブルをテーブル階層に追加する前に作成してデータ投入したい場合もあるでしょう。 これは、親テーブルのクエリから見えるようになる前にデータのロード、確認、変換できるでしょう。

CREATE TABLE measurement_y2008m02
  (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'

&#45;- possibly some other data preparation work

-- その他のデータ準備操作を行うこともあります。
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.12.3.3. 警告 #

<title>Caveats</title>

The following caveats apply to partitioning implemented using inheritance: 継承を使用して実装したパーティショニングには以下の注意事項があります。

  • 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 child tables and creates and/or modifies associated objects than to write each by hand. すべてのCHECK制約が相互に排他的であることを自動的に確認する手段はありません。 各子テーブルを手作業で作成するよりも、子テーブルを生成し、関連オブジェクトを作成、更新するコードを作成するのが安全でしょう。

  • Indexes and foreign key constraints apply to single tables and not to their inheritance children, hence they have some <link linkend="ddl-inherit-caveats">caveats</link> to be aware of. インデックスと外部キー制約は継承上の子ではなく、単一テーブルに適用されます。したがってそれらは警告に気を付ける必要があります。

  • 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 require it to move to another partition. 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 child tables, but it makes management of the structure much more complicated. ここで示した方法は、行のキー列の値が変わらないか、あるいは、少なくとも他のパーティションへの移動が必要になるような変更はないということを前提としています。 そのような変更をしようとするUPDATECHECK制約のためにエラーになります。 このような場合を処理できる必要があるなら、子テーブルに適切なUPDATEトリガーを設定することもできますが、構造の管理がずっと複雑になります。

  • If you are using manual <command>VACUUM</command> or <command>ANALYZE</command> commands, don't forget that you need to run them on each child table individually. A command like: 手作業でVACUUMあるいはANALYZEコマンドを実行している場合、それを個々の子テーブルに対して実行する必要があることを忘れないでください。 次のようなコマンドは、

    ANALYZE measurement;
    

    will only process the root table. rootテーブルしか処理しません。

  • <command>INSERT</command> statements with <literal>ON CONFLICT</literal> clauses are unlikely to work as expected, as the <literal>ON CONFLICT</literal> action is only taken in case of unique violations on the specified target relation, not its child relations. ON CONFLICT句のあるINSERT文は恐らく期待通りに動作しないでしょう。 ON CONFLICTの動作は対象となる指定リレーション上での一意制約違反の場合にのみ発生するもので、その子リレーションの場合には発生しないからです。

  • Triggers or rules will be needed to route rows to the desired 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. アプリケーションがパーティショニングのスキームについて明示的に意識しているのでなければ、トリガーまたはルールで行を適切な子テーブルに振り向ける必要があります。 トリガーを書くのは複雑であり、また宣言的パーティショニングによって内部的に実行されるタプルの振り向けよりずっと遅いでしょう。

5.12.4. パーティション除去 #

<title>Partition Pruning</title>

<firstterm>Partition pruning</firstterm> is a query optimization technique that improves performance for declaratively partitioned tables. As an example: パーティション除去は、宣言的パーティショニングテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。 例えば、

SET enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

Without partition pruning, the above query would scan each of the partitions of the <structname>measurement</structname> table. With partition pruning enabled, the planner will examine the definition of each partition and prove that the partition need not be scanned because it could not contain any rows meeting the query's <literal>WHERE</literal> clause. When the planner can prove this, it excludes (<firstterm>prunes</firstterm>) the partition from the query plan. パーティション除去がなければ、上記の問い合わせはmeasurementテーブルの各パーティションをスキャンするでしょう。 パーティション除去が有効になっているとき、プランナはそれぞれのパーティションの定義を検証し、パーティションが問い合わせのWHEREに一致する行を含んでいないためにスキャンされる必要が無いことを証明します。 プランナはこれを証明すると、問い合わせ計画からそのパーティションを除外(除去)します。

By using the EXPLAIN command and the <xref linkend="guc-enable-partition-pruning"/> configuration parameter, it's possible to show the difference between a plan for which partitions have been pruned and one for which they have not. A typical unoptimized plan for this type of table setup is: EXPLAINコマンドと設定パラメータenable_partition_pruning を使用することによって、パーティションの除去をした計画とそうでない計画の違いを明らかにすることを可能とします。 この種類のテーブル設定に対する典型的な最適化されない計画は以下のようになります。

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

Some or all of the partitions might use index scans instead of full-table sequential scans, but the point here is that there is no need to scan the older partitions at all to answer this query. When we enable partition pruning, we get a significantly cheaper plan that will deliver the same answer: 一部のパーティション、もしくはすべてのパーティションで、テーブル全体に対するシーケンシャルスキャンではなく、インデックススキャンが使用される可能性があります。 しかしここで重要なことは、この問い合わせに対する回答のために古いパーティションをスキャンする必要はまったく無いということです。 パーティション除去を有効にしたとき、同じ回答を返す計画で、大幅に安価なものを得ることができます。

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

Note that partition pruning is driven only by the constraints defined implicitly by the partition keys, not by the presence of indexes. Therefore it isn't necessary to define indexes on the key columns. Whether an index needs to be created for a given partition depends on whether you expect that queries that scan the partition will generally scan a large part of the partition or just a small part. An index will be helpful in the latter case but not the former. パーティション除去はパーティションキーによって暗黙的に定義された制約のみで動作し、インデックスの有無では動作しないことに注意してください。 よってキー列のインデックスを定義することは必要ではありません。 あるパーティションでインデックスが必要かどうかは、パーティションをスキャンする問い合わせが通常はパーティションの大部分をスキャンするのか、あるいは小さな部分をスキャンするのかによります。 インデックスは後者において役立ちますが、前者では役立ちません。

Partition pruning can be performed not only during the planning of a given query, but also during its execution. This is useful as it can allow more partitions to be pruned when clauses contain expressions whose values are not known at query planning time, for example, parameters defined in a <command>PREPARE</command> statement, using a value obtained from a subquery, or using a parameterized value on the inner side of a nested loop join. Partition pruning during execution can be performed at any of the following times: パーティション除去は与えられた問い合わせの計画時だけでなく、問い合わせの実行時にも可能です。 問い合わせの計画時、句が値のわからない式を含むときにより多くのパーティションを除去できるため便利です。 例えば、PREPARE文中に定義されたパラメータや、副問い合わせから取得される値の利用、ネステッドループ結合の内側でパラメータ化された値の利用です。 実行中のパーティション除去は、次のいずれかの時点で可能です。

  • During initialization of the query plan. Partition pruning can be performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's <command>EXPLAIN</command> or <command>EXPLAIN ANALYZE</command>. It is possible to determine the number of partitions which were removed during this phase by observing the <quote>Subplans Removed</quote> property in the <command>EXPLAIN</command> output. 問い合わせ計画の初期化時。 パーティション除去は、パラメータの値が分かる実行の初期化段階時に可能です。 この段階で除去されたパーティションは、問い合わせのEXPLAINEXPLAIN ANALYZE中に姿を見せることはないでしょう。 EXPLAIN出力中にSubplans removedプロパティを観察することによってこの段階で削除されるパーティションの数を特定することが可能です。

  • During actual execution of the query plan. Partition pruning may also be performed here to remove partitions using values which are only known during actual query execution. This includes values from subqueries and values from execution-time parameters such as those from parameterized nested loop joins. Since the value of these parameters may change many times during the execution of the query, partition pruning is performed whenever one of the execution parameters being used by partition pruning changes. Determining if partitions were pruned during this phase requires careful inspection of the <literal>loops</literal> property in the <command>EXPLAIN ANALYZE</command> output. Subplans corresponding to different partitions may have different values for it depending on how many times each of them was pruned during execution. Some may be shown as <literal>(never executed)</literal> if they were pruned every time. 問い合わせ計画の実行時。 パーティション除去では実際に問い合わせの実行をする際にのみ分かる値を用いてパーティションを取り除くことも同様に可能でしょう。 これは、副問い合わせからの値やネステッドループ結合でパラメータ化されたような実行時のパラメータからの値を含みます。 それらのパラメータの値は問い合わせの実行時に何回も変わるかもしれないため、パーティション除去はパーティション除去に使われる実行パラメータの値が変わるたびに行われます。 この段階で除去されたパーティションを特定するには、EXPLAIN ANALYZE出力中のloopsプロパティの慎重な調査が必要です。 異なるパーティションに対応するサブプランは、それぞれ実行時に除去された回数に応じて異なる値を持っているかもしれません。 毎回パーティションが除去される場合、一部は(never executed)と表示されるでしょう。

Partition pruning can be disabled using the <xref linkend="guc-enable-partition-pruning"/> setting. パーティション除去はenable_partition_pruning設定を使うことにより無効化できます。

5.12.5. パーティショニングと制約による除外 #

<title>Partitioning and Constraint Exclusion</title>

<firstterm>Constraint exclusion</firstterm> is a query optimization technique similar to partition pruning. While it is primarily used for partitioning implemented using the legacy inheritance method, it can be used for other purposes, including with declarative partitioning. 制約による除外はパーティション除去と同様に問い合わせ最適化技術です。 主に従来の継承方法を使用して実装されたパーティショニングのために使用されると同時に 宣言的パーティショニングを含む他の目的に使うことができます。

Constraint exclusion works in a very similar way to partition pruning, except that it uses each table's <literal>CHECK</literal> constraints &mdash; which gives it its name &mdash; whereas partition 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. 各テーブルの名前の付いたCHECK制約を使用すること(一方でパーティション除去は宣言的パーティショニングの場合にのみ存在するテーブルのパーティション境界を使用します)を除いて、制約による除外はパーティション除去と極めて同様な方法で動作します。 その他の違いは、制約による除外は計画時にのみ適用され実行時にパーティションの削除を試しません。

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 their internal partition bounds, constraint exclusion may be able to elide additional partitions from the query plan. 制約による除外はCHECK制約を使用しているためパーティション除去と比べて遅いですが、ときどき利点として使うことができます。 なぜなら、内部のパーティション境界に加えて宣言的パーティションテーブルにも制約は定義できるため、制約による除外は問い合わせ計画から追加のパーティションを取り除けるかもしれません。

The default (and recommended) setting of <xref linkend="guc-constraint-exclusion"/> is neither <literal>on</literal> nor <literal>off</literal>, but an intermediate setting called <literal>partition</literal>, which causes the technique to be applied only to queries that are likely to be working on inheritance partitioned tables. The <literal>on</literal> setting causes the planner to examine <literal>CHECK</literal> constraints in all queries, even simple ones that are unlikely to benefit. 実のところ、constraint_exclusionのデフォルト(かつ推奨)の設定は、onでもoffでもなく、partitionという中間の設定です。 これによりこの技法は、継承パーティションテーブルに対して動作することになる問い合わせのみに適用されるようになります。 on設定にすると、プランナは、効果のなさそうな単純な問い合わせを含め、すべての問い合わせでCHECK制約を検証します。

The following caveats apply to constraint exclusion: 制約による除外には以下の注意事項が適用されます。

  • Constraint exclusion is only applied during query planning, unlike partition pruning, which can also be applied during query execution. 問い合わせの実行中にも適用できるパーティション除去とは違い、制約による除外は問い合わせ計画時にのみ適用されます。

  • Constraint exclusion only works when the query's <literal>WHERE</literal> 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 child table the function's value might fall into at run time. 制約による除外は問い合わせのWHERE句が定数(または外部から供給されたパラメータ)を含んでいたときにのみ動作します。例えば、CURRENT_TIMESTAMPのような非immutable関数に対する比較は、関数の結果値がどの子テーブルに該当するかを実行時にプランナが知ることが出来ないため、最適化できません。

  • Keep the partitioning constraints simple, else the planner may not be able to prove that child tables might 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 contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators, because only B-tree-indexable column(s) are allowed in the partition key. パーティショニングの制約を簡単にしておいてください。そうしないとプランナは、子テーブルを使う必要がないことを立証できないでしょう。 前述の例で示したとおり、リストパーティショニングのために簡単な等号条件を使用してください。また範囲パーティショニングのために簡単な範囲テストを使用してください。 手っ取り早い良い方法は、パーティショニングの制約がパーティショニング列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。 なぜならパーティションキーにはB-treeでインデックス可能な列だけが使用できるからです。

  • 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 child tables; don't try to use many thousands of children. 親テーブルのすべての子テーブルのすべての制約は、制約による除外で試験されます。 よって子テーブルの数が多くなれば問い合わせ計画の時間がかなり増加します。 そのため、従来の継承を基にしたパーティショニングはおそらく100個までの子でうまく動作します。 何千もの子テーブルを使用することは避けてください。

5.12.6. 宣言的パーティショニングのベストプラクティス #

<title>Best Practices for Declarative Partitioning</title>

The choice of how to partition a table should be made carefully, as the performance of query planning and execution can be negatively affected by poor design. 不十分な設計によってクエリ計画および実行性能に負の影響がでる可能性があるため テーブルのパーティション方法の選択は注意して行う必要があります。

One of the most critical design decisions will be the column or columns by which you partition your data. Often the best choice will be to partition by the column or set of columns which most commonly appear in <literal>WHERE</literal> clauses of queries being executed on the partitioned table. <literal>WHERE</literal> clauses that are compatible with the partition bound constraints can be used to prune unneeded partitions. However, you may be forced into making other decisions by requirements for the <literal>PRIMARY KEY</literal> or a <literal>UNIQUE</literal> constraint. Removal of unwanted data is also a factor to consider when planning your partitioning strategy. An entire partition can be detached fairly quickly, so it may be beneficial to design the partition strategy in such a way that all data to be removed at once is located in a single partition. 最も重要な設計の決定の一つは、データを分割するための一つまたは複数の列です。 大抵最適な選択は、パーティションテーブル上で実行されるクエリのWHERE句に最もよく現れる列または列の組み合わせによって分割することです。 パーティション範囲制約と一致し互換性があるWHERE句の項目は、不要なパーティションを取り除く為に使うことができます。 しかしながら、PRIMARY KEYもしくはUNIQUE制約の条件により、他の決定を強いられるかもしれません。 不要なデータの削除も同様にパーティショニング戦略を計画する際に考えるべき要素です。 すべてのパーティションはとても早くデタッチすることができるため、一度に削除される全てのデータが単一のパーティション中に設置されるようにパーティション戦略を設計することが有益かもしれません。

Choosing the target number of partitions that the table should be divided into is also a critical decision to make. Not having enough partitions may mean that indexes remain too large and that data locality remains poor which could result in low cache hit ratios. However, dividing the table into too many partitions can also cause issues. Too many partitions can mean longer query planning times and higher memory consumption during both query planning and execution, as further described below. When choosing how to partition your table, it's also important to consider what changes may occur in the future. For example, if you choose to have one partition per customer and you currently have a small number of large customers, consider the implications if in several years you instead find yourself with a large number of small customers. In this case, it may be better to choose to partition by <literal>HASH</literal> and choose a reasonable number of partitions rather than trying to partition by <literal>LIST</literal> and hoping that the number of customers does not increase beyond what it is practical to partition the data by. テーブルを分割するパーティションの目標数を選択することもまた需要な決定です。 十分なパーティションがないとインデックスは大きくなりデータの局所性が貧しいままであるかもしれず、キャッシュヒット率が低い結果となる可能性があります。 しかしながら非常に多くのパーティションにテーブルを分割することもまた問題の原因となります。 以下に詳しく説明するように、非常に多くのパーティションは、クエリの計画時間が長くなり、クエリの計画および実行の両方の際にメモリ消費が高くなることを意味します。 テーブルを分割する方法を選択するとき、 将来に起こる変化を考慮することもまた重要です。 例えば、顧客毎に一つのパーティションを用意することを選択し、現在大規模な顧客が少数いる場合、数年以内に小規模な顧客を多数代わりに見つける可能性を含めて考慮します。 この場合、LISTによって分割しデータの分割が実用的な数以上に顧客の数が増加しないことを期待するより、HASHによって分割し妥当なパーティション数にすることを選択する方が良いかもしれません。

Sub-partitioning can be useful to further divide partitions that are expected to become larger than other partitions. Another option is to use range partitioning with multiple columns in the partition key. Either of these can easily lead to excessive numbers of partitions, so restraint is advisable. サブパーティショニングは、他のパーティションより巨大になると想定されるパーティションをさらに分割するために役立ちます。 他の選択は、パーティションキー中に複数の列を含む範囲パーティショニングを使うことです。 これらのどちらも容易に大量のパーティション数をもたらす結果になるので、自制することをお勧めします。

It is important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning. Another reason to be concerned about having a large number of partitions is that the server's memory consumption may grow significantly over time, especially if many sessions touch large numbers of partitions. That's because each partition requires its metadata to be loaded into the local memory of each session that touches it. クエリの計画および実行時のパーティショニングのオーバーヘッドを考慮することが重要です。 典型的なクエリではクエリプランナが少数のパーティションを除いて残り全てのパーティションを除外できるという前提に立てば、クエリプランナは通常最大数千パーティションのパーティション階層を適切に操作できます。 プランナがパーティション除去を行った後に多くのパーティションが残るほど、計画時間は長くなりメモリ消費は高くなります。 大量のパーティションを持っていることについて考慮するもうひとつの理由は、特に多くのセッションが大量のパーティションを参照する場合、ある期間にサーバのメモリ消費が著しく増加するかもしれないことです。 その理由は、各パーティションは参照される各セッションのローカルメモリにメタデータを読み込む必要があるためです。

With data warehouse type workloads, it can make sense to use a larger number of partitions than with an <acronym>OLTP</acronym> type workload. Generally, in data warehouses, query planning time is less of a concern as the majority of processing time is spent during query execution. With either of these two types of workload, it is important to make the right decisions early, as re-partitioning large quantities of data can be painfully slow. Simulations of the intended workload are often beneficial for optimizing the partitioning strategy. Never just assume that more partitions are better than fewer partitions, nor vice-versa. データウェアハウスタイプのワークロードでは、OLTPタイプのワークロードより大量のパーティションを使用するのが当然です。 通常、データウェアハウスでは処理時間の大半をクエリ実行に費やすため、クエリ計画時間はあまり問題になりません。 2種類のワークロードのいずれかでも、大量のデータを再パーティショニングすることは非常に遅いため、初期に適切な決定を下すことが重要です。 計画したワークロードのシミュレーションは、パーティショニング戦略を最適化するためにしばしば役立ちます。 単純に多数のパーティションがより少数のパーティションより優れていることや、少数のパーティションが多数のパーティションより優れていることを前提としないでください。