5.9. 分區 · PostgreSQL 中文文檔 9.3

# 5.9\. 分區 PostgreSQL支持基本的表分區功能。本節描述為什么需要表分區以及如何在數據庫設計中使用表分區。 ## 5.9.1\. 概述分區的意思是把邏輯上的一個大表分割成物理上的幾塊。分區可以提供若干好處： * 某些類型的查詢性能可以得到極大提升。特別是表中訪問率較高的行位于一個單獨分區或少數幾個分區上的情況下。分區可以減少索引體積從而可以將高使用率部分的索引存放在內存中。如果索引不能全部放在內存中，那么在索引上的讀和寫都會產生更多的磁盤訪問。 * 當查詢或更新一個分區的大部分記錄時，連續掃描那個分區而不是使用索引離散的訪問整個表可以獲得巨大的性能提升。 * 如果需要大量加載或者刪除的記錄位于單獨的分區上，那么可以通過直接讀取或刪除那個分區以獲得巨大的性能提升，因為`ALTER TABLE NO INHERIT`和`DROP TABLE` 比操作大量的數據要快的多。這些命令同時還可以避免由于大量`DELETE` 導致的`VACUUM`超載。 * 很少用的數據可以移動到便宜一些的慢速存儲介質上。這種好處通常只有在表可能會變得非常大的情況下才有價值。到底多大的表會從分區中收益取決于具體的應用，不過有個基本的拇指規則就是表的大小超過了數據庫服務器的物理內存大小。目前，PostgreSQL支持通過表繼承進行分區。每個分區必須做為單獨一個父表的子表進行創建。父表自身通常是空的，它的存在只是為了代表整個數據集。你在試圖實現分區之前，應該先熟悉繼承(參閱[Section 5.8](#calibre_link-1041))。 PostgreSQL可以實現下面形式的分區：范圍分區表被一個或者多個關鍵字段分區成"范圍"，這些范圍在不同的分區里沒有重疊。比如，我們可以通過時間范圍分區，或者根據特定業務對象的標識符范圍分區。列表分區表通過明確地列出每個分區里應該出現哪些關鍵字值實現。 ## 5.9.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](#calibre_link-1531)是打開的。沒有這個參數，查詢不會按照需要進行優化。比如，假設我們為一個巨大的冰激凌公司構造數據庫。該公司每天都測量最高溫度，以及每個地區的冰激凌銷售。概念上，我們需要一個這樣的表： ``` 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`約束。當這個函數比單月的情況更復雜時，它不需要經常的更新，因為分支可以在需要之前被添加。 > **Note:** 在實踐中，如果大部分插入該分區，它可能最好首先檢查最新分區。為簡單起見，我們已經在這個例子中的其他部分表明在同一順序下的觸發器的測試。我們可以看出，一個復雜的分區方案可能要求相當多的 DDL 。在上面的例子里我們需要每個月創建一次新分區，因此寫一個腳本自動生成需要的 DDL 是明智的。 ## 5.9.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; ``` ## 5.9.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](#calibre_link-1531)缺省(和建議）設置事實上不是`on` 也不是`off`，但是中間設置調用`partition`，導致很可能要工作在分區表上的技術只適用于查詢。`on` 設置導致規劃器在所有的查詢里檢查`CHECK`限制，即使是不可能受益的最簡單的限制。 ## 5.9.5\. 替代分區方法用一個不同的途徑去重新定向插入適當的分區表是在主表中建立規則，而不是觸發器，例如： ``` CREATE RULE measurement_insert_y2006m02 AS ON 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 AS ON 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_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; ``` 然而，增加和刪除各個分區的數據集，需要重新創建視圖，增加一個額外的步驟。在實際中這個方法跟使用繼承相比較幾乎沒有可取之處。 ## 5.9.6\. 警告下面的注意事項適合于已分區的表： * 沒有辦法自動驗證所有的`CHECK`約束是互斥的。創建代碼比每條用手生成分區和創建和/或修改關聯的對象寫更安全。 * 這里顯示的模式假設分區內一行的主字段永遠不變，或者至少不變足夠要求它移到另一個分區。一個`UPDATE`嘗試由于`CHECK`的約束將會失敗。如果您需要處理這種情況，您可以在分區表內放入合適的更新觸發器，但是它會使管理結構更加復雜。 * 如果您正在使用`VACUUM`手冊或者`ANALYZE`命令，不要忘記您需要在每個分區上分別運行他們，就像這樣的命令： ``` ANALYZE measurement; ``` 將只會處理主表。下面的注意事項適合于約束排除： * 約束排除只是在查詢的`WHERE`子句包含常量（或者外部提供的參數）的時候才生效。例如，一個非不可變的函數的比較，如`CURRENT_TIMESTAMP` 不能被優化，因為在運行時規劃器不知道該參數會選擇哪個分區。 * 保持分區約束的簡單性，否則規劃器可能不能證明分區不需要被訪問。為列表分區使用簡單平等的約束，或為范圍分區使用簡單的范圍測試，就像前面的例子說明。一個好的拇指規則是分區約束應該只包含分區字段和可添加B-tree索引的操作符使用的常量的比較。 * 主表的所有分區的所有約束在約束排除中被審查，所以大量的分區將大大增加查詢規劃時間。分區使用這些技術或許可以將分區提升到一百個且能很好的工作；不要試圖使用成千上萬的分區。