一、簡介
在數據庫日漸龐大的今天,為了方便對數據庫數據的管理,比如按時間,按地區去統計一些數據時,基數過于龐大,多有不便。很多商業數據庫都提供分區的概念,按不同的維度去存放數據,便于后期的管理,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 創建主表
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 = #
3.2 創建分區表
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 = #
3.3 分區鍵上建索引
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 = #
對于開發人員來說,希望數據庫是透明的,只管 insert into tbl_partition。對于數據插向哪個分區,則希望由DB決定。這點,ORACLE實現了,但是PG不行,需要前期人工處理下。
3.4 創建觸發器函數
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 = #
說明: 如果不想丟失數據,上面的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 查看所有表
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 = #
4.2 查看主表
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 = #
4.3 查看分區表
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 = #
五、測試
5.1 插入數據
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 = #
5.2 查看主表數據
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 = #
5.3 查看分區表數據
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 = #
六、管理分區
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);
同時,需要修改觸發器函數,將插入條件改成相應的值。
說明: 創建觸發器函數時,最好把插入條件寫更未來一點,比如多寫十年,這樣以后增加新分區時就不需要重新創建觸發器函數了,也可以避免一些不必要的錯誤。
另外,還可以如下增加新的分區:
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;
七、約束排除
約束排除(Constraint exclusion)是一種查詢優化技巧,它改進了用上面方法定義的表分區的性能。
確保postgresql.conf 里的配置參數constraint_exclusion 是打開的。沒有這個參數,查詢不會按照需要進行優化。這里我們需要做的是確保該選項在配置文件中沒有被注釋掉。
如果沒有約束排除,查詢會掃描tbl_partition 表中的每一個分區。打開了約束排除之后,規劃器將檢查每個分區的約束然后再試圖證明該分區不需要被掃描,因為它不能包含任何符合WHERE子句條件的數據行。如果規劃器可以證明這個,它就把該分區從查詢規劃里排除出去。
可以使用EXPLAIN 命令顯示一個規劃在constraint_exclusion 關閉和打開情況下的不同:
7.1 約束排除關閉
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 = #
從上面的查詢計劃中可以看出,PostgreSQL 掃描了所有分區。下面我們再看一下打開約束排除之后的查詢計劃: ?
7.2 約束排除開啟
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 = #
可以看到,PostgreSQL 只掃描四月份以后的分區表。
八、可選的分區方式
還可以通過建立規則的方式進行分區。
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. * );
九、注意事項
VACUUM?或 ANALYZE tbl_partition?只會對主表起作用,要想分析表,需要分別分析每個分區表。
十、參考資料
- PostgreSQL官方說明: 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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
