作者简介：
中国比较早的postgresql使用者,2001年就开始使用postgresql,自2003年底至2014年一直担任PGSQL中国社区论坛PostgreSQL的论坛板块版主、管理员，参与Postgresql讨论和发表专题文章7000多贴.拥有15年的erp设计,开发和实施经验,开源mrp系统PostMRP就是我的作品,该应用软件是一套基于Postgresql专业的制造业管理软件系统.目前任职于--中国第一物流控股有限公司/运力宝（北京）科技有限公司,为公司的研发部经理
一、 测试环境操作系统：CentOS 6.4
Postgresql版本号：10.0
CPU：Intel(R) Xeon(R) CPU E5-2407 v2 @ 2.40GHz 4核心 4线程
内存：32G
硬盘：2T SAS 7200
二、 编译安装PostgreSQL 10－－编译安装及初始化
－－修改一些参数
pg_hba.conf增加许可条目
host all all 192.168.1.0/24 trust
－－启动服务
PostgreSQL的分区表跟先前版本一样，也要先建立主表，然后再建立子表，使用继承的特性，但不需要手工写规则了，这个比较赞阿。目前支持range、list分区，10正式版本发布时不知会不会支持其它方法。
range分区表1、分区主表
分区主表不能建立全局约束，使用partition by range(xxx)说明分区的方式，xxx可以是多个字段，表达式……，具体见https://www.postgresql.org/docs/devel/static/sql-createtable.html
2、分区子表
说明：建立分区表时必需指定主表。分区表和主表的 列数量，定义 必须完全一致。分区表的列可以单独增加Default值，或约束。当用户向主表插入数据库时，系统自动路由到对应的分区，如果没有找到对应分区，则抛出错误。指定分区约束的值（范围，LIST值），范围，LIST不能重叠，重叠的路由会卡壳。指定分区的列必需设置成not null,如建立主表时没设置系统会自动加上。Range分区范围为 >=最小值 and <最大值……不支持通过更新的方法把数据从一个区移动到另外一个区，这样做会报错。如果要这样做的话需要删除原来的记录，再INSERT一条新的记录。修改主表的字段名，字段类型时，会自动同时修改所有的分区。TRUNCATE 主表时，会清除所有继承表分区的记录，如果要清除单个分区，请对分区进行操作。DROP主表时会把所有子表一起给DROP掉，如果drop单个分区，请对分区进行操作。使用psql能查看分区表的详细定义。 list分区表1、分区主表
2、分区子表
多级分区表先按地区分区，再按日期分区
1、主表
2、一级分区表
3、二级分区表
直接操作分区也要受分区规则的约束
postgres=# insert into order_range_201702 (id,userid,product,createdate) values(1,(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01')); ERROR: new row for relation "order_range_201702" violates partition constraintDETAIL: Failing row contains (1, 322345, 51a9357a78416d11a018949a42dd2f8d, 2017-01-01).
INSERT提示违反了分区约束
postgres=# update order_range_201701 set createdate='2017-02-01' where createdate='2017-01-17'; ERROR: new row for relation "order_range_201701" violates partition constraintDETAIL: Failing row contains (1, 163357, 7e8fbe7b632a54ba1ec401d969f3259a, 2017-02-01).UPDATE提示违反了分区约束
如果分区表是外部表，则约束失效，后面有介绍
使用ALTER TABLE xxx ATTACH[DETACH] PARTITION 增加或删除分区1、移除分区
录入2条测试数据
删除分区
查看确认分区没了
数据也查不出来了
但分区表还在
2、添加分区
增加子表里，约束需要与主表一致
加回来时可以修改其约束范围，但数据必需在约束的规则范围内
分区表又加回来了
添加外部表作为分区表－－增加一个新库，建立需要的外部表
－－在postgres库中增加外部表order_range_fdw
插入数据时竟然不能路由到外部表，这个是处于什么考虑呢？？？，源码中只是提示 /* We do not yet have a way to insert into a foreign partition */
还没有办法这样做，猜猜后面的版本应该能实现
下面再说说使用外部表作为分区表还有一些问题
1、无法约束向分区表插入约束外的数据，如下所示
如果这样操作会导致数据查询出现不匹配。
2、sql执行时无法下推
Sql执行无法下推的话对于聚集函数的执行存在很大的性能问题，使用时一定要特别的注意，如下所示
3、sql查询需要访问的分区表中包含了“外部分区表”和“非外部分区表”时， 无法使用Parallel Seq Scan，如下所示
外部分区表的应用场景
将业务库上的不再修改的冷数全部分离到另一个节点上面，然后做为外部分区表挂上来。这样可以保持业务库的容量尽可以的轻，同时也不会对业务有侵入，这一点是非常的友好。但要注意Sql执行无法下推的问题，无法使用Parallel Seq Scan问题。
如果在后面版本中能解决fdw partition insert路由问题和sql语句执行下推问题那么就可以拿来做olap应用了。
四、建立测试业务表下面模似一个用户收支流水表
－－非分区表
－－分区表
生成12个分区，一个月份一个表
五、性能测试 数据导入－－生成测试数据1000W条记录（尽可能平均分布）
－－非分区表数据导入测试
－－分区表数据导入测试
结论：数据导入时性能相差大约是一半，所以大数据量导入时最好直接导成分区表数据，然后直接对分区表进行操作
查询某一天的数据－－直接从cache里取数据
－－非分区表
－－分区表
结论：分区表的Planning time时间明显比非分区表要高，但比起Execution time基本可以忽略。
查询某个时间范围的数据1、时间范围落在同一个分区内
－－非分区表
－－分区表
在同一个分区内查询优势明显
2、不在同一个分区内
－－非分区表
－－分区表
跨分区查询，大约在跨一半分区时性能相当。
查询某个月里某个用户数据－－直接从cache里取数据1、数据都落在所在分区，并且数据量极少
－－非分区表
－－分区表
在返回记录极少的情况下由于分布表的Planning time开销较大，所以非分区表有优势
2、数据落在其它分区，并且数据量比较大
－－非分区表
－－分区表
这是分区表最大的优势体现了，性能提升不是一般的大
索引维护－－非分区表
－－分区表
这个也是分区表的优势，可以针对某个分区的索引进行重建。
删除整个分区数据－－非分区表
－－分区表
个也是分区表的优势，可以对某个分区直接truncate
全表扫描－－非分区表
－－分区表
全扫描时分区表落后，但还基本上能接收。
增加新的分区并导入数据－－生成新的分区数据
－－建立新的分区
－－非分区表
－－分区表
新的分区数据导入保持优势
到此这篇关于PostgreSQL 10分区表详解及性能测试报告的文章就介绍到这了,更多相关PostgreSQL 10分区表性能测试内容请搜索七叶笔记以前的文章或继续浏览下面的相关文章希望大家以后多多支持七叶笔记！
