RDS PostgreSQL开发运维建议_云数据库 RDS(RDS)-阿里云帮助中心

本文介绍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需要另外配置。

性能与稳定性

在RDS PostgreSQL中，单个数据库（DB）对应底层文件系统中的一个文件夹，数据库中的表、分区、索引对应文件夹中的文件，如果数据库对应文件夹中的文件数量超过2000万，将可能出现磁盘空间满的报错。建议适当拆分数据库或合并表文件。
新建索引，对于在线业务，推荐使用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云监控模板的告警项，参见管理报警。
您可以根据业务情况自行设置内存使用率告警阈值，建议设置在85%到95%之间。

问题排查

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

设计

权限

权限管理，以schema/role为单位分配权限，创建readwrite/readonly账号，遵循最小权限原则，请参见RDS PostgreSQL权限管理最佳实践。
若使用应⽤层读写分离，readonly客户端建议使⽤readonly账号，最小权限原则，避免权限误用。

表

表结构中字段定义的数据类型建议与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生。
对于存在定期历史数据删除需求的业务，建议数据表按时间分区，按时间月或年区分，删除时使用DROP或者TRUNCATE操作对应的子表，不建议使用DELETE操作。
对于频繁更新的表，建议在建表时指定表的FILLFACTOR=85，每页预留15%的空间用于HOT更新。
```
CREATE TABLE test123(id int, info text) WITH(FILLFACTOR=85);  
```
临时表建议以tmp_开头，子表建议根据业务场景以规则结尾，例如按年分区的主表如果为tbl，则子表为tbl_2016、tbl_2017等。

索引

B-Tree索引字段至多2000字节，如果存在超过2000字节的字段需要新建索引，建议使用函数索引（例如哈希值索引）或分词索引。
对于线性顺序存储的数据（如流式数据、时间字段或自增字段），通常查询时使用范围查询，建议使用BRIN索引，减少索引的大小，加快数据插入速度。
```
CREATE INDEX idx ON tbl using BRIN(id);
```
建议避免全表扫描（大数据量扫描的数据分析除外），PostgreSQL支持几乎所有数据类型的索引。
索引接口包括：B-Tree、Hash、GIN、GiST、SP-GiST、BRIN、RUM（扩展接口）、Bloom（扩展接口）、PASE（扩展接口）。
主键索引建议以pk_ 开头，唯一索引建议以uk_开头，普通索引建议以idx_开头。

数据类型及字符集

建议选择合适的数据类型，目标数据为数字时不建议使用字符串，目标数据可以存为树类型时不建议使用字符串。
使用合理的数据类型，可以提高数据的查询效率。
PostgreSQL支持的数据类型如下：精确的数字类型、浮点、货币、字符串、字符、字节流、日期、时间、布尔、枚举、几何、网络地址、比特流、文本、UUID、XML、JSON、数组、复合类型、范围类型、对象、行号、大对象、ltree树结构类型、cube多维类型、earth地球类型、hstore类型、pg_trgm相似类型、PostGIS（点、线段、面、路径、经纬度、raster、拓扑等）、HyperLogLog（近似估值统计分析）。
字符串排序规则LC_COLLATE推荐使用 'C'，而非 UTF8。LC_COLLATE=UTF8 性能相对差一些，并且索引需要明确指定UTF8 pattern ops，才能支持LIKE查询。

存储过程

如果业务逻辑冗长，建议减少数据库和程序之间的交互次数，使用数据库存储过程（如 PL/pgSQL）或内置函数。PostgreSQL内置的PL/pgSQL函数语言提供处理复杂业务逻辑的功能。PostgreSQL还内置了分析函数、聚合函数、窗口函数、普通类型函数、复杂类型函数、数学函数和几何函数等多种函数。

数据查询

不建议使用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聚合数据，查询时返回的记录数越少响应越快。

管理

建议开启SQL洞察和审计，该功能支持查询并导出SQL语句执行历史及其对应的各种信息（数据库、状态、执行时间等），对SQL进行健康情况诊断、性能问题排查、业务流量分析等。更多信息，请参见SQL洞察和审计。
如果您需要监控并记录阿里云账号的活动，包括通过阿里云控制台、OpenAPI、开发者工具对云上产品和服务的访问和使用行为，建议使用操作审计功能。您还可以将这些行为事件下载或保存到日志服务或OSS存储空间，然后进行行为分析、安全分析、资源变更行为追踪和行为合规性审计等操作。更多信息，请参见什么是操作审计。
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;
```
大批量删除和更新数据时，建议分批次操作，不建议在一个事务中完成，以免一次产生较多垃圾。