介绍
PostgreSQL分区表是逻辑上将一个大表通过表继承方式划分为若干个子表。PostgreSQL V10.0版本之前仅支持表继承方式分区表，V10版本之后支持声明式分区，PostgreSQL支持通过表继承来进行划分。每一个分区被创建为父表的一个子表。父表本身通常是空的，它的存在仅仅为了表示整个数据集。
声明式与继承式的区别：
- 对声明式分区来说，分区必须具有和分区表正好相同的列集合，而在表继承中，子表可以有父表中没有出现过的额外列。
- 表继承允许多继承。
- 声明式分区仅支持范围、列表以及哈希分区，而表继承允许数据按照用户的选择来划分（不过注意，如果约束排除不能有效地剪枝子表，查询性能可能会很差）。
- 在使用声明式分区时，一些操作比使用表继承时要求更长的持锁时间。例如，向分区表中增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁，而在常规继承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。
分区表有哪些优势：
- 在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
- 当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
- 如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
- 很少使用的数据可以被迁移到便宜且较慢的存储介质上。
什么时候使用表分区？
- PostgreSQL官方给出的建议：当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。
在PostgreSQL中划分分区的几种方式：
范围划分：表被根据一个关键列或一组列划分为"范围"，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。
列表划分：通过显式地列出每一个分区中出现的键值来划分表。
哈希分区：通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。
1 声明式分区
1.1 介绍
PostgreSQL V10新增声明式表分区，可以使用ATTACH PARTITION和DETACH PARTITION命令对分区进行管理。
个体分区在内部以继承的方式链接到分区表，不过无法对声明式分区表或其分区使用继承的某些一般特性。例如，分区不能有除其所属分区表之外的父表，一个常规表也不能从分区表继承使得后者成为其父表。这意味着分区表及其分区不会参与到与常规表的继承关系中。分区表及其分区组成的分区层次仍然是一种继承层次。
声明式分区表特性：
- 父表上的所有检查约束和非空约束都将自动被它的后代所继承，除非显式地指定了NO INHERIT子句。其他类型的约束（唯一、主键和外键约束）则不会被继承。
- 分区表的CHECK约束和NOT NULL约束总是会被其所有的分区所继承。不允许在分区表上创建标记为NO INHERIT的CHECK约束。
- 只要分区表中不存在分区，则支持使用ONLY仅在分区表上增加或者删除约束。一旦分区存在，那样做就会导致错误，因为当分区存在时是不支持仅在分区表上增加或删除约束的。不过，分区表本身上的约束可以被增加（如果它们不出现在父表中）和删除。
- 由于分区表并不直接拥有任何数据，尝试在分区表上使用TRUNCATE ONLY将总是返回错误。
- 分区不能有在父表中不存在的列。在使用CREATE TABLE创建分区时不能指定列，在事后使用ALTER TABLE时也不能为分区增加列。只有当表的列正好匹配父表时，才能使用ALTER TABLE ... ATTACH PARTITION将它作为分区加入。
- 如果NOT NULL约束在父表中存在，那么就不能删除分区的列上的对应的NOT NULL约束。
分区表有下列限制：
- 没有办法创建跨越所有分区的排除约束，只可能单个约束每个叶子分区。
- 分区表上的惟一约束（也就是主键）必须包括所有分区键列。存在此限制是因为PostgreSQL只能每个分区中分别强制实施唯一性。
- BEFORE ROW 触发器无法更改哪个分区是新行的最终目标。
- 不允许在同一个分区树中混杂临时关系和持久关系。因此，如果分区表是持久的，则其分区也必须是持久的，反之亦然。在使用临时关系时，分区数的所有成员都必须来自于同一个会话。
1.2 创建声明式分区
创建分区表并指定分区方式
create table log_history(id int not null,logdate date not null,num int) partition by range(logdate);
创建分区
create table log_history_2001 partition of log_history for values from ('2001-01-01') to ('2001-12-31');
create table log_history_2002 partition of log_history for values from ('2002-01-01') to ('2002-12-31');
create table log_history_2003 partition of log_history for values from ('2003-01-01') to ('2003-12-31');
create table log_history_2004 partition of log_history for values from ('2004-01-01') to ('2004-12-31');
create table log_history_2005 partition of log_history for values from ('2005-01-01') to ('2005-12-31');
create table log_history_2006 partition of log_history for values from ('2006-01-01') to ('2006-12-31');
create table log_history_2007 partition of log_history for values from ('2007-01-01') to ('2007-12-31');
create table log_history_2008 partition of log_history for values from ('2008-01-01') to ('2008-12-31');
create table log_history_2009 partition of log_history for values from ('2009-01-01') to ('2009-12-31');
create table log_history_2010 partition of log_history for values from ('2010-01-01') to ('2010-12-31');
create table log_history_2011 partition of log_history for values from ('2011-01-01') to ('2011-12-31');
create table log_history_2012 partition of log_history for values from ('2012-01-01') to ('2012-12-31');
create table log_history_2013 partition of log_history for values from ('2013-01-01') to ('2013-12-31');
create table log_history_2014 partition of log_history for values from ('2014-01-01') to ('2014-12-31');
create table log_history_2015 partition of log_history for values from ('2015-01-01') to ('2015-12-31');
create table log_history_2016 partition of log_history for values from ('2016-01-01') to ('2016-12-31');
create table log_history_2017 partition of log_history for values from ('2017-01-01') to ('2017-12-31');
create table log_history_2018 partition of log_history for values from ('2018-01-01') to ('2018-12-31');
create table log_history_2019 partition of log_history for values from ('2019-01-01') to ('2019-12-31');
create table log_history_2020 partition of log_history for values from ('2020-01-01') to ('2020-12-31');
创建分区并实现子分区，在创建分区的命令中指定partition by
create table log_history_2021 partition of log_history for values from ('2021-01-01') to ('2021-12-31') partition by range(logdate);
创建子分区
create table log_history_2021_01 partition of log_history_2021 for values from ('2021-01-01') to ('2021-03-31');
create table log_history_2021_02 partition of log_history_2021 for values from ('2021-04-01') to ('2021-06-30');
create table log_history_2021_03 partition of log_history_2021 for values from ('2021-07-01') to ('2021-08-31');
create table log_history_2021_04 partition of log_history_2021 for values from ('2021-09-01') to ('2021-12-31');
查看表定义查看分区
postgres=# \d+ log_history;
Partitioned table "public.log_history"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
num | integer | | | | plain | |
Partition key: RANGE (logdate)
Partitions: log_history_2001 FOR VALUES FROM ('2001-01-01') TO ('2001-12-31'),
log_history_2002 FOR VALUES FROM ('2002-01-01') TO ('2002-12-31'),
log_history_2003 FOR VALUES FROM ('2003-01-01') TO ('2003-12-31'),
log_history_2004 FOR VALUES FROM ('2004-01-01') TO ('2004-12-31'),
log_history_2005 FOR VALUES FROM ('2005-01-01') TO ('2005-12-31'),
log_history_2006 FOR VALUES FROM ('2006-01-01') TO ('2006-12-31'),
log_history_2007 FOR VALUES FROM ('2007-01-01') TO ('2007-12-31'),
log_history_2008 FOR VALUES FROM ('2008-01-01') TO ('2008-12-31'),
log_history_2009 FOR VALUES FROM ('2009-01-01') TO ('2009-12-31'),
log_history_2010 FOR VALUES FROM ('2010-01-01') TO ('2010-12-31'),
log_history_2011 FOR VALUES FROM ('2011-01-01') TO ('2011-12-31'),
log_history_2012 FOR VALUES FROM ('2012-01-01') TO ('2012-12-31'),
log_history_2013 FOR VALUES FROM ('2013-01-01') TO ('2013-12-31'),
log_history_2014 FOR VALUES FROM ('2014-01-01') TO ('2014-12-31'),
log_history_2015 FOR VALUES FROM ('2015-01-01') TO ('2015-12-31'),
log_history_2016 FOR VALUES FROM ('2016-01-01') TO ('2016-12-31'),
log_history_2017 FOR VALUES FROM ('2017-01-01') TO ('2017-12-31'),
log_history_2018 FOR VALUES FROM ('2018-01-01') TO ('2018-12-31'),
log_history_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-12-31'),
log_history_2020 FOR VALUES FROM ('2020-01-01') TO ('2020-12-31'),
log_history_2021 FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'), PARTITIONED
查看分区定义查看子分区
postgres=# \d+ log_history_2021;
Partitioned table "public.log_history_2021"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
num | integer | | | | plain | |
Partition of: log_history FOR VALUES FROM ('2021-01-01') TO ('2021-12-31')
Partition constraint: ((logdate IS NOT NULL) AND (logdate >= '2021-01-01'::date) AND (logdate < '2021-12-31'::date))
Partition key: RANGE (logdate)
Partitions: log_history_2021_01 FOR VALUES FROM ('2021-01-01') TO ('2021-03-31'),
log_history_2021_02 FOR VALUES FROM ('2021-04-01') TO ('2021-06-30'),
log_history_2021_03 FOR VALUES FROM ('2021-07-01') TO ('2021-08-31'),
log_history_2021_04 FOR VALUES FROM ('2021-09-01') TO ('2021-12-31')1.3 声明式分区维护
1.3.1 查看分区
查看有哪些分区表
postgres=# select partrelid::regclass,* from pg_partitioned_table;
partrelid | partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs
------------------+-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
log_history | 16399 | r | 1 | 0 | 2 | 3122 | 0 |
log_history_2001 | 16578 | r | 1 | 0 | 2 | 3122 | 0 |
log_history_2002 | 16577 | r | 1 | 0 | 2 | 3122 | 0 |
log_history_2003 | 16576 | r | 1 | 0 | 2 | 3122 | 0 |
log_history_2004 | 16575 | r | 1 | 0 | 2 | 3122 | 0 |
查看有哪些分区表（不查看子分区）
postgres=# \dP+
List of partitioned relations
Schema | Name | Owner | Type | Table | Total size | Description
--------+-------------+------------+-------------------+-------+------------+-------------
public | log_history | postgres12 | partitioned table | | 8192 bytes |
通过查看表定义查看分区
postgres=# \d+ log_history;
Partitioned table "public.log_history"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
num | integer | | | | plain | |
Partition key: RANGE (logdate)
Partitions: log_history_2001 FOR VALUES FROM ('2001-01-01') TO ('2001-12-31'),
log_history_2002 FOR VALUES FROM ('2002-01-01') TO ('2002-12-31'),
log_history_2003 FOR VALUES FROM ('2003-01-01') TO ('2003-12-31'),
log_history_2004 FOR VALUES FROM ('2004-01-01') TO ('2004-12-31'),
log_history_2005 FOR VALUES FROM ('2005-01-01') TO ('2005-12-31'),
log_history_2006 FOR VALUES FROM ('2006-01-01') TO ('2006-12-31'),
log_history_2007 FOR VALUES FROM ('2007-01-01') TO ('2007-12-31'),
log_history_2008 FOR VALUES FROM ('2008-01-01') TO ('2008-12-31'),
log_history_2009 FOR VALUES FROM ('2009-01-01') TO ('2009-12-31'),
log_history_2010 FOR VALUES FROM ('2010-01-01') TO ('2010-12-31'),
log_history_2011 FOR VALUES FROM ('2011-01-01') TO ('2011-12-31'),
log_history_2012 FOR VALUES FROM ('2012-01-01') TO ('2012-12-31'),
log_history_2013 FOR VALUES FROM ('2013-01-01') TO ('2013-12-31'),
log_history_2014 FOR VALUES FROM ('2014-01-01') TO ('2014-12-31'),
log_history_2015 FOR VALUES FROM ('2015-01-01') TO ('2015-12-31'),
log_history_2016 FOR VALUES FROM ('2016-01-01') TO ('2016-12-31'),
log_history_2017 FOR VALUES FROM ('2017-01-01') TO ('2017-12-31'),
log_history_2018 FOR VALUES FROM ('2018-01-01') TO ('2018-12-31'),
log_history_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-12-31'),
log_history_2020 FOR VALUES FROM ('2020-01-01') TO ('2020-12-31'),
log_history_2021 FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'), PARTITIONED1.3.2 删除分区
直接删除子表删除分区
drop table log_history_2001;提示：删除主表也会删除所包含的分区。
使用DETACH将分区从分区表中移除，成为一个独立的表
alter table log_history DETACH PARTITION log_history_2002;
drop table log_history_2002;1.3.3 新增分区
新增分区
create table log_history_2001 partition of log_history for values from ('2001-01-01') to ('2001-12-31');
使用ATTACH添加分区
#创建表
postgres=# create table log_history_2002(like log_history including defaults including constraints);
CREATE TABLE
#增加check约束
postgres=# alter table log_history_2002 add check (logdate >= date'2002-01-01' and logdate < date'2003-01-01');
ALTER TABLE
postgres=# \d+ log_history_2002;
Table "public.log_history_2002"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
num | integer | | | | plain | |
Check constraints:
"log_history_2002_logdate_check" CHECK (logdate >= '2002-01-01'::date AND logdate < '2003-01-01'::date)
Access method: heap
#ATTACH添加PARTITION
postgres=# alter table log_history ATTACH PARTITION log_history_2002 for values from ('2002-01-01') to ('2002-12-31');
ALTER TABLE1.4 enable_partition_pruning
enable_partition_pruning参数可以提升声明式分区表性能的查询优化技术。
select * from log_history where logdate<date'2010-09-11';如果启用enable_partition_pruning参数，上面的查询将会扫描log_history表的每一个分区。如果启用了此参数，规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询WHERE子句的行而无需扫描。当规划器可以证实这一点时，它会把分区从查询计划中排除。
启用enable_partition_pruning（默认启用此参数），查看执行计划，只扫描需要用到的分区
postgres=# set enable_partition_pruning = on;
SET
postgres=# explain select * from log_history where logdate<date'2010-09-11';
QUERY PLAN
--------------------------------------------------------------------------
Append (cost=0.00..389.00 rows=6800 width=12)
-> Seq Scan on log_history_2001 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2002 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2003 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2004 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2005 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2006 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2007 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2008 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2009 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2010 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
未启用enable_partition_pruning，查看执行计划，会扫描全部分区
postgres=# set enable_partition_pruning = off;
SET
postgres=# explain select * from log_history where logdate<date'2010-09-11';
QUERY PLAN
--------------------------------------------------------------------------
Append (cost=0.00..778.00 rows=13600 width=12)
-> Seq Scan on log_history_2001 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2002 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2003 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2004 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2005 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2006 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2007 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2008 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2009 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2010 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2011 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2012 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2013 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2014 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2015 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2016 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2017 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2018 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2019 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)
-> Seq Scan on log_history_2020 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2010-09-11'::date)2 继承式分区
2.1 介绍
声明式与继承式的区别：
- 对声明式分区来说，分区必须具有和分区表正好相同的列集合，而在表继承中，子表可以有父表中没有出现过的额外列。
- 表继承允许多继承。
- 声明式分区仅支持范围、列表以及哈希分区，而表继承允许数据按照用户的选择来划分（不过注意，如果约束排除不能有效地剪枝子表，查询性能可能会很差）。
- 在使用声明式分区时，一些操作比使用表继承时要求更长的持锁时间。例如，向分区表中增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁，而在常规继承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。
表继承的特性：
- 继承表自动继承父表的约束，非空约束。但是不自动继承uk、pk、fk、索引、存储参数等。
- 一个表可以同时继承多个父表，一个父表可以被多个子表继承。注意：一个表继承了多个主表的情况，共有字段上，所有的父表的约束包括not null的定义都必须继承过来（同样不包括pk、uk、fk等）。
- 查看主表默认情况下会连带查询所有的子表和自身。
2.2 创建继承式分区
创建主表，所有的子表都从主表继承，主表不包含数据，不在主表上定义任何检查约束。
create table log_history(id int not null,logdate date not null,num int);
创建子表
create table log_history_2011(check (logdate >= date'2011-01-01' and logdate < date'2012-01-01')) inherits(log_history);
create table log_history_2012(check (logdate >= date'2012-01-01' and logdate < date'2013-01-01')) inherits(log_history);
create table log_history_2013(check (logdate >= date'2013-01-01' and logdate < date'2014-01-01')) inherits(log_history);
create table log_history_2014(check (logdate >= date'2014-01-01' and logdate < date'2015-01-01')) inherits(log_history);
create table log_history_2015(check (logdate >= date'2015-01-01' and logdate < date'2016-01-01')) inherits(log_history);
create table log_history_2016(check (logdate >= date'2016-01-01' and logdate < date'2017-01-01')) inherits(log_history);
create table log_history_2017(check (logdate >= date'2017-01-01' and logdate < date'2018-01-01')) inherits(log_history);
create table log_history_2018(check (logdate >= date'2018-01-01' and logdate < date'2019-01-01')) inherits(log_history);
create table log_history_2019(check (logdate >= date'2019-01-01' and logdate < date'2020-01-01')) inherits(log_history);
create table log_history_2020(check (logdate >= date'2020-01-01' and logdate < date'2021-01-01')) inherits(log_history);
create table log_history_2021(check (logdate >= date'2021-01-01' and logdate < date'2022-01-01')) inherits(log_history);
查看继承分区
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2011 | log_history | 1
log_history_2012 | log_history | 1
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 1
分区表创建后，往主表插入数据并不会将数据重定向到合适的分区，因此需要创建合适的触发器函数来实现这一点。
postgres=# insert into log_history values(1,'2021-09-01',1);
INSERT 0 1
postgres=# select * from log_history;
id | logdate | num
----+------------+-----
1 | 2021-09-01 | 1
(1 row)
postgres=# select * from log_history_2021;
id | logdate | num
----+---------+-----
(0 rows)
创建函数
#vi log_history_insert_trigger.sql
CREATE OR REPLACE FUNCTION log_history_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2011-01-01' AND
NEW.logdate < DATE '2012-01-01' ) THEN
INSERT INTO log_history_2011 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2012-01-01' AND
NEW.logdate < DATE '2013-01-01' ) THEN
INSERT INTO log_history_2012 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2013-01-01' AND
NEW.logdate < DATE '2014-01-01' ) THEN
INSERT INTO log_history_2013 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2014-01-01' AND
NEW.logdate < DATE '2015-01-01' ) THEN
INSERT INTO log_history_2014 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2016-01-01' AND
NEW.logdate < DATE '2017-01-01' ) THEN
INSERT INTO log_history_2016 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2017-01-01' AND
NEW.logdate < DATE '2018-01-01' ) THEN
INSERT INTO log_history_2017 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2018-01-01' AND
NEW.logdate < DATE '2019-01-01' ) THEN
INSERT INTO log_history_2018 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2019-01-01' AND
NEW.logdate < DATE '2020-01-01' ) THEN
INSERT INTO log_history_2019 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2021-01-01' AND
NEW.logdate < DATE '2022-01-01' ) THEN
INSERT INTO log_history_2021 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the log_history_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
创建函数
postgres=# \i log_history_insert_trigger.sql
创建主表索引（子表不会继承主表的索引）
postgres=# create index index_log_history_logdate on log_history(logdate);
CREATE INDEX
postgres=# select * from pg_indexes where tablename like 'log_history%';
schemaname | tablename | indexname | tablespace | indexdef
------------+-------------+---------------------------+------------+------------------------------------------------------------------------------------
public | log_history | index_log_history_logdate | | CREATE INDEX index_log_history_logdate ON public.log_history USING btree (logdate)
创建分区索引
postgres=# create index index_log_history_2011_logdate on log_history_2011(logdate);
postgres=# create index index_log_history_2012_logdate on log_history_2012(logdate);
postgres=# create index index_log_history_2013_logdate on log_history_2013(logdate);
postgres=# create index index_log_history_2014_logdate on log_history_2014(logdate);
postgres=# create index index_log_history_2015_logdate on log_history_2015(logdate);
postgres=# create index index_log_history_2016_logdate on log_history_2016(logdate);
postgres=# create index index_log_history_2017_logdate on log_history_2017(logdate);
postgres=# create index index_log_history_2018_logdate on log_history_2018(logdate);
postgres=# create index index_log_history_2019_logdate on log_history_2019(logdate);
postgres=# create index index_log_history_2020_logdate on log_history_2020(logdate);
postgres=# create index index_log_history_2021_logdate on log_history_2021(logdate);
查看索引
postgres=# select * from pg_indexes where tablename like 'log_history%';
schemaname | tablename | indexname | tablespace | indexdef
------------+------------------+--------------------------------+------------+----------------------------------------------------------------------------------------------
public | log_history | index_log_history_logdate | | CREATE INDEX index_log_history_logdate ON public.log_history USING btree (logdate)
public | log_history_2011 | index_log_history_2011_logdate | | CREATE INDEX index_log_history_2011_logdate ON public.log_history_2011 USING btree (logdate)
public | log_history_2012 | index_log_history_2012_logdate | | CREATE INDEX index_log_history_2012_logdate ON public.log_history_2012 USING btree (logdate)
public | log_history_2013 | index_log_history_2013_logdate | | CREATE INDEX index_log_history_2013_logdate ON public.log_history_2013 USING btree (logdate)
public | log_history_2014 | index_log_history_2014_logdate | | CREATE INDEX index_log_history_2014_logdate ON public.log_history_2014 USING btree (logdate)
public | log_history_2015 | index_log_history_2015_logdate | | CREATE INDEX index_log_history_2015_logdate ON public.log_history_2015 USING btree (logdate)
public | log_history_2016 | index_log_history_2016_logdate | | CREATE INDEX index_log_history_2016_logdate ON public.log_history_2016 USING btree (logdate)
public | log_history_2017 | index_log_history_2017_logdate | | CREATE INDEX index_log_history_2017_logdate ON public.log_history_2017 USING btree (logdate)
public | log_history_2018 | index_log_history_2018_logdate | | CREATE INDEX index_log_history_2018_logdate ON public.log_history_2018 USING btree (logdate)
public | log_history_2019 | index_log_history_2019_logdate | | CREATE INDEX index_log_history_2019_logdate ON public.log_history_2019 USING btree (logdate)
public | log_history_2020 | index_log_history_2020_logdate | | CREATE INDEX index_log_history_2020_logdate ON public.log_history_2020 USING btree (logdate)
public | log_history_2021 | index_log_history_2021_logdate | | CREATE INDEX index_log_history_2021_logdate ON public.log_history_2021 USING btree (logdate)2.3 继承式分区维护
2.3.1 查看分区
使用\dp无法看到继承式分区表，只能看到声明式分区表
postgres=# \dP
List of partitioned relations
Schema | Name | Owner | Type | Table
--------+------+-------+------+-------
或者
postgres=# select partrelid::regclass,* from pg_partitioned_table;
partrelid | partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs
-----------+-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
查看继承分区
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2011 | log_history | 1
log_history_2012 | log_history | 1
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 12.3.2 删除分区
方法1：直接删除子表删除分区
postgres=# drop table log_history_2011;
DROP TABLE
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2012 | log_history | 1
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 1
方法2：通过继承层次表删除分区，分区会作为一张表
postgres=# alter table log_history_2012 NO INHERIT log_history;
ALTER TABLE
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 12.3.3 新增分区
方法1：添加分区，与创建原始子表一样（然后更新log_history_insert_trigger函数）
postgres=# create table log_history_2022(check (logdate >= date'2022-01-01' and logdate < date'2023-01-01')) INHERITS (log_history);
CREATE TABLE
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 1
log_history_2022 | log_history | 1
方法2：创建新子表，然后将子表添加到分区中
创建子表
postgres=# create table log_history_2023 (like log_history including defaults including constraints);
CREATE TABLE
查看子表定义
postgres=# \d+ log_history_2023;
Table "public.log_history_2023"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
num | integer | | | | plain | |
Access method: heap
给子表添加CHECK约束
postgres=# alter table log_history_2023 add check(logdate >= '2023-01-01' and logdate < date'2024-01-01');
ALTER TABLE
查看子表定义
postgres=# \d+ log_history_2023;
Table "public.log_history_2023"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
num | integer | | | | plain | |
Check constraints:
"log_history_2023_logdate_check" CHECK (logdate >= '2023-01-01'::date AND logdate < '2024-01-01'::date)
Access method: heap
往子表中导入数据
\copy log_history_2023 from 'log_history_2023.dump';
继承表方式添加分区
postgres=# alter table log_history_2023 INHERIT log_history;
ALTER TABLE
查看继承分区信息
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 1
log_history_2022 | log_history | 1
log_history_2023 | log_history | 1
然后更新对应函数，添加2022、2023年CHECK约束
postgres=# \df+ log_history_insert_trigger;
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------------------------------------
Schema | public
Name | log_history_insert_trigger
Result data type | trigger
Argument data types |
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres12
Security | invoker
Access privileges |
Language | plpgsql
Source code | +
| BEGIN +
| IF ( NEW.logdate >= DATE '2011-01-01' AND +
| NEW.logdate < DATE '2012-01-01' ) THEN +
| INSERT INTO log_history_2011 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2012-01-01' AND +
| NEW.logdate < DATE '2013-01-01' ) THEN +
| INSERT INTO log_history_2012 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2013-01-01' AND +
| NEW.logdate < DATE '2014-01-01' ) THEN +
| INSERT INTO log_history_2013 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2014-01-01' AND +
| NEW.logdate < DATE '2015-01-01' ) THEN +
| INSERT INTO log_history_2014 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2016-01-01' AND +
| NEW.logdate < DATE '2017-01-01' ) THEN +
| INSERT INTO log_history_2016 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2017-01-01' AND +
| NEW.logdate < DATE '2018-01-01' ) THEN +
| INSERT INTO log_history_2017 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2018-01-01' AND +
| NEW.logdate < DATE '2019-01-01' ) THEN +
| INSERT INTO log_history_2018 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2019-01-01' AND +
| NEW.logdate < DATE '2020-01-01' ) THEN +
| INSERT INTO log_history_2019 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2021-01-01' AND +
| NEW.logdate < DATE '2022-01-01' ) THEN +
| INSERT INTO log_history_2021 VALUES (NEW.*); +
| ELSE +
| RAISE EXCEPTION 'Date out of range. Fix the log_history_insert_trigger() function!';+
| END IF; +
| RETURN NULL; +
| END; +
|
Description |
postgres=# \ef log_history_insert_trigger
postgres-# \g
CREATE FUNCTION
postgres=# \df+ log_history_insert_trigger;
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------------------------------------
Schema | public
Name | log_history_insert_trigger
Result data type | trigger
Argument data types |
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres12
Security | invoker
Access privileges |
Language | plpgsql
Source code | +
| BEGIN +
| IF ( NEW.logdate >= DATE '2011-01-01' AND +
| NEW.logdate < DATE '2012-01-01' ) THEN +
| INSERT INTO log_history_2011 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2012-01-01' AND +
| NEW.logdate < DATE '2013-01-01' ) THEN +
| INSERT INTO log_history_2012 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2013-01-01' AND +
| NEW.logdate < DATE '2014-01-01' ) THEN +
| INSERT INTO log_history_2013 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2014-01-01' AND +
| NEW.logdate < DATE '2015-01-01' ) THEN +
| INSERT INTO log_history_2014 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2016-01-01' AND +
| NEW.logdate < DATE '2017-01-01' ) THEN +
| INSERT INTO log_history_2016 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2017-01-01' AND +
| NEW.logdate < DATE '2018-01-01' ) THEN +
| INSERT INTO log_history_2017 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2018-01-01' AND +
| NEW.logdate < DATE '2019-01-01' ) THEN +
| INSERT INTO log_history_2018 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2019-01-01' AND +
| NEW.logdate < DATE '2020-01-01' ) THEN +
| INSERT INTO log_history_2019 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2021-01-01' AND +
| NEW.logdate < DATE '2022-01-01' ) THEN +
| INSERT INTO log_history_2021 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2022-01-01' AND +
| NEW.logdate < DATE '2023-01-01' ) THEN +
| INSERT INTO log_history_2022 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2023-01-01' AND +
| NEW.logdate < DATE '2024-01-01' ) THEN +
| INSERT INTO log_history_2023 VALUES (NEW.*); +
| ELSE +
| RAISE EXCEPTION 'Date out of range. Fix the log_history_insert_trigger() function!';+
| END IF; +
| RETURN NULL; +
| END; +
|
Description | 2.4 constraint_exclusion
constraint_exclusion参数可以提升声明式分区表性能的查询优化技术。
select * from log_history where logdate<date'2015-09-11';
如果启用constraint_exclusion参数，上面的查询将会扫描log_history表的每一个分区。如果启用了此参数，规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询WHERE子句的行而无需扫描。当规划器可以证实这一点时，它会把分区从查询计划中排除。
启用constraint_exclusion = partition参数（默认启用partition），指定计划只会扫描需要的分区
postgres=# set constraint_exclusion = partition;
SET
postgres=# explain select * from log_history where logdate<date'2015-09-11';
QUERY PLAN
--------------------------------------------------------------------------
Append (cost=0.00..198.39 rows=3468 width=12)
-> Seq Scan on log_history (cost=0.00..3.55 rows=68 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2011 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2012 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2013 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2014 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2015 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
如果未启用constraint_exclusion参数，指定计划只会扫描需要的分区
postgres=# set constraint_exclusion = off;
SET
postgres=# explain select * from log_history where logdate<date'2015-09-11';
QUERY PLAN
--------------------------------------------------------------------------
Append (cost=0.00..431.79 rows=7548 width=12)
-> Seq Scan on log_history (cost=0.00..3.55 rows=68 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2011 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2012 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2013 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2014 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2015 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2016 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2017 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2018 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2019 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2020 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)
-> Seq Scan on log_history_2021 (cost=0.00..35.50 rows=680 width=12)
Filter: (logdate < '2015-09-11'::date)3 VACUUM或ANALYZE
VACUUM或ANALYZE tbl_partition只会对主表起作用，要想分析表，需要分别分析每个分区表。
查看分区表最后一次VACUUM和ANALYZE时间
postgres=# select relname,last_vacuum,last_analyze from pg_stat_all_tables where relname like 'log_history%';
relname | last_vacuum | last_analyze
------------------+-------------+--------------
log_history | |
log_history_2011 | |
log_history_2012 | |
log_history_2013 | |
log_history_2014 | |
log_history_2015 | |
log_history_2016 | |
log_history_2017 | |
log_history_2018 | |
log_history_2019 | |
log_history_2020 | |
log_history_2021 | |
vacuum主表只对主表起作用
postgres=# vacuum log_history;
VACUUM
postgres=# select relname,last_vacuum,last_analyze from pg_stat_all_tables where relname like 'log_history%';
relname | last_vacuum | last_analyze
------------------+-------------------------------+--------------
log_history | 2021-12-27 11:36:17.562586+08 |
log_history_2011 | |
log_history_2012 | |
log_history_2013 | |
log_history_2014 | |
log_history_2015 | |
log_history_2016 | |
log_history_2017 | |
log_history_2018 | |
log_history_2019 | |
log_history_2020 | |
log_history_2021 | |
vacuum主表和分区表
postgres=# vacuum log_history;
VACUUM
postgres=# vacuum log_history_2011;
VACUUM
postgres=# vacuum log_history_2012;
VACUUM
postgres=# vacuum log_history_2013;
VACUUM
postgres=# vacuum log_history_2014;
VACUUM
postgres=# vacuum log_history_2015;
VACUUM
postgres=# vacuum log_history_2016;
VACUUM
postgres=# vacuum log_history_2017;
VACUUM
postgres=# vacuum log_history_2018;
VACUUM
postgres=# vacuum log_history_2019;
VACUUM
postgres=# vacuum log_history_2020;
VACUUM
postgres=# vacuum log_history_2021;
VACUUM
postgres=# select relname,last_vacuum,last_analyze from pg_stat_all_tables where relname like 'log_history%';
relname | last_vacuum | last_analyze
------------------+-------------------------------+--------------
log_history | 2021-12-27 11:38:03.134217+08 |
log_history_2011 | 2021-12-27 11:38:03.134608+08 |
log_history_2012 | 2021-12-27 11:38:03.134919+08 |
log_history_2013 | 2021-12-27 11:38:03.135219+08 |
log_history_2014 | 2021-12-27 11:38:03.135507+08 |
log_history_2015 | 2021-12-27 11:38:03.135794+08 |
log_history_2016 | 2021-12-27 11:38:03.136088+08 |
log_history_2017 | 2021-12-27 11:38:03.136377+08 |
log_history_2018 | 2021-12-27 11:38:03.136668+08 |
log_history_2019 | 2021-12-27 11:38:03.136965+08 |
log_history_2020 | 2021-12-27 11:38:03.22314+08 |
log_history_2021 | 2021-12-27 11:38:03.435743+08 |
