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