大家好，今天和大家聊聊用pg的分区代替oracle的可能性。
众做周知，oracle 作为业界数据库的旗舰级产品，各项功能都是完善的， 近些年在国内某种大潮下，国产数据库或者开源数据库开始提供一些替代
oracle的m6米乐安卓版下载的解决方案。 目前oltp这块， 开源的mysql和pg做为了2个主力根的分支。 国产数据库大部分都是基于这2个根分支进行了研发或者魔改。
国内早期的许多项目（非互联网场景，大多数为传统行业的项目），严重依赖于oracle 数据库的分区，并行查询，存储过程，hint, 强力的优化器功能（上千行的复杂大sql，几十张表的join ）等等等… 使得再去o 的道路上困难重重。
作为重度依赖oracle 的项目对于 数据库选型来说 ：
mysql 这种小快灵的开发部署方式（devops），以及业界默认的规则 不要写复杂的sql（表连接不要超过3张），不要分区，短事务 等等，似乎对于老项目的改造实现起来很难。
（程序员往往就是喜欢照着翻译，上万行的plsql 翻译成 短小的mysql 代码 放到java程序中 基本上无任何希望。。。）
pg 虽然在国外有着几十年的历史，但是与兄弟数据库mysql比起来， 国内起步较晚，国内程序员接受程度普遍不是很高。
在国外来说，pg已经成为了最受开发者欢迎的数据库之一。
言归正传，我们首先看看pg的分区历史：
pg10 之前的版本， pg使用继承的方式来实现分区表的
从pg版本10 开始， pg可以使用声明式的分区定义，支持range, list 等主流分区的方式
从pg版本11开始， pg 支持hash key 的分区
我们先从pg基础的分区的定义来了解一下（本文分享的都是声明式的分区定义方式）：
range 范围分区
postgres=# create table payment_request (
id serial ,
account_no varchar(50) not null,
pay_amount decimal (6,2),
pay_date date,
pay_status int) partition by range(pay_date);
create table
由于pg没有全局索引的概念，所以主键或者唯一键必须要包含分区间，否则会报错： error: unique constraint on partitioned table must include all partitioning columns（这一点和mysql 的分区表是一致的，oracle支持更为强大的全局索引）
postgres=# alter table payment_request add constraint pk_id_paydate primary key (id,pay_date);
alter table
表payment_request 只是一个虚拟表的概念，我们需要添加具体的范围分区：
这里值得注意的是，对于范围分区来说,如果人为的指定范围错误，那么创建分区的时候也会报错： error: partition “xxxx” would overlap partition “xxxxxxxxxx”
postgres=# create table payment_request_y2022m08 partition of payment_request
postgres-# for values from ('2022-08-01') to ('2022-09-1');
create table
postgres=# create table peyment_request_y2022m09 partition of payment_request
postgres-# for values from ('2022-08-20') to ('2022-09-30');
error: partition "peyment_request_y2022m09" would overlap partition "payment_request_y2022m08"
line 2: for values from ('2022-08-20') to ('2022-09-30');
正确指定范围，手动创建分区： 2022-09 和 2022-09 2 个分区， 并在2个分区内插入数据
postgres=# create table payment_request_y2022m08 partition of payment_request
postgres-# for values from ('2022-08-01') to ('2022-09-1');
create table
postgres=# create table payment_request_y2022m09 partition of payment_request
postgres-# for values from ('2022-09-01') to ('2022-10-01');
create table
postgres=# insert into payment_request (account_no,pay_amount,pay_date,pay_status) values ('10000002',3500,'2022-09-01',1);
insert 0 1
postgres=# insert into payment_request (account_no,pay_amount,pay_date,pay_status) values ('10000001',2500,'2022-08-01',1);
insert 0 1
postgres=# select * from payment_request_y2022m08;
id | account_no | pay_amount | pay_date | pay_status
---- ------------ ------------ ------------ ------------
4 | 10000001 | 2500.00 | 2022-08-01 | 1
(1 row)
postgres=# select * from payment_request_y2022m09;
id | account_no | pay_amount | pay_date | pay_status
---- ------------ ------------ ------------ ------------
3 | 10000002 | 3500.00 | 2022-09-01 | 1
(1 row)
从pg11版本开始， 数据如果要在分区之间进行移动的话 ，比如执行 update 语句， 默认是 支持 enable row movement的。 （oracle 数据库的话，需要手动开启表级别的 enable row movement）
早期pg10的版本，跨分区之间移动数据的话，会进行 分区约束的校验 partition constraint : 会返回错误 ： error: new row for relation "xxx " violates partition constraint
postgres=# update payment_request set pay_date = '2022-08-15' where pay_date = '2022-09-01';
update 1
postgres=# select * from payment_request_y2022m08;
id | account_no | pay_amount | pay_date | pay_status
---- ------------ ------------ ------------ ------------
4 | 10000001 | 2500.00 | 2022-08-01 | 1
3 | 10000002 | 3500.00 | 2022-08-15 | 1
(2 rows)
postgres=# select * from payment_request_y2022m09;
id | account_no | pay_amount | pay_date | pay_status
---- ------------ ------------ ---------- ------------
(0 rows)
如何查看分区表, 相对应的子分区（分区索引）以及大小？
postgres=# select
postgres-# nmsp_parent.nspname as parent_schema,
postgres-# parent.relname as parent,
postgres-# nmsp_child.nspname as child_schema,
postgres-# child.relname as child,
postgres-# case
postgres-# when child.relkind = 'r' then 'ordinary table'
postgres-# when child.relkind = 'i' then 'index'
postgres-# when child.relkind = 's' then 'sequence'
postgres-# when child.relkind = 'v' then 'view'
postgres-# when child.relkind = 'm' then 'materialized view'
postgres-# when child.relkind = 'c' then 'composite type'
postgres-# when child.relkind = 't' then 'toast table'
postgres-# when child.relkind = 'f' then 'foreign table'
postgres-# end as child_type,
postgres-# pg_size_pretty(pg_relation_size(child.relname :: varchar)) as child_size
postgres-# from pg_inherits
postgres-# join pg_class parent on pg_inherits.inhparent = parent.oid
postgres-# join pg_class child on pg_inherits.inhrelid = child.oid
postgres-# join pg_namespace nmsp_parent on nmsp_parent.oid = parent.relnamespace
postgres-# join pg_namespace nmsp_child on nmsp_child.oid = child.relnamespace
postgres-# where parent.relname in ('payment_request','pk_id_paydate') order by parent.relname;
parent_schema | parent | child_schema | child | child_type | child_size
--------------- ----------------- -------------- ------------------------------- ---------------- ------------
public | payment_request | public | payment_request_y2022m08 | ordinary table | 5888 kb
public | payment_request | public | payment_request_y2022m09 | ordinary table | 592 kb
public | pk_id_paydate | public | payment_request_y2022m08_pkey | index | 2208 kb
public | pk_id_paydate | public | payment_request_y2022m09_pkey | index | 240 kb
(4 rows)
我们还可以利用函数 pg_partition_tree 查看表与分区之间的关系:
postgres=# select * from pg_partition_tree('payment_request');
relid | parentrelid | isleaf | level
-------------------------- ----------------- -------- -------
payment_request | | f | 0
payment_request_y2022m09 | payment_request | t | 1
payment_request_y2022m08 | payment_request | t | 1
(3 rows)
函数 pg_partition_root 可以通过子分区的名称，查找到root 表的名称：
postgres=# select * from pg_partition_root('payment_request_y2022m09');
pg_partition_root
-------------------
payment_request
(1 row)
list 分区
列表分区也是我们常见的一种分区方式，通常作用于 ndv 值很小并且是预先固定的常量值的属性类，类似于 城市， 商品类型，订单状态。
我们把之前的付款表加上一列 region 这个区域属性的列：
create table payment_request_list (
id serial ,
account_no varchar(50) not null,
pay_amount decimal (6,2),
pay_date date,
pay_status int,
region varchar(50)
) partition by list(region);
create table
创建4个分区： 根据区域的 东南西北
postgres=# create table payment_request_list_east partition of payment_request_list for values
in ('east');
create table
postgres=# create table payment_request_list_west partition of payment_request_list for values
in ('west');
create table
postgres=# create table payment_request_list_north partition of payment_request_list for values
in ('north');
create table
postgres=# create table payment_request_list_south partition of payment_request_list for values
in ('south');
create table
postgres=# \d payment_request_list;
partitioned table "public.payment_request_list"
column | type | collation | nullable | default | storage | compression | stats target | description
------------ ----------------------- ----------- ---------- -------------------------------------------------- ---------- ------------- -------------- -------------
id | integer | | not null | nextval('payment_request_list_id_seq'::regclass) | plain | | |
account_no | character varying(50) | | not null | | extended | | |
pay_amount | numeric(6,2) | | | | main | | |
pay_date | date | | | | plain | | |
pay_status | integer | | | | plain | | |
region | character varying(50) | | | | extended | | |
partition key: list (region)
partitions: payment_request_list_east for values in ('east'),
payment_request_list_north for values in ('north'),
payment_request_list_south for values in ('south'),
payment_request_list_west for values in ('west')
我们尝试插入一条在 list 列表之外的记录： 会返回错误 no partition of relation “payment_request_list” found for row
postgres=# insert into payment_request_list (account_no,pay_amount,pay_date,pay_status,region) values ('1000005',1500,'2022-09-27',0,'center');
error: no partition of relation "payment_request_list" found for row
detail: partition key of the failing row contains (region) = (center).
当然我们可以手动添加一个 list = ‘center’ 的分区 。
或者我们也可以添加一个 default 分区 ，default partition 可以收纳 分区键之前的所有值 。
（pg version 11 开始支持 default 分区，list 和 range 均可以添加 default 分区）
postgres=# create table payment_request_list_default partition of payment_request_list default;
create table
postgres=# insert into payment_request_list (account_no,pay_amount,pay_date,pay_status,region) values ('1000005',1500,'2022-09-27',0,'center');
insert 0 1
postgres=# \d payment_request_list
partitioned table "public.payment_request_list"
column | type | collation | nullable | default | storage | compression | stats target | description
------------ ----------------------- ----------- ---------- -------------------------------------------------- ---------- ------------- -------------- -------------
id | integer | | not null | nextval('payment_request_list_id_seq'::regclass) | plain | | |
account_no | character varying(50) | | not null | | extended | | |
pay_amount | numeric(6,2) | | | | main | | |
pay_date | date | | | | plain | | |
pay_status | integer | | | | plain | | |
region | character varying(50) | | | | extended | | |
partition key: list (region)
partitions: payment_request_list_east for values in ('east'),
payment_request_list_north for values in ('north'),
payment_request_list_south for values in ('south'),
payment_request_list_west for values in ('west'),
payment_request_list_default default
hash 哈希分区
哈希分区是pg version 11 版本开始支持的， 需要哈希的列 必须要数据分布均匀，不要有倾斜，否则失去了哈希打散数据的意义，数据热点无法分散。
我们还是以 payment_request 作为案例， id 作为hash key ， 具有唯一性，使得数据分布均匀。
postgres=# create table payment_request_hash (
id serial ,
account_no varchar(50) not null,
pay_amount decimal (6,2),
pay_date date,
pay_status int,
region varchar(50)
) partition by hash(id);
create table
手动创建4个分区， 利用 mode 函数 除以4取余
postgres=# create table payment_request_hash_01 partition of payment_request_hash for values with (modulus 4, remainder 0);
create table
postgres=# create table payment_request_hash_02 partition of payment_request_hash for values with (modulus 4, remainder 1);
create table
postgres=# create table payment_request_hash_03 partition of payment_request_hash for values with (modulus 4, remainder 2);
create table
postgres=# create table payment_request_hash_04 partition of payment_request_hash for values with (modulus 4, remainder 3);
create table
postgres=# \d payment_request_hash
partitioned table "public.payment_request_hash"
column | type | collation | nullable | default | storage | compression | stats target | description
------------ ----------------------- ----------- ---------- -------------------------------------------------- ---------- ------------- -------------- -------------
id | integer | | not null | nextval('payment_request_hash_id_seq'::regclass) | plain | | |
account_no | character varying(50) | | not null | | extended | | |
pay_amount | numeric(6,2) | | | | main | | |
pay_date | date | | | | plain | | |
pay_status | integer | | | | plain | | |
region | character varying(50) | | | | extended | | |
partition key: hash (id)
partitions: payment_request_hash_01 for values with (modulus 4, remainder 0),
payment_request_hash_02 for values with (modulus 4, remainder 1),
payment_request_hash_03 for values with (modulus 4, remainder 2),
payment_request_hash_04 for values with (modulus 4, remainder 3)
模拟插入10000条数据： 查看数据分布的情况是很均匀的
postgres=# insert into payment_request_hash select generate_series(1,10000),'100000010',2000,'2022-09-27',1,'east';
insert 0 10000
postgres=# select count(1) from payment_request_hash_01;
count
-------
2489
(1 row)
postgres=# select count(1) from payment_request_hash_02;
count
-------
2527
(1 row)
postgres=# select count(1) from payment_request_hash_03;
count
-------
2530
(1 row)
postgres=# select count(1) from payment_request_hash_04;
count
-------
2454
(1 row)
组合分区
接下来再看看常用的二级分区： list range 的形式 （一级 partition 是 list (region 列)， subpartition 是 range (request_date)）
pg 原生的语法并不支持直接创建二级分区表（国外商业版的 edb postgres 支持），我们可以通过多次创建分区表的方式来间接实现2级分区的功能：
1.创建一级分区的定义 – partition by list
postgres=# create table payment_request_compose_list_range partition of payment_request_compose_list for values in ('east') partition by range(pay_date);
create table
2.创建二级分区的定义 – partition by range
postgres=# create table payment_request_compose_west_range partition of payment_request_compose_list for values in ('west') partition by range(pay_date);
create table
postgres=# create table payment_request_compose_east_range partition of payment_request_compose_list for values in ('east') partition by range(pay_date);
create table
3.创建具体的二级分区表
postgres=# create table payment_request_compose_west_range202208 partition of payment_request_compose_west_range for values from ('2022-08-01') to ('2022-09-01');
create table
postgres=# create table payment_request_compose_west_range202209 partition of payment_request_compose_west_range for values from ('2022-09-01') to ('2022-10-01');
create table
postgres=# create table payment_request_compose_east_range202209 partition of payment_request_compose_east_range for values from ('2022-09-01') to ('2022-10-01');
create table
postgres=# create table payment_request_compose_east_range202208 partition of payment_request_compose_east_range for values from ('2022-08-01') to ('2022-09-01');
create table
4.查询二级分区表之间的关系，我们可以利用函数 pg_partition_tree , 可以看到 level 0 是一级分区表 list 的定义， level1 是 二级分区表 range的定义 ， level 2 是具体的二级分区表
postgres=# select * from pg_partition_tree('payment_request_compose_list');
relid | parentrelid | isleaf | level
------------------------------------------ ------------------------------------ -------- -------
payment_request_compose_list | | f | 0
payment_request_compose_west_range | payment_request_compose_list | f | 1
payment_request_compose_east_range | payment_request_compose_list | f | 1
payment_request_compose_west_range202208 | payment_request_compose_west_range | t | 2
payment_request_compose_west_range202209 | payment_request_compose_west_range | t | 2
payment_request_compose_east_range202209 | payment_request_compose_east_range | t | 2
payment_request_compose_east_range202208 | payment_request_compose_east_range | t | 2
(7 rows)
分区的维护性
原生的pg分区的维护性和oracle老大哥比起来还是存在一定差异的。
a)添加，删除，truncate 分区的命令，直接作用于具体的分区表中， 由于没有oracle的全局索引的概念， 也就没有了全局索引 rebuild 的烦恼.
添加
postgres=# create table payment_request_y2022m10 partition of payment_request for
postgres-# values from ('2022-10-01') to ('2022-11-01');
create table
删除：
postgres=# drop table payment_request_y2022m10;
drop table
truncate：
postgres=# truncate table payment_request_y2022m10;
truncate table
b)pg的交换分区命令（原生的pg不支持oracle的 exchange partition的命令）： attach 和 detach 分区
我们用 attach,detach 模拟一下 类似exchange partition 的功能：
我们归档一下表 payment_request 的 8月份的分区 ：
1）detach partition & create new partition
postgres=# alter table payment_request detach partition payment_request_y2022m08;
alter table
postgres=# create table payment_request_y2022m08_arch partition of payment_request
postgres-# for values from ('2022-08-01') to ('2022-09-1');
create table
2)detach partition & attach partition
postgres=# create table payment_request_y2022m08 (like payment_request including defaults including constraints);
create table
postgres=# alter table payment_request detach partition payment_request_y2022m08_arch;
alter table
postgres=# alter table payment_request attach partition payment_request_y2022m08 for values from ('2022-08-01') to ('2022-09-01');
alter table
c)如何split 拆分分区， 原生的pg是没有split 的 语法的（国外的edb的企业版有支持）， 我们只能用如下的步骤来代替：
我们把一个月的分区，一分为二的步骤如下： 当然生产环境的话，如果这个分区不是冷数据（静态的数据）的话，是需要应用系统有 outage 的，来确保的完整性
1）detach the partition
postgres=# alter table payment_request detach partition payment_request_y2022m09;
alter table
2)create new partition
postgres=# create table payment_request_y2022m09_half1 partition of payment_request for values from ('2022-09-01') to ('2022-09-15');
create table
postgres=# create table payment_request_y2022m09_half2 partition of payment_request for values from ('2022-09-15') to ('2022-10-01');
create table
3)load the data into new partition
postgres=# insert into payment_request_y2022m09_half1 select * from payment_request_y2022m09 where pay_date <= '2022-09-15';
insert 0 0
postgres=# insert into payment_request_y2022m09_half2 select * from payment_request_y2022m09 where pay_date > '2022-09-15';
insert 0 9991
pg 分区的好处大致是：
1）数据的维护，特别针对数据的删除delete操作，避免产生大量的日志文件（wal），避免大量死元祖的产生（表膨胀），避免频繁触发数据库 auto vacuum/vacuum的 backgroud 进程
2）性能上的优化：大家常说的 partition purning (分区消除)，大表转化为小表的思想
3）并行查询
4）避免单表大小32tb的限制
分区的限制和缺陷：
1）分区键对于业务系统（业务代码）的侵入性，或者说 只有在特定的业务下选择业务分区键，才能最有效发挥分区的优势
2）pg目前无interval 类型的自动扩展分区，需要自行编写脚本来维护和监控。
3) 不支持全局索引，应用端之前如果有依赖全局唯一索引做校验的话保证数据的完整性的话，需要做代码改动
4）对于非分区表到分区表的转换， 由于目前pg不支持类似oracle基于mv log的在线重定义， 需要编写触发器同步数据的方案或者干脆选择系统维护窗口来实现ctas
最后， 总结一下 oracle 分区表和pg分区表的功能对照表 （来自于 aws 的官方，个人简单的翻译了一下）
have a fun! 😃