【RDS PostgreSQL】RDS PostgreSQL开发运维建议

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 本文介绍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支持设置一键告警,对您关注的指标设置监控项,在触发监控项的报警规则时,通过邮件和短信通知报警联系组中的所有联系人,有利于及时发现隐患,提升系统稳定性。更多信息,请参见管理报警
  • RDS PostgreSQL提供自治服务DAS(Database Autonomy Service),提供智能的诊断及优化功能,能最大限度发现数据库存在的或潜在的健康问题,消除数据库管理的复杂性及人工操作引发的服务故障,有效保障数据库服务的稳定、安全及高效。更多信息,请参见性能优化与诊断


相关参考

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

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
2月前
|
存储 关系型数据库 数据库
附部署代码|云数据库RDS 全托管 Supabase服务:小白轻松搞定开发AI应用
本文通过一个 Agentic RAG 应用的完整构建流程,展示了如何借助 RDS Supabase 快速搭建具备知识处理与智能决策能力的 AI 应用,展示从数据准备到应用部署的全流程,相较于传统开发模式效率大幅提升。
附部署代码|云数据库RDS 全托管 Supabase服务:小白轻松搞定开发AI应用
|
2月前
|
SQL 运维 自然语言处理
Dataphin智能化重磅升级!编码难题一扫光,开发运维更高效!
Dataphin重磅推出三大核心智能化能力:智能代码助手提升SQL开发效率;智能运维助手实现移动化任务管理;智能分析通过自然语言生成SQL,助力数据价值释放。未来将持续开放智能ETL、安全助手等能力,助力企业构建高效、稳定的数据资产体系。
286 0
|
3月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
559 1
|
3月前
|
人工智能 OLAP 数据处理
解锁数仓内AI流水线,AnalyticDB Ray基于多模ETL+ML提效开发与运维
AnalyticDB Ray 是AnalyticDB MySQL 推出的全托管Ray服务,基于开源 Ray 的丰富生态,经过多模态处理、具身智能、搜索推荐、金融风控等场景的锤炼,对Ray内核和服务能力进行了全栈增强。
|
5月前
|
人工智能 运维 关系型数据库
|
6月前
|
人工智能 运维 安全
AI大模型运维开发探索第四篇:智能体分阶段演进路线
本文探讨了智能体工程的演进历程,从最初的思维链(智能体1.0)到实例化智能体(智能体2.0),再到结构化智能体(智能体3.0),最终展望了自演进智能体(智能体4.0)。文章详细分析了各阶段遇到的问题及解决策略,如工具调用可靠性、推理能力提升等,并引入了大模型中间件的概念以优化业务平台与工具间的协调。此外,文中还提到了RunnableHub开源项目,为读者提供了实际落地的参考方案。通过不断迭代,智能体逐渐具备更强的适应性和解决问题的能力,展现了未来AI发展的潜力。
|
2月前
|
敏捷开发 运维 数据可视化
DevOps看板工具中的协作功能:如何打破开发、测试与运维之间的沟通壁垒
在DevOps实践中,看板工具通过可视化任务管理和自动化流程,提升开发与运维团队的协作效率。它支持敏捷开发、持续交付,助力团队高效应对需求变化,实现跨职能协作与流程优化。
|
2月前
|
人工智能 运维 自然语言处理
首个智能体模型实测:产品、开发、运维“全包了”
2025年,AI进入“动手”时代。智谱发布新一代大模型GLM-4.5,全球排名第三、国产第一,专为智能体设计,融合推理、编码与智能体能力,实现自主规划与执行任务。通过8个Demo展示其强大能力,涵盖网页设计、课件制作、小游戏开发等,展现其“带手的脑”特性,推动AI从实验室走向真实场景。
117 0
|
8月前
|
SQL 人工智能 关系型数据库
【PG锦囊】阿里云 RDS PostgreSQL 版插件—AI 插件(rds_ai)
本文介绍了AI 插件(rds_ai)的核心优势、适用场景等,帮助您更好地了解 rds_ai 插件。想了解更多 RDS 插件信息和讨论交流,欢迎加入 RDS PG 插件用户专项服务群(103525002795)

推荐镜像

更多