PostgreSQL MVCC原理以及事务可见性对执行计划的影响

先从上次遇到的一个执行计划相关的疑问入手，类似于select count(1) from table的查询，可以在即某些较小字段上的索引进行扫描来替代全表扫描来实现count优化，这是一个MySQL中传统的优化套路，但是在postgresql中类似场景总是会走全表扫描而不是预期的索引扫描，为什么同样的套路搬到Postgresql上就不好使了？这是一个postgresql中一个典型的MVCC机制造成的，应该是一个比较有代表性的问题了，看起来是十年前就有人问过类似的问题了https://dba.stackexchange.com/questions/2070/postgresql-count-uses-a-sequential-scan-not-index  因此经验跟教条之间，其实很近很近，不能轻易“复印”的以往的经验。
 
Postgresql MVCC下的数据可见性
这里涉及到一个数据可见性的问题
Postgresql在数据修改时通过保留数据的历史版本来实现MVCC，也即不同的事务要看到同一条数据的不同版本，这需要依次保留不同版本的问题。
不同的数据库的MVCC机制实现是不同的，MySQL或者Oracle中是通过将历史记录写入undo表空间实现，Postgresql是直接在当前页面保留这个数据的历史版本。
这里暂时抛开Postgresql的HOT优化机制，粗略来看一条update或者delete发生时是如何实现多版本的。
数据修改操作：将某一行的data字段从a修改为b
可以直观地想象一下Postgresql中修改一条记录事生成的“undo”记录的实现，（当然除此之外这个undo记录与xlog有关）
其过程就是update的时候保留老的记录，重新写入一条新纪录的， 通过不同的事务Id决定不同的事务可以看到修改前或者修改后的记录
数据删除操作：这里示例删除上面修改后的记录的过程
删除操作是类似的一个过程，仅标记原始记录被删除（set t_xmax），但此时记录还保存在原地。
这里就存在2个问题：
1、谁&什么时候&什么条件下，清理历史版本
大量的历史版本会造成表膨胀的问题，不过目前看来应该不是问题，绝大多数情况下后台清理进程完全可以hold的住。
其实这个问题源自于MVCC需要保留不同版本数据的机制造成的，是一个支持MVCC的共性问题，MySQL中也有类似问题，MySQL 5.7之前undo 表空间膨胀且无法装直接收缩，业内也为此整出来各种奇淫巧技来处理该问题、所以某些问题是必须要经历或者说面对的，没有绝对好或者绝对坏的方法。
参考前面统计新信息更新时涉及到的vacuum自动化机制：https://www.cnblogs.com/wy123/p/13347176.html
2、如何解决索引键无法“直接”感知数据行的变化（索引上没有行版本信息）
多版本的只能在数据行级别体现，而无法在索引树中体现出来，也就是说索引上是没有版本信息的，删除一条记录会标记一条记录删除前的版本，以及将新写入一个条记录并标记为删除，这个过程可以认为该表上的索引是无感知的，或者对应的索引键是无法直接知道“我对应的记录被删除了”，这一点是postgresql所特有的。如果索引想知道其某个键值对应的数据行有没有发生变化或者被删除，是需要结合clog，也就是commit log（新版本中叫xact log）的，通过索引键访问数据行的时候，需要经过xlog做一次验证，才能决定该索引键是否发生了变换（增删改）。
所以现在可以想明白，为什么在count(1) 不会通过仅扫描索引就可以完成的了吧，因为在计算总行数的过程中，必须要通过“回表”重新验证该记录是否当当前事务可见。
“回表”这一点如何体现？如下demo select count(c2) from myschema.table_test where c2>100 and c2<103;
可以发现其执行过程中虽然是index only scan，但Heap Fetches标明依旧进行了回表（验证索引上符合条件数据的可见性），因此这里的执行计划显式的index only scan并不合适。
此外随着数据范围的增加，优化器开始采用bitmap scan的方式来执行，其目的只有一个：回表进行数据可见性的检查（Heap Blocks）
上面两种情况都是一个小范围的count，换成一个大范围或者全表的count，如果每次这么回表（Heap Fetches）或者bitmap index scan校验就太低效了，那么就直接全表扫描还是相对比较直接的做法。
 
 
可见性映射
为了避免索引上没有版本信息导致的回表recheck，PostgreSQL对目标表的做了一个可见性映射。也就是说，如果一个页面中存储的所有元组都是可见的，PostgreSQL使用索引元组的键，就无须回表再次确认数据的可见性，否则，PostgreSQL将从索引元组中读取指向的表元组，并检查元组的可见性，这是一个常规的过程。需要注意的是，这个可见性映射是一个非精确值。参考http://www.postgres.cn/docs/9.4/storage-vm.html
其原理如下图所示，当前事务通过一个visibility map元数据来判断哪些数据页面是可见的，哪些数据页面（因为发生过修改）是不可见的。
那么这个可见性映射visibility map如何直观地体现出来？
这里涉及到pg_class表的一个relallvisible字段，其含义是在表的可见映射中标记所有可见的页的数目。只是优化参考的一个估计值， 由VACUUM,ANALYZE 和几个 DDL 命令，比如CREATE INDEX更新。
这个字段的解释见这里：http://www.postgres.cn/docs/9.4/catalog-pg-class.html
在更新relallvisible字段的信息之后，再次执行select count(c2) from myschema.table_test where c2>100 and c2<103;因为可见性映射告诉优化器复合条件的数据页面的数据都是可见的，因此这里就无须再次回表recheck了

Postgresql MVCC机制的优缺点

这里称Postgresql的MVCC实现为“原地副本”，其特色是可实现快速回滚，一是因为事务修改前版本还在“原地”，二是依赖于事务的clog，事务的提交与否是通过事更新事务的clog中的标记位来实现的，因此事务的大小（修改1行和修改100W行数据），回滚时其代价是一样的。其次，基于“原地”的数据行副本，相比将数据修改前的副本转移到undo表空间需要来回移动数据，个人认为这样原地操作效率会稍高一点，同时这也是其缺点，大量的数据修改和删除，会造成表空间的膨胀，在vacuum回收之前会对加大读操作的代价。同时，即便有vacuum回收这个不可见数据副本，也更容易造成存储空间上的碎片。
参考链接

某些经验可以重用，但是不可复印

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wy123/p/13403676.html