RDS PostgreSQL开发运维建议

客户端连接池

• 使用绑定变量（PreparedStatement），消除数据库SQL硬解析CPU消耗，对性能提升巨大。
• 减少idle connection数量。可以降低RDS PostgreSQL连接内存占用，并提升GetSnapshotData()效率，提升性能。
• 使用应用连接池，避免短连接损耗和性能下降。如果应用没有连接池，建议在应用层和数据库之间架设连接池，例如使用PgBouncer或者Pgpool-II作为连接池。
• 应用连接池参数的配置建议：
  • minimumIdle=1：控制连接池空闲连接的最小数量，减少idle connection。
    说明 当前大多数连接池已取消了maxIdle参数，如果有，maxIdle参数设置为1。
  • maxLifetime：建议设置为60分钟，用来设置一个connection在连接池中的最大存活时间。可以极大降低连接Relcache内存导致OOM的概率。
  • maximumPoolSize=15：单个连接池实例允许的最大连接数，15已经满足绝大多数应用需求。如果连接池客户端个数较少，负载集中，可以适当增大这些客户端的maximumPoolSize。
  说明

  常见应用连接池参数的配置建议如下：

  • HikariCP（Java，推荐的连接池）:
    minimumIdle=1, maximumPoolSize=15, idleTimeout=600000 (10 minutes), maxLifetime=3600000 (60 minutes)

  • GORM（golang，推荐的连接池）:
    sqlDB.SetMaxIdleConns(1), sqlDB.SetMaxOpenConns(15), sqlDB.SetConnMaxLifetime(time.Hour)

  • Druid（Java）：
    initialSize=1, minIdle=1, maxIdle=1, maxActive=15, testOnBorrow=false, testOnReturn=false, testWhileIdle=true,minEvictableIdleTimeMillis=600000 (10 minutes), maxEvictableIdleTimeMillis=900000 (15 minutes), timeBetweenEvictionRunsMillis=60000 (1 minutes), maxWait=6000 (6 seconds).

  上述配置不涉及PreparedStatement配置，PreparedStatement需要另外配置。

性能与稳定性

• 单个库的表数量建议不要超过5000个，1个分区表的子分区个数建议不要过个128个，若按时间分区，使用按月或年分区，不建议按日分区。以此减少每个连接Relcache内存占用。
• 新建索引，对于在线业务，推荐使用CREATE INDEX CONCURRENTLY方式创建索引,，不堵塞其他会话对被创建索引表的DML（INSERT、UPDATE或DELETE）操作。创建方法请参见CONCURRENTLY方式创建索引。
• 重建索引，对PostgreSQL 12及以上大版本，使用REINDEX CONCURRENTLY。PostgreSQL 11及下大版本，使用CONCURRENTLY创建新索引成功后，再删除老索引。
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。特别是ON COMMIT DROP，务必慎用。通常临时表功能，可以使用WITH语句替代。
• PostgreSQL13在分区表、HashAggregate(group by)、并行查询等有较大的优化，建议升级PostgreSQL大版本。具体请参见RDS PostgreSQL升级数据库大版本。
• 游标使用后及时关闭。
• 使用TRUNCATE代替DELETE全表，提升性能。
• PostgreSQL支持DDL事务，支持回滚DDL，建议将DDL封装在事务中执行，必要时可以回滚，但是需要注意事务的长度，避免长时间堵塞DDL对象的读操作。
• 如果有大批量的数据入库，建议使用copy语法，或者INSERT INTO table VALUES (),(),...();的方式，提高写入速度。

实例小版本建议

• 使用Replication Slot（例如逻辑订阅），强烈建议升级小版本到20201230及以上，以开启逻辑复制槽故障转移，同时设置最大复制槽延迟告警，以防止订阅延迟或中断造成Slot位点无法推进，进而造成PostgreSQL数据库wal堆积。
• 开启审计日志或者性能洞察（log_statement=all），强烈建议升级小版本到20211031及以上。
  说明 log_statement=all在高并发场景（active_conn > 50）下，提升性能约4倍，并且消除了log_statement=all导致的SysCpu飙升问题。

监控告警

• 强烈建议开启RDS PostgreSQL云监控模版的告警项，参见管理报警。

问题排查

• RDS PostgreSQL慢SQL问题排查：请参见慢SQL统计和分析方法（EXPLAIN）。
• 查找最耗费资源的SQL（Top SQL）：请参见查找最耗费资源的 SQL（Top SQL）。

设计

数据查询

• 不建议使用COUNT(列名)或COUNT(常量)来替代COUNT(*)，COUNT(*)是SQL92定义的标准统计行数的语法，会统计NULL值（真实行数），而COUNT(列名)不会统计。
• 使用COUNT(多列列名)时，多列列名必须使用括号，例如COUNT( (col1,col2,col3) )。注意使用COUNT(多列列名)时，所有NULL行都会被计数，所以效果与COUNT(*)一致。
• 不建议使用SELECT * FROM t，用具体的字段列表代替*，避免返回用不到的字段。
• 除ETL（Extract-Transform-Load）程序外，建议避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
• 对于需要范围查询的场景，建议使用范围类型以及GiST索引，提高范围检索的查询性能。
• 如果应用经常访问较大结果集的数据（例如100条），建议将数据聚合成1条，例如经常要按ID访问此ID的数据，建议定期按ID聚合数据，查询时返回的记录数越少响应越快。

管理

• DDL操作之前务必要经过评审，并且选择在低峰期执行。
• 删除和修改记录时，为避免误删除，建议先使用SELECT确认后，再提交执行。如果业务逻辑上确定只更改1行，则添加LIMIT 1。
• DDL操作（以及类似的可能获取锁的操作，例如VACUUM FULL、CREATE INDEX等）建议设置锁等待，用于防止堵塞所有与该DDL锁对象相关的查询。
  begin;  
  SET local lock_timeout = '10s';  
  -- DDL query;  
  end;

• EXPLAIN (ANALYZE) 语句的工作方式类似于EXPLAIN，主要区别在于前者会实际执行SQL。如果SQL涉及数据变更，即DML SQL（UPDATE、INSERT或DELETE），务必在事务中执行EXPLAIN (ANALYZE)，查看完成后再进行回滚。
  begin;  
  EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>; 
  rollback;

• 大批量删除和更新数据时，建议分批次操作，不建议在一个事务中完成，以免一次产生较多垃圾。