PostgreSQL使用表繼承實現分割槽表

PostgreSQL9.6支援基本表的分割槽。這部分將描述為什麼以及如何來實現表分割槽作為你資料庫設計的一部分。

概述
分割槽指的是將邏輯上一的一個大表分成多個小的物理上的片(子表)，分割槽可以提供以下好處:
.在某些情況下查詢效能能夠顯著提升，特別是當那些訪問壓力大的行在一個分割槽或者少數幾個分割槽時。分割槽可以取代索引的主導列、減小索引尺寸以及使索引中訪問壓力大的部分更有可能被放在記憶體中。

.當查詢或更新訪問一個分割槽的大部分行時，可以透過該分割槽上的一個順序掃描來取代分散到整個表上的索引和隨機訪問，這樣可以改善效能。

.如果需求計劃使用劃分設計，可以透過增加或移除分割槽來完成批次載入和刪除。ALTER TABLE NO INHERIT和DROP TABLE都遠快於一個批次操作。這些命令也完全避免了由批次DELETE造成的VACUUM負載。

.很少使用的資料可以被遷移到便宜且較慢的儲存介質上。

當一個表非常大時，分割槽所帶來的好處是非常值得的。一個表何種情況下會從分割槽中獲益取決於應用，一個經驗法則是當表的尺寸超過了資料庫伺服器實體記憶體時，分割槽會為錶帶來好處。

當前，PostgreSQL支援透過表繼承來實現分割槽。每個分割槽必須被建立為單個父表的子表。父表它本身正常來說是空的；它存在僅僅是代表整個資料庫。在試圖設定分割槽之前應該要先熟悉表繼承。

在PostgreSQL中可以實現下列形式的分割槽：

範圍分割槽
表被根據一個關鍵列或一組列劃分為"範圍"分割槽，不同的分割槽的範圍之間沒有重疊。例如，我們可以根據日期範圍劃分分割槽，或者根據特定業務物件的識別符號劃分分割槽。

列表分割槽
透過顯式地列出每一個分割槽中出現的鍵值來劃分表。

實現分割槽
要建立一個分割槽表，可以這樣做：
1.建立一個"主"表，所有的分割槽都將繼承它。
這個表將不包含資料。不要對這個表定義任何檢查約束，除非你打算將這些約束應用到所有的分割槽。同樣也不需要定義任何索引或者唯一約束。

2.建立一些繼承於主表的"子"表。通常，這些表不會在從主表繼承的列集中增加任何列。
們將這些子表認為是分割槽，儘管它們在各方面來看普通的PostgreSQL表（或者可能是外部表）。

3.為分割槽表增加表約束以定義每個分割槽中允許的鍵值。
典型的例子是：

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

要確保這些約束能夠保證在不同分割槽所允許的鍵值之間不存在重疊。設定範圍約束時一種常見的錯誤是：

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

這是錯誤的，因為鍵值200並沒有被清楚地分配到某一個分割槽。注意在語法上範圍劃分和列表劃分沒有區別，這些術語只是為了描述方便而存在。

4.對於每一個分割槽，在關鍵列上建立一個索引，並建立其他我們所需要的索引（關鍵索引並不是嚴格必要的，但是在大部分情況下它都是有用的。如果我們希望鍵值是唯一的，則我們還要為每一個分割槽建立一個唯一或者主鍵約束。）

5.還可以有選擇地定義一個觸發器或者規則將插入到主表上的資料重定向到合適的分割槽上。

6.確保在postgresql.conf中constraint_exclusion配置引數沒有被禁用。如果它被禁用，查詢將不會被按照期望的方式最佳化。

例如，假設我們正在為一個大型的冰淇淋公司構建一個資料庫。該公司測量每天在每一個區域的最高氣溫以及冰淇淋銷售。在概念上，我們想要一個這樣的表：

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
insert into measurement values(1,date '2008-02-01',1,1);

由於該表的主要用途是為管理層提供線上報告，我們知道大部分查詢將只會訪問上週、上月或者上季度的資料。為了減少需要儲存的舊資料的量，我們決定只保留最近3年的資料。在每一個月的開始，我們將刪除最老的一個月的資料。

在這種情況下，我們可以使用分割槽來幫助我們滿足對於測量表的所有不同需求。按照上面所勾勒的步驟，分割槽可以這樣來建立：

1.主表是measurement表，完全按照以上的方式宣告。

jydb=# CREATE TABLE measurement (
jydb(#     city_id         int not null,
jydb(#     logdate         date not null,
jydb(#     peaktemp        int,
jydb(#     unitsales       int
jydb(# );
CREATE TABLE

2.下一步我們為每一個活動月建立一個分割槽：

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

每一個分割槽自身都是完整的表，但是它們的定義都是從measurement表繼承而來。

這解決了我們的一個問題：刪除舊資料。每個月，我們所需要做的是在最舊的子表上執行一個DROP TABLE命令併為新一個月的資料建立一個新的子表。

3.我們必須提供不重疊的表約束。和前面簡單地建立分割槽表不同，實際的表建立指令碼應該是：

jydb=# CREATE TABLE measurement_y2006m02 (
jydb(#     CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) jydb(# ) INHERITS (measurement); CREATE TABLE jydb=# CREATE TABLE measurement_y2006m03 ( jydb(# CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) jydb(# ) INHERITS (measurement); CREATE TABLE jydb=# CREATE TABLE measurement_y2008m02 ( jydb(# CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2008m03
jydb-#   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE

4.我們可能在關鍵列上也需要索引：

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

在這裡我們選擇不增加更多的索引。

5.我們希望我們的應用能夠使用INSERT INTO measurement ...並且資料將被重定向到合適的分割槽表。我們可以透過為主表附加一個合適的觸發器函式來實現這一點。如果資料將只被增加到最後一個分割槽，我們可以使用一個非常簡單的觸發器函式：

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

完成函式建立後，我們建立一個呼叫該觸發器函式的觸發器：

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

我們必須在每個月重新定義觸發器函式，這樣它才會總是指向當前分割槽。而觸發器的定義則不需要被更新。

我們也可能希望插入資料時伺服器會自動地定位應該加入資料的分割槽。我們可以透過一個更復雜的觸發器函式來實現之，例如：

jydb=# CREATE OR REPLACE FUNCTION measurement_insert_trigger()
jydb-# RETURNS TRIGGER AS $$
jydb$# BEGIN
jydb$#
jydb$#     IF ( NEW.logdate >= DATE '2006-03-01' AND
jydb$#             NEW.logdate < DATE '2006-04-01' ) THEN jydb$# INSERT INTO measurement_y2006m03 VALUES (NEW.*); jydb$# ELSIF ( NEW.logdate >= DATE '2008-02-01' AND
jydb$#             NEW.logdate < DATE '2008-03-01' ) THEN
jydb$#         INSERT INTO measurement_y2008m02 VALUES (NEW.*);
jydb$#     ELSE
jydb$#         RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
jydb$#     END IF;
jydb$#     RETURN NULL;
jydb$# END;
jydb$# $$
jydb-# LANGUAGE plpgsql;
CREATE FUNCTION
jydb=# CREATE TRIGGER insert_measurement_trigger
jydb-#     BEFORE INSERT ON measurement
jydb-#     FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
CREATE TRIGGER
jydb=# insert into measurement values(1,date '2006-03-03',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-03',1,1);
INSERT 0 0
jydb=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2006-03-03 |        1 |         1
(2 rows)
jydb=# select * from measurement_y2008m02;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
       1 | 2008-02-03 |        1 |         1
(5 rows)
jydb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2006-03-03 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
       1 | 2008-02-03 |        1 |         1
(7 rows)

觸發器的定義和以前一樣。注意每一個IF測試必須準確地匹配它的分割槽的CHECK約束。當該函式比單月形式更加複雜時，並不需要頻繁地更新它，因為可以在需要的時候提前加入分支。

注意: 在實踐中，如果大部分插入都會進入最新的分割槽，最好先檢查它。為了簡潔，我們為觸發器的檢查採用了和本例中其他部分一致的順序。

如我們所見，一個複雜的分割槽模式可能需要大量的DDL。在上面的例子中，我們需要每月建立一個新分割槽，所以最好能夠編寫一個指令碼自動地生成所需的DDL。

管理分割槽
通常當初始定義的表傾向於動態變化時，一組分割槽會被建立。刪除舊的分割槽並週期性地為新資料增加新分割槽是很常見的。劃分的一個最重要的優點是可以透過操縱分割槽結構來使得這種痛苦的任務幾乎是自發地完成，而不需要去物理地移除大量的資料。

移除舊資料的最簡單的選項是直接刪除不再需要的分割槽：

jydb=# DROP TABLE measurement_y2006m02;
DROP TABLE

這可以非常快地刪除百萬級別的記錄，因為它不需要逐一地刪除記錄。

另一個經常使用的選項是將分割槽從被劃分的表中移除，但是把它作為一個獨立的表保留下來：

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

這允許在資料被刪除前執行更進一步的操作。例如，這是一個很有用的時機透過COPY、pg_dump或類似的工具來備份資料。這也是進行資料聚集、執行其他資料操作或執行報表的好時機。

相似地我們也可以增加新分割槽來處理新資料。我們可以在被劃分的表中建立一個新的空分割槽：

jydb=# CREATE TABLE measurement_y2008m02 (
jydb(#     CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE

作為一種選擇方案，有時建立一個在分割槽結構之外的新表更方便，並且在以後才將它作為一個合適的分割槽。這使得資料可以在出現於分割槽表中之前被載入、檢查和轉換：

jydb=# CREATE TABLE measurement_y2008m03
jydb-#   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
jydb=# ALTER TABLE measurement_y2008m03 ADD CONSTRAINT y2008m03
jydb-#    CHECK ( logdate >= DATE '2008-03-01' AND logdate < DATE '2008-04-01' );
ALTER TABLE
jydb=# ALTER TABLE measurement_y2008m03 INHERIT measurement;
ALTER TABLE

分割槽與約束排除
約束排除是一種查詢最佳化技術，它可以為按照以上方式定義的分割槽表提高效能。例如：

jydb=# SET constraint_exclusion = on;
SET
jydb=# SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
 count
-------
     3
(1 row)

如果沒有約束排除，上述查詢將掃描measurement表的每一個分割槽。在啟用約束排除後，規劃器將檢查每一個分割槽的約束來確定該分割槽需不需要被掃描，因為分割槽中可能不包含滿足查詢WHERE子句的行。如果規劃器能夠證實這一點，則它將會把該分割槽排除在查詢計劃之外。

可以使用EXPLAIN命令來顯示開啟了constraint_exclusion的計劃和沒有開啟該選項的計劃之間的區別。一個典型的未最佳化的計劃是：

jydb=# SET constraint_exclusion = off;
SET
jydb=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=107.47..107.48 rows=1 width=8)
   ->  Append  (cost=0.00..102.69 rows=1913 width=0)
         ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
(10 rows)

其中的某些或者全部分割槽將會使用索引掃描而不是全表順序掃描，但是關鍵在於根本不需要掃描舊分割槽來回答這個查詢。當我們開啟約束排除後，對於同一個查詢我們會得到一個更加廉價的計劃：

jydb=# SET constraint_exclusion = on;
SET
jydb=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=72.80..72.81 rows=1 width=8)
   ->  Append  (cost=0.00..69.56 rows=1296 width=0)
         ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
(8 rows)

注意約束排除只由CHECK約束驅動，而非索引的存在。因此，沒有必要在關鍵列上定義索引。是否在給定分割槽上定義索引取決於我們希望查詢經常掃描表的大部分還是小部分。在後一種情況中索引將會發揮作用。

constraint_exclusion的預設（也是推薦）設定實際上既不是on也不是off，而是一個被稱為partition的中間設定，這使得該技術只被應用於將要在分割槽表上工作的查詢。設定on將使得規劃器在所有的查詢中檢查CHECK約束，即使簡單查詢不會從中受益。

替代的分割槽方法
另一種將插入資料重定向到合適的分割槽的方法是在主表上建立規則而不是觸發器，例如：

jydb=# CREATE RULE measurement_insert_y2006m03 AS
jydb-# ON INSERT TO measurement WHERE
jydb-# ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) jydb-# DO INSTEAD jydb-# INSERT INTO measurement_y2006m03 VALUES (NEW.*); CREATE RULE jydb=# CREATE RULE measurement_insert_y2008m02 AS jydb-# ON INSERT TO measurement WHERE jydb-# ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb-# DO INSTEAD
jydb-# INSERT INTO measurement_y2008m02 VALUES (NEW.*);
CREATE RULE
jydb=# insert into measurement values(1,date '2006-03-02',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-02',1,1);
INSERT 0 0
jydb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
(5 rows)
jydb=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-02 |        1 |         1
(1 row)
jydb=# select * from measurement_y2008m02;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-01 |        1 |         1
       1 | 2008-02-02 |        1 |         1
(4 rows)

一個規則比一個觸發器具有明顯更高的負荷，但是該負荷是由每個查詢承擔而不是每一個行，因此這種方法可能對於批次插入的情況有益。但是，在大部分情況下觸發器方法能提供更好的效能。

注意COPY會忽略規則。如果希望使用COPY來插入資料，我們將希望將資料複製到正確的分割槽表而不是主表。COPY會引發觸發器，因此如果使用觸發器方法就可以正常地使用它。

規則方法的另一個缺點是如果一組規則沒有覆蓋被插入的資料，則該資料將被插入到主表中而不會發出任何錯誤。

分割槽也可以使用一個UNION ALL檢視來組織。例如：

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

但是，如果要增加或者刪除單獨的分割槽，就需要重新地建立檢視。在實踐中，相對於使用繼承，這種方法很少被推薦。

警告
下面的警告適用於分割槽表:
.沒有自動的方法來驗證所有的CHECK約束是互斥的。建立程式碼來生成分割槽並建立或修改相關物件比手工寫命令要更安全。

.這裡展示的模式都假設分割槽的關鍵列從不改變，或者是其改變不足以導致它被移到另一個分割槽。一個嘗試將行移到另一個分割槽的UPDATE會失敗，因為CHECK約束的存在。如果我們需要處理這類情況，我們可以在分割槽表上放置合適的更新觸發器，但是它會使得結構的管理更加複雜。

.如果我們在使用手工的VACUUM或ANALYZE命令，別忘了需要在每一個分割槽上都執行一次。以下的命令：
ANALYZE measurement;
只會處理主表。

.帶有ON CONFLICT子句的INSERT 語句不太可能按照預期的方式工作，因為ON CONFLICT動作 只有在指定的目標關係（而非它的子關係）上有唯一違背的情況下才會被採用。

下面的警告適用於約束排除：
.只有在查詢的WHERE子句包含常量（或者外部提供的引數）時，約束排除才會起效。例如，一個與非不變函式（例如CURRENT_TIMESTAMP）的比較不能被最佳化，因為規劃器不知道該函式的值在執行時會落到哪個分割槽內。

.保持分割槽約束簡單，否則規劃器可能沒有辦法驗證無需訪問的分割槽。按前面的例子所示，為列表分割槽使用簡單相等條件或者為範圍分割槽使用簡單範圍測試。一個好的經驗法則是分割槽約束應該只包含使用B-tree索引運算子的比較，比較的雙方應該是分割槽列和常量。

.在約束排除期間，主表所有的分割槽上的所有約束都會被檢查，所以大量的分割槽將會顯著地增加查詢規劃時間。使用這些技術的分割槽在大約最多100個分割槽的情況下工作得很好，但是不要嘗試使用成千個分割槽。