本文介绍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(INSERTUPDATEDELETE)操作。创建方法请参见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云监控模版的告警项,参见管理报警

问题排查

设计

权限
  • 权限管理,以schema/role为单位分配权限,创建readwrite/readonly账号,遵循最小权限原则,请参见RDS PostgeSQL权限管理最佳实践
  • 若使用应⽤层读写分离,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 FULLCREATE INDEX等)建议设置锁等待,用于防止堵塞所有与该DDL锁对象相关的查询。
    begin;  
    SET local lock_timeout = '10s';  
    -- DDL query;  
    end;
  • EXPLAIN (ANALYZE) 语句的工作方式类似于EXPLAIN,主要区别在于前者会实际执行SQL。如果SQL涉及数据变更,即DML SQL(UPDATEINSERTDELETE),务必在事务中执行EXPLAIN (ANALYZE),查看完成后再进行回滚。
    begin;  
    EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>; 
    rollback;
  • 大批量删除和更新数据时,建议分批次操作,不建议在一个事务中完成,以免一次产生较多垃圾。