在生产环境用PostgreSQL 14跑了几个月，从最初的搭建到后来的优化和运维，踩了不少坑，也积累了一些经验。本文从配置、Schema设计、查询优化、备份恢复、高可用等方面，总结PostgreSQL 14的生产环境最佳实践，希望能帮到正在用PG的朋友。

一、安装和配置最佳实践

1. 用官方源安装。

不要用系统自带的PostgreSQL，版本通常比较旧。用PostgreSQL官方的APT/YUM源安装，可以获得最新版本和安全补丁。官方源还附带了很多有用的扩展和工具。

2. 数据目录和日志分盘。

生产环境一定要把数据目录（PGDATA）、WAL日志（pg_wal）、日志文件（log）放在不同的磁盘上。这样做有两个好处：一是WAL的写入不会和数据文件的读写竞争IO，二是数据盘坏了，WAL和日志还在，可以用来恢复。

我们有一次数据盘出了问题，因为WAL在另一块盘上，成功恢复了所有数据，没有丢失。从那以后，所有生产环境都严格分盘。

3. 核心配置参数。

PG的默认配置比较保守，需要根据服务器硬件来调整。几个最重要的参数：

  • shared_buffers：建议设为物理内存的25%，不要超过8GB。这是PG自己用的缓存，越大越好，但太大了会和操作系统的缓存抢内存。
  • workmem：排序和哈希操作的内存，建议从4MB开始，根据查询情况调整。复杂查询多的话可以调大，但要注意并发数，总内存=workmem × 并发数。
  • maintenanceworkmem：维护操作（VACUUM、CREATE INDEX等）的内存，建议64MB到1GB，大了维护操作更快。
  • effectivecachesize：告诉优化器操作系统有多少缓存可用，建议设为物理内存的50%-75%。这个参数不实际分配内存，只是影响查询计划的选择。
  • checkpointcompletiontarget：建议设为0.9，让checkpoint分散在更长的时间内，减少IO尖峰。
  • wal_buffers：建议16MB，WAL写入更高效。

这些参数不是一成不变的，要根据实际负载调整。可以用pgstatstatements监控慢查询，用EXPLAIN (ANALYZE, BUFFERS)看执行计划和缓存命中情况。

4. 客户端认证配置。

pg_hba.conf要严格配置，不要用trust认证（除了本地维护）。生产环境用scram-sha-256密码认证，只允许应用服务器的IP连接，拒绝所有其他来源。

还要定期审查pg_hba.conf，移除不需要的认证规则。最小权限原则同样适用于数据库。

二、Schema设计最佳实践

1. 主键设计。

每张表都要有主键。推荐用BIGINT GENERATED ALWAYS AS IDENTITY作为代理主键，不要用业务字段做主键，因为业务字段可能会变。

不要用UUID做主键，除非有分布式需求。UUID是随机的，会导致索引碎片化，插入性能差。如果必须用UUID，用uuidgeneratev1mc()（时间有序的）而不是v4。

2. 字段类型选择。

  • 字符串：长度固定用CHAR(n)，长度不固定用VARCHAR(n)或TEXT。PG的VARCHAR(n)和TEXT性能一样，只是多了长度约束。如果没有严格的长度限制，直接用TEXT就行。
  • 整数：根据范围选SMALLINT、INTEGER、BIGINT，不要一律用BIGINT，小类型更省空间，缓存效率更高。
  • 时间：用TIMESTAMPTZ（带时区的时间戳），不要用TIMESTAMP，避免时区混乱。
  • 金额：用NUMERIC，不要用浮点数（REAL/DOUBLE PRECISION），避免精度丢失。
  • JSON：用JSONB，不要用JSON。JSONB支持索引，查询更快。
  • 数组：谨慎使用，数组字段不利于规范化和查询，大部分情况用关联表更好。

3. 约束和索引。

  • 该加的约束一定要加：主键、外键、唯一约束、非空约束、检查约束。约束是数据质量的最后一道防线，不要指望应用层来保证。
  • 外键要加索引。PG不会自动为外键创建索引，如果外键字段没有索引，关联查询和删除主表记录时会很慢。
  • 索引不是越多越好。每个索引都会增加写入开销，占用存储空间。只给经常出现在WHERE、JOIN、ORDER BY中的字段加索引。
  • 组合索引的字段顺序很重要，把等值查询的字段放前面，范围查询的字段放后面。
  • 定期检查无用索引，用pgstatuserindexes看索引的使用情况，idxscan为0的索引可以考虑删除。

4. 分区表。

对于大表（比如超过几千万行），考虑用分区表。按时间分区是最常见的，比如按月或按天分区。分区表的好处是：查询可以只扫描相关分区，更快；旧数据可以直接DROP分区，比DELETE快得多；可以把旧分区放到更便宜的存储上。

PG 14对分区表做了很多优化，比如分区裁剪更智能、分区键更新更高效。但分区表也有局限性，比如唯一约束必须包含分区键，外键引用分区表有限制等，要根据实际情况选择。

三、查询优化最佳实践

1. 永远用参数化查询。

不要拼接SQL字符串，用参数化查询（预编译语句）。这样做有三个好处：防止SQL注入、避免重复解析和生成执行计划、更安全。

几乎所有的PG驱动都支持参数化查询，用起来很简单，没有理由不用。

*2. 避免SELECT 。**

只查询需要的字段，不要用SELECT *。原因：减少网络传输、减少内存使用、可能用到覆盖索引、表结构变更时不会出意外。

特别是大字段（TEXT、JSONB、BYTEA），不需要的时候一定不要查出来，这些字段的读取和传输开销很大。

3. 分页优化。

LIMIT offset, size在offset很大的时候性能很差，因为数据库要扫描并跳过前面所有行。深分页的优化方法：

  • 用游标分页（keyset pagination）：WHERE id > last_id ORDER BY id LIMIT size，性能稳定。
  • 如果必须用offset，限制最大offset，比如不超过10000。
  • 可以用延迟关联（deferred join）：先查主键，再用主键回表查详情。

4. 批量操作。

不要在循环里一条一条INSERT/UPDATE，用批量操作：

  • INSERT INTO ... VALUES (...), (...), (...) 一次插入多行
  • UPDATE ... FROM ... 用关联更新
  • COPY 导入大量数据，比INSERT快好几倍

批量操作能减少网络往返和事务开销，性能提升非常明显。

5. 理解执行计划。

慢查询一定要用EXPLAIN (ANALYZE, BUFFERS)看执行计划，不要凭感觉优化。重点看：

  • 有没有全表扫描（Seq Scan），如果有，考虑加索引
  • 估算行数和实际行数差距大不大，差距大说明统计信息不准，需要ANALYZE
  • 排序有没有用到索引，还是用了内存/磁盘排序
  • Join的顺序和方式对不对，Nested Loop、Hash Join、Merge Join各有适用场景
  • Buffers信息，看是从缓存读还是从磁盘读，磁盘读多说明缓存不够或查询效率低

四、事务和并发最佳实践

1. 事务要短。

长事务是PG的大敌。长事务会导致：

  • VACUUM无法清理死元组，表膨胀
  • 锁持有时间长，阻塞其他操作
  • 复制延迟增大
  • 回滚段（WAL）堆积

所以事务要尽量短，只把必须原子化的操作放在一个事务里。不要在事务里做外部API调用、不要在事务里等用户输入、不要在事务里做大量计算。

2. 选择合适的隔离级别。

PG的默认隔离级别是READ COMMITTED，大部分场景够用。如果需要更严格的一致性，用REPEATABLE READ或SERIALIZABLE，但要注意性能开销和冲突重试。

不要为了"安全"一律用SERIALIZABLE，在高并发下会有大量冲突，性能很差。根据业务需求选择合适的级别。

3. 锁的问题。

PG的锁机制很完善，但也要注意：

  • 避免长时间持有排他锁
  • DDL操作（ALTER TABLE、CREATE INDEX等）会锁表，尽量在低峰期执行，或者用CREATE INDEX CONCURRENTLY
  • 用pg_locks视图监控锁等待，发现长时间等待的会话要及时处理
  • 死锁会自动检测和回滚，但要在应用层做好重试

4. 连接管理。

不要为每个请求创建一个数据库连接，连接创建的开销很大。用连接池，比如PgBouncer或应用层的连接池。

连接池的大小要合理，不是越大越好。PG的最佳连接数大约是CPU核心数的2到3倍，太多连接会导致上下文切换和内存开销增大，性能反而下降。

五、备份和恢复最佳实践

1. 多种备份方式结合。

不要只靠一种备份方式，建议：

  • pg_dump逻辑备份：每天一次，保留最近7天。适合单表恢复和数据导出。
  • 物理备份+WAL归档：用pg_basebackup做基础备份，持续归档WAL，支持PITR（时间点恢复）。这是生产环境的主力备份。
  • 异地备份：把备份文件同步到另一个机房或云存储，防止机房级故障。

2. 定期测试恢复。

备份了不代表能恢复。一定要定期做恢复演练，至少每季度一次。测试完整的恢复流程，确认备份可用、恢复步骤正确、恢复时间在可接受范围内。

我们有一次磁盘故障，因为平时做过恢复演练，很快就恢复了服务，RTO不到30分钟。如果没演练过，临时查文档，可能要折腾好几个小时。

3. 监控备份状态。

备份任务要监控，失败了要告警。不要等需要恢复的时候才发现备份已经失败好几天了。

六、高可用最佳实践

1. 流复制。

生产环境至少要有一个热备节点，用流复制（Streaming Replication）。主库写入，备库实时同步。主库挂了，可以切换到备库。

复制模式选replica（默认），不要用sync（同步复制），除非对数据一致性要求极高。同步复制会增加写入延迟，而且备库挂了主库也会受影响。

2. 自动故障切换。

不要手动故障切换，太慢了。用Patroni或pgautofailover做自动故障切换，主库挂了自动提升备库，秒级切换。

还要配合负载均衡（如HAProxy），让应用自动连到新的主库。

3. 监控复制延迟。

复制延迟要监控，延迟过大说明有问题（网络、IO、大事务等）。用pgstatreplication视图看延迟，超过阈值就告警。

七、监控和运维最佳实践

1. 关键监控指标。

至少要监控这些指标：

  • 连接数：当前连接数、连接数使用率
  • 数据库大小、表大小、索引大小
  • 缓存命中率：pgstatdatabase中的blkshit / (blkshit + blks_read)，应该在99%以上
  • 慢查询：用pgstatstatements记录，定期分析
  • 复制延迟
  • 死锁数量
  • 长事务：超过5分钟的事务要告警
  • 表膨胀：死元组比例，过高说明VACUUM有问题

2. 定期VACUUM和ANALYZE。

PG的MVCC机制会产生死元组，需要VACUUM来清理。 autovacuum默认是开的，但大表可能需要调整参数（autovacuumvacuumscalefactor、autovacuumvacuumcostlimit等）。

ANALYZE更新统计信息，统计信息不准会导致执行计划变差。大表可以调大defaultstatisticstarget，让统计更精确。

3. 版本升级。

关注PG的小版本更新，安全补丁要及时升级。大版本升级（比如从13升到14）要在测试环境充分验证，用pg_upgrade或逻辑复制的方式升级，减少停机时间。

八、写在最后

PostgreSQL是一个功能强大、性能优秀的数据库，但要在生产环境用好它，需要注意的细节很多。本文总结的这些最佳实践，都是我们在实际项目中踩坑踩出来的。

当然，最佳实践不是教条，要根据自己的业务场景和硬件条件来调整。最重要的是：先理解原理，再做配置；先监控测量，再优化调整；先做好备份，再谈高可用。

如果你也在用PostgreSQL，希望这些经验能帮到你。数据库是应用的基石，把数据库用好，整个系统的稳定性和性能都会上一个台阶。