PostgreSQL 16 新特性之提升运维效率

在数据库管理和维护的世界中，效率和可靠性始终是DBA（数据库管理员）追求的核心目标。随着技术的不断进步，数据库系统也在持续地演化，以满足日益增长的业务需求和挑战。PostgreSQL 16 版本引入了一系列的新特性，旨在简化日常管理任务，增强系统的自动化能力，以及提供更为深入的性能分析工具，从而帮助DBA们更加高效地处理数据库的维护和优化工作。本篇文章，旨在让大家快速了解PostgreSQL 16 中一些有助于提升DBA工作效率的新特性。

pg_dump增强

1. 新增支持lz4算法和zstd算法，压缩效率得到了提升，下面是一个示例：
   创建一个users表

    CREATE TABLE IF NOT EXISTS users (
        id serial PRIMARY KEY, 
        account_name VARCHAR(60), 
        name VARCHAR(60),
        password VARCHAR(60), 
        email VARCHAR(60), 
        created_at TIMESTAMP,
        phone CHAR(11),
        address VARCHAR(100),
        ssn VARCHAR(18)
    );

   通过copy写入100万行数据

    -- users表写入了100万行数据
    postgres=# select count(*) from users;
      count  
    ---------
     1000000
    (1 row)

   查看表大小

    postgres=# select pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname='users';
     pg_size_pretty 
    ----------------
     208 MB
    (1 row)

   接下来我们使用pg_dump将其导出

    # 不使用任何压缩算法
    pg_dump --table=users --dbname=postgres -f users.sql
    [pg16@Rocky8_PG01:/home/pg16]$du -sh users.sql 
    165M    users.sql
    # 使用lz4压缩算法
    pg_dump --table=users --dbname=postgres -f users_lz4.sql --compress=lz4
    [pg16@Rocky8_PG01:/home/pg16]$du -sh users_lz4.sql 
    127M    users_lz4.sql
    # 使用zstd压缩算法
    pg_dump --table=users --dbname=postgres -f users_zstd.sql --compress=zstd
    [pg16@Rocky8_PG01:/home/pg16]$du -sh users_zstd.sql 
    75M     users_zstd.sql

2. 新增导出子表和分区表的功能，PostgreSQL 16之前，需要使用-t 显式指定分区表或者使用通配符匹配，但这都不完美，要么就是需要指定很多的分区表，要么就是模糊匹配有可能与其他表名重叠。而PostgreSQL 16的pg_dump新增的几个选项完美地解决了这个问题。

• --table-and-children=PATTERN：导出指定表，包括子表和分区表
• --exclude-table-and-children=PATTERN：不导出指定的表，包括子表和分区表
• --exclude-table-data-and-children=PATTERN：不导出指定表的数据，包括子表和分区表

VACUUM和VACUUMDB增强

VACUUM新增BUFFER_USAGE_LIMIT参数

设置vacuum时可以使用shadred buffer的大小，以下是几点注意事项：

• 取值范围：0, 128kB~16GB，设为0时，不做限制；
• 如果携带ANALYZE选项，则执行vacuum和analyze时都会遵循此限制；
• 不能与FULL选项一起使用，除非同时指定了ANALYZE选项；

使用示例：

postgres=# VACUUM (ANALYZE, BUFFER_USAGE_LIMIT '128kB') users;
VACUUM

单独指定FULL时会报错

postgres=# VACUUM (FULL, BUFFER_USAGE_LIMIT '128kB') users;
ERROR:  BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL

使用FULL加ANALYZE时则可以正常执行

postgres=# VACUUM (FULL, ANALYZE, BUFFER_USAGE_LIMIT '128kB') users;
VACUUM

PostgreSQL 16 还贴心地新增了一个数据库参数vacuum_buffer_usage_limit，默认值为256kB。

postgres=# show vacuum_buffer_usage_limit ;
 vacuum_buffer_usage_limit
---------------------------
 256kB

-- 携带SKIP_DATABASE_STATS选项先不刷新FrozenXID
postgres=# VACUUM (ANALYZE, BUFFER_USAGE_LIMIT '128kB', SKIP_DATABASE_STATS) users warehouse;
-- 使用ONLY_DATABASE_STATS选项刷新FrozenXID
postgres=# VACUUM (BUFFER_USAGE_LIMIT '128kB', ONLY_DATABASE_STATS);

注意：ONLY_DATABASE_STATS除了VERBOSE和BUFFER_USAGE_LIMIT不能跟其他选项一起使用，也不能指定table_and_columns。

清理TOAST表

VACUUUM可以使用下面的选项：

• PROCESS_MAIN：只清理主表，或者设为false时跳过主表；
• PROCESS_TOAST：只清理TOAST表，或者设为false时跳过TOAST表；

VACUUMDB则可以使用：

• —no-process-main：跳过主表
• —no-process-toast：跳过TOAST表

VACUUMDB 排除指定模式

• —schema：指定需要清理的模式；
• —exclude-schema：跳过指定的模式；

死元组清理性能提升

• AUTOVACCUM 进程刷新代价相关的参数变得更为积极，之前修改AUTOVACUUM代价参数autovacuum_vacuum_cost_limit时，需要等待当前表处理完成之后，新的参数才会生效，在PostgreSQL 16中，修改会在autovacuum_vacuum_cost_delay控制的下一个周期时应用。
• 对于VACUUM，就是上面提到的新增BUFFER_USAGE_LIMIT选项，PostgreSQL 16之前VACUUM使用的shared buffer和ANALYZE使用的ring buffer是固定的256kB，而这对于清理大表时显然是不够用的，清理会变得异常缓慢。得益于新增的BUFFER_USAGE_LIMIT选择和vacuum_buffer_usage_limit参数，使得VACUUM效率大幅提升。