POSTGRESQL 性能优化 DML 操作如何设计

无

论应用设计的在复杂，到了数据库阶段，可以专注两点 查询和写入，关于操作中的一切都是在内存中完成的。

这里有两个想法

1  读操作比写操作要耗费的资源少，读操作仅仅是将数据库从磁盘读取到内存，而写操作不是，写操作会导致一系列的操作，如日志的数据写入，关于更新数据的索引的操作，同时还需要考虑将数据在进行预处理后，在刷新到磁盘中，最终还有对数据的维护性的操作 VACUUM ,ANALYZE 等，所以DML 操作，对数据库的性能影响相对SELECT是大的。

同时另一个问题是对读的影响，如同如果一个系统中如果只是读的操作，那么维护起来相对于一个经常在系统中进行DML操作的数据库，那么是好维护的。而数据库奇妙的地方就是，如果你单单拿出任何一个数据库操作都很容易了解他对数据库的影响，但如果将这些单独的事务放到数据库中从量到值的变化后，互相影响就变得非常重要，需要理解，SELECT 与 DML 在共同工作中的影响。

这就产生了一个最近对数据库操作中的一贯的思维方式，事务的大小对于数据库的性能用影响的问题，之前我们习惯存储过程的思路来设计数据库的模式，在当今不流行或成为一种过时的思维模式，这与我们目前很多业务的并非量是有关的，一个系统如果没有并发，那么在设计中其实可以不考虑很多问题，只需要关注一个操作的速度。

而在一个并非量非常大的系统，就需要在DML 操作中考虑DML 操作的大小针对锁时间的使用，越大的DML操作会导致资源锁定的时间过长，而过长的锁定会对同样对这些资源使用的其他事物造成困扰。

继而产生事物的等待，甚至是死锁，所以在设计一个应用的时候，可能对于DML的设计还要考虑使用更小的事务代替大事务，让每个事务操作的缝隙，进来不要产生资源的冲突。而在POSTGRESQL 中针对UPDATE 的操作对比其他的数据库要更加关注, 从原理的角度上看，POSTGRESQL 最主要的性能损耗的操作就是UPDATE ，UPDATE 会产生如下问题

1   大量的死行

2   占用更多的空间

3   索引出现更多的指针和链

4   需要更多的VACUUM 操作的时间

5   处理不当占用更多的磁盘空间

同时在有UPDATE 较多的系统中，尤其是并非高的系统中，隔离级别的使用也是降低系统性能降低的一个选择，RR 总是比 RC 要在这方面消耗更多的资源，尤其使用SI 的方式处理隔离级别的方式，RR 在PG 中的产生的问题要比RC 多的多，尤其是并非中，事务执行失败的可能性对比RC 要高的多。

回到刚才的问题，基于PG UPDATE的操作产生新行的方式，对于索引产生碎片的速度，以及查询效率都有不同的损耗，所以PG 提出了HOT heap only tuples 的方式来处理部分问题，这里面又牵扯一个另外的问题  FACTOR ，填充因子，所以PG 在使用中，都是需要进行更细度的优化的，如果你的表经常进行UPDATE 那么FACTOR 的填充因子的数值就需要进行调整。

将如上这些问题都考虑，一个在PG中频繁进行数据更新的设计一定不会得到什么好的结果，最终会影响你的SELECT ，因为你的大了的page无法得到你有效的数据，导致必须将更多的页面加载到内存，所以导致恶性循环，UDPATE 的越多，死行越多，表和索引碎片的问题，FACTOR 调整导致数据读取的页面更多，shared buffer  浪费的更严重，最终你会得到一个很糟糕的数据库和基于上面的应用系统。

而这还没有完，更多的UDPATE 会触发VACUUM 的操作，导致I/O 系统的压力，所以此时你还认为你觉得在正常不过的UPDATE 那么的心安理得。

所以基于POSTGRESQL 对于在一个行上 频率很高的更新的方式的应用设计，是不适合的。需要从业务的架构和应用的设计，软件的架构设计来入手规避这个问题，那么那时你的基于PG 的应用系统才能更好的运行。

基于PG 的应用程序设计，需要注意的地方很多，如果你还用ORACLE的在UPDATE 上的经验在PG 上应用，那么就是一个失败的开始。