PostgreSQL 17发布已经快一年了，我们团队也在生产环境中用了大半年。

从最早的MySQL转到PostgreSQL，再从PostgreSQL 14一路升级到17，这几年踩了不少坑，也积累了不少经验。特别是PostgreSQL 17带来了很多新特性和性能改进，用好这些特性能让数据库的性能和稳定性提升一个档次。

这篇文章我想总结一下在生产环境中使用PostgreSQL 17的最佳实践。从安装配置、性能优化到高可用和运维，聊聊那些踩过的坑和总结的方法论。

如果你在用或者打算用PostgreSQL 17，希望这些经验能帮你少走一些弯路。

先说明一下，我主要基于PostgreSQL 17来写，不同版本的配置和特性可能会有差异。而且每个业务场景不一样，最佳实践也不是绝对的，需要结合自己的实际情况来调整。

安装和升级的最佳实践

先从安装和升级说起。

第一，尽量用官方源或者可信的第三方源安装。

不要随便从网上下载安装包，也不要用太旧的版本。建议用PostgreSQL官方的APT或者YUM源来安装，这样能保证版本是最新的，也能及时收到安全更新。

如果是在容器环境中部署，建议用官方的Docker镜像，不要用第三方修改过的镜像。官方镜像维护得比较好，安全性有保障。

第二，大版本升级要谨慎。

PostgreSQL的大版本升级（比如从16升到17）不是简单地替换二进制文件就行，因为数据文件的格式可能变了。需要用pg_upgrade工具来升级，或者用逻辑复制的方式来做。

升级之前一定要做好备份，最好在测试环境先演练一遍。升级的过程中可能会遇到各种问题，比如不兼容的SQL、性能退化、扩展不支持等，要提前做好准备。

我们的做法是，先用pg_upgrade在测试环境升级，跑一遍完整的测试，确认没有问题之后，再在生产环境升级。生产环境升级的时候，选在业务低峰期，并且做好回滚的准备。

第三，小版本要及时更新。

PostgreSQL的小版本更新（比如17.1到17.2）主要是修复bug和安全漏洞，不会有不兼容的变化。所以小版本要及时更新，不要一直用最初的版本。

我们的做法是，每个小版本发布之后，先在测试环境部署，观察一周没有问题，再更新到生产环境。这样既能及时修复bug，又不会因为更新而出问题。

第四，扩展要选稳定的。

PostgreSQL的扩展生态很丰富，但不是所有扩展都适合生产环境。选扩展的时候，要选那些维护活跃、社区成熟、被广泛使用的扩展。比如pgstatstatements、pgvector、PostGIS这些，都是经过大量生产验证的。

不要随便用那些很少人用、很久没更新的扩展，可能会有bug或者安全隐患。而且扩展要和PostgreSQL的版本兼容，升级PostgreSQL之前要确认所有扩展都支持新版本。

配置优化的最佳实践

安装好之后，接下来是配置优化。PostgreSQL的默认配置是很保守的，适合在小机器上运行，但生产环境需要根据硬件和业务情况来调整。

第一，内存相关的配置。

sharedbuffers：这是PostgreSQL最重要的内存配置，决定了数据库用多少内存来缓存数据。默认值通常很小，生产环境建议设置为系统内存的25%左右。比如机器有64G内存，sharedbuffers可以设为16G。

workmem：这个配置决定了排序、哈希连接等操作能用多少内存。默认值很小，只有4MB，生产环境可以调大一些，比如32MB或者64MB。但要注意，workmem是每个操作能用的内存，如果有很多并发查询，总的内存消耗可能会很大。所以要根据并发数来调整，不要设得太大。

maintenanceworkmem：这个配置决定了维护操作（比如VACUUM、CREATE INDEX）能用多少内存。可以设得大一些，比如1GB或者2GB，能加快维护操作的速度。

effectivecachesize：这个配置告诉优化器，系统总共有多少内存可以用来缓存数据（包括PostgreSQL的shared_buffers和操作系统的缓存）。建议设置为系统内存的50%到75%。优化器会根据这个值来判断索引扫描是否划算。

第二，检查点相关的配置。

maxwalsize：这个配置决定了WAL（预写日志）的最大大小。默认值是1GB，生产环境建议调大一些，比如10GB或者更大。调大maxwalsize能减少检查点的频率，提高写入性能。但也不能太大，否则崩溃恢复的时间会变长。

checkpointcompletiontarget：这个配置决定了检查点在多长时间内完成。默认值是0.9，意思是在两次检查点间隔的90%时间内完成。建议保持默认值或者设为0.9，这样检查点的I/O会比较平滑，不会对业务造成太大影响。

第三，连接相关的配置。

max_connections：这个配置决定了最大连接数。默认值是100，生产环境可能不够用。但也不要设得太大，因为每个连接都会消耗一定的内存，连接太多会导致内存不足。

如果需要很多连接，建议用连接池（比如PgBouncer），而不是把max_connections设得很大。连接池能复用连接，大大减少数据库的连接数。

我们的做法是，max_connections设为300，前面挂一个PgBouncer连接池，应用连接PgBouncer，实际数据库连接数控制在100左右。

第四，查询优化相关的配置。

randompagecost：这个配置影响优化器对随机读取成本的估算。如果用的是SSD，随机读取和顺序读取的成本差不多，可以把这个值设为1.1或者1.5。默认值是4，是针对机械硬盘的，用SSD的话设太高会导致优化器倾向于顺序扫描而不是索引扫描。

effectiveioconcurrency：这个配置告诉优化器，磁盘能同时处理多少个I/O请求。SSD可以设得高一些，比如200，机械硬盘设低一些，比如2。

性能优化的最佳实践

配置优化之后，接下来是性能优化。性能优化是一个大话题，这里挑几个最重要的说说。

第一，索引优化。

索引是数据库性能的关键。好的索引能让查询快几百倍，不好的索引反而会拖慢写入，还占用空间。

首先，要给经常用于WHERE、JOIN、ORDER BY的字段建索引。但也不要建太多索引，因为每个索引都会增加写入的开销，还占用存储空间。

其次，要定期检查索引的使用情况。用pgstatuser_indexes视图可以查看每个索引被使用了多少次。如果发现有些索引从来没被用过，就可以删掉，减少写入开销和存储空间。

PostgreSQL 17有一个新特性，可以更好地监控索引的使用情况，还能自动检测无用的索引。建议开启这个特性，定期清理无用索引。

另外，对于字符串字段的模糊查询，可以考虑用pg_trgm扩展做 trigram 索引，比传统的LIKE查询快很多。对于全文搜索，可以用全文索引或者pgvector做向量搜索。

第二，查询优化。

慢查询是数据库性能问题的主要来源。要定期分析慢查询，找出性能瓶颈。

首先，开启pgstatstatements扩展，它能记录所有SQL语句的执行情况，包括执行次数、总时间、平均时间、读取的行数等。通过这个扩展，可以快速找到最慢的那些SQL。

然后，对慢查询用EXPLAIN ANALYZE来分析执行计划，看看是哪里慢。是全表扫描了？是索引没用到？是排序太慢？还是连接太多？

找到原因之后，针对性地优化。比如加索引、改写SQL、调整配置、分页查询等。

PostgreSQL 17对查询优化器做了很多改进，比如更好的并行查询、更智能的连接顺序选择、更准确的统计信息。升级到17之后，很多查询会自动变快，但还是要定期分析慢查询。

第三，VACUUM和事务ID管理。

PostgreSQL的MVCC（多版本并发控制）机制会产生死元组，需要定期VACUUM来清理。如果VACUUM不及时，表会膨胀，性能会下降，还可能导致事务ID回卷的问题。

首先，要确保autovacuum是开启的，并且配置合理。默认的autovacuum配置可能太保守，生产环境可以调得积极一些，比如降低autovacuumvacuumscale_factor，让vacuum更频繁地触发。

其次，对于更新特别频繁的表，可以考虑调大autovacuumvacuumcost_limit，让vacuum跑得更快。或者对这些表单独设置存储参数，比如fillfactor，给更新预留空间，减少页面分裂。

PostgreSQL 17对vacuum做了很多优化，比如并行vacuum、更高效的死元组回收。升级到17之后，vacuum的性能有明显提升。但还是要监控vacuum的运行情况，确保它能及时完成。

第四，表分区。

对于特别大的表（比如几千万甚至上亿行），可以考虑用表分区。把大表按时间或者其他维度分成多个小表，查询的时候只扫描相关的分区，能大大提高查询性能。

PostgreSQL支持范围分区、列表分区、哈希分区等多种分区方式。最常用的是按时间范围分区，比如按月或者按天分区，这样旧数据可以直接删掉或者归档，非常方便。

但分区也不是银弹，分区表有一些限制，比如唯一约束必须包含分区键，外键支持有限等。要根据业务场景来决定是否用分区，以及怎么分区。

高可用和备份的最佳实践

生产环境中，高可用和备份是必须的。

第一，备份策略。

备份是数据库的最后一道防线。一定要有定期的备份，并且要定期测试备份是否能正常恢复。

PostgreSQL的备份方式有几种：

pg_dump：逻辑备份，把数据库导出成SQL文件或者归档文件。优点是简单灵活，能跨版本恢复；缺点是备份和恢复速度慢，大数据库不适合。

pg_basebackup：物理备份，直接复制数据文件。优点是速度快，适合大数据库；缺点是只能恢复到备份的时间点，不能跨大版本恢复。

WAL归档：把WAL日志归档保存，配合基础备份，可以做时间点恢复（PITR），能恢复到任意时间点。

我们的做法是，每天做一次pgbasebackup基础备份，持续归档WAL日志。这样可以恢复到任意时间点，RPO（恢复点目标）接近于零。同时每周做一次pgdump逻辑备份，作为额外的保障。

关键是要定期做恢复测试。很多人备份做了，但从来没试过恢复，真出问题了才发现备份不能用，那就晚了。我们每个季度都会做一次恢复演练，确保备份是可用的。

第二，高可用架构。

如果数据库不能宕机，就需要高可用架构。

最常用的是主从复制。一个主库负责读写，一个或多个从库负责只读，主库的数据实时同步到从库。主库挂了之后，可以把从库提升为主库，继续提供服务。

PostgreSQL的流复制（Streaming Replication）很成熟，配置也不复杂。可以用同步复制或者异步复制，同步复制数据更安全但性能稍差，异步复制性能好但可能有数据丢失。

如果需要自动故障转移，可以用Patroni、repmgr等工具。这些工具能自动检测主库故障，自动提升从库为主库，还能自动重新配置复制。

我们的架构是一主一从，用Patroni做自动故障转移，配合etcd做分布式协调。主库挂了之后，几十秒内就能自动切换到从库，业务基本无感知。

第三，读写分离。

如果读请求很多，可以做读写分离。写请求走主库，读请求走从库，分摊主库的压力。

读写分离可以在应用层做，也可以用中间件做。比如用PgBouncer的路由功能，或者用ProxySQL这样的中间件。

但读写分离要注意复制延迟的问题。刚写入主库的数据，可能还没同步到从库，这时候去从库读可能读不到。对于一致性要求高的读请求，还是要走主库。

运维和监控的最佳实践

最后说说运维和监控。

第一，监控指标。

要监控的关键指标包括：

连接数：当前连接数、空闲连接数、等待连接数。连接数接近上限的时候要告警。

查询性能：QPS、TPS、慢查询数量、平均查询延迟。这些指标能反映数据库的负载和性能。

复制状态：复制延迟、WAL发送和接收状态。复制延迟太大的时候要告警。

磁盘使用：数据目录大小、WAL大小、表空间使用率。磁盘满了会导致数据库挂掉，一定要提前告警。

内存和CPU：内存使用率、CPU使用率、swap使用情况。资源不足的时候要及时扩容。

vacuum状态：autovacuum的运行情况、死元组数量、最老的事务ID。防止表膨胀和事务ID回卷。

我们用Prometheus加Grafana做监控，用postgres_exporter采集PostgreSQL的指标。所有关键指标都有仪表盘和告警，出了问题能第一时间知道。

第二，日志管理。

PostgreSQL的日志很重要，出了问题要靠日志来排查。

要把日志级别设得合适，生产环境建议设为log或者以上。同时要记录慢查询，比如logminduration_statement设为1000毫秒，超过1秒的查询都会记录到日志里。

日志要集中收集，比如用ELK栈或者Loki，方便查询和分析。日志要保留足够长的时间，至少保留30天。

第三，定期维护。

数据库需要定期维护，比如：

定期分析慢查询，优化SQL和索引。

定期清理无用的索引和表，回收空间。

定期检查膨胀的表，必要时做VACUUM FULL或者pg_repack。

定期更新统计信息，确保优化器有准确的统计信息。

定期检查备份和高可用的状态，确保一切正常。

这些维护工作可以做成自动化脚本，定期运行，减少人工操作。

常见问题和解决方案

最后说说我们遇到过的一些常见问题和解决方案。

第一个问题是连接数打满。

表现是应用连不上数据库，报错"too many connections"。原因可能是应用没有正确关闭连接，或者有慢查询占用了连接。

解决方案是，先用连接池限制连接数，然后排查是哪些应用占用了连接，修复连接泄漏。同时优化慢查询，减少连接占用时间。

第二个问题是磁盘空间不足。

表现是数据库只读或者挂掉，报错"No space left on device"。原因可能是WAL日志太多、表膨胀、临时文件太大等。

解决方案是，先清理WAL归档和旧的备份，释放空间。然后找到占用空间最大的表和索引，清理无用数据，做VACUUM回收空间。长期来看，要监控磁盘使用，提前扩容。

第三个问题是慢查询导致CPU飙升。

表现是数据库CPU使用率很高，查询都变慢了。原因通常是某条SQL突然变慢了，比如统计信息不准导致执行计划变差，或者数据量增长后索引不够用了。

解决方案是，用pgstatstatements找到最慢的SQL，用EXPLAIN ANALYZE分析执行计划，然后加索引或者改写SQL。如果是统计信息不准，跑一下ANALYZE更新统计信息。

第四个问题是复制延迟太大。

表现是从库的数据比主库慢很多，读写分离的时候读不到最新数据。原因可能是主库写入压力太大，从库追不上；或者从库的硬件比主库差；或者有大事务导致复制阻塞。

解决方案是，检查主库的写入压力，必要时优化写入。确保从库的硬件配置不低于主库。避免大事务，把大事务拆成小事务。

这些问题我们都遇到过，也都解决了。关键是要有完善的监控，出了问题能快速定位，然后针对性地解决。

写在最后

PostgreSQL 17是一个非常优秀的版本，带来了很多新特性和性能改进。但再好的数据库，也需要合理的配置和运维，才能发挥出它的能力。

这篇文章总结的是我们团队在生产环境中积累的一些经验和最佳实践。从安装配置、性能优化到高可用和运维，每个环节都有很多细节需要注意。

当然，这些经验不是标准答案，每个业务场景都有自己的特点。重要的是理解背后的原理，然后根据自己的实际情况来调整和优化。

PostgreSQL是一个不断发展的数据库，每个版本都会带来新的特性和改进。我们也在持续学习和实践，不断完善我们的最佳实践。

如果你也在用PostgreSQL，或者对这个领域感兴趣，欢迎交流。让我们一起把数据库用好、运维好。

最后用一句话来结束这篇文章："数据库是应用的基石，基石稳了，上面的应用才能跑得稳。把数据库运维好，是每个技术团队的必修课。"

愿你的PostgreSQL，稳定而高效。