【RDS PostgreSQL】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 FULL

  、

  CREATE 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支持设置一键告警，对您关注的指标设置监控项，在触发监控项的报警规则时，通过邮件和短信通知报警联系组中的所有联系人，有利于及时发现隐患，提升系统稳定性。更多信息，请参见 管理报警
• RDS PostgreSQL提供自治服务DAS（Database Autonomy Service），提供智能的诊断及优化功能，能最大限度发现数据库存在的或潜在的健康问题，消除数据库管理的复杂性及人工操作引发的服务故障，有效保障数据库服务的稳定、安全及高效。更多信息，请参见 性能优化与诊断

相关参考

关于PostgreSQL的更多开发运维建议，请参见

PostgreSQL 数据库开发规范