pg分区表学习

表继承 t父表，a子表
postgres=# create table t (name text,age int,sex boolean);
CREATE TABLE
postgres=# create table a (no int) inherits (t);
CREATE TABLE

postgres=# insert into a values(‘a’,15,true,1);
INSERT 0 1
postgres=# insert into a values(‘b’,16,false,2);
INSERT 0 1

postgres=# select * from a;
name | age | sex | no
------±----±----±—
a | 15 | t | 1
b | 16 | f | 2
(2 rows)
postgres=# select * from t;
name | age | sex
------±----±----
a | 15 | t
b | 16 | f
(2 rows)

postgres=# update a set age=13 where name=‘a’;
UPDATE 1
postgres=# select * from t;
name | age | sex
------±----±----
b | 16 | f
a | 13 | t
(2 rows)

postgres=# insert into t values(‘c’,30,true);
INSERT 0 1
postgres=# select * from a;
name | age | sex | no
------±----±----±—
b | 16 | f | 2
a | 13 | t | 1
(2 rows)

当查询父表会把父表中子表的数据也查出来，反之则不行
如果只想查询父表的数据需要加only关键字

select * from only t;
一个子表可以有多个父表继承，这样他将会拥有所有父表字段的总和，如果同一个字段出现在多个父表中，在子表中会融合，融合字段会拥有父字段的所有检查约束，并且父字段为非空，子字段的融合后字段也为非空，dml(update,delete)访问父表，也会操作子表，alter修改父表，vacuum，reindex不会影响到子表，唯一约束外键使用不会扩大到子表。

postgres=# update t set age=15 where name=‘b’;
UPDATE 1
postgres=# select * from a;
name | age | sex | no
------±----±----±—
a | 13 | t | 1
b | 15 | f | 2

postgres=# alter table a no inherit t;

分区表
建立主表
create table s (id int not null,
price numeric(12,2),
amount int not null,
sale_date date not null,
buyer varchar(40),
buyer_contact text);
按销售日期分区，按月分区
create table sales_t1 (check (sale_date >=DATE ‘2019-01-01’ and sale_date < DATE ‘2019-02-01’)) inherits (s);
create table sales_t2 (check (sale_date >=DATE ‘2019-02-01’ and sale_date < DATE ‘2019-03-01’)) inherits (s);
create table sales_t3 (check (sale_date >=DATE ‘2019-03-01’ and sale_date < DATE ‘2019-04-01’)) inherits (s);

父表不存数据，仅作为删除分区使用，每个分区只允许插入本月的数据
分区键建索引示例
create index sale_date_t1 on sales_t1 (sale_date);
create index sale_date_t2 on sales_t2 (sale_date);
create index sale_date_t3 on sales_t3 (sale_date);

如果在父表插入数据，需要触发器同步到正确的子表
create or replace function sale_insert_trigger()
returns trigger as $$
begin
if (new.sale_date >= date ‘2019-01-01’ and new.sale_date < date ‘2019-02-01’) then
insert into sales_t1 values(new.);
elsif (new.sale_date >= date ‘2019-02-01’ and new.sale_date < date ‘2019-03-01’) then
insert into sales_t2 values(new.);
elsif (new.sale_date >= date ‘2019-03-01’ and new.sale_date < date ‘2019-04-01’) then
insert into sales_t3 values(new.*);
else
raise exception ‘date out of range. fix the sale_insert_trigger() function!’;
end if;
return null;
end;
$$
LANGUAGE PLPGSQL;

create trigger insert_sale_trigger before insert on s for each row execute procedure sale_insert_trigger();

postgres=# insert into s values(1,33.12,1,date ‘2019-01-02’,‘asdf’,‘yyyyy’);
INSERT 0 0
postgres=# select * from s;
id | price | amount | sale_date | buyer | buyer_contact
----±------±-------±-----------±------±--------------
1 | 33.12 | 1 | 2019-01-02 | asdf | yyyyy
(1 row)

postgres=# select * from sales_t1;
id | price | amount | sale_date | buyer | buyer_contact
----±------±-------±-----------±------±--------------
1 | 33.12 | 1 | 2019-01-02 | asdf | yyyyy
(1 row)

postgres=# insert into s values(1,33.12,1,date ‘2019-05-02’,‘asdf’,‘yyyyy’);
ERROR: date out of range. fix the sale_insert_trigger() function!
CONTEXT: PL/pgSQL function sale_insert_trigger() line 10 at RAISE
postgres=#
postgres=#
postgres=#
postgres=# drop table sales_t1;
DROP TABLE
postgres=# insert into s values(1,33.12,1,date ‘2019-03-02’,‘asdf’,‘yyyyy’);
INSERT 0 0

删除历史表1月，不会导致触发器失效

确定参数constraint_exclusion是否被禁止，若配置为off会影响分区表查询性能。
该参数有三个值：
 on：所有表都通过约束优化查询
 off：所有表都不通过约束优化查询
 partition：只对继承表（父表）和UNION ALL子查询通过检索约束来优化查询
该参数控制扫描分区表时是否扫描所有分区，当配置为OFF时会扫描所有分区，而配置为ON或partition时，优化器会根据子表上的约束判断检索哪些子表，而不用扫描所有子表，一般建议配置为partition，配置为on会消耗大量的性能。

触发器方式相比于rule方式的优点在于copy操作导数据会触发触发器，如果插入数据在规则之外则会插入进父表，而触发器会报错。

在 PostgreSQL 10 中，分区上的索引需要基于各个分区手动创建，而不能基于分区的父表创建索引。PostgreSQL 11 可以基于分区表创建索引。分区表上的索引并不会创建一个物理上的索引，而是为每个分区上的索引创建一个模板。
postgres=# CREATE TABLE measurement (
postgres(# city_id int not null,
postgres(# logdate date not null,
postgres(# peaktemp int,
postgres(# unitsales int
postgres(# ) PARTITION BY RANGE (logdate);
CREATE TABLE

CREATE TABLE measurement_y2018 PARTITION OF measurement
FOR VALUES FROM (‘2018-01-01’) TO (‘2019-01-01’);
CREATE TABLE measurement_y2019 PARTITION OF measurement
FOR VALUES FROM (‘2019-01-01’) TO (‘2020-01-01’);

CREATE INDEX idx_measurement_peaktemp ON measurement(peaktemp);

postgres=# \d measurement
Partitioned table “public.measurement”
Column | Type | Collation | Nullable | Default
-----------±--------±----------±---------±--------
city_id | integer | | not null |
logdate | date | | not null |
peaktemp | integer | | |
unitsales | integer | | |
Partition key: RANGE (logdate)
Indexes:
“idx_measurement_peaktemp” btree (peaktemp)
Number of partitions: 2 (Use \d+ to list them.)

postgres=# \d measurement_y2018
Table “public.measurement_y2018”
Column | Type | Collation | Nullable | Default
-----------±--------±----------±---------±--------
city_id | integer | | not null |
logdate | date | | not null |
peaktemp | integer | | |
unitsales | integer | | |
Partition of: measurement FOR VALUES FROM (‘2018-01-01’) TO (‘2019-01-01’)
Indexes:
“measurement_y2018_peaktemp_idx” btree (peaktemp)
自动创建的索引不能单独删除，可以通过分区表统一删除。

从Postgresql 10开始，推出了内置分区表，不在需要通过触发器加约束的方式来实现分区表，大大减少了维护成本，在10版本中仅支持范围分区和列表分区，Postgresql 10支持多级分区，针对大的分区表可以进行优化。

Postgresql 11版本继续针对分区表进行了大量的优化，包括支持了hash分区，索引功能的增强，DML的改进等，使的在11版本中，分区表有了更高的可用性。在Postgresql 11之前，不能插入超过分区范围的值，在11中，推出了默认分区来保存除分区表外的其它值，避免在进行insert时出错

postgres=# create table part_11(id int,info text,crt_time timestamp without time zone) partition by range(crt_time);
postgres=# create table part_11_201907 partition of part_11(crt_time) for values from (‘2019-07-01’) to (‘2019-08-01’);
postgres=# create table part_11_201908 partition of part_11(crt_time) for values from (‘2019-08-01’) to (‘2019-09-01’);
postgres=# create table part_11_201909 partition of part_11(crt_time) for values from (‘2019-09-01’) to (‘2019-10-01’);

postgres=# create table part_11_default partition of part_11(crt_time) default;

postgres=# insert into part_11 values (4,‘aa’,‘2019-02-02’);
INSERT 0 1
postgres=# select * from part_11_default;
id | info | crt_time
----±-----±--------------------
4 | aa | 2019-02-02 00:00:00
(1 row)

但是默认分区会出现一个问题，若有默认分区时，若新添加的分区范围中的值在默认分区中存在，则该分区不能被创建。
postgres=# create table part_11_201902 partition of part_11(crt_time) for values from (‘2019-02-01’) to (‘2019-03-01’);
ERROR: updated partition constraint for default partition “part_11_default” would be violated by some row

如果想让新添加的分区范围中的值在默认分区中存在，需要解绑默认分区，然后添加新分区
postgres=# ALTER TABLE part_11 DETACH PARTITION part_11_default;
ALTER TABLE
postgres=# create table part_11_201902 partition of part_11(crt_time) for values from (‘2019-02-01’) to (‘2019-03-01’);
CREATE TABLE
postgres=# insert into part_11 select * from part_11_default;
INSERT 0 1
postgres=# truncate table part_11_default;
TRUNCATE TABLE
postgres=# ALTER TABLE part_11 ATTACH PARTITION part_11_default DEFAULT;
ALTER TABLE

pg_pathman
在Postgresql 10版本之前，由于不支持内置分区表，采用触发器加约束的方式来进行创建分区表步骤较为繁琐，而且由于存在触发器的原因，会对语句的性能造成一定的影响。所以针对以上的问题，postgrespro公司开发了pg_pathman作为Postgresql 10之前版本的分区表的替代，简化了分区表的操作。

PG_PATHMAN安装
通过https://github.com/postgrespro/pg_pathman/releases下载PG_PATHMAN，解压后即可安装完成，通过以下步骤配置即可。
$ cd pg_pathman
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ cd /pgdata/pg115
$ vi postgresql.conf
shared_preload_libraries = ‘pg_pathman’ #需要重启数据库使配置生效
$ psql
postgres=# create extension pg_pathman;
CREATE EXTENSION

关于pg_pathman这里不做说明。