PostgreSQL10~14版本以来的新特性史上最全总结
1.前言
中启乘数科技是一家专业的PostgreSQL和Greenplum数据库服务提供商，专注于极致的性能。笔者感受很多一些用户生产系统还在使用PostgreSQL9.X的版本，对PostgreSQL10版本后的特性有一些了解，但了解不是太全，这篇文章给大家一个全面的介绍。
2.分区表的改进PostgreSQL10实现了声明式分区，PostgtreSQL11完善了功能，PostgreSQL12提升了性能。我们知道在PostgreSQL9.X时代需要通过表继承实现分区，这时还需要手工加触发器或规则把新插入的数据重新定向到具体的分区中，从PostgreSQL10之后不需要这样了，直接用声明式分区就可以了，语法如下：
CREATETABLEmeasurement(city_idintnotnull,logdatedatenotnull,peaktempint,unitsalesint)PARTITIONBYRANGE(logdate);CREATETABLEmeasurement_y2006m02PARTITIONOFmeasurementFORVALUESFROM(‘2006-02-01’)TO(‘2006-03-01’);
分区表更具体的一些变化如下：
PostgreSQL11：分区表增加哈希分区。PostgreSQL11：分区表支持创建主键、外键、索引、触发器。PostgreSQL11：分区表支持UPDATE分区键，如果在分区表上创建了一个索引，PostgreSQL自动为每个分区创建具有相同属性的索引。PosgtreSQL11支持为分区表创建一个默认（DEFAULT）的分区。对于PostgreSQL10中的分区表，无法创建引用其他表的外键约束。PostgreSQL11解决了这个限制，可以创建分区表上的外键。在PostgreSQL10中，分区上的索引需要基于各个分区手动创建，而不能基于分区的父表创建索引。PostgreSQL11可以基于分区表创建索引。如果在分区表上创建了一个索引，PostgreSQL自动为每个分区创建具有相同属性的索引。PostgreSQL12后：ALTERTABLEATTACHPARTITION不会阻塞查询。3.PostgreSQL10版本的功能增强3.1PostgreSQL10新功能总结如下：支持同步复制多个standby:QuorumCommitPostgreSQL10开始增加声明式分区PostgreSQL10增加了并行功能PostgreSQL10之后hash索引可以走流复制，从此可以大胆的使用hash索引了PostgreSQL10之后提供了逻辑复制的功能：发布订阅的功能PostgreSQL10可以把多列组合在一起再建直方图，让一些关联列上的执行计划更准确。可以支持同步复制到多个standby，即QuorumCommit以前的密码验证式md5，现在增加了安全级别更高的密码验证的方式：SCRAM-SHA-2563.2并行查询功能：实际上从9.6开始就有并行查询功能，但功能比较弱，到PostgreSQL10版本之后，功能大大增强，后续的每个大版本或多或少都有功能增强。并行的参数max_parallel_workers=16;max_parallel_workers_per_gather=4;min_parallel_table_scan_size：只有表的大小大于此值时才需要并行，默认为8M，可以设置为1G或更大的值。保持与9.X相同的行为，可以关闭并行setmax_parallel_workers_per_gather=0当需要并行时，可以手工设置max_parallel_workers_per_gather的值max_parallel_maintenance_workers3.3逻辑复制功能逻辑解码实际上是在PostgreSQL9.4开始准备的功能，在9.X时代，支持内置了逻辑解码的功能，如果要做两个数据库之间表数据的逻辑同步，需要自己写程序或使用一些开源的软件来实现。到PostgreSQL10版本，原生提供了逻辑复制的功能，实现了逻辑发布和订阅的功能，逻辑复制的功能变化如下：
PostgreSQL10版本不支持truncate的同步，导致在10版本中，作为逻辑同步的表不能做truncate。从PostgreSQL11版本之后可以支持truncate功能。不过PostgreSQL自带的逻辑复制功能有以下限制：
逻辑解码是在主库上完成的，会消耗主库的CPU。必须建逻辑复制槽。但是逻辑复制槽会把主库的WAL给hold住，很多新手配置了逻辑复制，后来停掉了，但是忘记把逻辑复制槽给删除掉，最后把主库空间给撑爆。逻辑复制槽不支持备库，如果使用流复制的高可用方案，主备库切换后，逻辑复制就废了。大事务会在主库中会生成一个临时文件，如果这个事务很大，这个临时文件也很大。需要把wal_level级别设置logical，这会导致更多的WAL日志生成。实际上中启乘数科技开发的有商业版的逻辑复制软件CMiner，解决了以上问题。CMiner本身是一个独立的程序，连接到主库上通过流复制协议拉取WAL日志，然后在本地解码，不会消耗主库的CPU，也不使用逻辑复制槽，没有把主库空间撑爆的风险，也可以方便的支持基于流复制的高可用方案，同时wal_level级别不需要设置为logical就可以完成解码。目前这套解决方案已经在银行中使用，有兴趣同学可以加微信osdba0，或邮件services@csudata.com。
3.4相关列上建组合的直方图统计信息用实例说明这个功能：
createtabletest_t(aint4,bint4);insertintotest_t(a,b)selectn%100,n%100fromgenerate_series(1,10000)n;
上面的两个列a和b的数据相关的，即基本是相同的，而PostgreSQL默认计算各列是按非相关来计算了，所以算出的的COST值与实际相差很大：
osdba=#explainanalyzeselect*fromtest_twherea=1andb=1;QUERYPLAN—————————————————————————————————-SeqScanontest_t(cost=0.00..195.00rows=1width=8)(actualtime=0.034..0.896rows=100loops=1)Filter:((a=1)AND(b=1))RowsRemovedbyFilter:9900PlanningTime:0.185msExecutionTime:0.916ms
如上面，估计出只返回1行，实际返回100行。这在一些复杂SQL中会导致错误的执行计划。
这时我们可以在相关列上建组合的直方图统计信息：
osdba=#CREATESTATISTICSstts_test_tONa,bFROMtest_t;CREATESTATISTICSosdba=#analyzetest_t;ANALYZEosdba=#explainanalyzeselect*fromtest_twherea=1andb=1;QUERYPLAN——————————————————————————————————SeqScanontest_t(cost=0.00..195.00rows=100width=8)(actualtime=0.012..0.830rows=100loops=1)Filter:((a=1)AND(b=1))RowsRemovedbyFilter:9900PlanningTime:0.127msExecutionTime:0.848ms(5rows)
从上面可以看出当我们建了相关列上建组合的直方图统计信息后，执行计划中估计的函数与实际一致了。
3.5一些其它功能hash索引从PostgreSQL10开始可以放心大胆的使用：
PostgreSQL9.X版本hash索引走不了流复制，所以基本没有人用hash索引，即如果用了hash索引，在激活备库时，需要重建hash索引。到PostgreSQL10.X，hash索引可以通过流复制同步到备库，所以没有这个问题了，这是可以大胆的使用hash索引了。到PostgreSQL10之后，很多函数都进行了改名，其中把函数名中的“xlog”都改成了“wal”，把“position”都改成了“lsn”：
pg_current_wal_lsnpg_current_wal_insert_lsnpg_current_wal_flush_lsnpg_walfile_name_offsetpg_walfile_namepg_wal_lsn_diffpg_last_wal_receive_lsnpg_last_wal_replay_lsnpg_is_wal_replay_pausedpg_switch_walpg_wal_replay_pausepg_wal_replay_resumepg_ls_waldirPostgreSQL10对一些目录也改名：
Renamewrite-aheadlogdirectorypg_xlogtopg_walrenametransactionstatusdirectorypg_clogtopg_xactPostgreSQL9.X，同步复制只能支持一个同步的备库，PostgtreSQL10可以支持多个同步的standby，这称为“QuorumCommit”，同步复制的配置发生如下变化：
synchronous_standby_namesFIRSTnum_sync(standby_name[,…])：保持前面几个备库必须与主库保持同步。ANYnum_sync(standby_name[,…])：保证num_sync个备库与主库保持同步。原先的配置：synchronous_standby_names=’stb01,stb02,stb03’实际相当于：synchronous_standby_names=FIRST1(stb01,stb02,stb03)’索引的增强：
BRIN索引增强：BRIN索引增加了存储选项autosummarize，可以自动计算摘要增加了函数brin_summarize_range()和brin_desummarize_range()可以手工为BRIN的指定块建摘要和去除摘要。以前BRIN只有函数brin_summarize_new_values()、gin_clean_pending_list()ImproveaccuracyindeterminingifaBRINindexscanisbeneficial(DavidRowley,EmreHasegeli)INET和CIDR类型上支持建SP-GiST类型的索引在GiST索引的插入和更新可以更高效的重用空间ReducepagelockingduringvacuumingofGINindexes串行隔离级别预加锁阈值可控
max_pred_locks_per_relation：当单个对象的行或者页预加锁数量达到阈值时，升级为对象预加锁。减少内存开销。max_pred_locks_per_page：当单个页内多少条记录被加预加锁时，升级为页预加锁。减少内存开销。PostgreSQL10提供了视图pg_hba_file_rules方便查询访问控制的黑白名单：
osdba=#select*frompg_hba_file_rules;line_number|type|database|user_name|address|netmask|auth_method|options|error————-+——-+—————+———–+———+———+————-+———+——-80|local|{all}|{all}|||peer||83|host|{all}|{all}|0.0.0.0|0.0.0.0|md5||88|local|{replication}|{all}|||peer||
psql增加了：\if,\elif,\else,and\endif.
SELECTEXISTS(SELECT1FROMcustomerWHEREcustomer_id=123)asis_customer,EXISTS(SELECT1FROMemployeeWHEREemployee_id=456)asis_employee\gset\if:is_customerSELECT*FROMcustomerWHEREcustomer_id=123;\elif:is_employee\echo‘isnotacustomerbutisanemployee’SELECT*FROMemployeeWHEREemployee_id=456;\else\ifyes\echo‘notacustomeroremployee’\else\echo‘thiswillneverprint’\endif\endif
其它的一些功能：
提升了聚合函数sum()、avg()、stddev()处理numeric类型的性能AllowhashedaggregationtobeusedwithgroupingsetsImprovesortperformanceofthemacaddrdatatype(BrandurLeach)Addpg_stat_activityreportingoflow-levelwaitstates(MichaelPaquier,RobertHaas,RushabhLathia)Thischangeenablesreportingofnumerouslow-levelwaitconditions,includinglatchwaits,filereads/writes/fsyncs,clientreads/writes,andsynchronousreplication.Showauxiliaryprocesses,backgroundworkers,andwalsenderprocessesinpg_stat_activity(KuntalGhosh,MichaelPaquier)Thissimplifiesmonitoring.Anewcolumnbackend_typeidentifiestheprocesstype.PreventunnecessarycheckpointsandWALarchivingonotherwise-idlesystems(MichaelPaquier)IncreasethemaximumconfigurableWALsegmentsizetoonegigabyte(BeenaEmerson)Addcolumnstopg_stat_replicationtoreportreplicationdelaytimes(ThomasMunro)Thenewcolumnsarewrite_lag,flush_lag,andreplay_lag.AllowspecificationoftherecoverystoppingpointbyLogSequenceNumber(LSN)inrecovery.conf(MichaelPaquier)PreviouslythestoppingpointcouldonlybeselectedbytimestamporXID.ImproveperformanceofhotstandbyreplaywithbettertrackingofAccessExclusivelocks(SimonRiggs,DavidRowley)Speeduptwo-phasecommitrecoveryperformance(StasKelvich,NikhilSontakke,MichaelPaquier)Allowrestrictiverow-levelsecuritypolicies(StephenFrost)AddCREATESEQUENCEAScommandtocreateasequencematchinganintegerdatatype(PeterEisentraut)AllowthespecificationofafunctionnamewithoutargumentsinDDLcommands,ifitisunique(PeterEisentraut)ImprovespeedofVACUUM’sremovaloftrailingemptyheappages(ClaudioFreire,álvaroHerrera)AddfulltextsearchsupportforJSONandJSONB(DmitryDolgov)Thefunctionsts_headline()andto_tsvector()cannowbeusedonthesedatatypes.自增列原先只有用serial和bigserial创建自增列，现在可以标准的语法创建自增列CREATETABLEtest01(idintPRIMARYKEYGENERATEDBYDEFAULTASIDENTITY,ttext);增加减号为jsonb类型的删除某个key的操作符
postgres=#select‘{“a”:1,“b”:2,“c”:3}’::jsonb-‘{a,c}’::text[];?column?-———{“b”:2}(1row)AllowspecificationofmultiplehostnamesoraddressesinlibpqconnectionstringsandURIs(RobertHaas,HeikkiLinnakangas)。libpqwillconnecttothefirstresponsiveserverinthelist.配合连接参数target_session_attrs=read-write，只是只会连接到一个主库上。Allowfile_fdwtoreadfromprogramoutputaswellasfiles(CoreyHuinker,AdamGomaa)Inpostgres_fdw,pushaggregatefunctionstotheremoteserver,whenpossible(JeevanChalke,AshutoshBapat)4.PostgreSQL11版本的新特性4.1PostgreSQL11版本的功能总结
总结如下:
JIT即时编译功能，提升一些批计算如SUM的性能，通常提升在10%左右。存储过程中可以加commit或rollback事物声明式分区表功能大大增强：分区表可以加主键、外键、索引，支持hash分区表CREATEINDEX可以并行增加非空列也是瞬间完成，不需要rewrite表hashjoin支持并行vacuum增强：空闲空间可以更快的被重用，跳过一些没有必要的索引扫描提升了多个并发事务commit的性能逻辑复制支持truncate的同步支持存储过程(CREATEPROCEDURE)，并可以在存储过程中嵌入事务CREATEINDEX使用INCLUDE可以非键值列放到索引中，以便走Coveringindexes而不必回表以前触发toast的压缩都需要插入的数据大于1996个字节时才会触发，这个1996字节是固定的，不能改，现在给表加了存储参数toast_tuple_target，可以设置更新的值就可以触发toast的压缩机制允许在initdb时改变WAL文件的大小，以前是需要重新编译程序才能改变WAL文件的大小现在在WAL日志中会把使用的部分填0，这样可以提高压缩率4.2PostgreSQL11版本的jit即时编译功能：
常用于CPU密集型SQL(分析统计SQL)，执行很快的SQL使用JIT由于产生一定开销，反而可能引起性能下降jit的参数：jit=onjit_provider=‘llvmjit’jit_above_cost=1000004.3PostgtreSQL一些其它增强新的变化：
可以手工调整复制槽的记录的位置：Allowreplicationslotstobeadvancedprogrammatically,ratherthanbeconsumedbysubscribers(PetrJelinek)Thisallowsefficientadvancementofreplicationslotswhenthecontentsdonotneedtobeconsumed.Thisisperformedbypg_replication_slot_advance().以前给表加有默认值的列时需要重写文件，现在不需要了AllowALTERTABLEtoaddacolumnwithanon-nulldefaultwithoutdoingatablerewrite(AndrewDunstan,SergeRielau)Thisisenabledwhenthedefaultvalueisaconstant.PostgreSQL11版本的一些新特性
PostgreSQL11:新增三个默认角色PostgreSQL11:可通过GRNAT权限下放的四个系统函数PostgreSQL11:Initdb/pg_resetwal支持修改WAL文件大小PostgreSQL11:新增非空默认值字段不需要重写–ALTERTABLEtable_nameADDCOLUMNflagtextDEFAULT‘defaultvalues’;PostgreSQL11:IndexsWithIncludeColumnsCREATETABLEt_include(aint4,nametext);CREATEINDEXidx_t_includeONt_includeUSINGBTREE(a)INCLUDE(name);PostgreSQL11:initdb/pg_resetwal支持修改WAL文件大小，以前需要重新编译程序，才能改变。
PostgreSQL10、11增加了一些系统角色，方便监控用户的权限：
PostgreSQL11新增三个默认系统角色，如下：pg_read_server_filespg_write_server_filespg_execute_server_programPostgreSQL10pg_read_all_settingspg_read_all_statspg_stat_scan_tablespg_monitorPostgreSQL9.6只有一个系统角色：pg_signal_backendPostgreSQL11版本的psql中增加了命令\gdesc可以查看执行结果的数据类型：
osdba=#select*fromtest01\gdescColumn|Type——–+———id|integerid2|integert|text(3rows)
PostgreSQL11版本psql增加了五个变量更容易查询SQL执行失败的原因：
ERRORSQLSTATEROW_COUNTLAST_ERROR_MESSAGELAST_ERROR_SQLSTATE使用示例如下：
osdba=#select*fromtest01;id|t—-+—–1|1112|222(2rows)osdba=#\echo:ERRORfalseosdba=#\echo:SQLSTATE00000osdba=#\echo:ROW_COUNT2osdba=#select*fromtest02;ERROR:relation“test02”doesnotexistLINE1:selectfromtest02;^osdba=#\echo:ERRORtrueosdba=#\echo:SQLSTATE42P01osdba=#\echo:LAST_ERROR_MESSAGErelation“test02”doesnotexistosdba=#\echo:LAST_ERROR_SQLSTATE42P015.PostgreSQL12版本的新特性5.1新特性总结 特性如下： osdba=#\hvacuumCommand:VACUUMDescription:garbage-collectandoptionallyanalyzeadatabaseSyntax:VACUUM[(option[,…])][table_and_columns[,…]]VACUUM[FULL][FREEZE][VERBOSE][ANALYZE][table_and_columns[,…]]whereoptioncanbeoneof:FULL[boolean]FREEZE[boolean]VERBOSE[boolean]ANALYZE[boolean]DISABLE_PAGE_SKIPPING[boolean]SKIP_LOCKED[boolean]INDEX_CLEANUP[boolean]TRUNCATE[boolean]andtable_and_columnsis:table_name[(column_name[,…])]URL:https://www.postgresql.org/docs/12/sql-vacuum.html 如上所示，增加了一些选项： 其它的一些变化： 总结如下：
具体可见：advancedpartitionmatchingalgorithmforpartition-wisejoin
看例子：
createtablet1(idint)partitionbyrange(id);createtablet1_p1partitionoft1forvaluesfrom(0)to(100);createtablet1_p2partitionoft1forvaluesfrom(150)to(200);createtablet2(idint)partitionbyrange(id);createtablet2_p1partitionoft2forvaluesfrom(0)to(50);createtablet2_p2partitionoft2forvaluesfrom(100)to(175);
然后我们分别在PostgreSQL12版本和PostgreSQL13执行下面的SQL:
explainselect*fromt1,t2wheret1.id=t2.id;
对比如下：
PostgreSQL13版本和PostgreSQL12版本的分区表范围对比
6.2.2三个分区表fullouterjoin也智能join看例子：
createtablep(aint)partitionbylist(a);createtablep1partitionofpforvaluesin(1);createtablep2partitionofpforvaluesin(2);setenable_partitionwise_jointoon;
PostgreSQL13版本和PostgreSQL12版本的分区表智能join
6.3索引消除重复项PostgreSQL13中对索引的重复的项做了优化处理，更节省空间。重复的项只存储一次。
看例子：
PostgreSQL13索引的大小：
postgres=#createtabletest01(idint,id2int);CREATETABLEpostgres=#insertintotest01selectseq,seq/1000fromgenerate_series(1,1000000)asseq;INSERT01000000postgres=#createindexidx_test01_id2ontest01(id2);CREATEINDEXpostgres=#\timingTimingison.postgres=#selectpg_relation_size(‘idx_test01_id2’);pg_relation_size——————7340032(1row)
如果是PostgreSQL9.6:
postgres=#selectpg_relation_size(‘idx_test01_id2’);pg_relation_size——————22487040(1row)
可以看到索引的大小是以前的三分之一。
索引中去除重复项的原理：
类似倒排索引GIN，一个索引的key值，对应多个物理行。pg_upgrade升级数据库后，需要reindex才能让旧索引使用到此特性有一些情况可能无法去除重复项：
numeric不能使用去重jsonb类型不能使用去重float4和float8不能使用去重INCLUDEindexes不能使用去重text,varchar,andchar类型的索引使用了非确定性排序（nondeterministiccollation)Containertypes(suchascompositetypes,arrays,orrangetypes)cannotusededuplication.给索引增加了存储参数deduplicate_items以支持这个功能。
6.4聚合时使用hash算法可以使用磁盘做溢出存储以前当表特别大时，hash表超过work_mem的内存时，聚合时就走不到hash，只能走排序的算法，而排序聚合比hash聚合通常慢几倍的性能，现在有了用磁盘存储溢出的hash表，聚合的性能大大提高同时增加了参数hash_mem_multiplier，hasn聚合的内存消耗现在变成了work_mem*hash_mem_multiplier，默认此参数hash_mem_multiplier为1，即hash表的大小还是以前的大小
现在使用了HyperLogLog算法来估算唯一值的个数，减少了内存占用。
请看例子：
CREATETABLEt_agg(xint,yint,znumeric);INSERTINTOt_aggSELECTid%2,id%10000,random()FROMgenerate_series(1,10000000)ASid;VACUUMANALYZE;SETmax_parallel_workers_per_gatherTO0;SETwork_memto‘1MB’;explainanalyzeSELECTx,y,avg(z)FROMt_aggGROUPBY1,2;
在12.4版本中聚合使用了排序算法，时间花了14.450秒，如下图所示：
PostgreSQL12.4版本中，聚合使用了排序算法花费的时间图
而在13版本中，走了hash聚合，时间花了6.186秒，时间缩短了一半还多，如下图所示：
PostgreSQL13版本中，走了hash聚合花费的时间图
6.5增量排序（Incrementalsort）的功能官方手册中也有例子：https://www.postgresql.org/docs/13/using-explain.html#USING-EXPLAIN-BASICS
见我们的例子：
createtabletest01(n1int,n2int);insertintotest01selectseq/3,(seq/97)%100fromgenerate_series(1,4000000)asseq;createindexidx_test01_n1ontest01(n1);analyzetest01;
然后分别在PostgreSQL12版本和PostgreSQL13版本下看下面SQL的执行计划和执行时间：
explainanalyzeselect*fromtest01orderbyn1,n2;
可以看到使用了增量排序后，速度更快了。在PG13中为1.447秒，在PG12中为2.015秒:
分别在PostgreSQL12版本和PostgreSQL13版本下看SQL的执行计划和执行时间
6.6vacumm增加了并行的功能具体实现是SQL命令vacuum上增加了parallel的选项：
vacuum(parallel5);
命令行工具vacuumdb增加了选项—parallel=：
vacuumdb-P3
主要是实现了对索引的并行vacuum并行度受到max_parallel_maintenance_workers参数的控制索引的大小至少要大于参数min_parallel_index_scan_size的值(512KB)，才会并行vacuum具体可以见：https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40d964ec997f64227bc0ff5e058dc4a5770a70a9
6.7其它的一些功能增强增强的功能如下：
增加参数autovacuum_vacuum_insert_threshold、autovacuum_vacuum_insert_scale_factor:原先如果对于只有insert的表（appendonlytable）不会触发vacuum，这时会一直累积到aggressivevacuum，这样会导致vacuum太不及时，现在有这个参数，解决了这个不及时的问题。为了实现这个功能在pg_stat_all_tables表中增加了列n_ins_since_vacuum，记录自上一次vacuum以来这个表插入了多少行。https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b07642dbcd8d5de05f0ee1dbb72dd6760dd30436https://news.knowledia.com/US/en/articles/postgresql-v13-new-feature-tuning-autovacuum-on-insert-only-tables-f0a6f5028ecaed253723bab69926a04b45cd3a2freindexdb增加了—jobs，可以建多个数据库连接来并发来重建索引。wal_skip_thresholdSkipWALfornewrelfilenodes,underwal_level=minimal.https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c6b92041d38512a4176ed76ad06f713d2e6c01a8提升了PL/pgSQL中简单表达式的性能，如”x+1”或”x>0”，性能提升大致2倍https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8f59f6b9c0376173a072e4fb7de1edd6a26e6b52effective_io_concurrency参数默认值改为1，与原先一样。如果设置大于1的值，则为实际的并发IO测试发现PostgreSQL在bitmapindexscan时，如果要读入大量堆page，读IO的速度会远低于正常的顺序读，影响性能，这时可以把此值设置大。允许的范围是1到1000，或0表示禁用异步I/O请求。当前这个设置仅影响位图堆扫描jsonb@>https://www.postgresql.org/message-id/12237.1582833074%sss.pgh.pa.usLess-sillyselectivityforJSONBmatchingoperatorshttps://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a80818605e5447b9b846590c3d3fab99060cb53epg_stat_slru查看slru的统计信息https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok%40developmenthttps://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-SLRU-VIEWPostgreSQL14新特性：索引方面的增强7.1频繁更新时Btree数减少了膨胀Btree索引是我们最常用的索引。PostgreSQL14对Btree索引有很多方便的性能增强：
让nbtree和heapam更好的配合，以便更积极的移除因MVCC产生的重复行避免因为多版本的重复行导致的索引块分裂自下向上的索引项删除（bottom-upindexdeletion）：在唯一索引中删除就版本时可以比较索引块的分裂（Deletingolderversionsinuniqueindexestoavoidpagesplits）会把逻辑层面索引想没有改变的标志（”logicallyunchangedindex”hint）下推到索引中，避免没有必要的索引的变更，具体可见：https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9dc718bd，the“extratuples”simpledeletionenhancement increasesthenumberofindextuplesdeletedwithalmostanyworkload thathasLP_DEADbitssetinleafpages.具体信息见：https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d168b666823b6e0bcf60ed19ce24fb5fb91b8ccf总之通过上面的这些优化，在一些频繁更新的场景下，Btree减少了膨胀，提升了性能。
7.2BRIN索引支持多区间和布隆过滤器原先的BRIN索引特别适合边界清晰的堆存储数据,例如BLOCK1到8存储的id范围是100-10000,9到16存储的id范围是100001到200000,检索id=1000时,只需要扫描1到8号数据块。然而经常可能会在固定的值范围内插入了一个非常大的值，导致BRIN索引失去过滤性。为了解决这个问题,PostgreSQL14支持多区间的BRIN，即multirangebrin,1到8号块存储的ID范围可能是1-199,10000-10019,20000-20000,占用5个value(1,199,10000,10019,20000),一个blocks区间存储多少个value取决于values_per_range参数(8到256).当不断插入数据时,这些范围还可以被合并。
见例子：
CREATETABLEt(aint);CREATEINDEXONtUSINGbrin(aint4_minmax_multi_ops(values_per_range=16));
上面的索引子句中int4_minmax_multi_ops(values_per_range=16))就是指定建立的一个多区间的BRIN索引。
更详细的信息可见：
https://www.postgresql.org/message-id/c1138ead-7668-f0e1-0638-c3be3237e812%402ndquadrant.comhttps://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ab596105b55f1d7fbd5a66b66f65227d210b047d在PostgreSQL14实现了基于布隆过滤器的BRIN索引，每个连续heapblocks,存储一个占位bits,被索引字段的hashvalue经过bloomhash填充占位bit，创建的方法如下：：
CREATETABLEt(aint);CREATEINDEXONtUSINGbrin(aint4_bloom_ops(false_positive_rate=0.05,n_distinct_per_range=100));
比较不同类型的BRIN索引的大小：
createindextest_brin_idxonbloom_testusingbrin(id);createindextest_bloom_idxonbloom_testusingbrin(iduuid_bloom_ops);createindextest_btree_idxonbloom_test(id);
大小统计如下：
Schema
Name
Type
Owner
Table
Size
public
test_bloom_idx
index
tomas
bloom_test
12MB
public
test_brin_idx
index
tomas
bloom_test
832kB
public
test_btree_idx
index
tomas
bloom_test
6016MB
7.3更快更小的GiST索引GiST索引现在可以在其构建过程中对数据进行预排序，从而可以更快地创建索引并缩小索引。
目前GiST对point类型实现了预排序。这个功能是通过为point_ops类增加了一个sortsupport的函数来实现的。见下面的例子：
建测试表：
createtablexasselectpoint(random(),random())fromgenerate_series(1,3000000,1);
PostgreSQL13.3中
postgres=#createindexONxusinggist(point);CREATEINDEXTime:49804.780ms(00:49.805)
大小为：223264768
PostreSQL14中：
postgres=#createindexONxusinggist(point);CREATEINDEXTime:2551.954ms(00:02.552)
索引的大小为：148955136
可以看到大小从223M减少到148M，创建时间更是直接从49秒减少到2.5秒，性能提升非常明显。
更具体的信息见：
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16fa9b2b30a357b4aea982bd878ec2e5e002dbcchttps://www.postgresql.org/message-id/1A36620E-CAD8-4267-9067-FB31385E7C0D%40yandex-team.ru7.4SP-GiST索引也支持覆盖索引（indexonlyscan）创建测试表和索引：
createtablestudents(ppoint,addrtext,studenttext);insertintostudentsselectpoint(random(),random()),seq,seqfromgenerate_series(1,1000000,1)asseq;createindexonstudentsusingspgist(p)include(addr,student);analyzetablestudents;
看执行计划：
postgres=#explainanalyzeselectp,addr,studentfromstudentswherep>^‘(0.99999,0.99999)’::point;QUERYPLAN—————————————————————————————————————————————————-IndexOnlyScanusingstudents_p_addr_student_idxonstudents(cost=0.29..4986.28rows=100000width=28)(actualtime=0.212..0.742rows=5loops=1)IndexCond:(p>^‘(0.99999,0.99999)’::point)HeapFetches:0PlanningTime:0.056msExecutionTime:0.759ms(5rows)Time:1.077ms
上面的p>^‘(0.99999,0.99999)’::point是查找在点0.99999,0.99999上面的点。可以看到走到了IndexOnlyScan。
7.5索引的VACUUM方面上的增强PostgreSQL14可以防止长时间创建索引导致VACUUM不能回收垃圾：当createindexconcurrently时,只要不是表达式索引,partialindex,不是rc或ssi隔离级别,那么这个操作的snapshotxmin就不会用做计算oldestxmin，从而它运行多长时间都不会导致vacuum无法回收某些垃圾而导致膨胀，具体可以建：
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d9d076222f5b94a85e0e318339cfc44b8f26022d为了避免每次vacuum都要清理index,PostgreSQL14进行了优化,当vacuum一个table时,如果低于2%的PAGE有deadLP(例如一个表占用了100个page,如果只有2个page里面有deadLP),那么VACUUM将跳过索引，并保留这些索引项。当table中的垃圾行（deadlp）积累到超过2%page时,才需要对索引执行垃圾回收。因为LP只占用4字节,所以不清理也影响不大,但是大幅降低了因对索引的vacuum导致的vacuum负担。目前阈值2%是在代码中写死的,未来也许会支持索引级别配置,或者支持GUC配置，更详细的信息见：
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5100010ee4d5c8ef46619dbd1d17090c627e6d0a7.6可以通过reindex命令把索引移动到另一个表空间中REINDEXcommand增加tablespace选项,支持通过重建索引的方法把索引移动到另一个表空间中。
REINDEX的语法如下：
postgres=#\hreindexCommand:REINDEXDescription:rebuildindexesSyntax:REINDEX[(option[,…])]{INDEX|TABLE|SCHEMA|DATABASE|SYSTEM}[CONCURRENTLY]namewhereoptioncanbeoneof:CONCURRENTLY[boolean]TABLESPACEnew_tablespaceVERBOSE[boolean]URL:https://www.postgresql.org/docs/14/sql-reindex.html
可以看到语法中增加了指定表空间的子句。
7.7reindex命令支持分区表REINDEX支持分区表,用这个命令可以自动重建所有子分区的索引，方便了分区表的管理。
本站文章，未经作者同意，请勿转载，如需转载，请邮件customer@csudata.com.
想要了解更多PostgreSQL数据库的内容可点击中启乘数技术文章网站：文章列表-全部文章
