POSTGRESQL 性能优化 数据的DML 需要进行优化吗？ 当然

实际上针对ORACLE ,SQL SERVER ，MYSQL 很少听说对于DML 语句进行特殊的优化，当然这里批量进行数据更新和小事务更新，数据包大小，一次更新，插入多少行，删除时使用逻辑的方式，等等，这和POSTGRESQL DML 优化是无关的，和所有的数据库的优化是有关的，所以今天说的是，只对，只对，只对，POSTGRESQL DML 操作优化有关的方法。

实际上POSTGRESQL 的DML 只分为两类

1 INSERT

2 SELECT INSERT = UPDATE OR DELETE

数据插入和找到需要进行修改的数据，并在次将新的数据进行插入的操作，或标记的工作。

所以基于两个DML的基本的操作我们需要优化的两个点

1 优化定位数据

2 优化数据的插入或标记

看上去很简单的工作，但我们考虑的方向却非常多，我们需要考虑如下的问题

1 表中的INDEX 的数量和质量问题

2 数据表中的FACTOR的状态和设置

3 UPDATE 的频率问题

4 Autovacuum 频率调整的问题

5 定期需要进行pg_repack 的工作

6 外键与约束的影响

我们从第一个问题看

1 表中的索引的问题，索引和表在数据的DML操作是具有原子性的，也就是你的表中涵盖的索引的数据量和索引的字段的多少和值的大小，直接影响了你数据DML 操作的性能。一般来说索引的字段的字节数如果与你的表中的每行的字节数如果大致相当的话，那么说明你的数据插入应该比独立的表要慢一倍。这是非常容易被理解的，同时还有一个问题就是在数据插入中尤其是频繁插入或UPDATE 产生的锁的问题，因为索引是有顺序的，所以数据在刷到磁盘时是必须重新进行排序并在数据表空间中找到新的位置进行数据的插入或标记，过多的索引在UPDATE 中会导致过多的链，指向已经失效的索引数据，等，对于查询本身也是一个性能的消耗。

2 Factor 因子，大部分数据表操作中都很少触及这个项目，但POSTGRESQL 在频繁的UPDATE 中尽量还是考虑这个问题，因为UPDATE = DELETE OLD + INSERT NEW ， 如果你的记录一直在被放到不同的页面中，导致数据页面的顺序与你数据的逻辑的顺序差别太大，你会让一些 RANGE 查询的效率降低并且会浪费更多的SHARED BUFFER POOL ， 因为本来一个页面解决的事情，在你应用设计不好的情况下，可能会有N 个页面参与。

频繁UPDATE 的页面一定要考虑预留更多的空间，让UPDATE 后的数据可以留在原来的页面。

3 UPDATE 的频率的问题，这点在其他数据库上还好，性能是收到影响的，但表空间和磁盘的空间可能影响的不大，但是针对与POSTGRESQL 本身那么频繁的UPDATE 一行数据，将POSTGRESQL 当做一些缓存型数据库使用，那么表空间会膨胀的厉害，让POSTGRESQL 在这个表上的查询性能衰减。

4 Autovacuum 这个是一个老生常谈的问题，随着表越来越大，AUTOVACUUM 必须针对这些表做有针对性的调整，而不是针对同一的参数进行调整，并且POSTGRESQL 应该有一套脚本来自动定期对大表的参数进行修改（具体请参看之前的关于AUTOVACUUM的文字），这样才能对POSTGRESQL 大表的DML 操作后进行有效的维护，保证表的性能不衰减的厉害。

5 对于一些中型的表，如果可以在业务非繁忙期，进行定期的PG_REPACK操作，回收表的空间，并且有助于提高系统磁盘空间的利用率和相关的表的性能的提高。同时操作PG_REPACK的同时对于索引的碎片也是一种修复。

6 外键与约束，约束本身并不会对表的数据的插入产生过多的影响，而一些设计不好的外键并且带有级联性质的设计，才是对表DML操作带来性能问题的一个因素，减少外键级联的设计，有助于提高POSTGRESQL 的数据的DML 操作的性能。实际上除了这些，还有trigger的操作，如果 trigger 本身并不是频繁触发，并且里面并不带有大了的UPDATE 语句，那么trigger 还是可以被接受的，但如果trigger 本身里面就带有大了的UPDATE 操作，并且触发频繁，那么这样的设计就有必要进行考量和改变了。