本文介绍RDS PostgreSQL的一些开发运维建议,帮助您有效提升数据库使用的规范性、稳定性和高性能。

设计

  • 表结构中字段定义的数据类型建议与应用程序中的定义保持一致,表之间字段校对规则一致,避免报错或无法使用索引的情况发生。
  • 建议分区表的主键序列全局唯一。
  • 对于存在定期历史数据删除需求的业务,建议数据表按时间分区,删除时使用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、拓扑等)。

  • 所有字符的存储与表示,建议使用UTF-8编码。
  • COMMENT内容不建议使用中文,避免由于写入与读取的编码不一样而降低可读性。
  • 逻辑备份(pg_dump)时建议与COMMENT内容的编码一致,避免乱码。

数据查询

  • SELECT语句中使用AS关键字设置别名时,建议使用:小写字母(a~z),下划线(_),数字(0~9)。
  • 不建议使用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聚合数据,查询时返回的记录数越少响应越快。

管理

  • 删除和修改记录时,为避免误删除,建议先使用SELECT确认后,再提交执行。
  • DDL操作(以及类似的可能获取锁的操作,例如VACUUM FULLCREATE INDEX等)建议设置锁等待,用于防止堵塞所有与该DDL锁对象相关的查询。
    begin;  
    SET local lock_timeout = '10s';  
    -- DDL query;  
    end;
  • 您可以使用EXPLAIN ANALYZE查看实际的执行计划,但是如果需要查看的执行计划涉及数据变更时,必须在事务中执行EXPLAIN ANALYZE,查看完成后再进行回滚。
    begin;  
    EXPLAIN ANALYZE query;  
    rollback;
  • 建议为每个业务分配不同的数据库账号,不建议多个业务共用一个数据库账号。
  • 数据库名推荐由部门名称与功能组成或与应用名一致,提升辨识度。
  • 建议为每个业务分配对应的SCHEMA,schema_name与user name一致。
  • 大批量删除和更新数据时,建议分批次操作,不建议在一个事务中完成,以免一次产生较多垃圾。如果一定要大批量操作的话,建议操作前检查数据表膨胀率,操作后使用清理表空间(pg_repack)重组表。

性能与稳定性

  • 在代码中写分页查询逻辑时,建议当count为0应直接返回,避免执行后面的分页语句。
  • 游标使用后建议及时关闭。
  • 建议使用TRUNCATE代替DELETE全表,提升性能。
  • 对于高并发的应用场景,建议使用绑定变量(PreparedStatement),防止数据库硬解析消耗过多的CPU资源。建议使用程序的连接池,避免性能下降。如果程序没有连接池,建议在应用层和数据库之间架设连接池,例如使用PgBouncer或者Pgpool-II作为连接池。
  • 如果您的数据不需要持久化,建议使用UNLOGGED table来提升数据写入和修改的性能。
  • 在函数或程序中,不建议使用COUNT(*)判断是否存在数据。 建议在语句中使用LIMIT 1
  • 避免频繁创建和删除临时表,以减少系统表资源的消耗。
  • PostgreSQL支持DDL事务,支持回滚DDL,建议将DDL封装在事务中执行,必要时可以回滚,但是需要注意事务的长度,避免长时间堵塞DDL对象的读操作。
  • 尽量在业务层面避免死锁的产生,例如一个用户的数据,尽量在一个线程内处理,而不要跨线程(即跨数据库会话处理)。
  • 对于网络复杂并且RT(Reaction Time)要求很高的场景,如果业务逻辑冗长,建议减少数据库和程序之间的交互次数,使用数据库存储过程(如 PL/pgSQL)或内置函数。PostgreSQL内置的PL/pgSQL函数语言提供处理复杂业务逻辑的功能。

    PostgreSQL还内置了分析函数、聚合函数、窗口函数、普通类型函数、复杂类型函数、数学函数和几何函数等多种函数。

  • 如果有大批量的数据入库,建议使用copy语法,或者INSERT INTO table VALUES (),(),...();的方式,提高写入速度。

监控与诊断分析

  • RDS PostgreSQL支持通过增强监控查看操作系统及数据库的性能监控项,您可以查看30天内任意时间范围内的指标数据变化,有利于判断性能下降的时间点,排查性能问题,还可以根据不同的时间范围(如30分钟、1小时、7天等)查看各指标的平均值、最大值和最小值,掌握实例的运行情况,比较系统操作峰值与非峰值时的性能差异。增强监控的更多信息,请参见查看增强监控
  • RDS PostgreSQL支持设置一键告警,对您关注的指标设置监控项,在触发监控项的报警规则时,通过邮件和短信通知报警联系组中的所有联系人,有利于及时发现隐患,提升系统稳定性。更多信息,请参见管理报警

相关参考

关于PostgreSQL的更多开发运维建议,请参见PostgreSQL 数据库开发规范