PostgreSQL分區介紹

標籤：

PostgreSQL支援基本的表資料分割函數。本文描述為什麼需要表分區以及如何在資料庫設計中使用表分區。

1. 概述

分區的意思是把邏輯上的一個大表分割成物理上的幾塊。分區可以提供若干好處：

• 某些類型的查詢效能可以得到極大提升。特別是表中訪問率較高的行位於一個單獨分區或少數幾個分區上的情況下。分區可以減少索引體積從而可以將高使用率部分的索引存放在記憶體中。如果索引不能全部放在記憶體中，那麼在索引上的讀和寫都會產生更多的磁碟訪問。

• 當查詢或更新一個分區的大部分記錄時，連續掃描那個分區而不是使用索引離散的訪問整個表可以獲得巨大的效能提升。

• 如果需要大量載入或者刪除的記錄位於單獨的分區上，那麼可以通過直接讀取或刪除那個分區以獲得巨大的效能提升，因為ALTER TABLE NO INHERIT和DROP TABLE 比操作大量的資料要快的多。這些命令同時還可以避免由於大量DELETE 導致的VACUUM超載。

• 很少用的資料可以移動到便宜一些的慢速儲存介質上。

這種好處通常只有在表可能會變得非常大的情況下才有價值。到底多大的表會從分區中收益取決於具體的應用，不過有個基本的拇指規則就是表的大小超過了資料庫伺服器的實體記憶體大小。

目前，PostgreSQL支援通過表繼承進行分區。每個分區必須做為單獨一個父表的子表進行建立。父表自身通常是空的，它的存在只是為了代表整個資料集。

PostgreSQL可以實現下面形式的分區：

定界分割

表被一個或者多個關鍵字段分區成"範圍"，這些範圍在不同的分區裡沒有重疊。比如，我們可以通過時間範圍分區，或者根據特定業務對象的標識符定界分割。

列表分區

表通過明確地列出每個分區裡應該出現哪些關鍵字值實現。

2. 實現分區

要設定一個分區的表，做下面的步驟：

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);

我們知道大多數查詢都只會訪問最後一周，最後一個月或者最後一個季度的資料，因為這個表的主要用途是為管理準備線上報告。為了減少需要儲存的舊資料，我們決定值保留最近三年的有用資料。在每個月的開頭，我們都會刪除最舊的一個月的資料。

在這種情況下，我們可以使用分區來協助實現所有對錶的不同需求。下面的步驟描述了上面的需求，分區可以這樣設定：

 

1. 主表是measurement表，就像上面那樣聲明。

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. 我們必須提供非重疊的資料表條件約束。而不是只像上面那樣建立分區表，所以我們的建表指令碼就變成：

   CREATE TABLE measurement_y2006m02 (    CHECK ( logdate >= DATE ‘2006-02-01‘ AND logdate < DATE ‘2006-03-01‘ )) INHERITS (measurement);CREATE TABLE measurement_y2006m03 (    CHECK ( logdate >= DATE ‘2006-03-01‘ AND logdate < DATE ‘2006-04-01‘ )) INHERITS (measurement);...CREATE TABLE measurement_y2007m11 (    CHECK ( logdate >= DATE ‘2007-11-01‘ AND logdate < DATE ‘2007-12-01‘ )) INHERITS (measurement);CREATE TABLE measurement_y2007m12 (    CHECK ( logdate >= DATE ‘2007-12-01‘ AND logdate < DATE ‘2008-01-01‘ )) INHERITS (measurement);CREATE TABLE measurement_y2008m01 (    CHECK ( logdate >= DATE ‘2008-01-01‘ AND logdate < DATE ‘2008-02-01‘ )) INHERITS (measurement);

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();

   我們必須每月重新定義觸發器，以便它總是指向當前分區。然而，觸發定義不需要更新。

   我們可能想插入資料並且想讓伺服器自動定位應該向哪個分區插入資料。我們可以用下面這個複雜的觸發器來實現這個目標，比如：

   CREATE OR REPLACE FUNCTION measurement_insert_trigger()RETURNS TRIGGER AS $$BEGIN    IF ( NEW.logdate >= DATE ‘2006-02-01‘ AND         NEW.logdate < DATE ‘2006-03-01‘ ) THEN        INSERT INTO measurement_y2006m02 VALUES (NEW.*);    ELSIF ( NEW.logdate >= DATE ‘2006-03-01‘ AND            NEW.logdate < DATE ‘2006-04-01‘ ) THEN        INSERT INTO measurement_y2006m03 VALUES (NEW.*);    ...    ELSIF ( NEW.logdate >= DATE ‘2008-01-01‘ AND            NEW.logdate < DATE ‘2008-02-01‘ ) THEN        INSERT INTO measurement_y2008m01 VALUES (NEW.*);    ELSE        RAISE EXCEPTION ‘Date out of range.  Fix the measurement_insert_trigger() function!‘;    END IF;    RETURN NULL;END;$$LANGUAGE plpgsql;

   每一個觸發器跟以前一樣。注意，每一個IF測試必須匹配其分區的 CHECK約束。

   當這個函數比單月的情況更複雜時，它不需要經常的更新，因為分支可以在需要之前被添加。

   注意: 在實踐中，如果大部分插入該分區，它可能最好首先檢查最新分區。為簡單起見，我們已經在這個例子中的其他部分表明在同一順序下的觸發器的測試。

我們可以看出，一個複雜的資料分割配置可能要求相當多的 DDL 。在上面的例子裡我們需要每個月建立一次新分區，因此寫一個指令碼自動產生需要的 DDL 是明智的。

3. 管理分區

通常分區集在定義表的時候就已經確定了，但我們常常需要周期性的刪除舊分區並添加新分區。分區最重要的好處是它能恰到好處的適應這個需求：以極快的速度操作分區的結構，而不是痛苦的物理移動大量資料。

刪除舊資料最簡單的方法是刪除不再需要的分區：

DROP TABLE measurement_y2006m02;

這個命令可以迅速刪除數包含數百萬條記錄的分區，因為它不需要單獨刪除每一條記錄。

還可以在刪除分區的同時保留其作為一個表訪問的能力：

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

這將允許將來對這些資料執行其它的操作(比如使用COPY,pg_dump 之類的工具進行備份)。並且此時也是執行其它資料操作(資料彙總或運行報表等)的有利時機。

同樣，我們可以像前面建立最初的分區一樣，建立一個新的空分區來處理新資料。

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

有時在分區結構之外建立新表並在一段時間之後將其變為分區更為方便。因為這將允許在該表變為分區之前對其中的資料進行載入、檢查、轉換之類的操作。

CREATE TABLE measurement_y2008m02  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02   CHECK ( logdate >= DATE ‘2008-02-01‘ AND logdate < DATE ‘2008-03-01‘ );\copy measurement_y2008m02 from ‘measurement_y2008m02‘--其它可能的資料準備工作ALTER TABLE measurement_y2008m02 INHERIT measurement;

4. 分區和約束排除

約束排除是一種查詢最佳化技巧，它改進了用上述方法定義的表分區的效能。比如：

SET constraint_exclusion = on;SELECT count(*) FROM measurement WHERE logdate >= DATE ‘2008-01-01‘;

如果沒有約束排除，上面的查詢會掃描measurement表中的每一個分區。開啟了約束排除之後，規劃器將檢查每個分區的約束然後試圖證明該分區不需要被掃描 (因為它不能包含任何符合WHERE子句條件的資料行)。如果規劃器可以證明這個，它就把該分區從查詢規劃裡排除出去。

你可以使用EXPLAIN命令顯示一個規劃在constraint_exclusion 開啟和關閉情況下的不同。一個為這種類型的表設定的典型的非最佳的規劃是：

SET constraint_exclusion = off;EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE ‘2008-01-01‘;                                          QUERY PLAN----------------------------------------------------------------------------------------------- Aggregate  (cost=158.66..158.68 rows=1 width=0)   ->  Append  (cost=0.00..151.88 rows=2715 width=0)         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)...         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)

部分或者全部分區可能會使用索引掃描而不是全表掃描，不過這裡要表達的意思是沒有必要掃描舊分區就可以回答這個查詢。在開啟約束排除之後，我們可以得到產生同樣回答的明顯簡化的規劃：

SET constraint_exclusion = on;EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE ‘2008-01-01‘;                                          QUERY PLAN----------------------------------------------------------------------------------------------- Aggregate  (cost=63.47..63.48 rows=1 width=0)   ->  Append  (cost=0.00..60.75 rows=1086 width=0)         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)               Filter: (logdate >= ‘2008-01-01‘::date)

請注意，約束排除只由CHECK約束驅動，而不會由索引驅動。因此，在關鍵字欄位上定義索引是沒有必要的。在給出的分區上是否需要建立索引取決於那些掃描該分區的查詢通常是掃描該分區的一大部分還是只是一小部分。對於後者，索引通常都有協助，對於前者則沒有什麼好處。

constraint_exclusion預設(和建議）設定事實上不是on 也不是off，但是中間設定調用partition，導致很可能要工作在分區表上的技術只適用於查詢。on 設定導致規劃器在所有的查詢裡檢查CHECK限制，即使是不可能受益的最簡單的限制。

5. 替代分區方法

用一個不同的途徑去重新定向插入適當的分區表是在主表中建立規則，而不是觸發器，例如：

CREATE RULE measurement_insert_y2006m02 ASON INSERT TO measurement WHERE    ( logdate >= DATE ‘2006-02-01‘ AND logdate < DATE ‘2006-03-01‘ )DO INSTEAD    INSERT INTO measurement_y2006m02 VALUES (NEW.*);...CREATE RULE measurement_insert_y2008m01 ASON INSERT TO measurement WHERE    ( logdate >= DATE ‘2008-01-01‘ AND logdate < DATE ‘2008-02-01‘ )DO INSTEAD    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

規則比觸發器有顯著的開銷，但是這個開銷是每檢查一次支付一次而不是每行支付一次，所以這種方法可能在批量插入的情況下有優勢。然而在更多的情況下，觸發器的方法更好。

請注意COPY會忽略規則。如果您想用COPY插入資料，您將需要複製分區表而不是主表。COPY觸發觸發器，如果您用觸發器的方法就可以正常使用。

另一個規則方法缺點是如果規則設定沒有覆蓋插入資料，那麼沒有簡單的路徑強制錯誤，資料將會悄悄代替主表中的資料。

安排分區也可以用UNION ALL視圖，而不是表繼承。例如，

CREATE VIEW measurement AS          SELECT * FROM measurement_y2006m02UNION ALL SELECT * FROM measurement_y2006m03...UNION ALL SELECT * FROM measurement_y2007m11UNION ALL SELECT * FROM measurement_y2007m12UNION ALL SELECT * FROM measurement_y2008m01;

然而，增加和刪除各個分區的資料集，需要重新建立視圖，增加一個額外的步驟。在實際中這個方法跟使用繼承相比較幾乎沒有可取之處。

6. 警告

下面的注意事項適合於已分區的表：

• 沒有辦法自動驗證所有的CHECK約束是互斥的。建立代碼比每條用手產生分區和建立和/或修改關聯的對象寫更安全。

• 這裡顯示的模式假設分區內一行的主欄位永遠不變，或者至少不變足夠要求它移到另一個分區。一個UPDATE嘗試由於CHECK的約束將會失敗。如果您需要處理這種情況，您可以在分區表內放入合適的更新觸發器，但是它會使管理結構更加複雜。

• 如果您正在使用VACUUM手冊或者ANALYZE命令，不要忘記您需要在每個分區上分別運行他們，就像這樣的命令：

  ANALYZE measurement;

  將只會處理主表。

下面的注意事項適合於約束排除：

• 約束排除只是在查詢的WHERE子句包含常量（或者外部提供的參數）的時候才生效。例如，一個非不可變的函數的比較，如CURRENT_TIMESTAMP 不能被最佳化，因為在運行時規劃器不知道該參數會選擇哪個分區。

• 保持分區約束的簡單性，否則規劃器可能不能證明分區不需要被訪問。為列表分區使用簡單平等的約束，或為定界分割使用簡單的範圍測試，就像前面的例子說明。一個好的拇指規則是分區約束應該只包含分區欄位和可添加B-tree索引的操作符使用的常量的比較。

• 主表的所有分區的所有約束在約束排除中被審查，所以大量的分區將大大增加查詢規劃時間。分區使用這些技術或許可以將分區提升到一百個且能很好的工作；不要試圖使用成千上萬的分區。

 

PostgreSQL分區介紹