一、关于分区表
表分区是在大数据优化中的一种常见的分表方案，通过将大数据按照一定的规则（最常见的是按照时间）进行分表处理，将逻辑上的一个大表分割成物理上的几块表，插入数据时，数据会自动插入到不同的分区表中，从而实现查询或者其它操作的性能优化。相比于一个大表，分区表具有以下优点：
（1）当查询或者更新一个分区的大部分记录时，采用顺序扫描而不是随机扫描，可以获得巨大的性能提升。
（2）使用不频繁的历史数据可以转移到一些低廉的存储介质上，而热数据放到性能较好的存储介质上，可以最大限度的减少成本。
（3）位于同一个分区的热数据可以全部缓存到内存中，查询hit的几率更高，可以加快查询速度。
Postgresql中有两种建立分区表的方法——使用声明式分区和表继承分区，接下来，笔者就这两种方式分别进行介绍。
二、声明式分区
声明式分区的意思就是Postgresql提供了相应的DDL语句创建分区表，使用声明式的方法建立分区表的方法比较简单，下面，我们就使用一个例子来介绍这种创建分区表的方法。
假设，我们创建一个operate_log表，用来记录用户的操作记录。因为操作记录会随着时间的累积，越来越大，所以我们选择建立一个分区表，并按照时间分区，每一个一个分区。
stock_analysis_data=# create table operate_log (id int,user_id int,operate_type int,content text,operate_date date) partition by range(operate_date);
CREATE TABLE注意partition by range这段语句，是在指明使用哪个字段进行分区。现在，我们只是创建了分区表的基础表，分区表目前是还没有创建，基础表是无法插入数据的。此时，向基础表里面插入数据，会报下面的错误：
stock_analysis_data=# insert into operate_log values(1,1,1,'用户注册','2021-06-24');
ERROR: no partition of relation "operate_log" found for row
DETAIL: Partition key of the failing row contains (operate_date) = (2021-06-24).
stock_analysis_data=# 接下来，我们来创建该基础表的分区表：
stock_analysis_data=# create table operater_log_202106 partition of operate_log for values from ('2021-06-01') to ('2021-07-01');
CREATE TABLE可以看到，创建分区表最关键的两个语句是：
（1）partition of —— 指定待分区的基础表是哪个
（2）for values from () to () —— 指定当前分区表的分区范围
现在，我们可以向基础表里面插入数据，它可以自动的插入到相应的分区中：
stock_analysis_data=# insert into operate_log values(1,1,1,'用户注册','2021-06-24');
INSERT 0 1
stock_analysis_data=# select * from operater_log_202106;
id | user_id | operate_type | content | operate_date
----+---------+--------------+----------+--------------
1 | 1 | 1 | 用户注册 | 2021-06-24
(1 row)从基础表里面直接select，可以查询到分区表里面的数据：
stock_analysis_data=# select * from operate_log;
id | user_id | operate_type | content | operate_date
----+---------+--------------+----------+--------------
1 | 1 | 1 | 用户注册 | 2021-06-24
(1 row)但是，如果插入定义在分区范围之外的数据，就会报错：
stock_analysis_data=# insert into operate_log values(1,1,1,'用户注册','2021-07-24');
ERROR: no partition of relation "operate_log" found for row
DETAIL: Partition key of the failing row contains (operate_date) = (2021-07-24).
stock_analysis_data=# 三、表继承方式实现分区表
Postgresql中所谓的表继承，就是我们在创建一个新表时，可以继承基础表，新表具有基础表的所有字段。而向新表中插入数据时，通过基础表也可以查询到这些数据。实际上，使用声明式方式创建分区表，内部实现就是利用表继承。我们还是利用operate_log表作为基础表，利用表继承的方式创建分区表。不过在此之前，我们需要先把原来的operate_log表drop掉，因为通过表继承的方式实现的分区表，基础表必须是普通表，而之前的operate_log表我们创建时声明成了partition。
stock_analysis_data=# drop table operate_log;
DROP TABLE
stock_analysis_data=# create table operate_log (id int,user_id int,operate_type int,content text,operate_date date);
CREATE TABLE下面，以新的operate_log为基础表，进行分区表的创建：
stock_analysis_data=# create table operate_log_202105(check(operate_date>='2021-05-01' and operate_date<'2020-06-01')) inherits (operate_log);
CREATE TABLE
stock_analysis_data=# create table operate_log_202106(check(operate_date>='2021-06-01' and operate_date<'2020-07-01')) inherits (operate_log);
CREATE TABLE可以看到，以表继承的方式创建分区表时需要使用inherits关键字显式的指明基础表，而且需要自己写检查约束来约束分区字段的取值范围。仅仅完成上述工作之后，你会发现，现在的分区表实际上还无法使用，我们向基础表里面插入数据时，并不能自动的插入到分区表中。
stock_analysis_data=# insert into operate_log values(1,1,1,'用户注册','2021-06-24');
INSERT 0 1上述命令，向基础表operate_log里面插入了一条数据，按理来说，应该插入到operate_log_202106表中，但是实际上，并没有插入进去：
stock_analysis_data=# select * from operate_log_202106;
id | user_id | operate_type | content | operate_date
----+---------+--------------+---------+--------------
(0 rows)原因是我们使用表继承的方式实现分区表时，需要自己去控制向主表插入数据时，数据自动插入分区表的逻辑。目前主要有两种方式可以实现这一逻辑：一种是使用触发器，另一种是使用自定义Rule。接下来，笔者分别进行介绍。
3.1 使用触发器进行数据自动插入分区表
我们先创建触发器函数：
create or replace function operate_log_insert_trigger()
returns trigger as
$$
begin
if (NEW.operate_date >= date'2021-06-01' and NEW.operate_date < date'2021-07-01') then
insert into operate_log_202106 values (NEW.*);
elsif(NEW.operate_date >= date'2021-05-01' and NEW.operate_date < date'2021-06-01') then
insert into operate_log_202105 values (NEW.*);
else
raise exception 'out of range!!!';
end if;
return null;
end;
$$
language plpgsql;然后创建触发器：
create trigger insert_sale_detail_trigger
before insert on operate_log
for each row execute procedure operate_log_insert_trigger();
CREATE TRIGGER接下来，向基础表中插入数据：
insert into operate_log values (2,2,1,'用户退出',date'2021-06-24');再从operate_log_202106表中查询：
stock_analysis_data=# select * from operate_log_202106;
id | user_id | operate_type | content | operate_date
----+---------+--------------+----------+--------------
2 | 2 | 1 | 用户退出 | 2021-06-24
(1 row)3.2 使用自定义Rule进行数据自动插入分区表
以上是使用触发器实现的自动从基础表中向分区表插入数据，接下来，笔者介绍下使用自定义Rule向分区表中插入数据的方法。示例如下：
create rule operate_log_insert_202106 as
on insert to operate_log where
(operate_date>=date'2021-06-01' and operate_date<date'2021-07-01')
do instead
insert into operate_log values (NEW.*);
create rule operate_log_insert_202105 as
on insert to operate_log where
(operate_date>=date'2021-05-01' and operate_date<date'2021-06-01')
do instead
insert into operate_log values (NEW.*);创建好上述规则之后，同样可以实现向基础表插入数据时自动插入到相应的分区表，但是和触发器相比，自定义的Rule有它自己的特点:
(1) 每次插入数据都要检查，找到合适的规则然后用insert语句替代原来的insert语句，开销明显比触发器要大。
(2) 创建的Rule不定义会一定触发，在COPY插入数据时就不能触发Rule，但是触发器可以正常使用
(3) 当插入的数据超过了定义的范围时，触发器会报错，但是Rule会直接插入到基础表里。
(4) 不管是触发器还是Rule，当我们需要扩展分区表时，都不得不修改触发器函数或者新建Rule。
四、约束排除
约束排除是种优化分区表性能的查询方法，简单说来就是：在从基础表进行查询时，如果where条件包含在某个分区表约束条件之内时。如果打开约束排除，则直接从改分区表进行查询，但是如果关闭约束排除，则遍历所有分区表查找。在文件中可以设置约束排除开启或是关闭:constraint_exclusion = partition (默认开启)，设置off关闭。
在约束排除开启的情况下，我们用explain查看查询情况：
stock_analysis_data=# explain (analyze,verbose,costs,buffers,timing) select count(*) from operate_log where operate_date >= date'2021-06-01';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=26.05..26.06 rows=1 width=8) (actual time=0.023..0.025 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=1
-> Append (cost=0.00..25.16 rows=357 width=0) (actual time=0.013..0.016 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on public.operater_log_202106 (cost=0.00..23.38 rows=357 width=0) (actual time=0.010..0.013 rows=1 loops=1)
Filter: (operater_log_202106.operate_date >= '2021-06-01'::date)
Buffers: shared hit=1
Planning Time: 0.193 ms
Execution Time: 0.065 ms
(10 rows)关闭约束排除，找到/var/lib/pgsql/11/data下的文件，修改：
constraint_exclusion = off重启数据库服务后，再explain上述查询语句（或者直接执行set constraint_exclusion = off，设置本次session中的参数）：
stock_analysis_data=# explain (analyze,verbose,costs,buffers,timing) select count(*) from operate_log where operate_date >= date'2021-06-01';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=78.16..78.17 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=3
-> Append (cost=0.00..75.48 rows=1071 width=0) (actual time=0.010..0.022 rows=3 loops=1)
Buffers: shared hit=3
-> Seq Scan on public.operater_log_202104 (cost=0.00..23.38 rows=357 width=0) (actual time=0.008..0.010 rows=1 loops=1)
Filter: (operater_log_202104.operate_date >= '2021-06-01'::date)
Buffers: shared hit=1
-> Seq Scan on public.operater_log_202105 (cost=0.00..23.38 rows=357 width=0) (actual time=0.003..0.004 rows=1 loops=1)
Filter: (operater_log_202105.operate_date >= '2021-06-01'::date)
Buffers: shared hit=1
-> Seq Scan on public.operater_log_202106 (cost=0.00..23.38 rows=357 width=0) (actual time=0.003..0.004 rows=1 loops=1)
Filter: (operater_log_202106.operate_date >= '2021-06-01'::date)
Buffers: shared hit=1
Planning Time: 0.120 ms
Execution Time: 0.076 ms
(16 rows)五、总结
根据本文的内容，我们可以得到如下结论：
（1）利用表分区可以实现将一个大表化整为零，数据按照日期或者其它分表规则划分到分区表上，一方面可以加快查询和更新效率，另一方面可以将不同热度的数据分布到不同的存储介质上，减少部署成本。
（2）表分区可以使用声明式分区或者表继承分区，前者的内部实现实际上也是利用表继承分区，但是前者使用较为简单，建议使用声明式的分区方式。
（3）使用分区表时，要开启约束排除，设置文件中的constraint_exclusion = partition（默认值）。