PostgreSQL分區表（Table Partitioning）應用 - David_Tang - 博客園 · postgresql手冊

隨筆-102? 文章-0? 評論-83? # [PostgreSQL分區表（Table Partitioning）應用](http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html) **一、簡介** 　　在數據庫日漸龐大的今天，為了方便對數據庫數據的管理，比如按時間，按地區去統計一些數據時，基數過于龐大，多有不便。很多商業數據庫都提供分區的概念，按不同的維度去存放數據，便于后期的管理，PostgreSQL也不例外。　　PostgresSQL分區的意思是把邏輯上的一個大表分割成物理上的幾塊兒。分區不僅能帶來訪問速度的提升，關鍵的是，它能帶來管理和維護上的方便。　　分區的具體好處是： - 某些類型的查詢性能可以得到極大提升。 - 更新的性能也可以得到提升，因為表的每塊的索引要比在整個數據集上的索引要小。如果索引不能全部放在內存里，那么在索引上的讀和寫都會產生更多的磁盤訪問。 - 批量刪除可以用簡單的刪除某個分區來實現。 - 可以將很少用的數據移動到便宜的、轉速慢的存儲介質上。　　在PG里表分區是通過表繼承來實現的，一般都是建立一個主表，里面是空，然后每個分區都去繼承它。無論何時，都應保證主表里面是空的。　　小表分區不實際，表在多大情況下才考慮分區呢？PostgresSQL官方給出的建議是：當表本身大小超過了機器物理內存的實際大小時（the size of the table should exceed the physical memory of the database server），可以考慮分區。　　PG目前（9.2.2）僅支持范圍分區和列表分區，尚未支持散列分區。 **二、環境** 系統環境：CentOS release 6.3 (Final) PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit **三、實現分區** 3.1 創建主表 [![](https://box.kancloud.cn/2015-10-30_5632e1c456b6e.gif)]( "復制代碼") ~~~ david=# create table tbl_partition ( david(# id integer, david(# name varchar(20), david(# gender boolean, david(# join_date date, david(# dept char(4)); CREATE TABLE david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c462b38.gif)]( "復制代碼") 3.2 創建分區表 [![](https://box.kancloud.cn/2015-10-30_5632e1c476472.gif)]( "復制代碼") ~~~ david=# create table tbl_partition_201211 ( check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# create table tbl_partition_201212 ( check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# create table tbl_partition_201301 ( check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# create table tbl_partition_201302 ( check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# create table tbl_partition_201303 ( check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# create table tbl_partition_201304 ( check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# create table tbl_partition_201305 ( check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' ) ) INHERITS (tbl_partition); CREATE TABLE david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c48732e.gif)]( "復制代碼") 3.3 分區鍵上建索引 [![](https://box.kancloud.cn/2015-10-30_5632e1c495187.gif)]( "復制代碼") ~~~ david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date); CREATE INDEX david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); CREATE INDEX david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date); CREATE INDEX david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); CREATE INDEX david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); CREATE INDEX david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); CREATE INDEX david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); CREATE INDEX david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c4a340c.gif)]( "復制代碼") 對于開發人員來說，希望數據庫是透明的，只管 insert into tbl_partition。對于數據插向哪個分區，則希望由DB決定。這點，ORACLE實現了，但是PG不行，需要前期人工處理下。 3.4 創建觸發器函數 [![](https://box.kancloud.cn/2015-10-30_5632e1c4b16ac.gif)]( "復制代碼") ~~~ david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.join_date >= DATE '2012-11-01' AND NEW.join_date < DATE '2012-12-01' ) THEN INSERT INTO tbl_partition_201211 VALUES (NEW.*); ELSIF ( NEW.join_date >= DATE '2012-12-01' AND NEW.join_date < DATE '2013-01-01' ) THEN INSERT INTO tbl_partition_201212 VALUES (NEW.*); ELSIF ( NEW.join_date >= DATE '2013-01-01' AND NEW.join_date < DATE '2013-02-01' ) THEN INSERT INTO tbl_partition_201301 VALUES (NEW.*); ELSIF ( NEW.join_date >= DATE '2013-02-01' AND NEW.join_date < DATE '2013-03-01' ) THEN INSERT INTO tbl_partition_201302 VALUES (NEW.*); ELSIF ( NEW.join_date >= DATE '2013-03-01' AND NEW.join_date < DATE '2013-04-01' ) THEN INSERT INTO tbl_partition_201303 VALUES (NEW.*); ELSIF ( NEW.join_date >= DATE '2013-04-01' AND NEW.join_date < DATE '2013-05-01' ) THEN INSERT INTO tbl_partition_201304 VALUES (NEW.*); ELSIF ( NEW.join_date >= DATE '2013-05-01' AND NEW.join_date < DATE '2013-06-01' ) THEN INSERT INTO tbl_partition_201305 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE FUNCTION david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c4be3c1.gif)]( "復制代碼") **說明：**如果不想丟失數據，上面的ELSE 條件可以改成?INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同時需要創建一張結構和tbl_partition 一樣的表tbl_partition_error_join_date，這樣，錯誤的join_date 數據就可以插入到這張表中而不是報錯了。 3.5 創建觸發器 ~~~ david=# CREATE TRIGGER insert_tbl_partition_trigger david-# BEFORE INSERT ON tbl_partition david-# FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger(); CREATE TRIGGER david=# ~~~ **四、查看表** 4.1 查看所有表 [![](https://box.kancloud.cn/2015-10-30_5632e1c5d03f1.gif)]( "復制代碼") ~~~ david=# \dt List of relations Schema | Name | Type | Owner --------+----------------------+-------+---------- public | tbl_partition | table | postgres public | tbl_partition_201211 | table | postgres public | tbl_partition_201212 | table | postgres public | tbl_partition_201301 | table | postgres public | tbl_partition_201302 | table | postgres public | tbl_partition_201303 | table | postgres public | tbl_partition_201304 | table | postgres public | tbl_partition_201305 | table | postgres (8 rows) david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c5dfec1.gif)]( "復制代碼") 4.2 查看主表 [![](https://box.kancloud.cn/2015-10-30_5632e1c5ecfe1.gif)]( "復制代碼") ~~~ david=# \d tbl_partition Table "public.tbl_partition" Column | Type | Modifiers -----------+-----------------------+----------- id | integer | name | character varying(20) | gender | boolean | join_date | date | dept | character(4) | Triggers: insert_tbl_partition_trigger BEFORE INSERT ON tbl_partition FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger() Number of child tables: 7 (Use \d+ to list them.) david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c60b9a5.gif)]( "復制代碼") 4.3 查看分區表 [![](https://box.kancloud.cn/2015-10-30_5632e1c650c82.gif)]( "復制代碼") ~~~ david=# \d tbl_partition_201304 Table "public.tbl_partition_201304" Column | Type | Modifiers -----------+-----------------------+----------- id | integer | name | character varying(20) | gender | boolean | join_date | date | dept | character(4) | Indexes: "tbl_partition_201304_joindate" btree (join_date) Check constraints: "tbl_partition_201304_join_date_check" CHECK (join_date >= '2013-04-01'::date AND join_date < '2013-05-01'::date) Inherits: tbl_partition david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c6602f8.gif)]( "復制代碼") **五、測試** 5.1 插入數據 [![](https://box.kancloud.cn/2015-10-30_5632e1c66cfeb.gif)]( "復制代碼") ~~~ david=# insert into tbl_partition values (1, 'David', '1', '2013-01-10', 'TS'); INSERT 0 0 david=# insert into tbl_partition values (2, 'Sandy', '0', '2013-02-10', 'TS'); INSERT 0 0 david=# insert into tbl_partition values (3, 'Eagle', '1', '2012-11-01', 'TS'); INSERT 0 0 david=# insert into tbl_partition values (4, 'Miles', '1', '2012-12-15', 'SD'); INSERT 0 0 david=# insert into tbl_partition values (5, 'Simon', '1', '2012-12-10', 'SD'); INSERT 0 0 david=# insert into tbl_partition values (6, 'Rock', '1', '2012-11-10', 'SD'); INSERT 0 0 david=# insert into tbl_partition values (7, 'Peter', '1', '2013-01-11', 'SD'); INSERT 0 0 david=# insert into tbl_partition values (8, 'Sally', '0', '2013-03-10', 'BCSC'); INSERT 0 0 david=# insert into tbl_partition values (9, 'Carrie', '0', '2013-04-02', 'BCSC'); INSERT 0 0 david=# insert into tbl_partition values (10, 'Lee', '1', '2013-01-05', 'BMC'); INSERT 0 0 david=# insert into tbl_partition values (11, 'Nicole', '0', '2012-11-10', 'PROJ'); INSERT 0 0 david=# insert into tbl_partition values (12, 'Renee', '0', '2013-01-10', 'TS'); INSERT 0 0 david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c67a903.gif)]( "復制代碼") 5.2 查看主表數據 [![](https://box.kancloud.cn/2015-10-30_5632e1c68c991.gif)]( "復制代碼") ~~~ david=# select * from tbl_partition; id | name | gender | join_date | dept ----+--------+--------+------------+------ 3 | Eagle | t | 2012-11-01 | TS 6 | Rock | t | 2012-11-10 | SD 11 | Nicole | f | 2012-11-10 | PROJ 4 | Miles | t | 2012-12-15 | SD 5 | Simon | t | 2012-12-10 | SD 1 | David | t | 2013-01-10 | TS 7 | Peter | t | 2013-01-11 | SD 10 | Lee | t | 2013-01-05 | BMC 12 | Renee | f | 2013-01-10 | TS 2 | Sandy | f | 2013-02-10 | TS 8 | Sally | f | 2013-03-10 | BCSC 9 | Carrie | f | 2013-04-02 | BCSC (12 rows) david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c69a0cb.gif)]( "復制代碼") 5.3 查看分區表數據 [![](https://box.kancloud.cn/2015-10-30_5632e1c6a8f8b.gif)]( "復制代碼") ~~~ david=# select * from tbl_partition_201301 ; id | name | gender | join_date | dept ----+-------+--------+------------+------ 1 | David | t | 2013-01-10 | TS 7 | Peter | t | 2013-01-11 | SD 10 | Lee | t | 2013-01-05 | BMC 12 | Renee | f | 2013-01-10 | TS (4 rows) david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c6b5446.gif)]( "復制代碼") **六、管理分區** 6.1 移除數據/分區實現分區表之后，我們就可以很容易地移除不再使用的舊數據了，最簡單的方法就是： ~~~ david=# drop table tbl_partition_201304; ~~~ 這樣可以快速移除大量數據，而不是逐條刪除數據。另一個推薦做法是將分區從分區表中移除，但是保留訪問權限。 ~~~ david=# alter table tbl_partition_201304 no inherit tbl_partition; ALTER TABLE david=# ~~~ 和直接DROP 相比，該方式僅僅是使子表脫離了原有的主表，而存儲在子表中的數據仍然可以得到訪問，因為此時該表已經被還原成一個普通的數據表了。這樣對于數據庫的DBA來說，就可以在此時對該表進行必要的維護操作，如數據清理、歸檔等，在完成諸多例行性的操作之后，就可以考慮是直接刪除該表(DROP TABLE)，還是先清空該表的數據(TRUNCATE TABLE)，之后再讓該表重新繼承主表。 ~~~ david=# alter table tbl_partition_201304 inherit tbl_partition; ALTER TABLE david=# ~~~ 6.2 增加分區我們可以像之前那樣增加一個分區 ~~~ david=# create table tbl_partition_201306 ( check ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' ) ) INHERITS (tbl_partition); ~~~ ~~~ david=# create index tbl_partition_201306_joindate on tbl_partition_201306 (join_date); ~~~ 同時，需要修改觸發器函數，將插入條件改成相應的值。 **說明：**創建觸發器函數時，最好把插入條件寫更未來一點，比如多寫十年，這樣以后增加新分區時就不需要重新創建觸發器函數了，也可以避免一些不必要的錯誤。另外，還可以如下增加新的分區： [![](https://box.kancloud.cn/2015-10-30_5632e1c7c1fdf.gif)]( "復制代碼") ~~~ david=# create table tbl_partition_201307 david-# (LIKE tbl_partition INCLUDING DEFAULTS INCLUDING CONSTRAINTS); CREATE TABLE david=# david=# alter table tbl_partition_201307 add constraint tbl_partition_201307_join_date_check david-# check ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' ); ALTER TABLE david=# david=# create index tbl_partition_201307_joindate on tbl_partition_201307 (join_date);david=# copy tbl_partition_201307 from '/tmp/tbl_partition_201307.sql'; //從文件中拷貝數據，這些數據可以是事前準備的 david=# alter table tbl_partition_201307 inherit tbl_partition; ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c7d3ade.gif)]( "復制代碼") **七、約束排除** 約束排除（Constraint exclusion）是一種查詢優化技巧，它改進了用上面方法定義的表分區的性能。確保postgresql.conf 里的配置參數constraint_exclusion 是打開的。沒有這個參數，查詢不會按照需要進行優化。這里我們需要做的是確保該選項在配置文件中沒有被注釋掉。 ![](https://box.kancloud.cn/2015-10-30_5632e1c7e1b70.jpg) 如果沒有約束排除，查詢會掃描tbl_partition 表中的每一個分區。打開了約束排除之后，規劃器將檢查每個分區的約束然后再試圖證明該分區不需要被掃描，因為它不能包含任何符合WHERE子句條件的數據行。如果規劃器可以證明這個，它就把該分區從查詢規劃里排除出去。可以使用EXPLAIN 命令顯示一個規劃在constraint_exclusion 關閉和打開情況下的不同： 7.1 約束排除關閉 [![](https://box.kancloud.cn/2015-10-30_5632e1c8000a9.gif)]( "復制代碼") ~~~ david=# set constraint_exclusion = off; SET david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01'; QUERY PLAN ------------------------------------------------------------------------------------------------- Aggregate (cost=172.80..172.81 rows=1 width=0) -> Append (cost=0.00..167.62 rows=2071 width=0) -> Seq Scan on tbl_partition (cost=0.00..0.00 rows=1 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201211 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201212 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201301 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201302 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201303 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201305 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201304 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201306 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201307 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) (22 rows) david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c80dfb3.gif)]( "復制代碼") 從上面的查詢計劃中可以看出，PostgreSQL 掃描了所有分區。下面我們再看一下打開約束排除之后的查詢計劃：? 7.2 約束排除開啟 [![](https://box.kancloud.cn/2015-10-30_5632e1c81cf31.gif)]( "復制代碼") ~~~ david=# set constraint_exclusion = on; SET david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01'; QUERY PLAN ------------------------------------------------------------------------------------------------- Aggregate (cost=76.80..76.81 rows=1 width=0) -> Append (cost=0.00..74.50 rows=921 width=0) -> Seq Scan on tbl_partition (cost=0.00..0.00 rows=1 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201305 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201304 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201306 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) -> Seq Scan on tbl_partition_201307 tbl_partition (cost=0.00..18.62 rows=230 width=0) Filter: (join_date >= '2013-04-01'::date) (12 rows) david=# ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c82cd66.gif)]( "復制代碼") 可以看到，PostgreSQL 只掃描四月份以后的分區表。 **八、可選的分區方式** 還可以通過建立規則的方式進行分區。 [![](https://box.kancloud.cn/2015-10-30_5632e1c83bc09.gif)]( "復制代碼") ~~~ CREATE RULE insert_tbl_partition_201211 AS ON INSERT TO tbl_partition WHERE ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' ) DO INSTEAD INSERT INTO tbl_partition_201211 VALUES (NEW.*); CREATE RULE insert_tbl_partition_201212 AS ON INSERT TO tbl_partition WHERE ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' ) DO INSTEAD INSERT INTO tbl_partition_201212 VALUES (NEW.*); ... CREATE RULE insert_tbl_partition_201306 AS ON INSERT TO tbl_partition WHERE ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' ) DO INSTEAD INSERT INTO tbl_partition_201306 VALUES (NEW.*); CREATE RULE insert_tbl_partition_201307 AS ON INSERT TO tbl_partition WHERE ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' ) DO INSTEAD INSERT INTO tbl_partition_201307 VALUES (NEW.*); CREATE RULE insert_tbl_partition_error_join_date AS ON INSERT TO tbl_partition WHERE ( join_date >= DATE '2013-08-01' OR join_date < DATE '2012-11-01' ) DO INSTEAD INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); ~~~ [![](https://box.kancloud.cn/2015-10-30_5632e1c84b796.gif)]( "復制代碼") **九、注意事項** VACUUM?或 ANALYZE tbl_partition?只會對主表起作用，要想分析表，需要分別分析每個分區表。 **十、參考資料** - PostgreSQL官方說明：[http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html](http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html) - ITEYE：http://diegoball.iteye.com/blog/713826 - kenyon(君羊)：http://my.oschina.net/Kenyon/blog/59455 分類: [Postgresql](http://www.cnblogs.com/mchina/category/381458.html) 標簽: [postgresql](http://www.cnblogs.com/mchina/tag/postgresql/), [分區表](http://www.cnblogs.com/mchina/tag/分區表/), [Table Partitioning](http://www.cnblogs.com/mchina/tag/Table Partitioning/) 綠色通道： [好文要頂]()[關注我]()[收藏該文]()[與我聯系](http://space.cnblogs.com/msg/send/David_Tang)[![](https://box.kancloud.cn/2015-10-30_5632e1cb91723.png)]( "分享至新浪微博") [![](https://box.kancloud.cn/2015-10-30_5632e1cba029f.jpg)](http://home.cnblogs.com/u/mchina/) [David_Tang](http://home.cnblogs.com/u/mchina/) [關注 - 1](http://home.cnblogs.com/u/mchina/followees) [粉絲 - 116](http://home.cnblogs.com/u/mchina/followers) [+加關注]() 4 0 (請您對文章做出評價) [? ](http://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html) 上一篇：[PostgreSQL的generate_series函數應用](http://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html "發布于2013-04-03 14:50") [? ](http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html) 下一篇：[PostgreSQL 序列（SEQUENCE）](http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html "發布于2013-04-10 15:52") posted @ 2013-04-09 11:14[David_Tang](http://www.cnblogs.com/mchina/) 閱讀(653) 評論(2) [編輯](http://www.cnblogs.com/mchina/admin/EditPosts.aspx?postid=2973427)[收藏](#) ![](https://box.kancloud.cn/2015-10-30_5632e1cbae75b.jpg) Copyright ?2013 David_Tang