首页
解决方案 ＋
数据库专业技术服务全栈式PostgreSQL解决方案Oracle分布式存储化数据库云PolarDB一体化解决方案
产品 ＋
CLup:PostgreSQL高可用集群平台 CMiner: PostgreSQL中的CDC CData高性能数据库云一体机 CBackup数据库备份恢复云平台 CPDA高性能双子星数据库机 CSYun超融合虚拟机产品 ZQPool数据库连接池 ConshGuard数据保护产品
文档
文章
客户及伙伴
中启开源
关于我们 ＋
公司简介 联系我们
中启开源

PostgreSQL10~13版本以来的新特性史上最全总结

编辑

1. 前言

中启乘数科技是一家专业的PostgreSQL和Greenplum数据库服务提供商，专注于极致的性能。笔者感受很多一些用户生产系统还在使用PostgreSQL 9.X的版本，对PostgreSQL 10版本后的特性有一些了解，但了解不是太全，这篇文章给大家一个全面的介绍。

2. 分区表的改进

PostgreSQL 10 实现了声明式分区，PostgtreSQL 11完善了功能，PostgreSQL 12提升了性能。我们知道在PostgreSQL 9.X时代需要通过表继承实现分区，这时还需要手工加触发器或规则把新插入的数据重新定向到具体的分区中，从PostgreSQL 10之后不需要这样了，直接用声明式分区就可以了，语法如下：

  1. CREATE TABLE measurement (
  2.     city_id         int not null,
  3.     logdate         date not null,
  4.     peaktemp        int,
  5.     unitsales       int
  6. ) PARTITION BY RANGE (logdate);
  8. CREATE TABLE measurement_y2006m02 PARTITION OF measurement
  9.     FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

分区表更具体的一些变化如下：

3. PostgreSQL 10版本的功能增强

3.1 PostgreSQL 10 新功能总结如下：

　3.2 并行查询功能：

3.3 逻辑复制功能

逻辑解码实际上是在PostgreSQL 9.4开始准备的功能，在9.X时代，支持内置了逻辑解码的功能，如果要做两个数据库之间表数据的逻辑同步，需要自己写程序或使用一些开源的软件来实现。到PostgreSQL 10版本，原生提供了逻辑复制的功能，实现了逻辑发布和订阅的功能，逻辑复制的功能变化如下：

不过PostgreSQL自带的逻辑复制功能有以下限制：

实际上中启乘数科技开发的有商业版的逻辑复制软件CMiner，解决了以上问题。CMiner本身是一个独立的程序，连接到主库上通过流复制协议拉取WAL日志，然后在本地解码，不会消耗主库的CPU，也不使用逻辑复制槽，没有把主库空间撑爆的风险，也可以方便的支持基于流复制的高可用方案，同时wal_level级别不需要设置为logical就可以完成解码。目前这套解决方案已经在银行中使用，有兴趣同学可以加微信 osdba0，或邮件 services@csudata.com 。

3.4 相关列上建组合的直方图统计信息

用实例说明这个功能：

  1. create table test_t( a int4, b int4);
  2. insert into  test_t(a,b)  select n%100,n%100  from generate_series(1,10000) n;

上面的两个列a和b的数据相关的，即基本是相同的，而PostgreSQL默认计算各列是按非相关来计算了，所以算出的的COST值与实际相差很大：

  1. osdba=# explain analyze select * from test_t where a=1 and b=1;
  2.                        QUERY PLAN
  3. ----------------------------------------------------------------------------------------------------
  4.  Seq Scan on test_t (cost=0.00..195.00 rows=1 width=8) (actual time=0.034..0.896 rows=100 loops=1)
  5.   Filter: ((a = 1) AND (b = 1))
  6.   Rows Removed by Filter: 9900
  7.  Planning Time: 0.185 ms
  8.  Execution Time: 0.916 ms

如上面，估计出只返回1行，实际返回100行。这在一些 复杂SQL中会导致错误的执行计划。

这时我们可以在相关列上建组合的直方图统计信息：

  1. osdba=# CREATE STATISTICS stts_test_t ON a, b FROM test_t;
  2. CREATE STATISTICS
  3. osdba=# analyze test_t;
  4. ANALYZE
  5. osdba=# explain analyze select  *  from  test_t  where a=1  and b=1;
  6.                                               QUERY PLAN
  7. ------------------------------------------------------------------------------------------------------
  8.  Seq Scan on test_t  (cost=0.00..195.00 rows=100 width=8) (actual time=0.012..0.830 rows=100 loops=1)
  9.    Filter: ((a = 1) AND (b = 1))
  10.    Rows Removed by Filter: 9900
  11.  Planning Time: 0.127 ms
  12.  Execution Time: 0.848 ms
  13. (5 rows)

从上面可以看出当我们建了相关列上建组合的直方图统计信息后，执行计划中估计的函数与实际一致了。

3.5 一些其它功能

hash索引从PostgreSQL 10开始可以放心大胆的使用：

到PostgreSQL 10之后，很多函数都进行了改名，其中把函数名中的“xlog”都改成了“wal”，把“position”都改成了“lsn”：

PostgreSQL 10对一些目录也改名：

PostgreSQL 9.X，同步复制只能支持一个同步的备库，PostgtreSQL 10 可以支持多个同步的standby，这称为“Quorum Commit”，同步复制的配置发生如下变化：

索引的增强：

串行隔离级别 预加锁阈值可控

PostgreSQL 10提供了视图pg_hba_file_rules方便查询访问控制的黑白名单：

  1. osdba=# select * from pg_hba_file_rules;
  2.  line_number | type  |   database    | user_name | address | netmask | auth_method | options | error
  3. -------------+-------+---------------+-----------+---------+---------+-------------+---------+-------
  4.           80 | local | {all}         | {all}     |         |         | peer        |         |
  5.           83 | host  | {all}         | {all}     | 0.0.0.0 | 0.0.0.0 | md5         |         |
  6.           88 | local | {replication} | {all}     |         |         | peer        |         |

psql增加了：\if, \elif, \else, and \endif.

  1. SELECT
  3.   EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
  5.   EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
  7. \gset
  9. \if :is_customer
  11.   SELECT * FROM customer WHERE customer_id = 123;
  13. \elif :is_employee
  15.   \echo 'is not a customer but is an employee'
  17.   SELECT * FROM employee WHERE employee_id = 456;
  19. \else
  21.   \if yes
  23.     \echo 'not a customer or employee'
  25.   \else
  27.     \echo 'this will never print'
  29.   \endif
  31. \endif

其它的一些功能：

  1. CREATE TABLE test01 (
  3.    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  5.    t text
  7. );
  1. postgres=# select '{"a": 1, "b": 2, "c":3}'::jsonb - '{a,c}'::text[];
  3.  ?column?
  5. \----------
  7.  {"b": 2}
  9. (1 row)

4. PostgreSQL 11版本的新特性

4.1 PostgreSQL 11版本的功能总结

总结如下:

4.2 PostgreSQL 11版本的jit

即时编译功能：

4.3 PostgtreSQL一些其它增强

新的变化：

PostgreSQL 11版本的一些新特性

PostgreSQL 10、11增加了一些 系统角色，方便监控用户的权限：

PostgreSQL 11 版本的psql中增加了命令\gdesc可以查看执行结果的数据类型：

  1. osdba=# select * from test01 \gdesc
  2.  Column |  Type
  3. --------+---------
  4.  id     | integer
  5.  id2    | integer
  6.  t      | text
  7. (3 rows)

PostgreSQL 11版本psql增加了五个变量更容易查询SQL执行失败的原因：

使用示例如下：

  1. osdba=# select * from test01;
  2.  id |  t
  3. ----+-----
  4.   1 | 111
  5.   2 | 222
  6. (2 rows)
  8. osdba=# \echo :ERROR
  9. false
  10. osdba=# \echo :SQLSTATE
  11. 00000
  12. osdba=# \echo :ROW_COUNT
  13. 2
  14. osdba=# select * from test02;
  15. ERROR:  relation "test02" does not exist
  16. LINE 1: select * from test02;
  17.                       ^
  18. osdba=# \echo :ERROR
  19. true
  20. osdba=# \echo :SQLSTATE
  21. 42P01
  22. osdba=# \echo :LAST_ERROR_MESSAGE
  23. relation "test02" does not exist
  24. osdba=# \echo :LAST_ERROR_SQLSTATE
  25. 42P01

5. PostgreSQL 12版本的新特性

5.1 新特性总结

特性如下：

5.2 对VACUUM的增强：

  1. osdba=# \h vacuum
  2. Command:     VACUUM
  3. Description: garbage-collect and optionally analyze a database
  4. Syntax:
  5. VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
  6. VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
  8. where option can be one of:
  10.     FULL [ boolean ]
  11.     FREEZE [ boolean ]
  12.     VERBOSE [ boolean ]
  13.     ANALYZE [ boolean ]
  14.     DISABLE_PAGE_SKIPPING [ boolean ]
  15.     SKIP_LOCKED [ boolean ]
  16.     INDEX_CLEANUP [ boolean ]
  17.     TRUNCATE [ boolean ]
  19. and table_and_columns is:
  21.     table_name [ ( column_name [, ...] ) ]
  23. URL: https://www.postgresql.org/docs/12/sql-vacuum.html

如上所示，增加了一些选项：

其它的一些变化：

6. PostgreSQL 13版本的新特性

6.1 新特性总结

总结如下：

6.2 分区表智能join

6.2.1 不要求分区的范围完全相等

具体可见：advanced partition matching algorithm for partition-wise join

看例子：

  1. create table t1(id int) partition by range(id);
  2. create table t1_p1 partition of t1 for values from (0) to (100);
  3. create table t1_p2 partition of t1 for values from (150) to (200);
  4. create table t2(id int) partition by range(id);
  5. create table t2_p1 partition of t2 for values from (0) to (50);
  6. create table t2_p2 partition of t2 for values from (100) to (175);

然后我们分别在PostgreSQL 12版本和PostgreSQL 13执行下面的SQL:

  1. explain select * from t1, t2 where t1.id=t2.id;

对比如下：
13版本的智能分区JOIN

6.2.2 三个分区表full outer join也智能join

看例子：

  1. create table p (a int) partition by list (a);
  2. create table p1 partition of p for values in (1);
  3. create table p2 partition of p for values in (2);
  4. set enable_partitionwise_join to on;

三个表的full outer join也可以走wise join

6.3 索引消除重复项

PostgreSQL 13中对索引的重复的项做了优化处理，更节省空间。重复的项只存储一次。

看例子：

PG13索引的大小：

  1. postgres=# create table test01(id int, id2 int);
  2. CREATE TABLE
  3. postgres=# insert into test01 select seq, seq / 1000 from generate_series(1, 1000000) as seq;
  4. INSERT 0 1000000
  5. postgres=# create index idx_test01_id2 on test01(id2);
  6. CREATE INDEX
  7. postgres=# \timing
  8. Timing is on.
  9. postgres=# select pg_relation_size('idx_test01_id2');
  10.  pg_relation_size
  11. ------------------
  12.           7340032
  13. (1 row)

如果是PG9.6:

  1. postgres=# select pg_relation_size('idx_test01_id2');
  2.  pg_relation_size
  3. ------------------
  4.          22487040
  5. (1 row)

可以看到索引的大小是以前的三分之一。

索引中去除重复项的原理：

有一些情况可能无法去除重复项：

给索引增加了存储参数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算法来估算唯一值的个数，减少了内存占用。

请看例子：

  1. CREATE TABLE t_agg (x int, y int, z numeric);
  2. INSERT INTO t_agg SELECT id % 2, id % 10000, random() 
  3.     FROM    generate_series(1, 10000000) AS id;
  4. VACUUM ANALYZE;
  5. SET max_parallel_workers_per_gather TO 0;
  6. SET work_mem to '1MB';
  7. explain analyze SELECT x, y, avg(z) FROM t_agg GROUP BY 1,2;

在12.4版本中聚合使用了排序算法，时间花了14.450秒，如下图所示：
PostgreSQL12.4版本中聚合使用了排序算法

而在13版本中，走了hash聚合，时间花了6.186秒，时间缩短了一半还多，如下图所示：

PostgreSQL13版本算法时间缩短显示图

6.5 增量排序（Incremental sort）的功能

官方手册中也有例子：https://www.postgresql.org/docs/13/using-explain.html#USING-EXPLAIN-BASICS

见我们的例子：

  1. create table test01(n1 int, n2 int);
  2. insert into test01 select seq/3, (seq / 97) % 100 from generate_series(1, 4000000) as seq;
  3. create index idx_test01_n1 on test01(n1);
  4. analyze test01;

然后分别在PostgreSQL 12版本和PostgreSQL 13版本下看下面SQL的执行计划和执行时间：

  1. explain analyze select * from test01 order by n1, n2;

可以看到使用了增量排序后，速度更快了。在PG13中为1.447秒，在PG12中为2.015秒:
使用增量排序后的速度变化

6.6 vacumm增加了并行的功能

具体实现是SQL命令vacuum上增加了parallel的选项：

  1. vacuum (parallel 5);

命令行工具vacuumdb增加了选项—parallel=： 

  1. vacuumdb -P 3

主要是实现了对索引的并行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 其它的一些功能增强

增强的功能如下：