postgresql分区技术及其优化

本文主要是介绍postgresql分区技术及其优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

　　PostgreSQL 10开始，实现了原生表分区，算是真正意义上进入了支持分区的数据库的圈子。

　　11实现了大量的功能如支持更新分区键、默认分区、自动创建分区索引、外键支持、唯一索引、分区聚合pushdown（enable_partitionwise_aggregate）、哈希分区、动态分区剪除，但这个版本的分区算不上真正的成熟（GA不代表mature），12才算是勉强的成熟，可以广泛用于生产。

创建分区

　　哈希分区的创建：https://blog.csdn.net/ctypyb2002/article/details/104971991

创建分区索引

　　和mysql一样，在pg实现中，只有分区本地索引，没有全局索引，但是社区已经有提案https://www.percona.com/blog/2019/11/20/proposal-for-global-indexes-in-postgresql/，主要是因为绝大部分情况下，分区数量没有成千上万的时候，全局索引搜索的效率远比搜索多个本地索引效率高，主键定义时必须包含分区键。

CREATE INDEX idx_entrus_no_f ON ses_entrust USING btree (entrust_no, init_date); -- 默认在所有分区上创建索引，如果指定了ONLY子句，则不在分区上创建索引，这样做通常无意义

查看分区定义

　　select * from pg_partition_tree('traffic_violations_p');

relid           |parentrelid|isleaf|level|
----------------|-----------|------|-----|
ses_entrust     |           |false |    0|
ses_entrust_1_f |ses_entrust|true  |    1|
ses_entrust_2_f |ses_entrust|true  |    1|
ses_entrust_3_f |ses_entrust|true  |    1|
ses_entrust_4_f |ses_entrust|true  |    1|
ses_entrust_5_f |ses_entrust|true  |    1|
ses_entrust_6_f |ses_entrust|true  |    1|
ses_entrust_7_f |ses_entrust|true  |    1|
ses_entrust_8_f |ses_entrust|true  |    1|
ses_entrust_9_f |ses_entrust|true  |    1|
ses_entrust_10_f|ses_entrust|true  |    1|
ses_entrust_11_f|ses_entrust|true  |    1|
ses_entrust_12_f|ses_entrust|true  |    1|
ses_entrust_13_f|ses_entrust|true  |    1|
ses_entrust_14_f|ses_entrust|true  |    1|
ses_entrust_15_f|ses_entrust|true  |    1|
ses_entrust_16_f|ses_entrust|true  |    1|
ses_entrust_17_f|ses_entrust|true  |    1|
ses_entrust_18_f|ses_entrust|true  |    1|
ses_entrust_19_f|ses_entrust|true  |    1|
ses_entrust_20_f|ses_entrust|true  |    1|
ses_entrust_21_f|ses_entrust|true  |    1|
ses_entrust_22_f|ses_entrust|true  |    1|
ses_entrust_23_f|ses_entrust|true  |    1|
ses_entrust_24_f|ses_entrust|true  |    1|
ses_entrust_25_f|ses_entrust|true  |    1|
ses_entrust_26_f|ses_entrust|true  |    1|
ses_entrust_27_f|ses_entrust|true  |    1|
ses_entrust_28_f|ses_entrust|true  |    1|
ses_entrust_29_f|ses_entrust|true  |    1|
ses_entrust_30_f|ses_entrust|true  |    1|
ses_entrust_31_f|ses_entrust|true  |    1|
ses_entrust_32_f|ses_entrust|true  |    1|

查看分区剪除（pruning）及按分区关联、聚合 

　　讨论分区，不看分区剪除的效果就是耍流氓。相关测试可以参见https://www.cnblogs.com/abclife/p/11647623.html（测试最多基于11版本，因为12版本届时尚未发布，基于13测试的测试见此）。

分区对OLTP的性能影响

　　至少在千万级以下（30个字段、或200字节每行为界），分区意义弊大于利（可能比非分区表增加10%左右响应时间以及cost），当分区只有个位数个时，分区意义也不大。

　　https://www.2ndquadrant.com/en/blog/partition-elimination-postgresql-11/

哈希分区数量的建议

　　推荐为2的倍数。参见：http://www.manongjc.com/article/90533.html。事实上因为分区数量有限，通常成百上千最多了，所以影响有限。

组合分区与多列分区

　　当前，pg暂不支持子分区（组合分区）。 

分区与并行

　　讨论分区，不和并行一起讨论是不完整的。可惜的是，在pg当前的实现中，所有Append下的子计划（subplan）间均不支持并行执行，只支持子计划内的并行执行。

其他

　　从pg 13开始，逻辑复制支持针对分区表。 

这篇关于postgresql分区技术及其优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！