postgresql的主分表

postgresql的主分表

1. 支持
   
   • 范围分区　每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。比如某个日期范围分区2001-2003,2004-2006
   • 列表分区　分区表显示列出其所包含的key值
2. 步骤,例子是按照年份来进行分表的操作
   
   • 创建主表。不用为该表定义任何检查限制，除非需要将该限制应用到所有的分区表中。同样也无需为该表创建任何索引和唯一限制。

create table master( 
    date_key date, account integer
     );

• 创建多个分区表。每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列

create table master_children_2015 () inherits (master);
create table master_children_2016 () inherits (master);
create table master_children_2017 () inherits (master);

• 为分区表添加限制。这些限制决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠

alter table master_children_2015  add constraint master_children_2015_date_key check('2015-1-1'::date<=date_key and date_key<'2015-12-31'::date);
alter table master_children_2016  add constraint master_children_2016_date_key check('2016-1-1'::date<=date_key and date_key<'2016-12-31'::date);
alter table master_children_2017  add constraint master_children_2017_date_key check('2017-1-1'::date<=date_key and date_key<'2017-12-31'::date);

• 为每一个分区表，在主要的列上创建索引.//可创建可不创建

create index master_children_date_key_2015 on master_children_2015(date_key);
create index master_children_date_key_2016 on master_children_2016(date_key);
create index master_children_date_key_2017 on master_children_2017(date_key);

-创建分区函数 tigger 触发器
（1）NEW
数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。

 CREATE OR REPLACE FUNCTION master_date_trigger()
     RETURNS TRIGGER as $mc_date$
     BEGIN
      IF DATE '2015-1-1 0:0:0'<= NEW.date_key and NEW.date_key< DATE '2015-12-31 0:0:'
     THEN

     insert into master_children_2015 values(new.*);
     ELSIF DATE '2016-1-1 0:0:0'<= NEW.date_key and NEW.date_key< DATE '2016-12-31 0:0:'
     THEN
     insert into master_children_2016 values(new.*);
     ELSIF DATE '2017-1-1 0:0:0'<= NEW.date_key and NEW.date_key< DATE '2017-12-31 0:0:'
     THEN
     insert into master_children_2017 values(new.*);
     END IF;
     RETURN NULL;
     END;
     $mc_date$
     LANGUAGE plpgsql;

• execute procedure//执行程序 挂载分区trigger

create trigger insert_master_date_trigger
before insert on master
for each row execute procedure master_date_trigger();

• 到这边其实已经创建好了
  但是我们会遇到一些情况,比如说上面的例子我们插入了2018年的数据,那么这条数据就不会被插入,毫无踪迹了在数据库中,所以由一种方法是将方法变为动态创建分区表
  下面的是动态创建分区表,一般来讲不建议使用,因为每次插入数据的时候都需要去走判断是否生成新的表的逻辑,开销大

CREATE OR REPLACE FUNCTION master_date_trigger()
RETURNS TRIGGER AS $$
DECLARE date_text TEXT;
DECLARE date_year TEXT;
DECLARE date_fyear TEXT;
DECLARE date_lyear TEXT;
DECLARE insert_statement TEXT;
BEGIN
SELECT to_char(NEW.date_key, 'YYYY_MM_DD') INTO date_text;
select to_char(new.date_key,'YYYY') into date_year;
select to_char(new.date_key,'YYYY-1-1') into date_fyear;
select to_char(new.date_key,'YYYY-12-31') into date_lyear;
insert_statement := 'INSERT INTO master_children_'
|| date_year
||' VALUES ($1.*)';
EXECUTE insert_statement USING NEW;
RETURN NULL;
EXCEPTION
WHEN UNDEFINED_TABLE
THEN
EXECUTE
'CREATE TABLE IF NOT EXISTS master_children_'
|| date_year
|| '(CHECK (date_key >= DATE'''
|| date_fyear
||'''and date_key < DATE'''
|| date_lyear
|| ''')) INHERITS (master)';
RAISE NOTICE 'CREATE NON-EXISTANT TABLE master_children_%', date_year;
EXECUTE
'CREATE INDEX master_date_key_'
|| date_year
|| ' ON master_children_'
|| date_year
|| '(date_key)';
EXECUTE insert_statement USING NEW;
RETURN NULL;
END;
$$
LANGUAGE plpgsql

• 移除分区表

DROP TABLE master_children_2017_8_9;//分区表被删除,不建议

• 解除其与主表的继承关系,但该方法并未释放磁盘。此时可通过更改该分区表，使其属于其它TABLESPACE，同时将该TABLESPACE的目录设置为其它磁盘分区上的目录，从而释放主表所在的磁盘。同时，如果之后还需要再次使用该“过期”数据，只需更改该分区表，使其再次与主表形成继承关系

ALTER TABLE master_children_2017_8_9 NO INHERIT master;
CREATE TABLESPACE cheap_table_space LOCATION '/data/cheap_disk';
master_children_2017_8_9 ALTER TABLE SET TABLESPACE cheap_table_space;

• 使用rule来创建规则,这是另一种写法,相比较触发器,Rule会带来更大的额外开销，但每个请求只造成一次开销而非每条数据都引入一次开销，所以该方法对大批量的数据插入操作更具优势。然而，实际上在绝大部分场景下，Trigger比Rule的效率更高
  同时，COPY操作会忽略Rule，而可以正常触发Trigger

CREATE RULE master_rule_2017 AS
ON INSERT TO master
WHERE
date_key >= DATE '2017-1-1' and date_key <'2017-12-31'
DO INSTEAD
INSERT INTO master_rule_2017 VALUES (NEW.*);