PostgreSQLのパーティションテーブル（パーティションの作成） – 第1部

投稿日: 2019-11-05 2019-11-05
タグ: performance, postgresql

PostgreSQLのパーティションテーブルは非常に簡単です。PostgreSQLの継承コンセプトとトリガが含まれています。ここでは、PostgreSQLでテーブルを分割する方法を示すサンプルを提供します。

先に進む前に、いくつかの基本的な概念を理解してください。より良い私はテーブルの中にパーティション “時間”の概念を提供します。

“母” – （子供1、子供2、子供3、子供4、子供5）

マスターテーブル – （Child

01

2008、Child

02

2008、Child

03

2008、Child

04

2008、Child

05

2008）

たとえば、2008年1月のMasterTableにレコードを挿入すると、レコードは自動的に子テーブル（Child

01

2008）にリダイレクト（トリガー）されます。ユーザがMasterテーブルからレコードを選択すると、PostgreSQLは自動的にMasterTableから継承したすべての子テーブルからデータを取得します。

1）単純なテーブル呼び出し “hashvalue__PT”を作成し、 “hash”と “hashtime”の2つの列のみを含める**

CREATE TABLE hashvalue__PT
(
  hash bytea NOT NULL,
  hashtime timestamp without time zone NOT NULL
);

2）メインのhashvalue__PTテーブルから異なる月と継承で10個のテーブルを作成する**

--Create Partition with check rule for validation

CREATE TABLE hashvalue__PT__y2008m01（
CHECK（ハッシュタイム> = DATE '2008-01-01'およびハッシュタイム<DATE '2008-01-31'）
 ）INHERITS（ハッシュ値__PT）;
 CREATE TABLE hashvalue__PT__y2008m02（
CHECK（ハッシュタイム> = DATE '2008-02-01'およびハッシュタイム<DATE '2008-02-29'）
 ）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m03（
CHECK（ハッシュタイム> = DATE '2008-03-01'およびハッシュタイム<DATE '2008-03-31'）
 ）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m04（
CHECK（ハッシュタイム> = DATE '2008-04-01'およびハッシュタイム<DATE '2008-04-30'）
 ）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m05（
CHECK（ハッシュタイム> = DATE '2008-05-01'およびハッシュタイム<DATE '2008-05-31'）
）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m06（
CHECK（ハッシュタイム> = DATE '2008-06-01'およびハッシュタイム<DATE '2008-06-30'）
）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m07（
CHECK（ハッシュタイム> = DATE '2008-07-01'およびハッシュタイム<DATE '2008-07-31'）
）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m08（
CHECK（ハッシュタイム> = DATE '2008-08-01'およびハッシュタイム<DATE '2008-08-31'）
）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m09（
CHECK（ハッシュタイム> = DATE '2008-09-01'およびハッシュタイム<DATE '2008-09-30'）
）INHERITS（ハッシュ値__PT）;
CREATE TABLE hashvalue__PT__y2008m010（
CHECK（ハッシュタイム> = DATE '2008-10-01' ANDハッシュタイム<DATE '2008-10-31'）
）INHERITS（ハッシュ値__PT）;

3）子テーブルごとに主キーを作成する**

ALTER TABLE hashvalue__PT__y2008m01 ADD CONSTRAINT hashvalue__PT__y2008m01__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）;
ALTER TABLE hashvalue__PT__y2008m02 ADD CONSTRAINT hashvalue__PT__y2008m02__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。
ALTER TABLE hashvalue__PT__y2008m03 ADD CONSTRAINT hashvalue__PT__y2008m03__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。
ALTER TABLE hashvalue__PT__y2008m04 ADD CONSTRAINT hashvalue__PT__y2008m04__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。
ALTER TABLE hashvalue__PT__y2008m05 ADD CONSTRAINT hashvalue__PT__y2008m05__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。
ALTER TABLE hashvalue__PT__y2008m06 ADD CONSTRAINT hashvalue__PT__y2008m06__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。
ALTER TABLE hashvalue__PT__y2008m07 ADD CONSTRAINT hashvalue__PT__y2008m07__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）;
ALTER TABLE hashvalue__PT__y2008m08 ADD CONSTRAINT hashvalue__PT__y2008m08__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。
ALTER TABLE hashvalue__PT__y2008m09 ADD CONSTRAINT hashvalue__PT__y2008m09__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）;
ALTER TABLE hashvalue__PT__y2008m010 ADD CONSTRAINT hashvalue__PT__y2008m010__pkey PRIMARY KEY（ハッシュタイム、ハッシュ）。

4）子テーブルごとにインデックスを作成する**

CREATE INDEX idx__hashvalue__PT__y2008m01__hashtime ON hashvalue__PT__y2008m01（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m02__hashtime ON hashvalue__PT__y2008m02（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m03__hashtime ON hashvalue__PT__y2008m03（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m04__hashtime ON hashvalue__PT__y2008m04（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m05__hashtime ON hashvalue__PT__y2008m05（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m06__hashtime ON hashvalue__PT__y2008m06（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m07__hashtime ON hashvalue__PT__y2008m07（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m08__hashtime ON hashvalue__PT__y2008m08（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m09__hashtime ON hashvalue__PT__y2008m09（ハッシュタイム）;
CREATE INDEX idx__hashvalue__PT__y2008m010__hashtime ON hashvalue__PT__y2008m010（ハッシュタイム）;

5）母親のテーブルにトリガーを作成してレコードを子にリダイレクトする
テーブル。**

--create a trigger to redirect records to child table
CREATE OR REPLACE FUNCTION hashvalue__PT__func__insert__trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.hashtime >= DATE '2008-01-01' AND NEW.hashtime < DATE '2008-01-31' ) THEN
        INSERT INTO hashvalue__PT__y2008m01 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-02-01' AND NEW.hashtime < DATE '2008-02-29' ) THEN
        INSERT INTO hashvalue__PT__y2008m02 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-03-01' AND NEW.hashtime < DATE '2008-03-31' ) THEN
        INSERT INTO hashvalue__PT__y2008m03 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-04-01' AND NEW.hashtime < DATE '2008-04-30' ) THEN
        INSERT INTO hashvalue__PT__y2008m04 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-05-01' AND NEW.hashtime < DATE '2008-05-31' ) THEN
        INSERT INTO hashvalue__PT__y2008m05 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-06-01' AND NEW.hashtime < DATE '2008-06-30' ) THEN
    INSERT INTO hashvalue__PT__y2008m06 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-07-01' AND NEW.hashtime < DATE '2008-07-31' ) THEN
    INSERT INTO hashvalue__PT__y2008m07 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-08-01' AND NEW.hashtime < DATE '2008-08-31' ) THEN
    INSERT INTO hashvalue__PT__y2008m08 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-09-01' AND NEW.hashtime < DATE '2008-09-30' ) THEN
    INSERT INTO hashvalue__PT__y2008m09 VALUES (NEW.** );
    ELSIF ( NEW.hashtime >= DATE '2008-10-01' AND NEW.hashtime < DATE '2008-10-31' ) THEN
    INSERT INTO hashvalue__PT__y2008m010 VALUES (NEW.** );
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement__insert__trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger__hashvalue__PT__insert
    BEFORE INSERT ON hashvalue__PT
    FOR EACH ROW EXECUTE PROCEDURE hashvalue__PT__func__insert__trigger();

6）完了、シンプル**

次のセッション私は、パーティションテーブルと非パーティションテーブルの間のパフォーマンスをテストするために、パーティションテーブルから数百万のデータを挿入する関数を作成します。 PostgreSQLのパーティションテーブル（数百万のデータをシミュレートする – 第2部）を参照してください。リンク://データベース/パーティションテーブルのpostgresql-simulate-millions-data-part-2/

performance

postgresql

getdocs

13036