Postgresql日常使用规范

Postgresql概述

PostgreSQL数据库是目前功能最强大的开源数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。而且它提供了丰富的接口，可以很容易地扩展它的功能，如可以在GiST框架下实现自己的索引类型等，它还支持使用C语言写自定义函数、触发器，也支持使用流行的语言写自定义函数，比如其中的PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Tcl，等等。

一、 Postgresql 介绍

1. Postgresql简介

PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES， 版本 4.21为基础的对象关系型数据库管理系统（ORDBMS）。POSTGRES 领先的许多概念在很久以后才出现在一些商业 数据库系统中。

PostgreSQL是最初的伯克利代码的开源继承者。它支持大部分 SQL 标准并且提供了许多现代 特性：

• 复杂查询

• 外键

• 触发器

• 可更新视图

• 事务完整性

• 多版本并发控制

同样，PostgreSQL可以用许多方法扩展，比如， 通过增加新的：

• 数据类型

• 函数

• 操作符

• 聚集函数

• 索引方法

• 过程语言

PostgreSQL是一个功能强大的开源对象-关系数据库系统。它支持几乎所有的SQL标准，并且提供了许多其他现代数据库系统所没有的高级功能，如复杂查询、外键、触发器、事务完整性、多版本并发控制等。

PostgreSQL支持标准的SQL语言并加入了很多其他的功能以确保数据能够安全存储，根据数据负载能够灵活扩展。它兼容所有的主流操作系统，除SQL的基本类型外还支持JSON、Key-value等数据类型，在数据一致性、高并发、高可用、数据恢复、数据安全等方面都有极为出色的表现，并且还有很多类似PostGIS这样的强大插件。PostgreSQL的上述强大特性为其在世界范围内赢得了很高的赞誉，也成为了很多开发者和机构首选的开源关系数据库系统。

2. Postgresql 优势

2.1.支持关系型和非关系型数据

PostgreSQL不仅可以做关系型数据库使用，还可以支持非关系型数据的存储，如KV存储、JSON等格式的数据。这使得PostgreSQL可以支持更加丰富和复杂的应用场景。PostgreSQL不仅支持传统的关系型存储，还可以存储和查询非关系型数据，如JSON格式的数据，这个队列在处理碳水化合物的数据类型时非常灵活。

2.2.强大的SQL功能

PostgreSQL支持功能强大的SQL，尤其在子查询、窗口函数等方面，要支持MySQL等数据库。这对于一些复杂的查询场景非常有用。 PostgreSQL提供了广泛而强大的SQL支持，包括复杂的查询、窗口函数、子查询等功能。这使得它能够处理复杂的数据操作需求。

2.3.良好的扩展性

PostgreSQL支持通过扩展的方式增加数据类型、函数等，使用非常灵活。这也使得PostgreSQL可以进行二次开发，实现定制化的需求。 PostgreSQL的扩展性非常出色，用户可以通过编写自定义的数据类型、函数和操作符来满足其特定的需求。这种扩展性为二次开发和定制提供了广泛的空间。

多版本并发控制（MVCC）：通过MVCC机制，PostgreSQL实现了高效的并发控制，减少了锁争用，提高了数据库的吞吐量。

并行查询：支持并行查询执行，可以显著提高复杂查询的执行速度。

集群和分片：通过扩展如Citus，PostgreSQL可以实现水平扩展，支持大规模数据集和高并发负载。

2.4.强大的兼容性

PostgreSQL在兼容性方面表现出色，能够无缝集成到各种技术栈和环境中：

SQL标准兼容性：PostgreSQL严格遵循SQL标准，确保应用程序的可移植性。

支持多种编程语言：提供了丰富的编程接口（API），支持包括Python、Java、C++等在内的多种编程语言。

跨平台支持：可以在Linux、Windows、macOS等多种操作系统上运行。

2.5. 稳定性和可靠性

PostgreSQL以其卓越的稳定性和可靠性闻名。PostgreSQL支持事务日志和数据恢复机制，能够在系统故障时进行快速恢复，确保数据的安全性和可用性。

二、技术规范

1.版本规范

目前百胜运维团队支持对如下Postgresql版本的交付与维护工作：

• Postgresql 11.xx

• Postgresql 9.XX

• 支持更高版本至16.XX

2.通用命令规范

DB object：database， schema， table， column， view， index， sequence， function， trigger 等名称：

• 使用小写字母、数字、下划线的组合。多个单词可以通过下划线来分割，一个名称建议不超过30个字符。注意PostgreSQL中名称最长长度不能超过63个字符。
• 不使用双引号即双引号包围的名称。这种用双引号中可以包含空格以及一些特殊字符，会带来管理上和安全上的麻烦。
• 禁止以 pg_ 开头（避免与系统 DB object 混淆），不建议以数字开头。
• 禁止使用 SQL 关键字，如 type， order 等：

www.postgresql.org/docs/14/sta…

3. 索引的命名要求

index命名规则为: idx_表名_列名,如idx_device_dep_id

4. 临时对象及备份的对象命名规则

临时对象以“_tmp”开头。

备份的 DB object：table、view 等以“_bak”结尾，建议加上日期， 如 app01.product_summay_bak_2023_05_19。

5. 序列对象命名规则

序列以名称“seq_”开头。

6. 函数对象命名规则

函数以名称“fun_”开头。

7. 关于注释（COMMENT)

建议对 DB object 加 注释COMMENT，便于后续了解业务及维护。

对于表（TABLE）和列（ COLUMN）一定要加COMMENT。

注释前后的数据表可读性对比，有注释的一看就明白:

| postgres=# \d+ productTable "public.product"Column | Type | Modifiers | Storage | Stats target | Description--------+---------+-----------+----------+--------------+-------------id | integer | | plain | |mc | text | | extended | |Indexes:"product_id_uidx" UNIQUE， btree (id)Has OIDs: nopostgres=# comment on table product is '产品表';COMMENTpostgres=# comment on column product.id is 'id号';COMMENTpostgres=# comment on column product.mc is '产品名称';COMMENTpostgres=# \d+ productTable "public.product"Column | Type | Modifiers | Storage | Stats target | Description--------+---------+-----------+----------+--------------+-------------id | integer | | plain | | id号mc | text | | extended | | 产品名称Indexes:"product_id_uidx" UNIQUE， btree (id)Has OIDs: no | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

三、设计规范

1.   Schema和用户

应用不要使用名称为public 的schema，应该建一个与应用同名的schema和用户。

2. COLUMN设计

要求如下：

• decimal(m,d)：和numeric类型等价，定点小数使用此 decimal 类型，禁止使用float和double;
• int：所有整数类型的字段只能使用 int 或者 bigint ，数值较大时建议使用 bigint 类型（int范围-21亿~21亿）;
• serial：自增整型使用 serial 或 bigserial;
• date：所有只需要精确到天的字段全部使用 date 类型;
• timestamptz(p)：所有需要精确到时间(时分秒)的字段均使用 timestamptz;
• varchar：所有动态长度字符串全部使用 varchar 类型，根据实际的宽度来选择 varchar(n) 类型的宽度，n表示的是字符数，长度上限为10M；
• char：定长字符串，空格填充，例如char(5)存储'ab'，查询结果为'ab '；
• text：不限长存储字符串，上限为1G，大字段建议使用文件存储，不建议存储在数据库中；
• bytea：不限长存储二进制字符串，上限为2G，大字段建议使用文件存储，不建议存储在数据库中；
• 建议能用数值类型的，就不用字符类型。
• 建议能用 varchar(N) 就不用 char(N)，以利于节省存储空间。
• 建议能用 varchar(N) 就不用 text，varchar。
• 建议使用 default NULL，而不用 default ''，以节省存储空间。
• 建议如有国际化业务的话，使用 timestamp with time zone(timestamptz)，而不用 timestamp without time zone，避免时间函数在对于不同时区的时间点返回值不同，也为业务国际化扫清障碍。
• 建议使用 NUMERIC(precision，scale) 来存储货币金额和其它要求精确计算的数值， 而也不用 real，double precision。也不建议使用money类型。
• 建议使用带时区的时间戳，如timestamp with time zone(timestamptz),而不用不带时区的时间戳，如timestamp without timezone，以避免不同时区下时间的混乱。
• 建议使用jsonb类型而不使用json类型来存储JSON (JavaScript Object Notation) 数据。
• 不建议使用xml类型，如果可能尽量使用jsonb类型来存储多层次的文本数据。
• 建议使用Geometric Types （www.postgresql.org/docs/9.6/st…

3. Constraints 设计

• 建议每个 table 有主键。
• 建议建表时一步到位把主键或者唯一索引也一起建立。

4. Index设计

有以下一些规则：

• PostgreSQL版提供的 index 类型：B-tree，Hash，GiST (Generalized Search Tree)，SP-GiST (space-partitioned GiST)，GIN (Generalized Inverted Index)， BRIN (Block Range Index)，通常情况下使用 B-tree。
• btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。
• 对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。
• 多表JOIN字段需要添加索引。
• ORDER BY，GROUP BY，DISTINCT的字段需要添加索引。
• 尽量使用区分度高的字段做索引，对于选择性比较差的字段上，如类似性别、布尔值类型的列上不要建索引。
• 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。（可以使用 count(distinct left(列名,索引长度)) / (count(*)+0.0) 的区分度来确定。）
• 建议 create 或 drop index 时，加 CONCURRENTLY 参数，达到与写入数据并发的效果。
• 如果索引列被频繁update或 delete，则应该设置此索引的 fillfactor =85或更低的值。
• 建议用 unique index 代替 unique constraints，便于后续维护。
• 建议对 where 中带多个字段 and 条件的高频 query，参考数据分布情况，建多个字段的联合 index。
• 合理创建组合字段索引，避免冗余，符合最左前缀原则：(a,b,c)相当于 (a) 、(a,b)、(a,b,c)。即已经建立了A、B两个字段的索引之后，就不要再建立只有A列的索引，已经建立了A、B、C三个字段的索引后，就不要再建立只有A列的索引以及只有A、B两列的索引。
• 创建组合索引的多个字段，应将选择性高的字段放靠前放。
• 建议对固定条件的（一般有特定业务含义）且选择时数据占比低的 query，建议带 where 的 Partial Indexes：

select * from test where status=1 and col=?; -- 其中status=1为固定的条件create index on test (col) where status=1;

• 建议对经常使用表达式作为查询条件的 query，可以使用表达式或函数索引加速 query：

select * from test where exp(xxx);create index on test ( exp(xxx) );

• 建议单表不要建过多 index，一般不要超过6个，核心 table（产品，订单）可适当增加 index 个数。
• 主键设计控制在20字符以内，超过字符主键需要提出申请并说明原因，唯一索引一个表中最多只有一个，复合索引字段限制在3个及以内，另PG 支持include 索引，可以考虑使用include 的方式加速查询。

5. 表设计规范

需要遵循下列规范：

• 对于单个schema下的的数据表的数量控制在1000个以内
• 多表中的相同列，必须保证列名一致，数据类型一致。
• 用户在设计表结构时，建议规划好，避免经常需要添加字段，或者修改字段类型或长度
• 表的行数超过3千万行后，应该使用分区表，每个分区的行数不要超过3千万行。
• 分区表的子分区数量控制在200内，定期维护分区。
• Postgresql 临时表属于当前sesession连接，当此session连接终止后，临时表会被释放，如需要全局临时表可以使用实体表代替。清理时请使用 truncate table 的方式清理，减少wal log的产生。
• 插入到表中的数据时请使用utf8统一字符编码设计，避免在后续数据库备份、移库等出现一些不必要的麻烦。
• 对于频繁更新的表，建议建表时指定表的fillfactor=85或更低的值，每页预留15%以上的空间给HOT更新使用。
• 表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生。
• 建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表。
• 如果表的字段有计算操作，则必须有默认值，不能设计为空NULL
• 建表时列的顺序，固定长度的列尽量放在前面，这样扫描行时会快一些。
• 建议在表字段中加上插入时间和更新数据字段，便于后期增量抽取数据（CDC）

6. 其它设计规范

有如下要求：

• 严禁出现触发器和DBLINK
• 避免使用视图，如果需要使用视图中，请与DBA协商。
• 当业务有中文分词的查询需求时，建议使用PostgreSQL的分词插件zhparser或jieba，用户还可以通过接口自定义词组。建议在分词字段使用gin索引，提升分词匹配的性能。
• 高并发应用场景使用数据库连接池
• 判空使用is null、is not null，不用=、!=判空
• 大批量的数据入库时，使用copy替代insert，可以提高写入速度
• 在 MyBatis框架中使用SQL语句时，不要写 useGeneratedKeys="true"， 或直接设置 useGeneratedKeys="false"，避免returning *的产生，从而浪费带宽，如果需要返回某个字段，比如id，可以按照如下设置：在StatementHandler为PreparedStatementHandleuseGeneratedKeys=“true”; keyColumn=“id”; keyProperty="id"

四、使用规范

1. 表的使用

有如下一些要求：

• 在使用中对于JOIN 的表的数量控制在3个及3个以内，如必须增加JOIN的表的数量，需要和DBA组商议，表设计中需要注意关联表之间的设计关系，关联字段类型的数据类型必须一致，建议字段名也应一致。
• 应该尽量避免全表扫描(除了大数据量扫描的数据分析)，PostgreSQL支持几乎所有数据类型的索引。
• 千万行级别的表中少使用count(*) 来进行相关的数据处理，必须有条件或走索引扫描计算。
• 在应用设计中，不能频繁固定更新某一个行（如1秒内更新同一行上千次）。
• JAVA在操作PG的时候，和MYSQL的区别在于如果JAVA承接的数据类型是INT(MYSQL)，是可以用JAVA的STRING类型承接，但是POSTGRESQL（INT）类型JAVA是不能用STRING类型承接，必须用整型INT，同时反过来也是一样，所以开发者们请注意，POSTGRESQL是一个严谨性的数据库，在使用pg数据库的时候进行变量的给予和提取，JAVA的变量对应的一定要是相对应数据库的数据类型，和MYSQL的兼容行比较，POSTGRESQL是需要更认真的对待的。
• POSTGRESQL 表操作中，表必须有别名。

2. DDL变更规范

要求如下：

• DDL变更，即对象结构的变更，不限于基础表、视图、索引的变更，改变对换结构的语句都 应注意。
• sql关键字：有create、alter、drop、truncate、vacuum full、cluster等。
• 禁止：禁止在业务高峰时间段对表执行DDL、在没有配置锁超时的情况下执行DDL。DDL被阻塞， 会阻塞后面所有对这个表的操作，造成大量连接堆积、资源消耗，进而造成整个数据库的卡顿。
• 禁止在开发代码中使用 TRUNCATE TABLE 语句。TRUNCATE TABLE 可能会造成生产的性能事故和安全事故。整表数据删除时，TRUNCATE 比 DELETE FROM 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 执行不当有可能造成事故。
• 建议：在变更前必须提交变更申请，描述清楚变更原由、变更内容、变更可能的影响范围等；变更申请审核通过后才能实施，必须在维护窗口期，或在业务低峰期进行，并安排审核人员，验证人员进行业务验证。

3. DCL变更规范

要求如下：

• DCL变更，即对象权限的变更，对象权限的变更，会导致原有对象访问权限的变化，误操作会导致业务出错。
• sql关键字：grant、revoke。
• 禁止：禁止在业务高峰时间段对表执行DCL.
• 建议：在变更前必须提交变更申请，描述清楚变更原由、变更内容、变更可能的影响范围等；变更申请审核通过后才能实施，必须在维护窗口期，或在业务低峰期进行，并安排审核人员，验证人员进行业务验证。

4. 数据库导入和导出规范

要求如下：

• 导入数据的大小限制：如果导入的单个文本文件超过10G，建议按行进行拆分，拆分成不大于10G的多个文件，建议单任务导入；如对导入时间有要求，可根据服务器配置情况，当前业务峰值情况调整，较高的并发导入可能会占用大量IO，影响在线业务。
• 手工导入SQL文件时，建议在SQL文件的开始和结尾分别添加begin;和commit;，避免出现部分SQL执行失败而没有发现的情况（默认会跳过失败的SQL继续执行，刷了很多日志时容易漏看中间的报错）；如果中间的SQL执行失败就会回滚掉之前执行的SQL，后续的SQL会继续执行，但会报错事务回滚，可以通过Ctrl + C提前中断。

五、SQL规范

1. 过滤条件的字段上避免使用函数或表达式

在取出字段上可以使用相关函数,但是在 Where 条件中的过滤条件字段上避免使用任何函数,包括数据类型转换函数，以避免索引失效。或者可以考虑使用表达式索引功能。

2. 禁止使用 select *

禁止使用select 以及select a.，只取所需要的自动，这样可以减少网络带宽消耗，以及当表结构变化后，select *并不报错容易导致的隐蔽逻辑错误。

3. UPDATE和DELETE必须有where条件

禁止没有where条件的UPDATE和DELETE语句。

4. IN 的条件中避免大量的元素

通常应控制在300个以内。

5. OR运算符的要求

用 in/union 替换 or，并注意 in 的个数小于 300。OR计算符有可能导致坏的执行计划，应该尽量避免。核心系统查询中，不允许使用OR 计算符号

6. having子句中不能出现能放在where子句中的条件

如果把能放在where子句中的条件放到了having子句中，会导致SQL执行变慢。

7. 事务要求

要求如下：

• 不允许出现长时间的事务，避免单个事务过大、过长、过于复杂，将单个事务中多条sql操作分解、拆分，或者放到不同的事务里，每个事务粒度尽可能小，这样可以尽量减少资源lock，检查锁阻塞、死锁的产生
• 在事务中，将读SQL放在前方，写SQL放在后方。

8. 避免使用游标

非特殊要求，不要数据库的服务端的游标。

9. 避免在数据库中进行隐式转换

应用应正确处理数据类型，避免在数据库中进行隐式转换。

10. 去掉不必要的is not null过滤条件

去掉不必要的is not null过滤条件，如下面的例子：

SELECT c1, c2 from TABLE01 where c1>100 and c1<1000 and c1 is not null;

可以优化为：

SELECT c1, c2 from TABLE01 where c1>100 and c1<1000;

11. 避免向客户端返回大量数据

向客户端返回大量数据，会消耗应用程序和数据库服务器的大量处理能力，同时也挤占网络带宽。尽量用where条件或limit子句过滤掉部分数据。

12. NULL值的使用规范

    1.   使用IS NULL，IS NOT NULL做判断

不要用=或!=对NULL做判断，要使用IS NULL或IS NOT NULL做判断

2. boolean 类型也可能取NULL值

在数据库中的boolean类型的取值也可能有一个NULL值，并不是只有true和false。

3. 注意 NOT IN 集合中带有 NULL 元素

如下所示:

| postgres=# create table table01(id int, nickname varchar(20));CREATE TABLEpostgres=# insert into table01 values(1, 'hello'),(2, 'world'), (3, 'osdba');INSERT 0 3postgres=# select * from table01;id | nickname----+----------1 | hello2 | world3 | osdba(3 rows)postgres=# select * from table01 where nickname not in ('aaaa', null);id | nickname----+----------(0 rows) | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

4. 对字符串型做 NULL 值处理后再进行 || 操作

在PostgreSQL连接两个字符串时，如果有一个字符串为空，则结果也是为空，所以需要使用coalesce()函数对可能是空值的字段进行处理：

| postgres=# select id，nickname from table01 limit 1;id | nickname----+-------------1 | hello(1 row)postgres=# select id，nickname||null from table01 limit 1;id | ?column?----+----------1 |(1 row)postgres=# select id，nickname||coalesce(null，'') from table01 limit 1;id | ?column?----+-------------1 | hello(1 row) | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

5. 使用 count(1) 或 count(*) 来统计行数

使用 count(1) 或 count(*) 来统计行数，而不建议使用 count(col) 来统计行数，因为 NULL 值不会计入。

---

注意：

count(多列列名) 时，多列列名必须使用括号，例如 count( (col1，col2，col3) )，注意多列的 count，即使所有列都为 NULL，该行也被计数，所以效果与 count(*) 一致：

| postgres=# select * from table01 ;id | nickname----+---------------1 | hello2 | world5 |3 | osdba4 | table01 default(5 rows)postgres=# select count(1) from table01;count-------5(1 row)postgres=# select count(*) from table01;count-------5(1 row)postgres=# select count(nickname) from table01;count-------4(1 row)postgres=# select count((id，nickname)) from table01;count-------5(1 row) | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

6. count(distinct col) 统计某列的不重复数量，NULL 不被计数

count(distinct col) 计算某列的非 NULL 不重复数量，NULL 不被计数。

---

注意：

count(distinct (col1，col2，...) ) 计算多列的唯一值时，NULL 会被计数，同时 NULL 与 NULL 会被认为是相同的。

postgres=# select count(distinct nickname) from table01;count-------3(1 row)postgres=# select count(distinct (id，nickname)) from table01;count-------5(1 row)

7. 比较两个值不能返回NULL的方法

需要注意的是使用=或!=等运算符，是可能返回空的，如果需要只返回true或false，而不要返回NULL值是，则需要使用IS DISTINCT FROM或IS NOT DISTINCT FROM，如下所示：

| postgres=# \pset null '<>'Null display is "<>".postgres=# select a.a, b.b, a.a IS DISTINCT FROM b.b AS "Is Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES (1), (2), (NULL)) AS b (b);a | b | Is Distinct From----------+----------+------------------1 | 1 | f1 | 2 | t1 | <> | t2 | 1 | t2 | 2 | f2 | <> | t<> | 1 | t<> | 2 | t<> | <> | f(9 rows)postgres=# select a.a, b.b, a.a IS NOT DISTINCT FROM b.b AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES (1), (2), (NULL)) AS b (b);a | b | Is Not Distinct From----------+----------+----------------------1 | 1 | t1 | 2 | f1 | <> | f2 | 1 | f2 | 2 | t2 | <> | f<> | 1 | f<> | 2 | f<> | <> | t(9 rows) | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

从上面看出，即使比较的值中有NULL，IS DISTINCT FROM和IS NOT DISTINCT FROM返回的结果中是没有NULL值的，而如果我们用“=”做比较，则比较的值中有一个是NULL，就会返回NULL值，如下所示：

| postgres=# \pset null '<>'Null display is "<>".postgres=# select a.a, b.b, a.a = b.b AS "Is equal" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES (1), (2), (NULL)) AS b (b);a | b | Is equal----------+----------+------------------1 | 1 | t1 | 2 | f1 | <> | <>2 | 1 | f2 | 2 | t2 | <> | <><> | 1 | <><> | 2 | <><> | <> | <>(9 rows) | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

六、Postgresql12-16版本新特性概述

PostgreSQL 12-16新特性概述
PostgreSQL 16提升了vacuum的性能。
PostgreSQL 16在视图pg_stat_tables和pg_stat_indexes中增加了last_seq_scan和last_idx_scan，可以方便监控不活跃索引。
PostgreSQL 16支持在备库进行逻辑解码，解决了循环赋值的问题。
PostgreSQL 15改进了增量排序。
PostgreSQL 15的wal_commpression增加了pglz、lz4、zstd的压缩算法，可以减少WAL日志量，又可以降低对CPU的占用。
PostgreSQL 15的pg_basebackup支持了服务端压缩的功能，减少了备份时对网络带宽的占用。
PostgreSQL 15提供了merge into语言，使用此语法比用一些其它的方法的性能要好一些。
PostgreSQL 15逻辑复制，支持在发布时加where子句做过滤。
PostgreSQL 15逻辑复制出现冲突后解决的命令有一些麻烦， PostgreSQL 15提供了alter subscription my_sub skip (lsn='0/87673BD70';
PostgreSQL14新特性：海量连接性能基本不下降。这对于copon有大量的数据库连接还是有一定的意义的。	www.pgsql.tech/article_101…
PostgreSQL 14之前在创建索引过程中，vacuum垃圾清理会停止工作，导致表碰撞。PostgreSQL 14解决了这个问题：当create index concurrently时, 只要不是表达式索引, partial index, 不是rc或ssi隔离级别, 那么这个操作的snapshot xmin就不会用做计算oldestxmin，从而它运行多长时间都不会导致vacuum无法回收某些垃圾而导致膨胀	www.pgsql.tech/article_101…
PostgreSQL 14可以通过reindex concurrently 命令把索引在线的移动到另一个表空间中
PostgreSQL 14 Bree索引的性能增强了，频繁更新时Btree索引减少了膨胀
PostgreSQL 14 BRIN 索引支持多区间和布隆过滤器
PostgreSQL 14更小更块的GiST 索引，SP-GiST 索引也支持覆盖索引（index only scan）
PostgreSQL 14支持流式的逻辑复制，减少了大事务的延迟。
PostgreSQL 14支持idel_in_transaction_session_timeout、idel_session_timeout、client_connection_check_interval，lock_timeout、statement_timeout这些参数
PostgreSQL 13开始索引内部项有去重功能，让索引更小了.
PostgreSQL 13增量排序（Incremental sort）的功能:即如果索引只在列col1上，如果我们的查询的order by有两个列col1，col2， 也能走到这个索引。
PostgreSQL 13的hash聚合可以使用磁盘做溢出存储。以前如果hash agg需要的内存超过了work_mem参数的大小时，就只能走sort agg，SQL会慢一倍以上，到PostgreSQL 13可以走到hash agg了，通常会比原先块一倍以上。
PostgreSQL 13并行vacuum功能(主要是针对索引），可以加速copon大表的vacuum。
PostgreSQL 13改变流复制的配置无需重启，特别是在改变备库的流复制的上级库的关系时非常有用。
PostgreSQL 13可以看到某个SQL产生的WAL日志量
PostgreSQL 13提供了工具pg_verifybackup检验备份的完整性。
PostgreSQL 12之后分区表的性能得到了提升。原先一张表分区（上百个分区）过多了，执行SQL的生成执行计划的过程太慢（可能达到几个毫秒），PostgreSQL 12之后ALTER TABLE ATTACH PARTITION不会阻塞查询。
PostgreSQL 12之前，如果vacuum和analyze遇到锁会一直等待，而12版本之后增加skip_locked选项，可以跳过锁住的表，这样不影响其它表的vacuum。
PostgreSQL12之后支持在线重建索引REINDEX CONCURRENTLY，之前需要在同列上新建一个索引，然后再删除索引，有一些麻烦。
PostgreSQL 12的VACUUM增加了选项TRUNCATE，有可能不需要vacuum full也能释放部分空间到操作系统。这一点对于copon是很有意思的。原理是表的数据文件如果中间有一段空间（至少是一个数据块是空的），可以利用稀疏文件的原理释放空间，当然查看文件的大小还是没有变，但是空间释放给文件系统了。
PostgreSQL 12之前不能把where条件下推到CTE中，12版本之后可以where条件下推到CTE中，提升了性能。