01.分区表性能PostgreSQL很早就支持分区表。10.0之前，分区表需要用户通过继承手动创建。从10.0开始支持声明式分区，即可以直接通过SQL创建分区表，提高了分区表的易用性；11、支持HASH分区，在规划和执行阶段，增强分区剪枝策略，提高分区表的查询性能；PostgreSQL12进一步提升了分区表的查询和数据导入性能，尤其是对于分区数量较多的场景，查询优化效果尤为显着。在阿里云上创建两个相同规格（4c8g）的RDSPostgreSQL11和12实例，测试不同分区号下使用COPY导入1亿行数据的性能对比如下。可以看出，随着分区数的增加，导入性能始终优于PostgreSQL11。COPY导入数据的性能提升是因为12支持分区表的批量插入。在此之前，只有一行一次得到支持。对于查询操作，在PostgreSQL10中，会依次检查每个分区表，以确定它可能有满足条件的数据。每个分区表的处理与普通表类似；定位需要提早访问的分区；PostgreSQL12进一步推进分区剪枝功能，避免为每个分区加载元数据并生成相应的内部结构，使得查询计划耗时，并进一步与不相关的分区解耦。可以看出，优化与查询条件的分区过滤性有关。分区过滤性越好，需要处理的分区越少，优化效果越好。不同分区号下分区键（也是主键）的查询性能对比如下。可以看到，分区越多，PostgreSQL12的性能提升越明显，高达150倍。随着分区数量的增加，PostgreSQL12的性能保持相对稳定。虽然分区表的性能有了很大的提升，但是和单表相比，在很多场景下性能还是有一定的差距。在设计表结构的时候，还是要根据实际业务场景来选择是否分区以及分区个数。02.索引增强B树索引广泛应用于数据库系统，可以有效减少查询需要访问的数据量，提高查询性能。索引是一种“以空间换时间”的查询优化策略。它还占用一些存储空间，其性能对查询也至关重要。PostgreSQL12提高了标准B树的整体性能并减少了磁盘空间使用。对于复合索引，其空间占用最多可减少40%，可有效节省用户的磁盘空间；对于有重复的B-tree索引，它的性能也得到了提升。另外引入REINDEXCONCURRENTLY命令，让用户可以在没有业务意识的情况下重建索引。下面通过测试直观感受下B树索引的空间使用优化。在PostgreSQL11和12分别创建如下表和索引，插入2000万行数据，VACUUM更新统计。*请请左右左右阅览createtablefoo（aidBigintNotNull，bidbigintnotnull）;Altertablefoodcondconstraintfoo_pkeyprimarykey（aid，bid）;createIndexfoo_bid_idxonfoo（bid）foo_bid_idx索引在两个PostgreSQL版本中的大小如下：*请左右滚动阅读#PostgreSQL11postgres=>\di+foo_bid_idxListofrelationsSchema|Name|Type|Owner|Table|Persistence|Size|Description--------+--------------+--------+------------+--------+------------+--------+------------public|foo_bid_idx|index|postgres|foo|permanent|544MB|(1row)#PostgreSQL12postgres=>\di+foo_bid_idxListofrelationsSchema|Name|Type|Owner|Table|Persistence|Size|Description------+------------+-------+------------+--------+------------+--------+-------------public|foo_bid_idx|index|postgres|foo|permanent|408MB|(1row)可以看出PostgreSQL11的索引比PostgreSQL12大了33%。在有很多独立的场景xes，这么大的空间节省还是很可敬的。除了B-tree索引，其他索引也得到了增强。例如，减少GiST、GIN、SP-GiST索引生成WAL日志的开销，支持用GiST创建覆盖索引，支持用SP-GiST索引的距离算子执行K-NN查询等03.支持SQL/JSON路径语言（pathlanguage）PostgreSQL在之前的版本中已经支持JSON数据类型，支持对简单JSON数据的查询操作。如果JSON数据比较复杂，比如嵌套较多，包括数组等，无法方便查询取值，往往需要依赖外部插件，比如支持SQL/JSON路径语言的jsquery插件。PostgreSQL12进一步支持非结构化数据。内置支持SQL2016标准引入的JSON特性和丰富的路径查询方式，引入新的数据类型jsonpath来表示路径表达式（pathexpression），支持对JSON的各种复杂查询，不再依赖插件。具体使用方法请参考文档，这里不再赘述。04.参数控制Preparedplan对于PREPARE语句的重复执行，PostgreSQL会缓存其执行计划。在执行PREPARE语句时，PostgreSQL会自动选择是重新生成一个新的计划（通常称为自定义计划，customplan），还是使用一个缓存的计划（genericplan），但是在特定的场景下，数据库的选择可能不是最佳的。PostgreSQL12为用户提供了一个参数plan_cache_mode来选择独立使用哪个计划。例如，如果查询参数总是固定的，你可以明确地设置这个参数，让优化器总是使用一个通用的计划来避免SQL解析和重放。写入成本以优化查询性能。执行PREPARE并运行，前5次使用自定义计划：*请左右滑动阅读postgres=>preparep(integer)asselectaidfromfoowhereaid=$1;PREPAREpostgres=>EXPLAINEXECUTEp(1);QUERYPLAN---------------------------------------------------------------------IndexOnlyScanusingfoo_pkeyonfoo(cost=0.44..1.56rows=1width=8)IndexCond:(aid=1)(2rows)#后面四次执行的结果执行第六次时此处省略使用总计划，如下：*请左右滑动阅读postgres=>EXPLAINEXECUTEp(1);QUERYPLAN--------------------------------------------------------------------IndexOnlyScanusingfoo_pkeyonfoo(cost=0.44..1.56rows=1width=8)IndexCond:(aid=$1)(2rows)重新执行PREPARE，并将plan_cache_mode设置为force_generic_plan，观察plan的使用情况，可以看出总计划将在第一次执行时使用，无需等待第六次执行。*请左右滑动阅读postgres=>DEALLOCATEp;DEALLOCATEpostgres=>preparep(integer)asseselectaidfromfoowhereaid=$1;PREPARE#plan_cache_mode设置为force_generic_planpostgres=>setplan_cache_mode=force_generic_plan;SETpostgres=>EXPLAINEXECUTEp(1);------ULAN------------------------------------------------------------IndexOnlyScanusingfoo_pkeyonfoo(cost=0.44..1.56rows=1width=8)IndexCond:(aid=$1)(2rows)是否使用通用计划能否通过执行计划中的变量是通过参数化来判断的。05.可插拔的表存储接口长期以来，PosgreSQL只支持堆表作为存储引擎，其实现更多地与其他模块耦合。PostgreSQL12借鉴了自身索引的可扩展实现，抽象出一层存储引擎访问接口，为后续支持多种存储引擎打下基础，如ZHeap、列存储、K/V存储、内存引擎等。可插拔表存储访问接口的架构如下。在原有架构的基础上，增加表访问管理层（TableAccessManager），提供统一的表访问接口。不同的存储引擎只需要实现这个接口就可以访问。目前存储引擎还只支持Heap表，相信在不久的将来会支持更多的存储引擎。有兴趣的读者也可以尝试自己实现一个存储引擎。*请左右滑动浏览postgres=>select*frompg_am;oid|amname|amhandler|amtype------+--------+-----------------------+--------2|heap|heap_tableam_handler|t403|btree|bthandler|i405|hash|hashhandler|i783|gist|gisthandler|i2742|gin|ginhandler|i4000|spgist|spghandler|i3580|brin|brinhandler|i(7rows)06。丰富的插件支持阿里云RDSPostgreSQL12提供了更丰富的插件支持，以满足用户在一些垂直领域和特殊场景下的需求。下面介绍一些比较常用的，比较有意思的插件，更多支持的插件请参考PostgreSQL的支持插件列表。?roaringbitmap使用roaringbitmap作为内置数据类型，提供丰富的函数支持，使用RoaringBitmap算法，大大提高了位图计算性能。?RDKit支持mol数据类型（描述分子类型）和fp数据类型（描述分子指纹），支持化学分子计算和化学分子搜索等功能。?Ganos阿里云自研时空数据引擎，支持时空数据的高效存储、索引、查询、分析和计算。?PASE高性能向量检索插件，采用业界成熟、稳定、高效的ANN（近似最近邻）检索算法，包括IVFFlat和HNSW算法，通过这两种算法，可以在PostgreSQL数据库中实现极高速的向量查询.?zhparser中文分词插件，帮助实现中文全文搜索。?oss_fdw这个插件可以从OSS加载数据到PostgreSQL，也支持从PostgreSQL写数据到OSS。07.总结RDSPostgreSQL12在功能和性能上都有很大提升，包括分区表查询性能优化、B-tree索引空间优化和性能提升、参数模式选择Prepare语句执行计划、内置全功能SQL/JSONPath语言和更丰富的插件支持。可插拔的表访问接口作为未来支持多种存储引擎的基础，意义重大。目前只支持heaptables，用户暂时意识不到。除了本文介绍的特性外，该版本还有很多其他的特性，比如多列MCV（Most-Common-Value）统计、内联CTE（Commontableexpressions）等，本文没有介绍.有兴趣的读者可以参考相关文献，点击阅读原版阿里云购买实例进行体验。08.参考文献https://www.postgresql.org/about/press/presskit12/https://www.postgresql.org/docs/12/release-12.htmlhttps://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATHhttps://www.postgresql.org/docs/12/tableam.html
