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 0

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,

              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)