SlideShare a Scribd company logo

パーティションのATTACH時の注意ポイント (第49回PostgreSQLアンカンファレンス@東京 発表資料)

•
•
N
NTT DATA Technology & Innovation

パーティションのATTACH時の注意ポイント (第49回PostgreSQLアンカンファレンス@東京 発表資料) 2024年10月12日(土) NTTデータグループ Innovation技術部 笠原 辰仁

1 of 16
© 2024 NTT DATA GROUP Corporation © 2024 NTT DATA GROUP Corporation PostgreSQLアンカンファレンス パーティションのATTACH時の注意ポイント 2024年10月12日 NTTデータグループ 技術革新統括本部 笠原辰仁
© 2024 NTT DATA GROUP Corporation 2 はじめに PostgreSQLではテーブルのパーティションをサポートしていますが、本日はパーティション定義の操 作の一つにあたるATTACH処理にフォーカスして、その注意ポイントを解説したいと思います。 ATTACHは既存のテーブルを既存のパーティションテーブルのパーティションとするための処理です。 パーティションテーブルへのパーティションの入れ替えを行う際に使われる処理ですが、運用中に実 施する場合には少し注意しておくことがあります。 資料は後日に公開します。
© 2024 NTT DATA GROUP Corporation 3 パーティショニングについての簡単な振り返り パーティショニングとは、論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分 割することを指す。(PostgreSQLのマニュアルより) パーティションキーをもとに一つのテーブルを複数のパーティションに分割したもの。 • パーティションテーブル：実体のデータを持たない仮想的なテーブル(この資料では親と表現) • パーティション：パーティションテーブルに紐づく個々のテーブル(この資料では子と表現) 詳しい説明はマニュアルを見てください。 5.11. テーブルのパーティショニング (postgresql.jp) パーティションテーブル(親) パーティション(子) クエリ パーティションはキーによりRANGE/LIST/HASHの 3つの方法で分割可能。 パーティションテーブルに発行されたクエリはキーに応じて 適切なパーティションへ処理がルーティングされる。 各パーティションへ直接クエリすることも可能。
© 2024 NTT DATA GROUP Corporation 4 パーティションの運用 一般的には、RANGE(時系列に増えるデータ中心)やLIST(カテゴリや属性で分かれるデータ中心)の利用が多く、 パーティションの入れ替えが運用のメインとなる。 パーティションのローテートが多い LISTパーティショニング [01-01, 02-01) [02-01, 03-01) [03-01, 04-01) 古いパーティションを (DETACHして) DROP 新しいパーティションを CREATE (してATTACH) メンテ対象パーティションを DETACHして メンテ済みのものをATTACH key=1 key=2 key=3 パーティションのスワップが多い RANGEパーティショニング
© 2024 NTT DATA GROUP Corporation 5 ATTACHコマンドについて 既存テーブルをパーティションテーブルのパーティションとして組み込むコマンド。 ★パーティションテーブルを作成 =# CREATE TABLE pt (c1 int, c2 int) PARTITION BY LIST(c2); CREATE TABLE ★普通のテーブルを作成 =# CREATE TABLE pt1 (c1 int, c2 int); CREATE TABLE ★作成したテーブルをパーティションとしてATTACH。 =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE
© 2024 NTT DATA GROUP Corporation 6 注意ポイントとTIPS
© 2024 NTT DATA GROUP Corporation 7 親の定義が伝搬される パーティションのATTACHの際、親の制約などの定義に従う必要があるもの、およびATTACH時に伝搬(子に作成、ま たは確認処理)される定義がある。 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの 時間 主キー 不要 〇 (主キー作成) 長 ユニークキー 不要 〇 (ユニークキー作成) 長 インデックス 不要 〇 (インデックス作成) 長 外部キー制約 不要 〇 (外部キー作成とバリデーション) 中 CHECK制約 必要 (親と同名の必要あり) × 無 NOT NULL制約 必要 × 無 トリガ (BEFORE STATEMENT) 不要 × 無 トリガ (BEFORE ROW) 不要 〇 (トリガ作成) 短(無) トリガ (AFTER STATEMENT) 不要 × 無 トリガ (BEFORE STATEMENT) 不要 〇 (トリガ作成) 短(無) IDENTITY(シーケンス) 不要 × (親のシーケンスが使われる) 無 分割方式 (Partition Constraint) 不要 〇 (CHECK制約に類似の制約) 中
© 2024 NTT DATA GROUP Corporation 8 インデックスの作成 親に主キー、(ユニーク)インデックスがあると、同じものをATTACH対象のテーブルにも作成する。ATTACHするテーブル に大量のデータがあると、当然のことながら時間がかかる。同じ定義の主キーや(ユニーク)インデックスを作成しておくと、 ATTACH時の作成をスキップできる。 ★パーティションテーブルとインデックスを作成 =# CREATE TABLE pt (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2)) PARTITION BY LIST(c2); =# CREATE UNIQUE INDEX pt_uniq_idx ON pt(c3); =# CREATE INDEX pt_c1_c3_idx ON pt(c1, c3); 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの 時間 主キー 不要 〇 (主キー作成) 長 ユニークキー 不要 〇 (ユニークキー作成) 長 インデックス 不要 〇 (インデックス作成) 長
© 2024 NTT DATA GROUP Corporation 9 インデックスの作成 =# CREATE TABLE pt1 (c1 int NOT NULL, c2 int NOT NULL); =# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); =# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | Partition of: pt FOR VALUES IN (1) Indexes: "pt1_pkey" PRIMARY KEY, btree (c1, c2) "pt1_c1_c3_idx" btree (c1, c3) "pt1_c2_idx" UNIQUE, btree (c2) =# CREATE TABLE pt2(c1 int,c2 int,c3 int,PRIMARY KEY (c1, c2)); =# CREATE UNIQUE INDEX pt2_uniq_idx ON pt2(c2); =# CREATE INDEX pt2_c1_c3_idx ON pt2(c1, c3); =# ¥d pt2 Table "public.pt2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | Indexes: "pt2_pkey" PRIMARY KEY, btree (c1, c2) "pt2_c1_c3_idx" btree (c1, c3) "pt2_uniq_idx" UNIQUE, btree (c2) =# ALTER TABLE pt ATTACH PARTITION pt2 FOR VALUES IN (2); =# ¥d pt2 Table "public.pt2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | Partition of: pt FOR VALUES IN (2) Indexes: "pt2_pkey" PRIMARY KEY, btree (c1, c2) "pt2_c1_c3_idx" btree (c1, c3) "pt2_uniq_idx" UNIQUE, btree (c2) 新規作成される 既存のものが利用される
© 2024 NTT DATA GROUP Corporation 10 インデックスの作成 =# ¥d+ pt_pkey Partitioned index "public.pt_pkey" Column | Type | Key? | Definition | Storage | Stats target --------+---------+------+------------+---------+-------------- c1 | integer | yes | c1 | plain | c2 | integer | yes | c2 | plain | primary key, btree, for table "public.pt" Partitions: pt1_pkey, pt2_pkey Access method: btree =# ¥d+ pt_uniq_idx Partitioned index "public.pt_uniq_idx" Column | Type | Key? | Definition | Storage | Stats target --------+---------+------+------------+---------+-------------- c2 | integer | yes | c2 | plain | unique, btree, for table "public.pt" Partitions: pt1_c2_idx, pt2_uniq_idx Access method: btree パーティションテーブルに付与されているインデックスも パーティションテーブルと同様、仮想的なインデックス。 ATTACH時に、パーティションインデックスと同じものが なければ作成し、既にあれば内部的にはインデックスの ATTACHと同じことが実施される。
© 2024 NTT DATA GROUP Corporation 11 外部キー制約のチェック パーティションテーブルに外部キーがある(別のテーブルを参照している)と、そのチェックが走って時間 が長期化する。可能であれば外部キー定義を予めATTACH対象のテーブルに付与しておくと良い。 =# CREATE TABLE st(c1 int primary key); =# CREATE TABLE pt (c1 int, c2 int, c3 int REFERENCES st(c1)) PARTITION BY LIST(c2); =# CREATE TABLE pt1 (c1 int, c2 int, c3 int); CREATE TABLE t=# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | | c2 | integer | | | c3 | integer | | | =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE =# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | | c2 | integer | | | c3 | integer | | | Partition of: pt FOR VALUES IN (1) Foreign-key constraints: "pt_c3_fkey" FOREIGN KEY (c3) REFERENCES st(c1) 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの 時間 外部キー制約 不要 〇 (外部キー作成とバリデーション) 中
© 2024 NTT DATA GROUP Corporation 12 Partition Constraintのチェック パーティションを組み込む際には、RANGEやLISTの範囲や値を指定する。ATTACH時にこの指 定値に従っているかのチェックがATTACH対象のテーブルで実施される。パーティションの分割方式 と同じ定義のCHECK制約を予めATTACH対象のテーブルに付与しておくと良い。 ★ 親を作成 =# CREATE TABLE pt (c1 int, c2 int) PARTITION BY LIST(c2); ★ 何も付与しないテーブル作成 =# CREATE TABLE pt1 (c1 int, c2 int); ★ ATTACHのタイミングでpt1のc2がIS NOT NULLかつ1の値のみ持つかを検査する =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE ★ パーティションの分割方式と同じ定義のCHECK制約を持つテーブル作成 =# CREATE TABLE pt2 (c1 int, c2 int, CHECK ((c2 IS NOT NULL) AND (c2 = 2))); ★ ATTACHでの検査はスキップされる =# ALTER TABLE pt ATTACH PARTITION pt2 FOR VALUES IN (2); INFO: partition constraint for table “pt2” is implied by existing constraints 注意) PG12からはDEBUGレベルで出ます ALTER TABLE
© 2024 NTT DATA GROUP Corporation 13 Defaultパーティションがいる場合の注意 デフォルトパーティションにデータがある場合、新規のパーティションの追加時に、デフォルトパーティション上のデータを含む かの走査が実施される。含む場合はパーティションのCREAREやATTACHはできない。デフォルトパーティションからデー タを手動で取り除く/移動する必要がある。 =# CREATE TABLE dpt (c1 int, c2 int) PARTITION BY LIST (c2); =# CREATE TABLE dpt1 PARTITION OF dpt FOR VALUES IN (1); =# CREATE TABLE dpt2 PARTITION OF dpt FOR VALUES IN (2); =# CREATE TABLE dptf PARTITION OF dpt DEFAULT; =# INSERT INTO dpt SELECT 1, generate_series(1,5); ★ デフォルトパーティションにキー値3, 4, 5がある =# SELECT * FROM dptf; c1 | c2 ----+---- 1 | 3 1 | 4 1 | 5 (3 rows) ★この状態でキー値3を持つパーティションは作成できない =# CREATE TABLE dpt3 PARTITION OF dpt FOR VALUES IN (3); ERROR: updated partition constraint for default partition "dptf" would be violated by some row ★消すか移せばOK =# DELETE FROM dpt WHERE c2 = 3; DELETE 1 =# CREATE TABLE dpt3 PARTITION OF dpt FOR VALUES IN (3); CREATE TABLE
© 2024 NTT DATA GROUP Corporation 14 【参考】CHECK制約の注意 親にCHECK制約がある場合、同名のCHECK制約をATTACH対象のテーブルに作成しておく必要がある。 ★ (c3 > 10) というCHECK制約をもつパーティションテーブルを作成 =# CREATE TABLE pt (c1 int, c2 int, c3 int, CHECK (c3 > 10)) PARTITION BY LIST (c2); ★ CHECK制約を何もつけないテーブルはATTACHできない =# CREATE TABLE pt1 (c1 int, c2 int, c3 int); =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ERROR: child table is missing constraint "pt_c3_check“ ★ 同じ定義のCHECK制約を持つテーブルでもエラーで弾かれる =# CREATE TABLE pt1 (c1 int, c2 int, c3 int, CHECK (c3 > 10)); =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ERROR: child table is missing constraint "pt_c3_check“ ★同じ定義で同名のCHECK制約が必要 =# CREATE TABLE pt1 (c1 int, c2 int, c3 int, CONSTRAINT pt_c3_check CHECK (c3 > 10)); =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの CHECK制約 必要 (親と同名の必要あり) ×
© 2024 NTT DATA GROUP Corporation 15 まとめ 以下のケースではATTACH時に時間がかかることがあるので、短時間の実施を計画する場合は注意しておきましょう。 制約やインデックスなど ATTACH時に伝搬されるもの 時間 対策 主キー 〇 (主キー作成) 長 事前に子に同じ定義の主キーを作成 ユニークキー 〇 (ユニークキー作成) 長 事前に子に同じ定義のユニークキーを作成 インデックス 〇 (インデックス作成) 長 事前に子に同じ定義のインデックス作成 外部キー制約 〇 (外部キー作成とバリデーション) 中 事前に同じ定義の外部キーを作成 分割方式 (Partition Constraint) 〇 (CHECK制約に類似の制約) 中 事前に同じ定義のCHECK制約を作成 その他、デフォルトパーティションがある場合は、その中のデータのパーティションキーがATTACH対象のテーブルのパーティションキーと 重複しないかを確認し、存在した場合は事前にATTACHするテーブルに移す。
その他、記載されている会社名、商品名、又はサービス名は、各 社の登録商標又は商標です。

More Related Content

パーティションのATTACH時の注意ポイント (第49回PostgreSQLアンカンファレンス@東京 発表資料)

  • 1. © 2024 NTT DATA GROUP Corporation © 2024 NTT DATA GROUP Corporation PostgreSQLアンカンファレンス パーティションのATTACH時の注意ポイント 2024年10月12日 NTTデータグループ 技術革新統括本部 笠原辰仁
  • 2. © 2024 NTT DATA GROUP Corporation 2 はじめに PostgreSQLではテーブルのパーティションをサポートしていますが、本日はパーティション定義の操 作の一つにあたるATTACH処理にフォーカスして、その注意ポイントを解説したいと思います。 ATTACHは既存のテーブルを既存のパーティションテーブルのパーティションとするための処理です。 パーティションテーブルへのパーティションの入れ替えを行う際に使われる処理ですが、運用中に実 施する場合には少し注意しておくことがあります。 資料は後日に公開します。
  • 3. © 2024 NTT DATA GROUP Corporation 3 パーティショニングについての簡単な振り返り パーティショニングとは、論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分 割することを指す。(PostgreSQLのマニュアルより) パーティションキーをもとに一つのテーブルを複数のパーティションに分割したもの。 • パーティションテーブル：実体のデータを持たない仮想的なテーブル(この資料では親と表現) • パーティション：パーティションテーブルに紐づく個々のテーブル(この資料では子と表現) 詳しい説明はマニュアルを見てください。 5.11. テーブルのパーティショニング (postgresql.jp) パーティションテーブル(親) パーティション(子) クエリ パーティションはキーによりRANGE/LIST/HASHの 3つの方法で分割可能。 パーティションテーブルに発行されたクエリはキーに応じて 適切なパーティションへ処理がルーティングされる。 各パーティションへ直接クエリすることも可能。
  • 4. © 2024 NTT DATA GROUP Corporation 4 パーティションの運用 一般的には、RANGE(時系列に増えるデータ中心)やLIST(カテゴリや属性で分かれるデータ中心)の利用が多く、 パーティションの入れ替えが運用のメインとなる。 パーティションのローテートが多い LISTパーティショニング [01-01, 02-01) [02-01, 03-01) [03-01, 04-01) 古いパーティションを (DETACHして) DROP 新しいパーティションを CREATE (してATTACH) メンテ対象パーティションを DETACHして メンテ済みのものをATTACH key=1 key=2 key=3 パーティションのスワップが多い RANGEパーティショニング
  • 5. © 2024 NTT DATA GROUP Corporation 5 ATTACHコマンドについて 既存テーブルをパーティションテーブルのパーティションとして組み込むコマンド。 ★パーティションテーブルを作成 =# CREATE TABLE pt (c1 int, c2 int) PARTITION BY LIST(c2); CREATE TABLE ★普通のテーブルを作成 =# CREATE TABLE pt1 (c1 int, c2 int); CREATE TABLE ★作成したテーブルをパーティションとしてATTACH。 =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE
  • 6. © 2024 NTT DATA GROUP Corporation 6 注意ポイントとTIPS
  • 7. © 2024 NTT DATA GROUP Corporation 7 親の定義が伝搬される パーティションのATTACHの際、親の制約などの定義に従う必要があるもの、およびATTACH時に伝搬(子に作成、ま たは確認処理)される定義がある。 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの 時間 主キー 不要 〇 (主キー作成) 長 ユニークキー 不要 〇 (ユニークキー作成) 長 インデックス 不要 〇 (インデックス作成) 長 外部キー制約 不要 〇 (外部キー作成とバリデーション) 中 CHECK制約 必要 (親と同名の必要あり) × 無 NOT NULL制約 必要 × 無 トリガ (BEFORE STATEMENT) 不要 × 無 トリガ (BEFORE ROW) 不要 〇 (トリガ作成) 短(無) トリガ (AFTER STATEMENT) 不要 × 無 トリガ (BEFORE STATEMENT) 不要 〇 (トリガ作成) 短(無) IDENTITY(シーケンス) 不要 × (親のシーケンスが使われる) 無 分割方式 (Partition Constraint) 不要 〇 (CHECK制約に類似の制約) 中
  • 8. © 2024 NTT DATA GROUP Corporation 8 インデックスの作成 親に主キー、(ユニーク)インデックスがあると、同じものをATTACH対象のテーブルにも作成する。ATTACHするテーブル に大量のデータがあると、当然のことながら時間がかかる。同じ定義の主キーや(ユニーク)インデックスを作成しておくと、 ATTACH時の作成をスキップできる。 ★パーティションテーブルとインデックスを作成 =# CREATE TABLE pt (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2)) PARTITION BY LIST(c2); =# CREATE UNIQUE INDEX pt_uniq_idx ON pt(c3); =# CREATE INDEX pt_c1_c3_idx ON pt(c1, c3); 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの 時間 主キー 不要 〇 (主キー作成) 長 ユニークキー 不要 〇 (ユニークキー作成) 長 インデックス 不要 〇 (インデックス作成) 長
  • 9. © 2024 NTT DATA GROUP Corporation 9 インデックスの作成 =# CREATE TABLE pt1 (c1 int NOT NULL, c2 int NOT NULL); =# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); =# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | Partition of: pt FOR VALUES IN (1) Indexes: "pt1_pkey" PRIMARY KEY, btree (c1, c2) "pt1_c1_c3_idx" btree (c1, c3) "pt1_c2_idx" UNIQUE, btree (c2) =# CREATE TABLE pt2(c1 int,c2 int,c3 int,PRIMARY KEY (c1, c2)); =# CREATE UNIQUE INDEX pt2_uniq_idx ON pt2(c2); =# CREATE INDEX pt2_c1_c3_idx ON pt2(c1, c3); =# ¥d pt2 Table "public.pt2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | Indexes: "pt2_pkey" PRIMARY KEY, btree (c1, c2) "pt2_c1_c3_idx" btree (c1, c3) "pt2_uniq_idx" UNIQUE, btree (c2) =# ALTER TABLE pt ATTACH PARTITION pt2 FOR VALUES IN (2); =# ¥d pt2 Table "public.pt2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | Partition of: pt FOR VALUES IN (2) Indexes: "pt2_pkey" PRIMARY KEY, btree (c1, c2) "pt2_c1_c3_idx" btree (c1, c3) "pt2_uniq_idx" UNIQUE, btree (c2) 新規作成される 既存のものが利用される
  • 10. © 2024 NTT DATA GROUP Corporation 10 インデックスの作成 =# ¥d+ pt_pkey Partitioned index "public.pt_pkey" Column | Type | Key? | Definition | Storage | Stats target --------+---------+------+------------+---------+-------------- c1 | integer | yes | c1 | plain | c2 | integer | yes | c2 | plain | primary key, btree, for table "public.pt" Partitions: pt1_pkey, pt2_pkey Access method: btree =# ¥d+ pt_uniq_idx Partitioned index "public.pt_uniq_idx" Column | Type | Key? | Definition | Storage | Stats target --------+---------+------+------------+---------+-------------- c2 | integer | yes | c2 | plain | unique, btree, for table "public.pt" Partitions: pt1_c2_idx, pt2_uniq_idx Access method: btree パーティションテーブルに付与されているインデックスも パーティションテーブルと同様、仮想的なインデックス。 ATTACH時に、パーティションインデックスと同じものが なければ作成し、既にあれば内部的にはインデックスの ATTACHと同じことが実施される。
  • 11. © 2024 NTT DATA GROUP Corporation 11 外部キー制約のチェック パーティションテーブルに外部キーがある(別のテーブルを参照している)と、そのチェックが走って時間 が長期化する。可能であれば外部キー定義を予めATTACH対象のテーブルに付与しておくと良い。 =# CREATE TABLE st(c1 int primary key); =# CREATE TABLE pt (c1 int, c2 int, c3 int REFERENCES st(c1)) PARTITION BY LIST(c2); =# CREATE TABLE pt1 (c1 int, c2 int, c3 int); CREATE TABLE t=# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | | c2 | integer | | | c3 | integer | | | =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE =# ¥d pt1 Table "public.pt1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- c1 | integer | | | c2 | integer | | | c3 | integer | | | Partition of: pt FOR VALUES IN (1) Foreign-key constraints: "pt_c3_fkey" FOREIGN KEY (c3) REFERENCES st(c1) 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの 時間 外部キー制約 不要 〇 (外部キー作成とバリデーション) 中
  • 12. © 2024 NTT DATA GROUP Corporation 12 Partition Constraintのチェック パーティションを組み込む際には、RANGEやLISTの範囲や値を指定する。ATTACH時にこの指 定値に従っているかのチェックがATTACH対象のテーブルで実施される。パーティションの分割方式 と同じ定義のCHECK制約を予めATTACH対象のテーブルに付与しておくと良い。 ★ 親を作成 =# CREATE TABLE pt (c1 int, c2 int) PARTITION BY LIST(c2); ★ 何も付与しないテーブル作成 =# CREATE TABLE pt1 (c1 int, c2 int); ★ ATTACHのタイミングでpt1のc2がIS NOT NULLかつ1の値のみ持つかを検査する =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE ★ パーティションの分割方式と同じ定義のCHECK制約を持つテーブル作成 =# CREATE TABLE pt2 (c1 int, c2 int, CHECK ((c2 IS NOT NULL) AND (c2 = 2))); ★ ATTACHでの検査はスキップされる =# ALTER TABLE pt ATTACH PARTITION pt2 FOR VALUES IN (2); INFO: partition constraint for table “pt2” is implied by existing constraints 注意) PG12からはDEBUGレベルで出ます ALTER TABLE
  • 13. © 2024 NTT DATA GROUP Corporation 13 Defaultパーティションがいる場合の注意 デフォルトパーティションにデータがある場合、新規のパーティションの追加時に、デフォルトパーティション上のデータを含む かの走査が実施される。含む場合はパーティションのCREAREやATTACHはできない。デフォルトパーティションからデー タを手動で取り除く/移動する必要がある。 =# CREATE TABLE dpt (c1 int, c2 int) PARTITION BY LIST (c2); =# CREATE TABLE dpt1 PARTITION OF dpt FOR VALUES IN (1); =# CREATE TABLE dpt2 PARTITION OF dpt FOR VALUES IN (2); =# CREATE TABLE dptf PARTITION OF dpt DEFAULT; =# INSERT INTO dpt SELECT 1, generate_series(1,5); ★ デフォルトパーティションにキー値3, 4, 5がある =# SELECT * FROM dptf; c1 | c2 ----+---- 1 | 3 1 | 4 1 | 5 (3 rows) ★この状態でキー値3を持つパーティションは作成できない =# CREATE TABLE dpt3 PARTITION OF dpt FOR VALUES IN (3); ERROR: updated partition constraint for default partition "dptf" would be violated by some row ★消すか移せばOK =# DELETE FROM dpt WHERE c2 = 3; DELETE 1 =# CREATE TABLE dpt3 PARTITION OF dpt FOR VALUES IN (3); CREATE TABLE
  • 14. © 2024 NTT DATA GROUP Corporation 14 【参考】CHECK制約の注意 親にCHECK制約がある場合、同名のCHECK制約をATTACH対象のテーブルに作成しておく必要がある。 ★ (c3 > 10) というCHECK制約をもつパーティションテーブルを作成 =# CREATE TABLE pt (c1 int, c2 int, c3 int, CHECK (c3 > 10)) PARTITION BY LIST (c2); ★ CHECK制約を何もつけないテーブルはATTACHできない =# CREATE TABLE pt1 (c1 int, c2 int, c3 int); =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ERROR: child table is missing constraint "pt_c3_check“ ★ 同じ定義のCHECK制約を持つテーブルでもエラーで弾かれる =# CREATE TABLE pt1 (c1 int, c2 int, c3 int, CHECK (c3 > 10)); =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ERROR: child table is missing constraint "pt_c3_check“ ★同じ定義で同名のCHECK制約が必要 =# CREATE TABLE pt1 (c1 int, c2 int, c3 int, CONSTRAINT pt_c3_check CHECK (c3 > 10)); =# ALTER TABLE pt ATTACH PARTITION pt1 FOR VALUES IN (1); ALTER TABLE 制約やインデックスなど ATTACHするテーブルに必要なもの ATTACH時に伝搬されるもの CHECK制約 必要 (親と同名の必要あり) ×
  • 15. © 2024 NTT DATA GROUP Corporation 15 まとめ 以下のケースではATTACH時に時間がかかることがあるので、短時間の実施を計画する場合は注意しておきましょう。 制約やインデックスなど ATTACH時に伝搬されるもの 時間 対策 主キー 〇 (主キー作成) 長 事前に子に同じ定義の主キーを作成 ユニークキー 〇 (ユニークキー作成) 長 事前に子に同じ定義のユニークキーを作成 インデックス 〇 (インデックス作成) 長 事前に子に同じ定義のインデックス作成 外部キー制約 〇 (外部キー作成とバリデーション) 中 事前に同じ定義の外部キーを作成 分割方式 (Partition Constraint) 〇 (CHECK制約に類似の制約) 中 事前に同じ定義のCHECK制約を作成 その他、デフォルトパーティションがある場合は、その中のデータのパーティションキーがATTACH対象のテーブルのパーティションキーと 重複しないかを確認し、存在した場合は事前にATTACHするテーブルに移す。