PostgreSQL分區表建立

標籤：

pgsql 分區表：--主表create table test(id integer, name varchar(32));create index idx_test_id on test using btree(id);--分表create table test_b (like test including constraints including defaults including indexes) inherits(test);create table test_c (like test including constraints including defaults including indexes) inherits(test);alter table test_b add constraint con_test_c check(id >=1001 and id <= 2000);alter table test_c add constraint con_test_b check(id >=2001 and id <= 3000);create table test_d( check(id >=3001 and id <= 4000))inherits (test);--插資料insert into test_b select generate_series(1001,2000),‘bbb‘;insert into test_c select generate_series(2001,3000),‘ccc‘;insert into test_d select generate_series(3001,4000),‘ddd‘;--分析表，加入計劃analyze test_a;analyze test_b;analyze test_c;analyze test_d;--測試查詢計劃 constraint_exclusionset  constraint_exclusion = off/partition;show constraint_exclusion;explain select * from test where id = 1;--測試Insertinsert into test values (11,‘aaaaa‘);select * from test where name = ‘aaaaa‘select * from test_a;insert into test values(1,‘a‘),(1111,‘v‘),(2222,‘vv‘);select * from test_c;--建立觸發器函數create function tb_partition_insert()returns trigger  as$$beginif (id >=1 and id <= 1000) then    insert into test_a values(NEW.*);elseif (id >=1001 and id <= 2000) then    insert into test_b values(NEW.*);  elseif (id >=2001 and id <= 3000) then   insert into test_c values (new.*);   else    raise exception ‘Date out of range. Fix the tbl_partition_insert_trigger() function!‘; end if ;return null; end$$language plpgsql;--觸發器create trigger insert_test_parition    before insert on test    for each row execute procedure tb_partition_insert();--測試Insertinsert into test values(1,‘a‘),(1111,‘v‘),(2222,‘vv‘);select pg_size_pretty(pg_relation_size(‘test‘));insert into test select generate_series(1,1000),‘aa‘;select count(1) from test_a;select pg_size_pretty(pg_relation_size(‘test‘));select pg_table_size(‘test‘)

 

參考：

為主：http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html
為輔：http://my.oschina.net/Kenyon/blog/59455 ，

         http://www.postgres.cn/docs/9.4/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION
例子參考：http://francs3.blog.163.com/blog/static/4057672720112422436937/
結合：http://www.cnblogs.com/stephen-liu74/archive/2012/04/27/2291814.html
查看錶大小等操作：http://www.cnblogs.com/liuyuanyuanGOGO/p/3224554.html
分區表用到了繼承，在對錶進行清除資料操作時，要注意是否會同時清除父表和字表 http://francs3.blog.163.com/blog/static/40576727201011203725668/

PostgreSQL分區表建立