postgresql----表分區

標籤：

--下面的描述不記得在哪裡抄來的了？！

表分區就是把邏輯上一個大表分割成物理上的多個小塊，表分區可提供如下若干好處：

1.某些類型的查詢效能可以得到極大提升。

2.更新的效能可以得到提升，因為表的每塊索引要比整個資料集上的索引要小，如果索引不能全部放在記憶體裡，那麼在索引上的讀寫都會產生磁碟訪問。

3.大量刪除可以用簡單的刪除某個分區

4.將很少使用的資料移動到便宜的慢一些的儲存介質上。

樣本1. 

1.建立主表

create table tbl_inherits_test(    a int,    b timestamp without time zone);create index idx_tbl_inherits_test_b on tbl_inherits_test using btree (b);

 

2.建立觸發器函數，在INSERT父表時根據時間欄位b寫入時間b的分表，如果分表b不存在，則建立分表b，然後再INSERT分表

create or replace function f_insert_tbl_inherits_test() returns trigger as$body$declare tablename varchar(32) default ‘‘;begin    tablename=‘tbl_inherits_test_‘||to_char(NEW.b,‘YYYY_MM_DD‘);       execute ‘insert into ‘||tablename||‘(a,b) values(‘||NEW.a||‘,‘‘‘||NEW.b||‘‘‘)‘;

    return null;

    EXCEPTION        when undefined_table then        execute ‘create table ‘||tablename||‘() inherits (tbl_inherits_test)‘;        execute ‘create index idx_‘||tablename||‘_b on ‘||tablename||‘ using btree(b)‘;        execute ‘insert into ‘||tablename||‘(a,b) values(‘||NEW.a||‘,‘‘‘||NEW.b||‘‘‘)‘;    return null;    end;$body$language plpgsql; 

 

3.建立觸發器，當INSERT主表時執行觸發器函數

create trigger trg_insert_tbl_inherits_test before insert on tbl_inherits_test for each row execute procedure f_insert_tbl_inherits_test();

 

4.向主表寫資料驗證結果

test=#insert into tbl_inherits_test(a,b) values(1,‘2016-06-20 17:40:21‘);test=# \d+ tbl_inherits_test                             Table "public.tbl_inherits_test" Column |            Type             | Modifiers | Storage | Stats target | Description --------+-----------------------------+-----------+---------+--------------+------------- a      | integer                     |           | plain   |              |  b      | timestamp without time zone |           | plain   |              | Indexes:    "idx_tbl_inherits_test_b" btree (b)Triggers:    trg_insert_tbl_inherits_test BEFORE INSERT ON tbl_inherits_test FOR EACH ROW EXECUTE PROCEDURE f_insert_tbl_inherits_test()Child tables: tbl_inherits_test_2016_06_20

5.結果顯示INSERT主表時會根據INSERT的資料b（2016-06-20 17:40:21）自動建立一個分表tbl_inherits_test_2016_06_20，再寫入幾條資料，查看結果

test=# insert into tbl_inherits_test(a,b) values (2,‘2016-06-20 08:08:08‘),(3,‘2016-06-21 19:00:00‘);INSERT 0 0test=# \d+ tbl_inherits_test                            Table "public.tbl_inherits_test" Column |            Type             | Modifiers | Storage | Stats target | Description --------+-----------------------------+-----------+---------+--------------+------------- a      | integer                     |           | plain   |              |  b      | timestamp without time zone |           | plain   |              | Indexes:    "idx_tbl_inherits_test_b" btree (b)Triggers:    trg_insert_tbl_inherits_test BEFORE INSERT ON tbl_inherits_test FOR EACH ROW EXECUTE PROCEDURE f_insert_tbl_inherits_test()Child tables: tbl_inherits_test_2016_06_20,              tbl_inherits_test_2016_06_21

 

6.分別查詢主表和分表的資料，直接查詢主表會查詢到所有分表的資料，但是使用only查詢主表發現，主表中並沒有資料（因為觸發器函數中返回的是null）

test=# select * from tbl_inherits_test_2016_06_20 ; a |          b          ---+--------------------- 1 | 2016-06-20 17:40:21 2 | 2016-06-20 08:08:08(2 rows)test=# select * from tbl_inherits_test_2016_06_21 ; a |          b          ---+--------------------- 3 | 2016-06-21 19:00:00(1 row)test=# test=# select * from tbl_inherits_test ; a |          b          ---+--------------------- 1 | 2016-06-20 17:40:21 2 | 2016-06-20 08:08:08 3 | 2016-06-21 19:00:00(3 rows)test=# select * from only tbl_inherits_test ; a | b ---+---(0 rows)

 

postgresql----表分區