PgSQL · 新版本调研 · 13 Beta 1 初体验
数据库功能相关 数据库运维管理相关 数据库安全性相关 其他亮点
PostgreSQL 13 和PostgreSQL 12相比，相同类型且没有重复值的B树索引，大小相同。 PostgreSQL 13 和PostgreSQL 12相比，相同类型且有大量重复值的B树索引更小。 PostgreSQL 13 和PostgreSQL 12相比，如果索引有大量重复值且执行计划走index only scan 的话，需要读取的索引页更少，效率更高。
text, varchar, char 类型数据，如果使用了特殊的collation，则可能需要大小写和口音不同来定义数据是否相等，无法使用deduplicate 功能。 numeric 类型数据无法使用deduplicate 功能，因为numeric 类型需要结合不同的展示范围才能定义数值是否相等。 jsonb 类型数据无法使用deduplicate 功能，因为jsonb B树操作类型内部使用了numeric 类型。 float4 和float8 类型数据无法使用该优化，因为在这两个类型中-0 和0 数值被认为是相等的。
(1, 5)(1, 2)(2, 9)(2, 1)(2, 5)(3, 3)(3, 7)
(1, 5) (1, 2) (2, 9) (2, 1) (2, 5) (3, 3) (3, 7)===================== (1, 2) (1, 5) (2, 1) (2, 5) (2, 9) (3, 3) (3, 7)抓取相对安全的行数不需要检查之前的排序键进行全排，这里的安全是基于一些代价的考虑。 抓取所有的行，基于之前的排序键上再进行分组排序。
postgres=# create table t (a int, b int, c int);CREATE TABLE postgres=# insert into t select mod(i,10),mod(i,10),i from generate_series(1,10000) s(i);INSERT 10000 postgres=# create index on t (a);CREATE INDEX postgres=# analyze t;ANALYZE postgres=# set enable_incrementalsort = off;SET postgres=# explain analyze select a,b,sum(c) from t group by 1,2 order by 1,2,3 limit 1;QUERY PLAN------------------------------------------------------------------------------------------------------------------------Limit (cost=231.50..231.50 rows=1 width=16) (actual time=2.814..2.815 rows=1 loops=1) -> Sort (cost=231.50..231.75 rows=100 width=16) (actual time=2.813..2.813 rows=1 loops=1)Sort Key: a, b, (sum(c))Sort Method: top-N heapsort Memory: 25kB -> HashAggregate (cost=230.00..231.00 rows=100 width=16) (actual time=2.801..2.804 rows=10 loops=1)Group Key: a, b Peak Memory Usage: 37 kB -> Seq Scan on t (cost=0.00..155.00 rows=10000 width=12) (actual time=0.012..0.951 rows=10000 loops=1) Planning Time: .169 ms Execution Time: 2.858 ms (10 rows)
postgres=# set enable_incrementalsort = on;SET postgres=# explain analyze select a,b,sum(c) from t group by 1,2 order by 1,2,3 limit 1;QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------Limit (cost=133.63..146.52 rows=1 width=16) (actual time=1.177..1.177 rows=1 loops=1) -> Incremental Sort (cost=133.63..1422.16 rows=100 width=16) (actual time=1.176..1.176 rows=1 loops=1)Sort Key: a, b, (sum(c)) Presorted Key: a, bFull-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB -> GroupAggregate (cost=120.65..1417.66 rows=100 width=16) (actual time=0.746..1.158 rows=2 loops=1)Group Key: a, b -> Incremental Sort (cost=120.65..1341.66 rows=10000 width=12) (actual time=0.329..0.944 rows=2001 loops=1)Sort Key: a, b Presorted Key: aFull-sort Groups: 3 Sort Method: quicksort Average Memory: 28kB Peak Memory: 28kB Pre-sorted Groups: 3 Sort Method: quicksort Average Memory: 71kB Peak Memory: 71kB -> Index Scan using t_a_idx on t (cost=0.29..412.65 rows=10000 width=12) (actual time=0.011..0.504 rows=3001 loops=1) Planning Time: .164 ms Execution Time: 1.205 ms (15 rows)支持了带有OR选项或是IN/ANY选项的查询使用扩展的统计信息（通过 CREATE STATISTICS 创建），这样就可以得到更合理的查询规划和性能提升。 支持大数据集查询时，hashagg 使用磁盘存储（enable_hashagg_disk=on）。在之前的版本中，如果hashagg 使用的内存预测要大于work_mem，则不使用hashagg。 对jsonpath的查询增加了.datetime()函数， 它可以将日期或时间的字符串自动转换为对应的PostgreSQL日期或时间数据类型。 支持 gen_random_uuid() 内置函数生成随机的UUID，而不依赖外部插件。
目前仅限于索引，每个索引可以分配一个vacuum worker。 不支持在加上FULL选项后使用。 只有在至少有2个以上索引的表上使用parallel选项才有效。
=================================PG 13 parallel vacuum=============================== postgres=# create table testva(id int,info text);CREATE TABLETime: 2.334 ms postgres=# insert into testva select generate_series(1,1000000),md5(random()::text);INSERT 1000000Time: 1448.098 ms (00:01.448) postgres=# create index idx_testva on testva(id);CREATE INDEXTime: 364.988 ms postgres=# create index idx_testva_info on testva(info);CREATE INDEXTime: 873.416 ms postgres=# vacuum (parallel 4) testva; VACUUMTime: 114.846 ms =================================PG 12 normal vacuum=============================== postgres=# create table testva(id int,info text);CREATE TABLETime: 5.817 ms postgres=# insert into testva select generate_series(1,1000000),md5(random()::text);INSERT 1000000Time: 3023.958 ms (00:03.024) postgres=# create index idx_testva on testva(id);CREATE INDEXTime: 631.632 ms postgres=# create index idx_testva_info on testva(info);CREATE INDEXTime: 1374.849 ms (00:01.375) postgres=# vacuum testva; VACUUMTime: 216.944 msreindexdb 命令增加–jobs 选项，允许用户并行重建索引。 引入了“可信插件”的概念，它允许超级用户指定一个普通用户可以安装的扩展，当然，该用户需要具有CREATE权限。 增强数据库状态的监控，跟踪WAL日志的使用统计、基于流式备份的进度和ANALYZE指令的执行进度。 支持pg_basebackup 命令生成辅助清单文件，可以使用pg_verifybackup 工具来验证备份的完整性。 可以限制为流复制槽所保留的WAL空间。 可以为standby 设置临时流复制槽。 pg_dump 命令新增了–include-foreign-data参数，可以实现在导出数据时导出外部数据封装器所引用的其他服务器上的数据。 pg_rewind 命令不仅可以在宕机后自动恢复，并且可以通过–write-recover-conf选项来配置PostgreSQL备库。支持在目标实例上使用restore_command来获取所需的WAL日志。
用于强大的psql工具和很多PostgreSQL连接驱动的libpq库，现在新增了几项参数用于安全的服务器连接。引入了channel_binding的参数，可以让客户端指定通道绑定作为SCRAM的组成部分， 并且，使用一个含密码保护的TLS证书的客户端现在可以通过sslpassword参数来指定密码。PostgreSQL现在也支持DER算法编码的证书。 PostgreSQL的外部文件封装器postgres_fdw现在也新增了几个参数来实现安全的连接，包括使用基于证书进行身份验证去连接其他数据库集群。另外，非特权的帐号现在可以通过postgre_fdw直接连接另一个PostgreSQL数据库而不必使用密码。
PostgreSQL 13 继续提升在Windows平台上的操作性，现在Windows平台上的用户也有了可以通过UDS通讯方式来连接PostgreSQL服务的选项。 PostgreSQL 13文档中增加了术语汇总表，来帮助用户了解PostgreSQL和一些通用的数据库概念。同时对函数和表中的操作符的显示也进行了优化，以帮助提升在线文档和PDF文档的可阅读性。 用于性能测试的pgbench，现在支持系统用户表的分区操作，这样在对包含分区表的性能测试时更加容易。 psql工具现在包括类似于\echo的\warn 指令用于输出数据，差别是\warn会将输出重定向至stderr标准错误的虚拟设备。现在用户在需要了解更多PostgreSQL指令时，使用-help选项会包含一个网络链接，指向：https://www.postgresql.org.
相关文章