一.表继承
表继承是pg中特有的东西.继承表拥有父表的所有字段,下面有一个a1表如下:
test=# create table a1(id int,name varchar(20));
CREATE TABLE
创建一个a2表继承a1表,使用inherits关键字.表继承可以继承多个表,那么子表将拥有父表的所有字段,是所有父表的融合.继承会继承父表的约束,并且如果某个父表的字段存在非空约束,那么融合后的字段也要是非空.但是不会融合索引
test=# create table a2(age int) inherits(a1);
CREATE TABLE
test=# \d a2
Table "test.a2"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
age | integer | | |
Inherits: a1
test=# \d t1
Table "test.t1"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | |
name | character varying(10) | | |
ostime | timestamp without time zone | | |
test=# \d t3
Table "test.t3"
Column | Type | Collation | Nullable | Default
--------+-------+-----------+----------+---------
image | bytea | | |
test=# create table t5(flag int) inherits(t1,t3);
CREATE TABLE
test=# \d t5
Table "test.t5"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | |
name | character varying(10) | | |
ostime | timestamp without time zone | | |
image | bytea | | |
flag | integer | | |
Inherits: t1,
t3
下面是表继承特性测试:
1.如果在父表中插入修改数据,那么这些数据只有父表可以查询到,子表无法查询
test=# insert into a1 values(1,'brent');
INSERT 0 1
test=# select * from a1;
id | name
----+-------
1 | brent
(1 row)
test=# select * from a2;
id | name | age
----+------+-----
(0 rows)
2.如果在子表中插入修改数据,那么这些数据在父表和子表都能查询到
test=# insert into a2 values(2,'bob',19);
INSERT 0 1
test=# select * from a1;
id | name
----+-------
1 | brent
2 | bob
(2 rows)
test=# select * from a2;
id | name | age
----+------+-----
2 | bob | 19
(1 row)
3.如果在父表中只想查询父表的数据,需要添加only关键字
test=# select * from only a1;
id | name
----+-------
1 | brent
4.如果删除父表的数据,那么子表和父表的数据都会被删除,如果删除子表的数据,则不会删除父表的数据
一个子表可以继承多个父表.
二.分区表
pg中的分区表是使用继承来实现的.在使用继承实现分区表的时候,一般会让父表为空,数据都存储于子表中.
创建分区表的步骤:
1.创建父表,所有的分区都从它继承.这个表中没有数据,不要在这个表上定义任何检查和约束,除非你想约束所有的分区,同样在其上定义任何索引和约束也没有任何意义.
2.创建几个子表,每个表都是从父表继承,通常这些表不会增加任何字段,我们把子表称为分区,实际上他们就是普通的pg表.
3.给分区表增加约束,定义每个分区允许的键值
4.对于每个分区,在关键字段上创建索引
5.定义一个规则或者触发器,把对父表的数据插入重定向到合适的分区表.
6.确保constraint_exclusion里的配置参数postgresql.conf是打开的,值为partition.打开后,如果查询中的where子句的过滤条件与分区 的约束条件匹配,那么这个查询会智能的查询这个分区,而不会查询其它分区.
注意,在pg10以前需要使用规则或者触发器来实现插入数据到子表.而到了10以后,不需要再手工创建规则或者触发器,即可实现,
下面是具体的实例(适用于pg10以前的版本):
(1)创建父表
create table prud(id int,name varchar(10),logdate date not null);
(2)创建子表
CREATE TABLE prud_2014 (CHECK ( logdate >= DATE '2014-01-01' AND logdate < DATE '2015-01-01' )) INHERITS (prud);
CREATE TABLE prud_2015 (CHECK ( logdate >= DATE '2015-01-01' AND logdate < DATE '2016-01-01' )) INHERITS (prud);
CREATE TABLE prud_2016 (CHECK ( logdate >= DATE '2016-01-01' AND logdate < DATE '2017-01-01' )) INHERITS (prud);
CREATE TABLE prud_2017 (CHECK ( logdate >= DATE '2017-01-01' AND logdate < DATE '2018-01-01' )) INHERITS (prud);
CREATE TABLE prud_2018 (CHECK ( logdate >= DATE '2018-01-01' AND logdate < DATE '2019-01-01' )) INHERITS (prud);
CREATE TABLE prud_2019 (CHECK ( logdate >= DATE '2019-01-01' AND logdate < DATE '2020-01-01' )) INHERITS (prud);
根据需要创建索引:
CREATE INDEX prud_2014_index ON prud_2014 (logdate);
CREATE INDEX prud_2015_index ON prud_2015 (logdate);
CREATE INDEX prud_2016_index ON prud_2016 (logdate);
CREATE INDEX prud_2017_index ON prud_2017 (logdate);
CREATE INDEX prud_2018_index ON prud_2018 (logdate);
CREATE INDEX prud_2019_index ON prud_2019 (logdate);
(3)创建函数和触发器
CREATE OR REPLACE FUNCTION prud_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2014-01-01' AND
NEW.logdate < DATE '2005-01-01' ) THEN
INSERT INTO prud_2014 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2015-01-01' AND
NEW.logdate < DATE '2016-01-01' ) THEN
INSERT INTO prud_2015 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2016-01-01' AND
NEW.logdate < DATE '2017-01-01' ) THEN
INSERT INTO prud_2016 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2017-01-01' AND
NEW.logdate < DATE '2018-01-01' ) THEN
INSERT INTO prud_2017 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2018-01-01' AND
NEW.logdate < DATE '2019-01-01' ) THEN
INSERT INTO prud_2018 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2019-01-01' AND
NEW.logdate < DATE '2020-01-01' ) THEN
INSERT INTO prud_2019 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
创建触发器:
create trigger insert_prud
before insert on prud
for each row execute procedure prud_insert_trigger();
(5)测试
test=# insert into prud values(1,'brent','2016-02-01');
INSERT 0 0
test=# select * from prud;
id | name | logdate
----+-------+------------
1 | brent | 2016-02-01
(1 row)
test=# select * from prud_2016;
id | name | logdate
----+-------+------------
1 | brent | 2016-02-01
如果插入超出分区的数据,则会抛出异常:
test=# insert into prud values(1,'brent','2020-02-01');
ERROR: Date out of range. Fix the measurement_insert_trigger() function!
CONTEXT: PL/pgSQL function prud_insert_trigger() line 22 at RAISE
查询执行计划,只扫描了一个分区:
test=# show constraint_exclusion;
constraint_exclusion
----------------------
partition
(1 row)
test=# explain select count(*) from prud where logdate>='2016-01-01' and logdate <'2017-01-01';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=84.93..84.94 rows=1 width=8)
-> Append (cost=0.00..84.83 rows=37 width=0)
-> Seq Scan on prud (cost=0.00..2.65 rows=1 width=0)
Filter: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2014 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2014_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2015 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2015_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2016 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2016_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2017 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2017_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2018 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2018_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2019 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2019_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
(28 rows)
test=# explain select count(*) from prud where logdate>='2016-01-01' and logdate <'2017-01-01';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=16.36..16.37 rows=1 width=8)
-> Append (cost=0.00..16.35 rows=7 width=0)
-> Seq Scan on prud (cost=0.00..2.65 rows=1 width=0)
Filter: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2016 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2016_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
如果将constraint_exclusion设置为off,则需要扫描所有的子表.
test=# set constraint_exclusion='off';
SET
test=# explain select count(*) from prud where logdate>='2016-01-01' and logdate <'2017-01-01';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=84.93..84.94 rows=1 width=8)
-> Append (cost=0.00..84.83 rows=37 width=0)
-> Seq Scan on prud (cost=0.00..2.65 rows=1 width=0)
Filter: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2014 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2014_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2015 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2015_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2016 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2016_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2017 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2017_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2018 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2018_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Heap Scan on prud_2019 (cost=4.21..13.70 rows=6 width=0)
Recheck Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
-> Bitmap Index Scan on prud_2019_index (cost=0.00..4.21 rows=6 width=0)
Index Cond: ((logdate >= '2016-01-01'::date) AND (logdate < '2017-01-01'::date))
(28 rows)
将子表从父表中剔除:
test=# alter table prud_2015 no inherit prud;
下面是适用于10以后的版本的创建方法,不再需要创建触发器或者规则,10支持range和list分区:
(1)创建父表,并且指定分区键
create table prud(id int,name varchar(10),logdate date not null) PARTITION BY RANGE (logdate);
(2)创建子表
CREATE TABLE prud_2014 partition of prud for values from ('2014-01-01') to ('2015-01-01');
CREATE TABLE prud_2015 partition of prud for values from ('2015-01-01') to ('2016-01-01');
CREATE TABLE prud_2016 partition of prud for values from ('2016-01-01') to ('2017-01-01');
CREATE TABLE prud_2017 partition of prud for values from ('2017-01-01') to ('2018-01-01');
CREATE TABLE prud_2018 partition of prud for values from ('2018-01-01') to ('2019-01-01');
CREATE TABLE prud_2019 partition of prud for values from ('2019-01-01') to ('2020-01-01');
根据需要创建索引:
CREATE INDEX prud_2014_index ON prud_2014 (logdate);
CREATE INDEX prud_2015_index ON prud_2015 (logdate);
CREATE INDEX prud_2016_index ON prud_2016 (logdate);
CREATE INDEX prud_2017_index ON prud_2017 (logdate);
CREATE INDEX prud_2018_index ON prud_2018 (logdate);
CREATE INDEX prud_2019_index ON prud_2019 (logdate);
(3)测试
test=# insert into prud values(1,'aa','2018-01-01');
INSERT 0 1
test=# select * from prud;
id | name | logdate
----+------+------------
1 | aa | 2018-01-01
(1 row)
test=# select * from prud_2018;
id | name | logdate
----+------+------------
1 | aa | 2018-01-01
(1 row)
如果插入不在子分区的值,会产生报错:
test=# insert into prud values(1,'aa','2013-01-01');
ERROR: no partition of relation "prud" found for row
DETAIL: Partition key of the failing row contains (logdate) = (2013-01-01).
分区表的常用操作,一般如果需要对分区进行操作,则直接对子表进行操作即可,例如添加索引,删除索引,添加约束等.如果需要删除一个分区的数据,直接truncate子表即可.
子表还可以创建子分区,语法如下:
test=# CREATE TABLE prud_2020 partition of prud for values from ('2020-01-01') to ('2021-01-01') PARTITION BY RANGE (id);
CREATE TABLE
test=# create table prud_2020_id1 partition of prud_2020 for values from (1) to (100);
CREATE TABLE
检查子表的数据:
test=# insert into prud values(10,'xxx','2020-01-10');
INSERT 0 1
test=# select * from prud;
id | name | logdate
----+------+------------
1 | aa | 2014-01-02
1 | aa | 2016-01-02
10 | xxx | 2020-01-10
(3 rows)
test=# select * from prud_2020;
id | name | logdate
----+------+------------
10 | xxx | 2020-01-10
(1 row)
test=# select * from prud_2020_id1;
id | name | logdate
----+------+------------
10 | xxx | 2020-01-10
如果需要将子表从父表中删除,但是不删除子表的数据,可以使用如下语法;
ALTER TABLE prud DETACH PARTITION prud_2020;
删除父表,使用
drop table prud cascade;
创建list分区语法如下:
create table prud(id int,name varchar(10),logdate date not null) PARTITION BY list (name);
CREATE TABLE prud_jack partition of prud for values in ('jack');
CREATE TABLE prud_bob partition of prud for values in ('bob');
CREATE TABLE prud_tom partition of prud for values in ('tom');