Clever Geek Handbook

PostgreSQL 10以降でのパーティション分割







PostgreSQL 10の成果の多くは、パーティショニングセクションに登録されています。 これは事実です。9.6から10への移行中に、強力な技術の飛躍が起こったことは明らかです。 以前のバージョンでは、既存の手段、つまり、すべての制限と不都合を伴う継承メカニズムによって、セクションが構築および管理されていました。







バージョン10では、より特殊なメカニズムとより使い慣れた（Oracleユーザーを含む、これを考慮しなければならない）構文に切り替えました。 10からバージョン11への移行におけるこのジャンプは、機能を拡張し、パーティショニングを使用する操作のパフォーマンスを改善する重要な追加によってサポートされるはずでした。 しかし、最後の委員会フェスト（4月8日に終了）の結果から、考えられていたすべてが作業状態になったわけではないことは明らかです。つまり、すべてがバージョン11に分類されるわけではありません。







さらに、Postgres Professionalのpg_pathmanモジュールの開発は近年進行中です。 いくつかの重要な機能が交差し、一部はPostgreSQLとpg_pathman



固有のままpg_pathman



（これはバニラバージョンで動作します。つまり、PostgreSQL 10 + pg_pathmanはすでに非常に印象的な機能を提供します）。 これは別の記事になります。 バージョン11およびpg_pathmanに関連する注記は、便宜上イタリック体で示しています。







この記事はPostgreSQL 10のNouveaulitésde PostgreSQL 10の改訂版と拡張版です。（c）Dalibo、イゴール・レフシン（フランス語）によるフランス語からの翻訳。 本からの例は、より明確にするために検証され、時には適合され、ローカライズされます。







古いパーティション分割アプローチ



バージョン10より前は、PostgreSQLのパーティショニングはテーブル継承メカニズムに基づいていました。 子テーブルは、1つの相続人-親として作成されます。 この場合、親テーブルの読み取り要求は、親からだけでなく子テーブルからもデータを読み取ることを意味します。 PostgreSQLへの後続の追加により、整合性の制約により、これらのセクションに検索対象のデータが含まれないことが保証されている場合に、子テーブルの読み取りを防ぐことができました。 したがって、このような選択的な読み取りはオプティマイザーによって決定されます。







録音は異なります。 親テーブルへの挿入は、対応する子テーブルに自動的にリダイレクトされません。親テーブルへの挿入をキャンセルし、データを正しい子テーブルにリダイレクトするトリガーを追加する必要があります。 分割キーを使用して列の値を更新することはできません。 最後に、削除は自動的に修正されます。







これらすべては、セクションを作成するときに多くの作業を必要とします。 セクションの管理は必ずしも簡単ではありません。 挿入トリガーが正しく機能するように、セクションが最初から正しく作成されたことを確認する必要があります。







他の不便さの中でも、インデックスに関する問題について言及しています。 グローバルインデックス（つまり、複数のテーブルにまたがるインデックス）を作成することは不可能です。パーティションテーブルにグローバルプライマリキーを作成することはできません。 本質的に、これは一意性を制限することが不可能であることを意味します。







言い換えれば、このメカニズムは非常に特殊な場合に興味深い場合があり、特にパフォーマンスにおいて他の問題が発生しないことを十分に保証する必要があります。 他のすべての場合では、そのようなセクショニングなしで行う方が良いです。







パーティション分割の新しいアプローチ



バージョン10では、PostgreSQLにすでに存在するインフラストラクチャに基づいた新しいパーティションシステムが導入されています。







開発者の目標は、パーティションテーブルの作成と管理を簡素化することでした。 既存のSQLステートメント（ CREATE TABLE



やALTER TABLE



）に、セクションの作成、結合、分離のための特別なオファーが追加されました。 変更点のリストは次のとおりです。









セクションの作成を簡素化するのは簡単です。挿入と更新を制御するための特別なトリガー関数を作成する必要はありません（古いスタイルのトリガーが作成される例は記事の最後にあります）。 セクションの定義時に作成された関数によって、データが自動的にルーティングされるようになりました。 入力データに適したセクションがない場合、エラーが生成されます。 この自動ルーティングは便利なだけでなく、挿入を高速化します。







pg_class



ディレクトリpg_class



変更され、次の情報が含まれるようになりました。









pg_partitioned_table



ディレクトリには、次の列が含まれています。







コラム 内容
部分的に このパーティションテーブルのpg_class



のOID
パートナー パーティション化戦略：l =リスト別（ BY LIST



）、r =範囲別（ BY RANGE



）
参加者 分割キーの列数
参加者 テーブルのどの列がパーティションキーの一部であるかを示す長さの部分配列。
パーツクラス パーティション化キーの列ごとに、この配列には適用可能な演算子クラスのOIDが含まれます。
部分照合 パーティション化キーの列ごとに、この配列にはソートルールのOIDが含まれます**）
partexprs 分割キーの一部の式ツリー。***）


*）たとえば、値1と3は、パーティションキーがテーブルの1列目と3列目であることを意味します。 この配列のゼロは、パーティションキーの対応する部分が式であり、単一の列へのリンクではないことを意味します。

**）セクショニングの場合、この列のデータ型がソート可能でない場合は0。

***）単純な列参照ではないパーティションキーの部分の式ツリー（ビューnodeToString（）内）。 このリストには、partattrsの各ヌル値に対して1つの項目が含まれています。 分割キーのすべての部分が単純な列方向である場合、値はNULLになります。







セクショニングタイプ









PostgreSQL 10は、3番目を除くすべてをサポートします。 最初の2つに焦点を当てます。







値リストのセクション化



メインテーブルとそのセクションを作成します。 







 habr_10=# CREATE TABLE parti_1(c1 integer, c2 text) PARTITION BY LIST (c1); CREATE TABLE habr_10=# CREATE TABLE parti_1_a PARTITION OF parti_1 FOR VALUES IN (1, 2, 3); CREATE TABLE habr_10=# CREATE TABLE parti_1_b PARTITION OF parti_1 FOR VALUES IN (4, 5); CREATE TABLE


セクションを切断および接続できます。







切断： 







 habr_10=# ALTER TABLE parti_1 DETACH PARTITION parti_1_a;


これは独立したテーブルです。 取り付け直します： 







 habr_10=# ALTER TABLE parti_1 ATTACH PARTITION parti_1_a FOR VALUES IN (1, 2, 3);


データを入力します。 最初に、リストにない値を入力してみてください。 パーティションキーに対応するセクションがない場合、エラーが生成されます。 







 habr_10=# INSERT INTO parti_1 VALUES (0); ERROR: no PARTITION OF relation "parti_1" found for row DETAIL: Partition key of the failing row contains (c1) = (0). habr_10=# INSERT INTO parti_1 VALUES (6); ERROR: no PARTITION OF relation "parti_1" found for row DETAIL: Partition key of the failing row contains (c1) = (6).


正しいデータを入力してください： 







 habr_10=# INSERT INTO parti_1 VALUES (1); INSERT 0 1 habr_10=# INSERT INTO parti_1 VALUES (2); INSERT 0 1 habr_10=# INSERT INTO parti_1 VALUES (5); INSERT 0 1 habr_10=# SELECT * FROM parti_1_a; c1 | c2 ----+---- 1 | 2 | (2 rows) habr_10=# SELECT * FROM parti_1_b; c1 | c2 ----+---- 5 | (1 row)


挿入中、データはセクションに正しく分散されます。 親パーティションテーブルは空です。親パーティションテーブルにデータは保存されません。 これを確認するには、SELECTステートメントとONLY



ステートメントを使用します。 







 habr_10=# SELECT * FROM ONLY parti_1; c1 | c2 ----+---- (0 rows)


範囲分割



メインテーブルと1つのセクションを作成します。 







 habr_10=# CREATE TABLE parti_2(c1 integer, c2 text) PARTITION BY RANGE (c1); CREATE TABLE habr_10=# CREATE TABLE parti_2_1 PARTITION OF parti_2 FOR VALUES FROM (1) to (100); CREATE TABLE


セクションにデータを入力します。 







 habr_10=# INSERT INTO parti_2 VALUES (0); ERROR: no PARTITION OF relation "parti_2" found for row DETAIL: Partition key of the failing row contains (c1) = (0). habr_10=# INSERT INTO parti_2 VALUES (1); INSERT 0 1 habr_10=# INSERT INTO parti_2 VALUES (2); INSERT 0 1 habr_10=# INSERT INTO parti_2 VALUES (5); INSERT 0 1 habr_10=# INSERT INTO parti_2 VALUES (101); ERROR: no PARTITION OF relation "parti_2" found for row DETAIL: Partition key of the failing row contains (c1) = (101).


パーティションキーに対応するセクションがない場合、エラーが生成されます。







分割キー



分割分割キーは、範囲で分割されている場合にのみ機能します。 複合キーを使用してマスターテーブルを作成します。 







 habr_10=# CREATE TABLE parti_3(c1 integer, c2 text, c3 date) PARTITION BY RANGE (c1, c3);


複合分割キーを使用してセクションを追加します。 







 habr_10=# CREATE TABLE parti_3_a PARTITION of parti_3 FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11');


以下の例を使用して、さまざまな表スペースの共有を説明します。 表スペースを作成することから始めましょう。 テーブルスペースディレクトリが存在し、空であり、Postgresを実行しているOSのユーザーに属している必要があることを思い出してください。 例： 







 test#mkdir /tmp/tablespaces/ts0 test#sudo chown postgres /tmp/tablespaces/ts0


今： 







 habr_10=# CREATE TABLESPACE ts0 LOCATION '/tmp/tablespaces/ts0'; CREATE TABLESPACE habr_10=# CREATE TABLESPACE ts1 LOCATION '/tmp/tablespaces/ts1'; CREATE TABLESPACE habr_10=# CREATE TABLESPACE ts2 LOCATION '/tmp/tablespaces/ts2'; CREATE TABLESPACE habr_10=# CREATE TABLESPACE ts3 LOCATION '/tmp/tablespaces/ts3'; CREATE TABLESPACE


パーティションテーブルと2つのセクションを作成します。 







 habr_10=# DROP TABLE parti_3; DROP TABLE


（セクションはカスケードされます） 







 habr_10=# CREATE TABLE parti_3(c1 integer, c2 text, c3 date not null) PARTITION BY RANGE (c1, c3); CREATE TABLE habr_10=# CREATE TABLE parti_3_1 PARTITION OF parti_3 FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11') TABLESPACE ts1; CREATE TABLE habr_10=# CREATE TABLE parti_3_2 PARTITION OF parti_3 FOR VALUES FROM (100,'2017-08-11') TO (200, '2017-08-12') TABLESPACE ts2; CREATE TABLE


値がセクションの境界を超えない場合： 







 habr_10=# INSERT INTO parti_3 VALUES (1, 'test', '2017-08-10'); INSERT 0 1 habr_10=# INSERT INTO parti_3 VALUES (150, 'test2', '2017-08-11'); INSERT 0 1


c1



小さすぎる場合： 







 habr_10=# INSERT INTO parti_3 VALUES (0, 'test', '2017-08-10'); ERROR: no partition of relation "parti_3" found for row DETAIL : Partition key of the failing row contains (c1, c3) = (0, 2017-08-10).


c3



（日付データ型の列）が時間範囲の下限の前にある場合： 







 habr_10=# INSERT INTO parti_3 VALUES (1, 'test', '2017-08-09'); ERROR: no partition of relation "parti_3" found for row DETAIL : Partition key of the failing row contains (c1, c3) = (1, 2017-08-09).


特別な値MINVALUEおよびMAXVALUEを使用すると、境界のいずれかの値を指定できません。 たとえば、parti_3_0およびparti_3_3セクションは以下に示すように宣言でき、上記のエラーメッセージの原因となった行を正常に挿入できます。







注：beta3リリースの前に発行された一部のオンライン記事には、特別な値UNBOUNDEDが記載されていましたが、後にMINVALUEおよびMAXVALUEに置き換えられました。 







 habr_10=# CREATE TABLE parti_3_0 PARTITION OF parti_3 FOR VALUES FROM (MINVALUE, MINVALUE) TO (1,'2017-08-10') TABLESPACE ts0; habr_10=# CREATE TABLE parti_3_3 PARTITION OF parti_3 FOR VALUES FROM (200,'2017-08-12') TO (MAXVALUE, MAXVALUE) TABLESPACE ts3;


最後に、 pg_class



テーブルを参照して、作成されたさまざまなタイプのセクションが存在することを確認できます。 







 habr_10=# ANALYZE parti_3; ANALYZE habr_10=# SELECT relname,relispartition,relkind,reltuples FROM pg_class WHERE relname LIKE 'parti_3%'; relname | relispartition | relkind | reltuples -------------+----------------+---------+----------- parti_3 | f | p | 0 parti_3_1 | t | r | 1 parti_3_2 | t | r | 1 (5 rows)


デフォルトセクションと自動セクション作成







エラーのために毎回停止しないように、指定されたセクションの境界を超えるすべてのレコードが該当するセクションがあると便利です。 この機能はバージョン11で登場します。







pg_pathmanモジュールには、値が割り当てられた範囲の境界に入るかどうかを考えないようにする非常に便利な関数があります。 そこで、セクションの値の間隔を設定できます。モジュールは、入力された値に対応するために必要な数だけセクションを作成します。 これはBY RANGEビューセクションで機能します。







PostgreSQL 10にはハッシュパーティショニングがないことに注意してください。 この重大な欠陥は11で修正されました。対応するパッチはcommitfestに合格したため、PostgreSQL 11を待つ必要があります。ハッシュによるパーティション化が現在必要であるか望ましい場合は、 pg_pathman



を使用できます。







挿入パフォーマンス



テーブルno_parti-パーティション分割されていないテーブル。 次のように作成します。 







 CREATE TABLE no_parti (c1 integer, c2 text); INSERT INTO no_parti SELECT i, 'something' FROM generate_series(0, 9999999) i; Time: 10097.098 ms (00:10.097)


PostgreSQL 10の新機能を使用してparti_newテーブルを作成します。 







 CREATE TABLE parti_new (c1 integer, c2 text) PARTITION BY RANGE (c1); CREATE TABLE parti_new_1 PARTITION OF parti_new FOR VALUES FROM ( 0) TO ( 1000000); CREATE TABLE parti_new_2 PARTITION OF parti_new FOR VALUES FROM (1000000) TO ( 2000000); CREATE TABLE parti_new_3 PARTITION OF parti_new FOR VALUES FROM (2000000) TO ( 3000000); CREATE TABLE parti_new_4 PARTITION OF parti_new FOR VALUES FROM (3000000) TO ( 4000000); CREATE TABLE parti_new_5 PARTITION OF parti_new FOR VALUES FROM (4000000) TO ( 5000000); CREATE TABLE parti_new_6 PARTITION OF parti_new FOR VALUES FROM (5000000) TO ( 6000000); CREATE TABLE parti_new_7 PARTITION OF parti_new FOR VALUES FROM (6000000) TO ( 7000000); CREATE TABLE parti_new_8 PARTITION OF parti_new FOR VALUES FROM (7000000) TO ( 8000000); CREATE TABLE parti_new_9 PARTITION OF parti_new FOR VALUES FROM (8000000) TO ( 9000000); CREATE TABLE parti_new_0 PARTITION OF parti_new FOR VALUES FROM (9000000) TO (10000000); INSERT INTO parti_new SELECT i, 'something' FROM generate_series(0, 9999999) i; Time: 11448.867 ms (00:11.449)


古いパーティショニング方法を使用して、 parti_oldテーブルを作成します。 







 CREATE TABLE parti_old (c1 integer, c2 text); CREATE TABLE parti_old_1 (CHECK (c1 BETWEEN 0 AND 1000000)) INHERITS (parti_old); CREATE TABLE parti_old_2 (CHECK (c1 BETWEEN 1000000 AND 2000000)) INHERITS (parti_old); CREATE TABLE parti_old_3 (CHECK (c1 BETWEEN 2000000 AND 3000000)) INHERITS (parti_old); CREATE TABLE parti_old_4 (CHECK (c1 BETWEEN 3000000 AND 4000000)) INHERITS (parti_old); CREATE TABLE parti_old_5 (CHECK (c1 BETWEEN 4000000 AND 5000000)) INHERITS (parti_old); CREATE TABLE parti_old_6 (CHECK (c1 BETWEEN 5000000 AND 6000000)) INHERITS (parti_old); CREATE TABLE parti_old_7 (CHECK (c1 BETWEEN 6000000 AND 7000000)) INHERITS (parti_old); CREATE TABLE parti_old_8 (CHECK (c1 BETWEEN 7000000 AND 8000000)) INHERITS (parti_old); CREATE TABLE parti_old_9 (CHECK (c1 BETWEEN 8000000 AND 9000000)) INHERITS (parti_old); CREATE TABLE parti_old_0 (CHECK (c1 BETWEEN 9000000 AND 10000000)) INHERITS (parti_old); CREATE OR REPLACE FUNCTION insert_into() RETURNS TRIGGER LANGUAGE plpgsql AS $FUNC$ BEGIN IF NEW.c1 BETWEEN 0 AND 1000000 THEN INSERT INTO parti_old_1 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 1000000 AND 2000000 THEN INSERT INTO parti_old_2 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 2000000 AND 3000000 THEN INSERT INTO parti_old_3 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 3000000 AND 4000000 THEN INSERT INTO parti_old_4 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 4000000 AND 5000000 THEN INSERT INTO parti_old_5 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 5000000 AND 6000000 THEN INSERT INTO parti_old_6 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 6000000 AND 7000000 THEN INSERT INTO parti_old_7 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 7000000 AND 8000000 THEN INSERT INTO parti_old_8 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 8000000 AND 9000000 THEN INSERT INTO parti_old_9 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 9000000 AND 10000000 THEN INSERT INTO parti_old_0 VALUES (NEW.*); END IF; RETURN NULL; END; $FUNC$; CREATE TRIGGER tr_insert_parti_old BEFORE INSERT ON parti_old FOR EACH ROW EXECUTE PROCEDURE insert_into(); INSERT INTO parti_old SELECT i, 'something' FROM generate_series(0, 9999999) i; Time: 125351.918 ms (02:05.352)


パーティショニングへの新しいアプローチによる挿入速度は、桁違いに高いことがわかります。







バージョン11には、パーティションキーのデータが単調またはほぼ単調に増加した場合に挿入速度を大幅に向上させるパッチが含まれています。







読み取りパフォーマンスに関しては、ステージ10-> 11で根本的な変更が発生しました。明らかにデータがない計画セクションから効果的に除外することが可能になりました（パーティションプルーニング）。 これは、1つまたは別のセクションに入る条件が事前にわからない実行段階でも実行できます。 これは、たとえば、サブクエリの場合に発生します。







これまでのところ、セクションの除外は（確実に）リストのパーティション化でのみ機能します。 セクションが多数ある場合（および実際のプロジェクトでは数千、または数万もの場合）、セクションを除外するとクエリの実行時間が大幅に短縮されます。 pg_pathman



、プロジェクトの最初からほとんど不要なセクションpg_pathman



除外が実装されています。













例による古いセクションと新しいセクションの比較



バージョン9.6と10でのパーティション分割のアプローチの違いを理解します。







場所と日付に関連付けられた温度のテーブルを作成します。 各場所および各月のセクションを作成します。







9.6でそのようなテーブルを作成するためのコマンド： 







 CREATE TABLE meteo ( t_id serial, place text NOT NULL, hour_mesure timestamp DEFAULT now(), temperature real NOT NULL ); CREATE TABLE meteo_moscow_201709 ( CHECK ( place = 'Moscow' AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_moscow_201710 ( CHECK ( place = 'Moscow' AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_sochi_201709 ( CHECK ( place = 'Sochi' AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_sochi_201710 ( CHECK ( place = 'Sochi' AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_magadan_201709 ( CHECK ( place = 'Magadan' AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_magadan_201710 ( CHECK ( place = 'Magadan' AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) ) INHERITS (meteo); CREATE OR REPLACE FUNCTION meteo_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.place = 'Moscow' ) THEN IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN INSERT INTO meteo_moscow_201709 VALUES (NEW.*); ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN INSERT INTO meteo_moscow_201710 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Moscow)'; END IF; ELSIF ( NEW.place = 'Sochi' ) THEN IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN INSERT INTO meteo_sochi_201709 VALUES (NEW.*); ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN INSERT INTO meteo_sochi_201710 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Sochi)'; END IF; ELSIF ( NEW.place = 'Magadan' ) THEN IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN INSERT INTO meteo_magadan_201709 VALUES (NEW.*); ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN INSERT INTO meteo_magadan_201710 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Magadan)'; END IF; ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger() !'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_meteo_trigger BEFORE INSERT ON meteo FOR EACH ROW EXECUTE PROCEDURE meteo_insert_trigger();


10でそのようなテーブルを作成するためのコマンド。 







 CREATE TABLE meteo ( t_id integer GENERATED BY DEFAULT AS IDENTITY, place text NOT NULL, hour_mesure timestamp DEFAULT now(), temperature real NOT NULL ) PARTITION BY RANGE (place, hour_mesure); CREATE TABLE meteo_moscow_201709 PARTITION of meteo FOR VALUES FROM ('Moscow', '2017-09-01 00:00:00') TO ('Moscow', '2017-10-01 00:00:00'); CREATE TABLE meteo_moscow_201710 PARTITION of meteo FOR VALUES FROM ('Moscow', '2017-10-01 00:00:00') TO ('Moscow', '2017-11-01 00:00:00'); CREATE TABLE meteo_sochi_201709 PARTITION of meteo FOR VALUES FROM ('Sochi', '2017-09-01 00:00:00') TO ('Sochi', '2017-10-01 00:00:00'); CREATE TABLE meteo_sochi_201710 PARTITION of meteo FOR VALUES FROM ('Sochi', '2017-10-01 00:00:00') TO ('Sochi', '2017-11-01 00:00:00'); CREATE TABLE meteo_paris_201709 PARTITION of meteo FOR VALUES FROM ('Magadan', '2017-09-01 00:00:00') TO ('Magadan', '2017-10-01 00:00:00'); CREATE TABLE meteo_paris_201710 PARTITION of meteo FOR VALUES FROM ('Magadan', '2017-10-01 00:00:00') TO ('Magadan', '2017-11-01 00:00:00');


バージョン10の宣言構文は、構文9.6よりもはるかに単純であることに注意してください。 その中で、例からわかるように、作業の最も面倒な部分はトリガー関数の作成です。







（ pg_pathman



開発者はさらに進んでいます。既存のセクションを切り取り、隣接するセクションと他の多くのセクションを結合する機能があります。 ）







以下は、テーブルにランダムな行を作成する関数の例です。 







 CREATE OR REPLACE FUNCTION populate_meteo() RETURNS TEXT AS $$ DECLARE placex text[] := '{}'; v_place text; v_hour timestamp; v_temperature real; v_nb_insertions integer := 500000; v_insertion integer; BEGIN placex[0]='Moscow'; placex[1]='Sochi'; placex[2]='Magadan'; FOR v_insertion IN 1 .. v_nb_insertions LOOP v_place=placex[floor((random()*3))::int]; v_hour='2017-09-01'::timestamp + make_interval(days => floor((random()*60))::int, secs => floor((random()*86400))::int); v_temperature:=round(((random()*14))::numeric+10,2); IF EXTRACT(MONTH FROM v_hour) = 10 THEN v_temperature:=v_temperature-4; END IF; IF EXTRACT(HOUR FROM v_hour) <= 9 OR EXTRACT(HOUR FROM v_hour) >= 20 THEN v_temperature:=v_temperature-5; ELSEIF EXTRACT(HOUR FROM v_hour) >= 12 AND EXTRACT(HOUR FROM v_hour) <= 17 THEN v_temperature:=v_temperature+5; END IF; INSERT INTO meteo (place,hour_mesure,temperature) VALUES (v_place,v_hour,v_temperature); END LOOP; RETURN v_nb_insertions||' mesures de température insérées'; END; $$ LANGUAGE plpgsql;


habr_9_6およびhabr_10からmeteoテーブルに行を挿入します。 







 habr_9_6=# EXPLAIN ANALYSE SELECT populate_meteo(); QUERY PLAN ------------------------------------------------------------------------------------------ Result (cost=0.00..0.26 rows=1 width=32) (actual time=33315.067..33315.068 rows=1 loops=1) Planning time: 0.034 ms Execution time: 33315.084 ms (3 rows) habr_10=# EXPLAIN ANALYSE SELECT populate_meteo(); QUERY PLAN --------------------------------------------------------------------------------------- Result (cost=0.00..0.26 rows=1 width=32) (actual time=14976.438..14976.438 rows=1 loops= 1) Planning time: 0.016 ms Execution time: 14976.499 ms (3 rows)


バージョン10では、データが2倍の速度で挿入されることがわかります。







セクションファミリ全体を一度に管理する







継承メカニズムを使用して作成されたセクションを持つ操作の場合、明示的な列挙が必要です。 







 habr_9_6=# SELECT 'VACUUM ANALYZE '||relname AS operation FROM pg_stat_user_tables WHERE relname LIKE 'meteo_%'; operation ------------------------------------ VACUUM ANALYZE meteo_moscow_201709 VACUUM ANALYZE meteo_moscow_201710 VACUUM ANALYZE meteo_sochi_201709 VACUUM ANALYZE meteo_sochi_201710 VACUUM ANALYZE meteo_paris_201709 VACUUM ANALYZE meteo_paris_201710 (6 rows) habr_9_6=# \gexec VACUUM VACUUM VACUUM VACUUM VACUUM VACUUM


10 , VACUUM ANALYSE , : 







 habr_10=# VACUUM ANALYZE meteo; VACUUM habr_10=# SELECT now() AS date,relname,last_vacuum,last_analyze FROM pg_stat_user_tables WHERE relname LIKE 'meteo_sochi%'; -[ RECORD 1 ]+------------------------------ date | 2018-04-06 23:38:48.59511+03 relname | meteo_sochi_201709 last_vacuum | 2018-04-06 23:37:05.931573+03 last_analyze | 2018-04-06 23:37:05.958845+03 -[ RECORD 2 ]+------------------------------ date | 2018-04-06 23:38:48.59511+03 relname | meteo_sochi_201710 last_vacuum | 2018-04-06 23:37:05.973254+03 last_analyze | 2018-04-06 23:37:06.002487+03








10



Index







10 - : 







 habr_10=# CREATE INDEX meteo_hour_mesure_idx ON meteo (hour_mesure); ERROR: cannot create index on partitioned table "meteo"


, , .







, .







, 9.6, : 







 habr_10=# CREATE INDEX meteo_moscow_201710_hour_idx ON meteo_moscow_201710 (hour_mesure); CREATE INDEX


11 ( ). , (FOREIGN KEY)













10 UPDATE



, . 







 habr_10=# UPDATE meteo SET place='Sochi' WHERE place='Moscow'; ERROR: new row for relation "meteo_moscow_201709" violates partition constraint DETAIL : Failing row contains (5, Sochi, 2017-09-15 05:09:23, 9.43).


11 : .













10 : 







 CREATE TABLE meteo_moscow_ancienne PARTITION of meteo FOR VALUES FROM ('Moscow', MINVALUE) TO ('Moscow', '2017-09-01 00:00:00'); CREATE TABLE meteo_sochi_ancienne PARTITION of meteo FOR VALUES FROM ('Sochi', MINVALUE) TO ('Sochi', '2017-09-01 00:00:00'); CREATE TABLE meteo_paris_ancienne PARTITION of meteo FOR VALUES FROM ('Magadan', MINVALUE) TO ('Magadan', '2017-09-01 00:00:00');










11.







PostgreSQL , , , . .







[ postgres- ]







More articles:


All Articles