建立分割槽表

1. 建立父表，如果父表上定義了約束，子表會繼承，因此除非是全域性約束，否則不應該在表上定義約束，另外，父表不應該寫入資料

   CREATE TABLE log_ins(id serial,
       user_id int4,
       create_time timestamp(0) without time zone)

2. 透過 INHERITS 方式建立繼承表，也稱之為子表或分割槽，子表的欄位定義應該和父表保持一致

3. 給所有子表建立約束，只有滿足約束條件的資料才能寫入對應分割槽，注意分割槽約束至範圍不要有重疊。

   create TABLE log_ins_history(CHECK(create_time < '2020-01-01')) INHERITS(log_ins);
   create TABLE log_ins_202001(CHECK(create_time >= '2020-01-01' and create_time < '2020-02-01')) INHERITS(log_ins);
   create TABLE log_ins_202002(CHECK(create_time >= '2020-02-01' and create_time < '2020-03-01')) INHERITS(log_ins);
   ...

4. 給所有子表建立索引，由於繼承操作不會整合父表上的索引，因此索引需要手工建立。

   CREATE INDEX idx_his_ctime ON log_ins_history USING btree(create_time);
   CREATE INDEX idx_log_ins_202001_ctime ON log_ins_202001 USING btree(create_time);
   CREATE INDEX idx_log_ins_202002_ctime ON log_ins_202002 USING btree(create_time);
   ...

5. 在父表上定義 INSERT、DELETE、UPDATE 觸發器，將 SQL 分發到對應分割槽。

   CREATE OR REPLACE FUNCTION log_ins_insert_trigger()
       RETURNS trigger
       LANGUAGE plpgsql
   AS $function$
   BEGIN
       IF (NEW.create_time < '2020-01-01') THEN
           INSERT INTO log_ins_history VALUES (NEW.*);
       ELSIF (NEW.create_time > '2020-01-01' and NEW.create_time < '2020-02-01') THEN
           INSERT INTO log_ins_201701 VALUES (NEW.*);
       ELSIF (NEW.create_time > '2020-02-01' and NEW.create_time < '2020-03-01') THEN
           INSERT INTO log_ins_201702 VALUES (NEW.*);
       ...
       ELSE
           RAISE EXCEPTION 'create_time out of range. Fix the log_ins_insert_trigger() function!';
       END IF;
       RETURN NULL;
   END;
   $function$

6. 啟用 constraint_exclusion 引數，如果這個引數設定成 off，則父表上的 SQL 效能會降低。

使用分割槽表

在實際生產過程中，對於傳統分割槽表分割槽方式，不建議應用訪問父表，而是直接訪問子表。

constraint_exclusion 引數

• ON：所有表都透過約束最佳化查詢

• OFF：所有表都不透過約束最佳化查詢

• PARTITION：只有繼承表和 UNION ALL 子查詢透過檢索約束來最佳化查詢；

新增分割槽

1. 建立分割槽

   CREATE TABLE log_ins_202101(LIKE log_ins INCLUDING ALL)

2. 新增約束

   ALTER TABLE log_ins_202101 AND CONSTRAINT log_ins_202101_create_time_check CHECK(create_time >= '2021-01-01' and create_time < '2021-02-01')

3. 更新觸發器

4. 將分割槽繼承父表

   ALTER TABLE log_ins_202101 INHERIT log_ins

刪除分割槽

DROP TABLE log_ins_202001;
ALTER TABLE log_ins_202001 NO INHERIT log_ins;

檢視分割槽

1. \d

   \d log_in

2. SELECT

   SELECT nmsp_parent.nspname AS parent_schema,
          parent.relname AS parent,
          nmsp_child.nspname AS child_schema,
          child,relname AS child_table
     FROM pg_inherits
     JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
     JOIN pg_class child ON pg_inherits.inhrelid = child.oid
     JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
     JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
    WHERE parent.relname = 'log_ins'

3. 統計

   SELECT nspname, relname, count(*) AS partition_num
     FROM pg_class c
     JOIN pg_namespace n ON c.relnamespace = n.oid
     JOIN pg_inherits i ON c.oid = i.inhparent
    WHERE c.relhassubclass
      AND c.relkind in ('r','p')
    GROUP BY 1, 2
    ORDER BY partition_num DESC

注意事項

1. 當往父表插入資料時，需事先在父表上建立路由函式和觸發器，資料才會根據分割槽鍵路由規則插入到對應分割槽中，目前僅支援範圍分割槽和列表分割槽。

2. 分割槽表上的索引、約束需要使用單獨的命令建立，目前沒有辦法一次性自動在所有分割槽上建立索引、約束。

3. 父表和子表允許單獨定義主鍵，因此父表和子表可能存在重複的主鍵記錄，目前不支援在分割槽表上定義全域性主鍵。

4. UPDATE 時不建議更新分割槽鍵資料，特別是會使資料從一個分割槽移動到另一個分割槽的場景，可透過更新觸發器實現，但會帶來管理上的成本。

5. 效能方面：傳統分割槽表根據非分割槽鍵查詢相比普通表效能差距較大，因為這種場景下分割槽會掃描所有分割槽；根據分割槽鍵查詢相比普通表效能有小幅降低，而查詢分割槽表子表效能相比普通表略有提升；