pg_pathman拡張の使用法 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、pg_pathman拡張機能の使用方法について説明します。

背景情報

pg_pathmanは、PolarDB for PostgreSQLクラスターで使用できる拡張機能です。 pg_pathman拡張機能は、効率的なパーティショニング機能を提供し、パーティションを効果的に管理し、パーティションテーブルのパフォーマンスを向上させます。

拡張機能を作成するCreate the extension

説明

pg_pathman拡張のパーティション管理機能を使用するには、お問い合わせください。

CREATE EXTENSION IF NOT EXISTS pg_pathman;

pg_pathman拡張機能を作成した後、次のSQL文を実行してバージョンを表示できます。

SELECT extname,extversion FROM pg_extension WHERE extname = 'pg_pathman';

サンプル結果:

  extname   | extversion 
------------+------------
 pg_pathman | 1.5
(1 row)

拡張機能のアップグレード

PolarDB for PostgreSQLは、データベースサービスを改善するためにpg_pathman拡張機能を定期的にアップグレードします。 pg_pathman拡張機能を手動でアップグレードするには、クラスターを最新バージョンにアップグレードします。

特徴

ハッシュ分割と範囲分割をサポートします。
自動および手動のパーティション管理を提供します。自動パーティション管理では、関数を使用して自動的にパーティションを作成し、プライマリテーブルからパーティションにデータを移行します。手動パーティション管理では、関数を使用して、既存のテーブルをプライマリテーブルにアタッチするか、プライマリテーブルからテーブルをデタッチします。
INT、FLOAT、DATE列タイプ、カスタムドメインなど、パーティション分割用の幅広い列タイプをサポートします。
パーティション分割テーブルのクエリに最適化されたプランを提供し、プランで結合やサブクエリなどの戦略を使用します。
カスタムプランノードRuntimeAppendおよびRuntimeMergeAppendを使用して、動的パーティション選択を有効にします。
PartitionFilter機能を使用して、クエリ条件に基づいてパーティションを動的にフィルタリングします。
挿入されたデータが現在のパーティション境界を超えると、新しいパーティションが自動的に追加されます。この機能は、範囲分割でのみ使用できます。
COPY from /TOステートメントを使用して、パーティションテーブルからデータを直接読み取り、パーティションテーブルにデータを書き込みます。
トリガーを追加してパーティションキーを更新できます。パーティションキーを更新する必要がない場合は、トリガーがパフォーマンスに悪影響を与える可能性があるため、トリガーを追加しないことをお勧めします。
パーティションの作成時に自動的に呼び出されるカスタムコールバック関数を設定できます。
パーティションテーブルを作成し、プライマリテーブルからパーティションにデータを非ブロッキングで移行できます。
PostgreSQLのforeign data Wrappers (FDW) または他のFDWによって管理される外部テーブルにデータを挿入できます。この機能を設定するには、pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw) パラメーターを使用します。

詳細については、「GitHub」をご参照ください。

ビューおよびテーブル

pg_pathman拡張機能は、関数を使用してパーティションテーブルを管理し、パーティションテーブルのステータスを表示するためのビューを作成します。

pathman_config

CREATE TABLE IF NOT EXISTS pathman_config (
    partrel         REGCLASS NOT NULL PRIMARY KEY,  -- The object identifier (OID) of the primary table.
    attname         TEXT NOT NULL,  -- The name of the partition key column.
    parttype        INTEGER NOT NULL,  -- The partitioning type (hash or range).
    range_interval  TEXT,  -- The range or span of values that each partition covers.

    CHECK (parttype IN (1, 2)) /* check for allowed part types */ );

pathman_config_params

CREATE TABLE IF NOT EXISTS pathman_config_params (
    partrel        REGCLASS NOT NULL PRIMARY KEY,  -- The OID of the primary table.
    enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to filter the primary table in the optimizer.
    auto           BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to automatically add new partitions.
    init_callback REGPROCEDURE NOT NULL DEFAULT 0); -- The OID of the initialization callback function that is invoked each time partitions are created.

pathman_concurrent_part_tasks

-- helper SRF function
CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()  
RETURNS TABLE (
    userid     REGROLE,
    pid        INT,
    dbid       OID,
    relid      REGCLASS,
    processed  INT,
    status     TEXT)
AS 'pg_pathman', 'show_concurrent_part_tasks_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();

pathman_partition_list

-- helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
    parent     REGCLASS,
    partition  REGCLASS,
    parttype   INT4,
    partattr   TEXT,
    range_min  TEXT,
    range_max  TEXT)
AS 'pg_pathman', 'show_partition_list_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();

パーティション管理

範囲分割の実行

範囲パーティションの作成には、4 つの管理関数を使用します。開始値、間隔、およびパーティション数の指定には、2 つの関数を使用します。次の構文を使用して、これら2つの関数を定義できます。

create_range_partitions(relation       REGCLASS,  -- The OID of the primary table.
                        attribute      TEXT,      -- The name of the partition key column.
                        start_value    ANYELEMENT,  -- The start value.
                        p_interval     ANYELEMENT,  -- The interval of all data types, which is applicable to all types of partitioned tables.
                        p_count        INTEGER DEFAULT NULL,   --  The number of partitions.
                        partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.

create_range_partitions(relation       REGCLASS,  -- The OID of the primary table.
                        attribute      TEXT,      -- The name of the partition key column.
                        start_value    ANYELEMENT,  -- The start value.
                        p_interval     INTERVAL,    -- The interval of an interval data type, which is applicable to ingestion-time partitioned tables.
                        p_count        INTEGER DEFAULT NULL,   --  The number of partitions.
                        partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.

他の 2 つの関数は、開始値、終了値、および間隔の指定に使用します。次の構文を使用して関数を定義できます。

create_partitions_from_range(relation       REGCLASS,  -- The OID of the primary table.
                             attribute      TEXT,      -- The name of the partition key column.
                             start_value    ANYELEMENT,  -- The start value.
                             end_value      ANYELEMENT,  -- The end value.
                             p_interval     ANYELEMENT,  -- The interval of all data types, which is applicable to all types of partitioned tables.
                             partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.

create_partitions_from_range(relation       REGCLASS,  -- The OID of the primary table.
                             attribute      TEXT,      -- The name of the partition key column.
                             start_value    ANYELEMENT,  -- The start value.
                             end_value      ANYELEMENT,  -- The end value.
                             p_interval     INTERVAL,    -- The interval of an interval data type, which is applicable to ingestion-time partitioned tables.
                             partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.

例：

テストデータを分割して挿入するプライマリテーブルを作成します。

--- Create a primary table that you want to partition.
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);  -- -- The partition key column must contain the NOT NULL constraint.  

--- Insert test data into the primary table to simulate a primary table that already has data.
INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);

プライマリテーブルのデータを照会します。

SELECT * FROM part_test limit 10;

サンプル結果:

 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713
  2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893
  3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904
  4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691
  5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916
  6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921
  7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693
  8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936
  9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942
 10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947
(10 rows)

パーティションを作成し、各パーティションに1か月のデータが含まれるようにします。

--- Create partitions and ensure that each partition contains one month of data.  
SELECT                                             
create_range_partitions('part_test'::regclass,             -- The OID of the primary table.
                        'crt_time',                        -- The name of the partition key column.
                        '2016-10-25 00:00:00'::timestamp,  -- The start value of the range for the first partition.
                        interval '1 month',                -- The range or span of values that each partition covers.
                        24,                                -- The number of partitions.
                        false) ;                            -- The data is not migrated.

-非ブロッキングデータ移行を実行します。

--- The data is still in the primary table before migration.
SELECT count(*) FROM ONLY part_test;
 count 
-------
 10000
(1 row)


--- Non-blocking migration API  
partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                             batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the primary table.
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The retry interval following a failed attempt to acquire a row lock. After 60 failed attempts, the task is ended. 


-- Perform non-blocking data migration.
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);


--- After the migration is completed, all data is migrated to the partitions, and the primary table is empty.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

データを移行した後、プライマリテーブルを無効にすることを推奨します。このように、親テーブルは実行プランに含まれません。

--- Disable the primary table.
SELECT set_enable_parent('part_test'::regclass, false);

--- Verify the execution plan.
EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.18 rows=1 width=45)
   ->  Seq Scan on part_test_1  (cost=0.00..16.18 rows=1 width=45)
         Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
(3 rows)

説明

範囲分割を使用する場合は、次の項目に注意してください。

パーティションキー列にNOT NULL制約を含める必要があります。
既存のすべてのレコードに十分対応できるパーティション数とする必要があります。
ノンブロッキングデータ移行を実行します。
データ移行の完了後、プライマリテーブルを無効化します。

ハッシュ分割の実行

次の管理機能を使用して範囲パーティションを作成し、パーティションの開始値、間隔、および数を指定できます。

create_hash_partitions(relation         REGCLASS,  -- The OID of the primary table.
                       attribute        TEXT,      -- The name of the partition key column.
                       partitions_count INTEGER,   -- The number of partitions.
                       partition_data   BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to the partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.

例：

テストデータを分割して挿入するプライマリテーブルを作成します。

--- Create a primary table that you want to partition.
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);    -- The partition key column must contain the NOT NULL constraint.  

--- Insert test data into the primary table to simulate a primary table that already has data.
INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval FROM generate_series(1,10000) t(id);

プライマリテーブルのデータを照会します。

SELECT * FROM part_test limit 10;

サンプル結果:

 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879
  2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048
  3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059
  4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065
  5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407
  6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076
  7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081
  8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087
  9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092
 10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097
(10 rows)

パーティションを作成します。

--- Create 128 partitions.
SELECT                                              
create_hash_partitions('part_test'::regclass,              The OID of the primary table.
                        'crt_time',                        -- The name of the partition key column.
                        128,                               -- The number of partitions that you want to create.
                        false) ;                            -- The data is not migrated.

-非ブロッキングデータ移行を実行します。

--- The data is still in the primary table before migration.
SELECT count(*) FROM ONLY part_test;
 count 
-------
 10000
(1 row)


--- Non-blocking migration API  
partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                             batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the primary table.
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The retry interval following a failed attempt to acquire a row lock. After 60 failed attempts, the task is ended. 

-- Perform non-blocking data migration.
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

--- After the migration is completed, all data is migrated to the partitions, and the primary table is empty.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

データを移行した後、プライマリテーブルを無効にすることを推奨します。このように、親テーブルは実行プランに含まれません。

--- Disable the primary table.
SELECT set_enable_parent('part_test'::regclass, false);

実行計画を確認します。

--- Query a single partition.
EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..1.91 rows=1 width=45)
   ->  Seq Scan on part_test_122  (cost=0.00..1.91 rows=1 width=45)
         Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
(3 rows)

-- パーティションテーブルには次の制限があります。 pg_pathman拡張は、ステートメントを自動的に変換します。従来の継承ベースのパーティション分割では、SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00:00 '::timestamp; のようなステートメントを使用してパーティションをフィルタリングすることはできません。

\d+ part_test_122
                                Table "public.part_test_122"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122)
Inherits: part_test

説明

ハッシュ分割を使用する場合は、次の項目に注意してください。

パーティションキー列にNOT NULL制約を含める必要があります。
ノンブロッキングデータ移行を実行します。
データ移行の完了後、プライマリテーブルを無効化します。
pg_pathman拡張は、ステートメントの書き方に関係なく機能します。たとえば、select * from part_test where crt_time = '2016-10-25 00:00:00:00 '::timestamp; などのステートメントは、ハッシュパーティショニングでも認識および実行できます。
ハッシュパーティション列は、int 型の列に限定されません。列の型はハッシュ関数によって自動的に変換されます。

データをパーティションに移行する

親テーブルにデータが残っている場合は、ノンブロッキングデータ移行を実行してデータをパーティションに移行します。機能：

WITH tmp AS (DELETE FROM <Primary table> limit xx nowait returning *) INSERT INTO <Partitions> SELECT * FROM tmp;

次の文を使用して行にラベルを付けることもできます。次に、DELETEおよびINSERT操作を実行します。

SELECT array_agg(ctid) FROM <Primary table> limit xx FOR UPDATE nowati;

機能：

partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                             batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the primary table.
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The retry interval following a failed attempt to acquire a row lock. After 60 failed attempts, the task is ended.

例：

SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

バックグラウンドで実行されているデータ移行タスクを確認します。

SELECT * FROM pathman_concurrent_part_tasks;

範囲パーティションの分割

過度に大きな範囲のパーティションを小さなパーティションに分割するには、次の関数を使用します。

split_range_partition(partition      REGCLASS,            -- The OID of the partition.
                      split_value    ANYELEMENT,          -- The split value.
                      partition_name TEXT DEFAULT NULL) -- The name of the added partition

例：

[範囲分割の実行] セクションのテーブルを次の構造で使用します。

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_1,
              part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_2,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

パーティションを分割します。

SELECT split_range_partition('part_test_1'::regclass,              -- The OID of the partition.
                      '2016-11-10 00:00:00'::timestamp,     -- The point in time at which the partition is split.
                      'part_test_1_2');                     -- The name of the new partition.

パーティションは次のパーティションに分割されます。

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_1_2 
                                Table "public.part_test_1_2"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_2_3_check" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

データは自動的に他のパーティションに移行されます。

SELECT count(*) FROM part_test_1;
 count 
-------
   373
(1 row)

SELECT count(*) FROM part_test_1_2;
 count 
-------
   360
(1 row)

次の例では、継承関係について説明します。

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_1,
              part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_1_2,    -- The added partition.
              part_test_2,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

範囲パーティションのマージ

範囲パーティションのみがサポートされ、隣接するパーティションである必要があります。次の関数を呼び出して、範囲パーティションをマージします。

--- Specify two partitions that you want to merge.  
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

例：

[範囲パーティションの分割] セクションのテーブルを使用して、パーティションを結合します。

SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);

説明

隣接していないパーティションをマージすると、次のエラーが返されます。

SELECT merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ;
ERROR:  merge failed, partitions must be adjacent
CONTEXT:  PL/pgSQL function merge_range_partitions_internal(regclass,regclass,regclass,anyelement) line 27 at RAISE
SQL statement "SELECT public.merge_range_partitions_internal($1, $2, $3, NULL::timestamp without time zone)"
PL/pgSQL function merge_range_partitions(regclass,regclass) line 44 at EXECUTE

パーティションをマージすると、いずれかのパーティションが削除されます。

\d part_test_1_2
Did not find any relation named "part_test_1_2".

\d part_test_1
             Table "public.part_test_1"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | 
 info     | text                        | 
 crt_time | timestamp without time zone | not null
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

SELECT count(*) FROM part_test_1;
 count 
-------
   733
(1 row)

新しい範囲パーティションを追加する

パーティション分割されたプライマリテーブルにパーティションを追加するには、いくつかの方法を使用できます。ここでは、新しいレンジパーティションを追加し、新しいレンジパーティションの前に追加し、パーティションの開始値を指定する3つの方法について説明します。

新しい範囲パーティションの追加

プライマリテーブルに新しいパーティションを追加する場合、パーティションテーブルの作成時に指定された間隔が使用されます。 pathman_configテーブルから各パーティションテーブルの間隔値を照会できます。

SELECT * FROM pathman_config;
  partrel  | attname  | parttype | range_interval 
-----------+----------+----------+----------------
 part_test | crt_time |        2 | 1 mon
(1 row)

次の関数を呼び出して、新しいレンジパーティションを追加します (テーブルスペースを指定することはできません) 。

append_range_partition(parent         REGCLASS,            -- The OID of the primary table.
                       partition_name TEXT DEFAULT NULL,   -- The name of the new partition. This parameter is not required.
                       tablespace     TEXT DEFAULT NULL)   -- The tablespace for the new partition. This parameter is not required.

例：

SELECT append_range_partition('part_test'::regclass);

\d+ part_test_25
                                Table "public.part_test_25"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_24
                                Table "public.part_test_24"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_24_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

新しい範囲パーティションの準備

次の関数を呼び出して、新しいレンジパーティションの先頭に追加します。

prepend_range_partition(parent         REGCLASS,
                        partition_name TEXT DEFAULT NULL,
                        tablespace     TEXT DEFAULT NULL)

例：

SELECT prepend_range_partition('part_test'::regclass);

\d+ part_test_26
                                Table "public.part_test_26"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_26_3_check" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

新しいレンジパーティションを追加するためのパーティションの開始値の指定

パーティションの開始値を指定して、新しいパーティションを作成できます。作成するパーティションの範囲が既存のパーティションと重ならない場合は、パーティションが作成されます。この方法では、隣接しないパーティションを作成できます。たとえば、既存のパーティションの範囲が2010から2015の場合、2020からパーティションを作成できます。 2015 から 2020 の間のパーティションを作成する必要はありません。機能：

add_range_partition(relation       REGCLASS,    -- The OID of the primary table
                    start_value    ANYELEMENT,  -- The start value.
                    end_value      ANYELEMENT,  -- The end value.
                    partition_name TEXT DEFAULT NULL,  -- The name of the partition.
                    tablespace     TEXT DEFAULT NULL)  -- The tablespace for the partition.

例：

postgres=# select add_range_partition('part_test'::regclass,    -- The OID of the primary table.
                    '2020-01-01 00:00:00'::timestamp,  -- The start value.
                    '2020-02-01 00:00:00'::timestamp); -- The end value.

\d+ part_test_27
                                Table "public.part_test_27"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_27_3_check" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zone)
Inherits: part_test

パーティションを削除する

単一のパーティション範囲を削除するには、以下の関数を呼び出します。

drop_range_partition(partition TEXT,   -- The name of the partition.
                    delete_data BOOLEAN DEFAULT TRUE) -- Specifies whether to delete data of the partition. If you set the value to FALSE, data of the partition is migrated to the primary table.   

Drop RANGE partition and all of its data if delete_data is true.

すべてのパーティションを削除し、データをプライマリテーブルに移行するかどうかを指定します。機能：

drop_partitions(parent      REGCLASS,
                delete_data BOOLEAN DEFAULT FALSE)

Drop partitions of the parent table (both foreign and local relations). 
If delete_data is false, the data is copied to the parent table first. 
Default is false.

例：

Delete a partition and migrate data of the partition to the primary table.

SELECT drop_range_partition('part_test_1',false);
SELECT drop_range_partition('part_test_2',false);

現在のプライマリテーブルのデータを照会します。

SELECT count(*) FROM part_test;
 count 
-------
 10000
(1 row)

データを親テーブルに移行せずに、パーティションとパーティションのデータを削除します。
```
SELECT drop_range_partition('part_test_3',true);
```
現在のプライマリテーブルのデータを照会します。
```
SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

SELECT count(*) FROM ONLY part_test;
 count 
-------
  1453
(1 row)
```

すべてのパーティションを削除します。

SELECT drop_partitions('part_test'::regclass, false);  -- Delete all partitions and migrate the data of partitions to the primary table.

プライマリテーブルのデータを照会します。

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

テーブルをパーティションにアタッチする

既存のテーブルをプライマリテーブルのパーティションにアタッチします。アタッチするテーブルは、プライマリテーブルと同じスキーマを持つ必要があります。たとえば、2つのテーブルは同じドロップ列を持つ必要があります。 pg_attributeパラメーターは、テーブルのスキーマを指定します。機能：

attach_range_partition(relation    REGCLASS,    -- The OID of the primary table.
                       partition   REGCLASS,    -- The OID of the partition.
                       start_value ANYELEMENT,  -- The start value of the range for the partition.
                       end_value   ANYELEMENT)  -- The end value of the range for the partition.

例：

テーブルを作成します。

CREATE TABLE part_test_1 (like part_test including all);

テーブルをプライマリテーブルにアタッチします。

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 

SELECT attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp);

テーブルをアタッチすると、継承関係と制約が自動的に作成されます。

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2019-01-01 00:00:00'::timestamp without time zone AND crt_time < '2019-02-01 00:00:00'::timestamp without time zone)
Inherits: part_test

パーティションのデタッチ

パーティションをデタッチすると、データは削除されませんが、継承と制約は削除されます。機能：

detach_range_partition(partition REGCLASS)  -- Specify the partition that you want to convert into a common table.

例：

現在のプライマリテーブルとパーティションテーブルのデータを照会します。

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

SELECT count(*) FROM part_test_2;
 count 
-------
   733
(1 row)

パーティションのバインドを解除します。

SELECT detach_range_partition('part_test_2');

現在のプライマリテーブルとパーティションテーブルのデータを照会します。

SELECT count(*) FROM part_test_2;
 count 
-------
   733
(1 row)

SELECT count(*) FROM part_test;
 count 
-------
  8523
(1 row)

pg_pathman拡張機能を無効にする

親テーブルのpg_pathman拡張を無効にできます。機能：

重要

disable_pathman_for は取り消しできません。作業は慎重に行ってください。

\sf disable_pathman_for
CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
BEGIN
        PERFORM public.validate_relname(parent_relid);

        DELETE FROM public.pathman_config WHERE partrel = parent_relid;
        PERFORM public.drop_triggers(parent_relid);

        /* Notify backend about changes */
        PERFORM public.on_remove_partitions(parent_relid);
END
$function$

例：

SELECT disable_pathman_for('part_test');

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_25,
              part_test_26,
              part_test_27,
              part_test_28,
              part_test_29,
              part_test_3,
              part_test_30,
              part_test_31,
              part_test_32,
              part_test_33,
              part_test_34,
              part_test_35,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_10
                                Table "public.part_test_10"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
Inherits: part_test

pg_pathman拡張機能を無効にした後も、継承と制約は変更されません。 pg_pathman拡張は、カスタムスキャン実行プランに介入しません。次の例では、pg_pathman拡張機能を無効にした後の実行計画について説明します。

EXPLAIN SELECT * FROM part_test WHERE crt_time='2017-06-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.00 rows=2 width=45)
   ->  Seq Scan on part_test  (cost=0.00..0.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
   ->  Seq Scan on part_test_10  (cost=0.00..16.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 rows)

高度なパーティション管理

プライマリテーブルを無効化する

プライマリテーブルの全データをパーティションに移行後、プライマリテーブルを無効にできます。機能：

set_enable_parent(relation REGCLASS, value BOOLEAN)


Include/exclude parent table into/from query plan. 

In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. 

You can use disable_parent() if you are never going to use parent table as a storage. 

Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions. 

If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. 

Otherwise it is enabled.

例：

SELECT set_enable_parent('part_test', false);

自動パーティション伝播の有効化

レンジパーティションテーブルの自動パーティション伝播を有効にできます。挿入されたデータが既存のパーティションの範囲を超える場合、パーティションが自動的に作成されます。

set_auto(relation REGCLASS, value BOOLEAN)

Enable/disable auto partition propagation (only for RANGE partitioning). 

It is enabled by default.

例：

現在のテーブルのパーティションを照会します。

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_25,
              part_test_26,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_26
                                Table "public.part_test_26"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_25
                                Table "public.part_test_25"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone)
Inherits: part_test

既存のパーティションの範囲外のデータを挿入すると、テーブルのパーティション分割時に指定された間隔に基づいて新しいパーティションが作成されます。この操作は、完了するのに長い時間を必要とし得る。

INSERT INTO part_test VALUES (1,'test','2222-01-01'::timestamp);

パーティションを照会します。

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_100,
              part_test_1000,
              part_test_1001,
              ......

説明

不適切な自動パーティション伝播が完了するまでに時間がかかる場合があるため、範囲パーティションテーブルの自動パーティション伝播を無効にすることをお勧めします。

コールバック関数の設定

コールバック関数は、パーティションが作成されるたびに自動的に呼び出されます。たとえば、DDL論理レプリケーションのコールバック関数を設定して、ステートメントをテーブルに格納できます。機能：

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)

Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE). 

The callback must have the following signature: 

part_init_callback(args JSONB) RETURNS VOID. 

Parameter arg consists of several fields whose presence depends on partitioning type:

/* RANGE-partitioned table abc (child abc_4) */
{
    "parent":    "abc",
    "parttype":  "2",
    "partition": "abc_4",
    "range_max": "401",
    "range_min": "301"
}

/* HASH-partitioned table abc (child abc_0) */
{
    "parent":    "abc",
    "parttype":  "1",
    "partition": "abc_0"
}

例：

コールバック関数を作成します。

CREATE OR REPLACE FUNCTION f_callback_test(jsonb) RETURNS void AS
$$
DECLARE
BEGIN
  CREATE TABLE if NOT EXISTS rec_part_ddl(id serial primary key, parent name, parttype int, partition name, range_max text, range_min text);
  if ($1->>'parttype')::int = 1 then
    raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition';
    INSERT INTO rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
  elsif ($1->>'parttype')::int = 2 then
    raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min';
    INSERT INTO rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max', $1->>'range_min');
  END if;
END;
$$ LANGUAGE plpgsql strict;

テストテーブルを準備します。

CREATE TABLE tt(id int, info text, crt_time timestamp not null);

--- Configure a callback function for the test table.
SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc);

--- Create partitions.
SELECT                                                           
create_range_partitions('tt'::regclass,                    -- The OID of the primary table.
                        'crt_time',                        -- The name of the partition key column.
                        '2016-10-25 00:00:00'::timestamp,  -- The start value of the range for the first partition.
                        interval '1 month',                -- The range or span of values that each partition covers.
                        24,                                -- The number of partitions.
                        false) ;

-- コールバック関数が呼び出されているかどうかを確認します。

SELECT * FROM rec_part_ddl;

サンプル結果:

 id | parent | parttype | partition |      range_max      |      range_min      
----+--------+----------+-----------+---------------------+---------------------
  1 | tt     |        2 | tt_1      | 2016-11-25 00:00:00 | 2016-10-25 00:00:00
  2 | tt     |        2 | tt_2      | 2016-12-25 00:00:00 | 2016-11-25 00:00:00
  3 | tt     |        2 | tt_3      | 2017-01-25 00:00:00 | 2016-12-25 00:00:00
  4 | tt     |        2 | tt_4      | 2017-02-25 00:00:00 | 2017-01-25 00:00:00
  5 | tt     |        2 | tt_5      | 2017-03-25 00:00:00 | 2017-02-25 00:00:00
  6 | tt     |        2 | tt_6      | 2017-04-25 00:00:00 | 2017-03-25 00:00:00
  7 | tt     |        2 | tt_7      | 2017-05-25 00:00:00 | 2017-04-25 00:00:00
  8 | tt     |        2 | tt_8      | 2017-06-25 00:00:00 | 2017-05-25 00:00:00
  9 | tt     |        2 | tt_9      | 2017-07-25 00:00:00 | 2017-06-25 00:00:00
 10 | tt     |        2 | tt_10     | 2017-08-25 00:00:00 | 2017-07-25 00:00:00
 11 | tt     |        2 | tt_11     | 2017-09-25 00:00:00 | 2017-08-25 00:00:00
 12 | tt     |        2 | tt_12     | 2017-10-25 00:00:00 | 2017-09-25 00:00:00
 13 | tt     |        2 | tt_13     | 2017-11-25 00:00:00 | 2017-10-25 00:00:00
 14 | tt     |        2 | tt_14     | 2017-12-25 00:00:00 | 2017-11-25 00:00:00
 15 | tt     |        2 | tt_15     | 2018-01-25 00:00:00 | 2017-12-25 00:00:00
 16 | tt     |        2 | tt_16     | 2018-02-25 00:00:00 | 2018-01-25 00:00:00
 17 | tt     |        2 | tt_17     | 2018-03-25 00:00:00 | 2018-02-25 00:00:00
 18 | tt     |        2 | tt_18     | 2018-04-25 00:00:00 | 2018-03-25 00:00:00
 19 | tt     |        2 | tt_19     | 2018-05-25 00:00:00 | 2018-04-25 00:00:00
 20 | tt     |        2 | tt_20     | 2018-06-25 00:00:00 | 2018-05-25 00:00:00
 21 | tt     |        2 | tt_21     | 2018-07-25 00:00:00 | 2018-06-25 00:00:00
 22 | tt     |        2 | tt_22     | 2018-08-25 00:00:00 | 2018-07-25 00:00:00
 23 | tt     |        2 | tt_23     | 2018-09-25 00:00:00 | 2018-08-25 00:00:00
 24 | tt     |        2 | tt_24     | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
(24 rows)