PostgreSQL 17发布已经半年多了，我们团队也从15升级到了17。

升级之后，除了享受新版本的性能提升，我们还挖掘了很多实用的技巧和特性。有些是17版本新增的，有些是之前就有但很多人不知道的。用好了这些技巧，数据库的性能和可维护性都能提升一个档次。

这篇文章，我想分享一些PostgreSQL 17+的进阶技巧，从性能优化、新特性、运维管理到开发最佳实践，聊聊那些能让你的数据库跑得更快、用得更顺的方法。

一、性能优化技巧

1. 增量备份和恢复更快了

PostgreSQL 17对增量备份做了重要改进。之前的版本，做基础备份的时候需要扫描整个数据目录，即使大部分数据没变化也要重新拷贝。17引入了更高效的增量备份机制，可以只拷贝变化的数据块，大大减少了备份时间和存储空间。

具体来说，17支持基于块的增量备份（file-level incremental backup）。配合pg_basebackup的--incremental选项，可以只备份自上次备份以来变化的页面。对于大数据库（几百GB甚至TB级），这个改进非常显著，备份时间从几小时降到几十分钟。

恢复的时候也更快了，因为只需要应用增量的部分，不用全量恢复。对于经常需要做备份恢复的场景，这个特性值得升级。

2. vacuum 性能提升

VACUUM是PostgreSQL维护的重要操作，但大表的vacuum经常很慢，影响业务。17对vacuum做了多项优化：

  • 改进了dead tuple的跟踪和清理，减少了不必要的扫描。
  • 支持并行vacuum（之前的版本对索引vacuum有并行，17进一步优化了heap vacuum的并行）。
  • 减少了vacuum时的锁持有时间，降低了对业务的影响。

实际使用中，我们的一张几亿行的大表，vacuum时间从原来的40多分钟降到了15分钟左右，而且vacuum期间业务查询的延迟没有明显上升。

建议升级到17后，调整一下autovacuum的参数，比如增加autovacuumworkmem，让autovacuum更高效。

3. 逻辑复制的性能改进

逻辑复制在17中有了很大的改进。之前的版本，逻辑复制在大事务、大表的场景下，延迟比较高。17优化了逻辑解码的性能，减少了WAL解析的开销，同时支持了并行的逻辑复制应用。

具体来说，17的逻辑复制支持：

  • 更高效的WAL解码，减少了CPU开销。
  • 订阅端可以并行应用事务，提高了复制吞吐量。
  • 支持两阶段提交的逻辑复制，分布式事务更可靠。
  • 改进了初始数据同步的速度，大表的初始化更快。

我们有一个跨地域的逻辑复制场景，之前延迟经常在几分钟到几十分钟，升级到17后，延迟稳定在几秒以内，效果非常明显。

4. 查询优化器的改进

PostgreSQL 17的查询优化器也有不少改进：

  • 改进了连接顺序的搜索算法，对于多表连接（10张表以上），能找到更优的执行计划。
  • 改进了统计信息的使用，估算更准确，减少了因为估算错误导致的执行计划变差。
  • 支持了更多的表达式下推和优化，比如常量折叠、子查询提升。
  • 改进了并行查询的决策，能更智能地决定是否使用并行、用多少个worker。

实际效果上，我们有一些复杂的报表查询，升级后执行时间提升了20%-50%不等。建议升级后，对重要的查询重新做EXPLAIN ANALYZE，看看执行计划有没有变化。

二、17版本的新特性

1. SQL/JSON的改进

PostgreSQL对JSON的支持一直很强，17进一步增强了SQL/JSON的功能：

  • 支持JSON_TABLE的更多语法，可以更方便地把JSON数据展开成关系表。
  • 改进了JSONB的操作性能，尤其是大JSONB的更新和查询。
  • 支持更多的JSON路径表达式，查询嵌套JSON更方便。
  • 支持JSON数据的唯一性约束和更丰富的索引。

对于存储了大量JSON数据的应用，这些改进能提升查询性能，也让JSON的使用更灵活。

2. 异步IO支持

PostgreSQL 17引入了异步IO的支持（默认关闭，需要通过iomethod=iouring开启）。对于高IO负载的场景（比如大数据量的顺序扫描、备份恢复），异步IO能显著提升吞吐量，降低延迟。

这个特性在Linux上效果最好，需要内核支持io_uring（Linux 5.1以上）。我们在测试环境试了一下，大表顺序扫描的吞吐量提升了30%左右。不过目前还是比较新的特性，生产环境建议先充分测试再开启。

3. 安全增强

17在安全方面也有不少改进：

  • 支持更细粒度的权限控制，比如可以授予用户查看其他用户会话的权限，但不能终止会话。
  • 改进了SSL/TLS的支持，支持更新的加密算法。
  • 增强了行级安全策略（RLS）的性能和功能。
  • 改进了密码管理，支持更安全的密码哈希算法。

对于对安全要求高的场景，这些改进很有价值。

4. 分区表的改进

分区表是PostgreSQL的重要特性，17做了不少优化：

  • 改进了分区裁剪（partition pruning），更多场景下能正确裁剪不需要的分区。
  • 支持分区表的更多DDL操作，比如一次性修改所有分区的属性。
  • 改进了分区表上的查询优化，尤其是跨分区的聚合和连接。
  • 支持默认分区的更多操作，数据管理更灵活。

我们有一个按时间分区的大表（每天一个分区，几百个分区），升级后查询性能提升明显，尤其是只查最近几天数据的查询，分区裁剪更准确了。

三、实用技巧

1. 合理使用BRIN索引

对于大表（尤其是按时间顺序插入的表），BRIN索引比B-tree索引小很多，维护成本低，查询效果也不错。很多人只知道B-tree索引，不知道BRIN索引，导致索引占用了大量空间。

BRIN索引的原理是，记录每个数据块范围内的最大值和最小值，查询时可以快速跳过不相关的数据块。对于有序数据（比如自增ID、时间戳），BRIN索引的效果非常好，索引大小可能只有B-tree的几十分之一。

17对BRIN索引也做了优化，支持了更多的数据类型和操作符。建议对大表的有序字段，考虑用BRIN替代B-tree，能节省大量空间和维护成本。

2. 用好物化视图

对于复杂的、不要求实时的报表查询，物化视图是个好东西。它把查询结果预先计算并存储起来，查询的时候直接读结果，速度很快。

PostgreSQL的物化视图支持REFRESH MATERIALIZED VIEW CONCURRENTLY，可以在不锁表的情况下刷新。17对物化视图的刷新性能也做了优化。

建议把那些执行时间长、但数据不需要实时的报表查询，改造成物化视图，定时刷新。这样既能提升查询速度，又能减少数据库的负载。

3. 监控等待事件

PostgreSQL的pgstatactivity视图能看到每个会话当前的等待事件。很多时候，数据库慢不是因为CPU不够，而是因为在等待某种资源（锁、IO、网络等）。

建议定期查看pgstatactivity，看看主要的等待事件是什么。如果经常看到锁等待，说明有并发冲突；如果经常看到IO等待，说明存储可能是瓶颈；如果经常看到CPU，说明查询可能需要优化。

17增加了更多的等待事件类型，诊断问题更方便。配合pgstatstatements扩展，可以定位到具体是哪些SQL导致的等待。

4. 合理设置work_mem

work_mem是排序和哈希操作使用的内存大小，默认是4MB，对于复杂查询来说太小了。很多查询慢，就是因为排序或者哈希操作溢出到磁盘，导致大量的临时文件IO。

建议根据实际情况调大workmem，但也不能太大，否则高并发的时候内存会不够。一个经验值是，把workmem设为总内存的1%到5%，然后根据实际情况调整。

可以在会话级别设置work_mem，比如对于复杂的报表查询，单独设大一些；对于简单的OLTP查询，用默认值就行。这样既能提升复杂查询的性能，又不会影响整体的内存使用。

5. 使用EXPLAIN ANALYZE BUFFERS

排查慢查询的时候，不要只用EXPLAIN，要用EXPLAIN ANALYZE BUFFERS。EXPLAIN只显示估算的执行计划，EXPLAIN ANALYZE会实际执行并显示真实的时间和行数，BUFFERS还会显示IO情况（读了多少块、命中多少缓存）。

通过EXPLAIN ANALYZE BUFFERS，你可以看到：

  • 每个操作的实际时间和估算时间的差异，找出估算不准的地方。
  • 哪些操作产生了大量的IO，是不是索引没用到。
  • 缓存命中率怎么样，是不是需要调大shared_buffers。

17的EXPLAIN输出也有改进，信息更丰富，更容易定位问题。

四、运维管理技巧

1. 用好pgstatstatements

pgstatstatements是PostgreSQL最重要的扩展之一，它能记录所有SQL的执行统计信息，包括执行次数、总时间、平均时间、读取的块数等。

通过pgstatstatements，可以快速找到最耗时的SQL、执行次数最多的SQL、IO最多的SQL，然后针对性地优化。很多时候，优化了前几个慢SQL，数据库的整体性能就提升了一大截。

建议开启pgstatstatements，并定期分析。可以用以下查询找最耗时的SQL：

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

2. 定期分析和统计信息更新

PostgreSQL的查询优化器依赖统计信息来生成执行计划。如果统计信息过时了，优化器可能会选错执行计划，导致查询变慢。

建议确保autovacuum正常运行（它会自动更新统计信息），对于大表或者数据变化快的表，可以适当调大统计信息的采样比例（defaultstatisticstarget），让估算更准确。

也可以手动用ANALYZE命令更新统计信息，尤其是在大批量数据导入之后。17的ANALYZE速度也有提升，对大表的分析更快了。

3. 连接池的使用

PostgreSQL的连接是比较重的，每个连接都要分配内存和进程。高并发的时候，如果直接连数据库，连接数太多会导致内存不足、性能下降。

建议使用连接池（比如PgBouncer、Pgpool-II），让应用连接连接池，连接池再连数据库。这样可以复用数据库连接，减少连接开销，提高并发能力。

PgBouncer是最常用的连接池，配置简单，性能好。建议在生产环境中使用，尤其是应用服务器多、并发高的场景。

4. 备份策略的设计

备份是数据库运维的重中之重。PostgreSQL的备份方式有很多：

  • pg_dump：逻辑备份，适合小数据库或者需要迁移的场景。
  • pg_basebackup + WAL归档：物理备份，适合大数据库，支持时间点恢复（PITR）。
  • 第三方工具：比如pgBackRest、Barman，功能更强大，支持增量备份、并行压缩、远程备份等。

17的增量备份让pg_basebackup更实用了，但对于复杂的备份需求，还是推荐用pgBackRest这样的专业工具。

建议设计完整的备份策略：全量备份+增量备份+WAL归档，定期做恢复演练，确保备份可用。备份了但恢复不了，等于没备份。

5. 版本升级的注意事项

从旧版本升级到17，有几个注意事项：

  • 先在测试环境充分测试，包括功能测试和性能测试。
  • 用pg_upgrade做升级，比导出导入快很多，尤其是大数据库。
  • 升级前做好完整备份，确保可以回滚。
  • 升级后运行ANALYZE，更新统计信息。
  • 检查扩展的兼容性，有些扩展可能需要升级版本。
  • 关注不兼容的变化，比如废弃的参数、移除的功能。

我们升级的时候，用pg_upgrade，一个2TB的数据库，升级时间不到1小时（加上应用切换的时间）。升级后性能有明显提升，还是很值得的。

五、开发最佳实践

1. 避免SELECT *

只查询需要的列，不要用SELECT *。原因有几个：减少数据传输量、减少内存使用、可能用到覆盖索引、表结构变化时不容易出问题。

尤其是大表或者宽表，SELECT *会读取很多不需要的数据，浪费IO和内存。养成只查需要列的习惯，对性能和可维护性都有好处。

2. 合理使用事务

事务要尽量短，不要在事务里做耗时的操作（比如调用外部接口、等待用户输入）。长事务会导致锁持有时间长、WAL堆积、vacuum无法清理dead tuple，影响整个数据库的性能。

建议把事务控制在必要的范围内，需要一致性的操作放在事务里，不需要的放在事务外。对于批量操作，可以分批提交，避免一个超大事务。

3. 批量操作优于循环

不要在应用层循环执行单条SQL，尽量用批量操作。比如，批量插入用INSERT ... VALUES (...), (...), (...)，批量更新用CASE或者临时表，批量删除用IN或者JOIN。

批量操作能减少网络往返、减少事务开销、提高效率。对于大量数据的操作，性能差异可能是几十倍甚至上百倍。

4. 正确使用索引

索引不是越多越好。每个索引都会增加写入的开销（INSERT、UPDATE、DELETE都要更新索引），占用存储空间。只在经常查询、过滤、排序、连接的列上建索引。

建索引的时候要考虑：

  • 联合索引的列顺序，把等值查询的列放前面，范围查询的列放后面。
  • 对字符串列，可以考虑用前缀索引，减少索引大小。
  • 对低基数的列（比如性别、状态），B-tree索引效果不好，可以考虑部分索引或者表达式索引。
  • 定期检查无用的索引，删掉它们。

17的索引监控也更完善了，可以通过pgstatuser_indexes查看索引的使用情况，找出没用的索引。

5. 注意N+1查询问题

在ORM（比如Django ORM、Hibernate、MyBatis）中，很容易出现N+1查询问题：先查一次主表，然后对每条记录再查一次关联表。如果主表有1000条记录，就会执行1001次查询，性能很差。

解决方法是用预加载（eager loading），一次查询把关联数据也查出来。在SQL层面就是用JOIN或者子查询。开发的时候要注意ORM生成的SQL，避免N+1问题。

写在最后

PostgreSQL是一个功能强大、持续演进的数据库。17版本在性能、功能、安全、可维护性方面都有不少改进，值得升级。

这篇文章分享的技巧，有些是17的新特性，有些是通用的最佳实践。不管你用的是哪个版本，这些技巧都能帮你更好地使用PostgreSQL。

数据库的优化是一个持续的过程，没有银弹。要不断监控、分析、调整，根据业务的变化优化数据库。但只要掌握了基本的原理和方法，就能应对大部分的性能问题。

希望这些技巧对你有帮助。如果你也有PostgreSQL的使用经验或者问题，欢迎在评论区交流。