PostgreSQL 持续稳定使用的小技巧 - 最佳实践、规约、规范

背景

如同其他数据库一样，使用时需要注意一些问题，那么如何使用PG，可以保证长期稳定。

部署形态设计实践

根据对可靠性、可恢复性、可用性等等的不同要求，选择部署形态：

1、分布式部署（例如pg+citus插件）

容量上限：100节点以上，PB级。

计算能力上限：100节点以上，6400核以上。

读写带宽上限：100节点以上，200GB/s以上。

RPO：如果每个计算节点都采用多副本存储，RPO=0。

RTO：如果每个计算节点都采用HA，RTO可以做到1分钟内。

使用限制：有一些SQL限制。

适应场景：应用代码可控程度高的情况下，适合TP和AP业务。

2、单节点本地存储

容量上限：10TB级。

计算能力上限：64核级。

读写带宽上限：2GB/s级。

RPO：RPO无保障。

RTO：RTO无保障。

使用限制：SQL无限制。

适应场景：测试环境，非生产环境，对数据库RPO，RTO都没有要求的环境。

3、单节点多副本存储

容量上限：32TB级。

计算能力上限：64核级。

读写带宽上限：2GB/s级。

RPO：单机房RPO=0，（如果存储支持跨机房多副本，可以做到多机房RPO=0）。

RTO：10分钟级。

使用限制：SQL无限制。

适应场景：非核心场景生产、测试。

4、双节点共享存储

容量上限：32TB级。

计算能力上限：64核级。

读写带宽上限：2GB/s级。

RPO：单机房RPO=0，（如果存储支持跨机房多副本，可以做到多机房RPO=0）。

RTO：1分钟级。

使用限制：SQL无限制。

适应场景：核心、非核心场景生产。

5、双节点主备异步复制

容量上限：32TB级（使用远程存储），10TB级（使用本机存储）

计算能力上限：64核级。

读写带宽上限：2GB/s级。

RPO：10GB网络，REDO延迟毫秒级、1MB以内。（支持跨机房部署）。心跳机制可确保RPO < 60秒

RTO：1分钟级。

使用限制：SQL无限制。

适应场景：非核心场景生产。

6、双节点主备半同步复制

容量上限：32TB级（使用远程存储），10TB级（使用本机存储）

计算能力上限：64核级。

读写带宽上限：2GB/s级。

RPO：

无节点或单一节点异常时，可保证RPO=0。

两个节点都异常时，RPO取决于备份延迟。采用基于PG流复制的持续REDO备份，可以做到RPO毫秒级。

RTO：1分钟级。

使用限制：SQL无限制。

适应场景：核心、非核心场景生产。

7、三节点及以上多副本全同步复制

容量上限：32TB级（使用远程存储），10TB级（使用本机存储）

计算能力上限：64核级。

读写带宽上限：2GB/s级。

RPO：

小于半数节点异常时，可保证RPO=0。

半数以上节点异常时，RPO取决于 1、10GB网络，REDO延迟毫秒级、1MB以内。2、备份延迟。采用基于PG流复制的持续REDO备份，可以做到RPO毫秒级。

RTO：1分钟级。

使用限制：SQL无限制。

适应场景：核心场景生产。

8、计算存储分离（存储多副本）（比如阿里云POLARDB PG）

容量上限：100TB级。

计算能力上限：16节点，1024核级。

读写带宽上限：32GB/s级。

RPO：单机房RPO=0，（如果存储支持跨机房多副本，可以做到多机房RPO=0）。

RTO：15秒级。

使用限制：SQL无限制。

适应场景：核心、非核心场景生产。

9、计算存储分离（存储多副本）+ 双机房半同步

容量上限：100TB级。

计算能力上限：16节点，1024核级。

读写带宽上限：32GB/s级。

RPO：

无节点或单一节点异常时，可保证RPO=0。

两个节点都异常时，RPO取决于备份延迟。采用基于PG流复制的持续REDO备份，可以做到RPO毫秒级。

RTO：15秒级。

使用限制：SQL无限制。

适应场景：核心、非核心场景生产。

10、计算存储分离（存储多副本）+ 多机房多副本全同步

容量上限：100TB级。

计算能力上限：16节点，1024核级。

读写带宽上限：32GB/s级。

RPO：

小于半数节点异常时，可保证RPO=0。

半数以上节点异常时，RPO取决于 1、10GB网络，REDO延迟毫秒级、1MB以内。2、备份延迟。采用基于PG流复制的持续REDO备份，可以做到RPO毫秒级。

RTO：15秒级。

使用限制：SQL无限制。

适应场景：核心场景生产。

11、只读节点

使用限制：SQL无限制。

适应场景：扩展读能力。

12、非核心功能

12.1、业务透明的读写分离

使用限制：SQL无限制。

适应场景：扩展读能力。

12.2、跨库交互

使用限制：SQL无限制。

适应场景：跨库DBLINK，跨库外部表，跨库物化视图。

12.3、单元化

使用限制：SQL无限制。

适应场景：多实例共享少量数据，多写。

使用实践(规约) - 避坑大法

1、连接数过多（2000以上），可能导致性能下降。

建议使用连接池（例如应用程序使用连接池，或者使用pgbouncer之类的连接池）。连接到数据库的连接在10倍CPU核数以内，达到最高的处理吞吐能力。

2、大吞吐高并发的短连接，性能下降。

建议使用长连接。

3、长连接，长期不释放重建。如果连接访问了大量元数据，可能导致内存占用过大。

建议设置空闲长连接释放机制。确保不会出现大量内存霸占的情况。

《PostgreSQL relcache在长连接应用中的内存霸占"坑"》

4、长事务，以及未结束的2PC事务。

最老事务开始后产生的垃圾版本，无法被垃圾回收进程回收。长事务可能导致垃圾膨胀。

5、业务死锁

6、检查点过短

检查点设置过短，导致FPW狂写，性能下降严重。

建议max wal size, min wal size设置为shared buffer 2倍以及一半。

7、大内存未使用huge page

大内存，未设置shared buffer为huge page，可能导致hash table巨大无比，浪费内存，OOM等连锁反应。

建议32G以上shared buffer，使用huge page。

8、不合理的索引

导致DML性能下降，SELECT性能下降。

建议删除，或修改索引定义。

9、不合理的SQL

《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级》

10、pending list 未合并过大

使用GIN倒排索引，如果写入量特别大，可能导致PENDING LIST合并不及时，当有大量PENDING LIST数据时，查询性能下降急剧。

11、ctype使用错误，例如要查询中文模糊查询加速（pg_trgm），使用ctype=c会导致中文模糊查询无法使用索引。

《PostgreSQL 中英文混合分词特殊规则(中文单字、英文单词) - 中英分明》

12、数据存放不合理导致IO放大

例如空间查询为切片，组必要条件查询未分区。

《PostgreSQL 空间切割(st_split, ST_Subdivide)功能扩展 - 空间对象网格化 (多边形GiST优化)》

《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

13、IO太弱，频繁更新产生垃圾，垃圾回收不及时，膨胀

建议使用SSD硬盘。

14、关闭自动垃圾回收，会导致垃圾无法自动回收，膨胀。

建议打开自动垃圾回收。

15、长时间锁等待

业务逻辑问题，长时间锁等待，可能引发雪崩，连接耗尽等问题。

16、长时间大锁等待，例如在业务系统中高峰期使用DDL语句，可能导致长时间大锁等待。引发雪崩。

建议对DDL操作前，加锁超时参数，避免雪崩。

17、分区过多，导致查询效率下降，连接内存占用过大。

建议合理的设置分区数，例如对于高并发频繁操作的表，建议64个以内分区。对于时间分区表，建议不需要查询的分区或者已经清理数据的分区，从分区中deatch出去，减少优化器压力。

18、DDOS

如果对外开放了连接监听，即使攻击者没有密码，也可以使用DDOS攻击来消耗数据库连接，即利用认证超时的时间窗口，大量建连接，等认证超时，实际上已占用SLOT。导致连接耗尽。

19、滥用超级用户权限账号。

建议业务使用普通权限账号。

20、事务号回卷

如果长事务一直存在并导致了FREEZE无法冻结，超过20亿事务后，数据库为了避免事务号回卷，会强制停库，需要进入单用户进行修复。

21、FREEZE风暴

在9.6以前的版本，FREEZE会导致全表扫描，导致IO风暴。可以预测和防止。

《PostgreSQL Freeze 风暴预测续 - 珍藏级SQL》

《PostgreSQL freeze 风暴导致的IOPS飙升 - 事后追溯》

《PostgreSQL的"天气预报" - 如何预测Freeze IO风暴》

《PostgreSQL 大表自动 freeze 优化思路》

22、slot 堵塞

使用slot进行流复制（逻辑或物理）时，未消耗的日志会在数据库中保留（不会被清理），如果消耗日志很慢可能导致REDO占用空间巨大，甚至导致膨胀到占满磁盘。

有一些SLOT建立后，不需消费它，更加危险。

23、standby feedback

standby 开启feedback后，standby上面的SQL会反馈给主库，主库会延迟回收垃圾，减少STANDBY的SQL与REDO APPLY回放冲突。

但是如果垃圾产生较多，并且autovacuum nap time 唤醒很频繁，会导致CPU和IO的升高。

《PostgreSQL物理"备库"的哪些操作或配置，可能影响"主库"的性能、垃圾回收、IO波动》

24、delay vacuum

主库开启vacuum delay，并且垃圾产生较多，并且autovacuum nap time 唤醒很频繁，会导致CPU和IO的升高。

原因和23一样。

25、大表分区

《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》

内部原理

了解原理后，知道为什么要这些最佳实践

《阿里云 PostgreSQL 产品生态；案例、开发管理实践、原理、学习资料、视频；PG天天象上沙龙记录 - 珍藏级》

《PostgreSQL 2天培训大纲》

监控

《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》

《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级》

《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》

《PostgreSQL 实时健康监控 大屏 - 低频指标 - 珍藏级》

《PostgreSQL 实时健康监控 大屏 - 高频指标(服务器) - 珍藏级》

《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》

《PostgreSQL pgmetrics - 多版本、健康监控指标采集、报告》

日常维护

《PostgreSQL DBA 日常管理 SQL》

培训

体系化培训内容

《PostgreSQL 2天培训大纲》

规范

《PostgreSQL 数据库开发规范》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合