這篇文章將為大家詳細講解有關PostgreSQL中怎么利用表繼承實現分區表，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
概述
分區指的是將邏輯上一的一個大表分成多個小的物理上的片(子表)，分區可以提供以下好處:
.在某些情況下查詢性能能夠顯著提升，特別是當那些訪問壓力大的行在一個分區或者少數幾個分區時。分區可以取代索引的主導列、減小索引尺寸以及使索引中訪問壓力大的部分更有可能被放在內存中。
.當查詢或更新訪問一個分區的大部分行時，可以通過該分區上的一個順序掃描來取代分散到整個表上的索引和隨機訪問，這樣可以改善性能。
.如果需求計劃使用劃分設計，可以通過增加或移除分區來完成批量載入和刪除。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
這可以非?？斓貏h除百萬級別的記錄，因為它不需要逐一地刪除記錄。
另一個經常使用的選項是將分區從被劃分的表中移除，但是把它作為一個獨立的表保留下來：
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個分區的情況下工作得很好，但是不要嘗試使用成千個分區。
關于PostgreSQL中怎么利用表繼承實現分區表就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
