查看pg 用户组_PostgreSQL中文社区走进平安活动用户反馈__近日，来自PostgreSQL社区的数据库专家、平安科技数据库团队、以及平安寿险科技中心团队进行了深入的数据库技术交流。 Post...

#### 近日，来自PostgreSQL社区的数据库专家、平安科技数据库团队、以及平安寿险科技中心团队进行了深入的数据库技术交流。

#### PostgreSQL数据库作为技术最先进、功能最丰富的开源数据库，也是未来发展的一个趋势。此次活动，很荣幸邀请到了PostgreSQL中文社区主席赵振平、PostgreSQL中文社区用户组核心成员朱贤文、PostgreSQL中文社区发起人之一Digoal前来，就开发人员和运营人员关注的问题、PG使用场景、功能特性等方面，多维度地展开交流。

#### 交流过程中，详细解读了PostgreSQL11、12的重磅新特性、分享VACUUM优化经验、分析表膨胀原理及处理措施、探秘PG私有内存，并针对寿险在PG数据库使用中遇到的问题进行了专业的解答。




#### 从2014年开始，平安科技逐步引入多种开源数据库产品，从RDBMS到NoSQL到NewSQL。从以前单纯的Oracle数据库运维，逐渐发展和打造PostgreSQL、MySQL、Redis、MongoDB等多种数据库“武器”，满足不同业务场景的需要。2016年开始逐步推出DBaaS服务，提供数据库自动化交付和管理支持。2018年引入Neo4j、TiDB、YBD等新型数据库产品，并且自研DRDS分布式数据库。2019年计划引入分布式内存数据库和低成本数据仓库，当前正处于选型阶段。通过引入和自研这些数据库，形成了全方位的数据库产品组合，支撑了集团内部各专业子公司的业务发展，同时极大降低了过往对于商业化数据库的依赖。



#### 在PostgreSQL中，频繁update导致的表膨胀问题可能引发许多DB故障与异常，可谓是后患无穷；以下我们通过一些案例演示来分析其根本原理与机制，还有如何有效去避免此类表膨胀问题。

#### 新建一张表newrelic并插入几条数据，查询结果如下：


#### 此时通过pageinspect插件可以看到每行数据是有序紧密分布在数据块上。


#### 更新部分数据：


#### 此时再去检查数据行的分布，可以发现update操作产生了新tuple，而且update行为只是通过逻辑上删除了旧tuple，并将指针指向新tuple，旧的数据行与数据块并不会被及时清理与回收。


#### 由于HEAP引擎的过期数据版本会存储在数据文件中，如果这些数据不能够及时得到清理，会导致表的膨胀。PostgreSQL有自动清理的机制，运维人员也可以通过手动VACUUM进行垃圾回收，但是无法清理掉高水位；需要通过VACUUM FULL来清理掉表上的高水位。



#### 了解了PostgreSQL的MVCC机制实现，就知道过期的数据，应用已经不再使用的数据如果无法及时得到清理，就会导致表以及索引的膨胀。在DB层面，可以通过对于VACUUM相关参数的调整来对过期数据进行回收，使得数据库对象的大小控制在一定范围内。但更重要的是，在应用的设计开发阶段，就应该有意识地尽量少产生过期数据，或者让DB的VACUUM能够工作地更加有效率。

### **那么，如何避免表、索引的膨胀问题?**

#### 1.正确设置 fillfactor，默认为100，意思是数据块空间全部用完，只适合insert only。更新频繁的系统，建议设置为85%，根据实际情况调整。

#### 2.正确设置回收相关的参数(如下参数会影响autovacuum的触发频率及垃圾回收处理效率，需根据业务实际情况做调整)：

```

autovacuum_vacuum_scale_factor = 0.2;

autovacuum_vacuum_threshold = 50

autovacuum_analyze_scale_factor = 0.1;

autovacuum_analyze_threshold = 50

autovacuum_max_workers = 8

autovacuum_naptime= 60/N; N the number of databases

autovacuum_vacuum_cost_limit = -1 (defaults to vacuum_cost_limit = 200)

autovacuum_vacuum_cost_delay = 20ms

vacuum_cost_page_hit = 1

vacuum_cost_page_miss = 10

vacuum_cost_page_dirty = 20

```

#### 3.在应用设计开发阶段，尽量避免长事务。长事务会导致VACUUM无法对过期数据进行清理，而且消耗不必要的系统资源；

#### 4.在SQL语句开发时，改变在其他数据库中的书写习惯，尽量避免产生过多的冗余数据，这些冗余数据最后会变为过期数据；


#### PostgreSQL 11中，分区表就有了非常多的增强特性，比如：

#### 支持哈希分区。

#### 支持触发器

#### 支持默认分区

#### 允许修改分区字段

#### 在PostgreSQL 12中，分区表性能更有百倍提升。对比单表、分区表(1024个分区)的查询、upsert性能，查询性能提升469倍，UPSERT性能提升85倍。

#### 详细测试过程可参考：

#### https://github.com/digoal/blog/blob/master/201905/20190521_01.md



#### PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计，已支持几十种场景的并行计算。在36个测试场景中，平均有20倍性能提升。

#### 详情可参考:

#### https://github.com/digoal/blog/blob/master/201903/20190318_05.md


#### PostgreSQL一直以来都是通过create function来创建函数，通过select或者perform来调用函数，通过inline code来模拟类似procedure的用法。PostgreSQL 11新增了create procedure语法，支持存储过程中的事务管理，这是存储过程与函数的最大区别。

#### 最后，感谢PostgreSQL中文社区对此次活动的大力支持，感谢PostgreSQL中文社区主席赵振平、PostgreSQL中文社区用户组核心成员朱贤文、PostgreSQL中文社区发起人之一Digoal全面深入的数据库技术分享。