分享程序员开发的那些事...
更新时间：2022-08-12 17:16:38
PostgreSQL , 索引 , 多版本 , 垃圾版本 , pageinspect
首先介绍几个背景知识，由于这些背景知识的存在，所以在某些情况下索引扫描的性能可能会出现一些问题或抖动。
导致性能下降，CPU开销增加，应用程序访问产生更多的连接等连锁反应。
1、当记录被删除，更新时，PostgreSQL目前的存储引擎会在原HEAP PAGE中保留老的记录版本，如果是UPDATE会写入一条新版本。
2、表上索引也会产生一个新的版本。
3、另一方面，PostgreSQL索引中没有包含版本信息，无法辨识该索引对应记录的可见性，PostgreSQL又引入了一个VM文件（标记每个HEAP PAGE是否所有记录对所有事务可见），从而实现INDEX ONLY SCAN。
4、普通的index scan，需要访问INDEX ITEM对应HEAP TUPLE的TUPLE HEADER来判断记录的可见性。
5、当垃圾回收时，回收heap tuple，回收index item。
6、索引页里面的所有index item都被回收后，索引页才会被回收。（回收该索引块，同时该页左右链接的两个数据块建立直接链接）
那么问题来了，如果索引中大量的index item对应的heap tuple都是不可见（或者已删除，但是没有回收）的版本，那么索引扫描的性能就会下降。同时引入更多的CPU消耗（判断版本的可见性）。
1、创建测试表
关闭垃圾回收（更容易模拟问题）
create table t123(id int, info text);
create index idx_t123_id on t123(id);
alter table t123 set (autovacuum_enabled =off);
2、插入数据
insert into t123 select generate_series(1,1000000), 'test';
3、查询，没有问题，扫描了4个数据块。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t123_id on public.t123 (cost=0.42..3520.93 rows=5000 width=36) (actual time=0.030..0.031 rows=1 loops=1)
Output: id, info
Index Cond: (t123.id = 1)
Buffers: shared hit=4
Planning time: 0.076 ms
Execution time: 0.053 ms
(6 rows)
4、插入不可见数据
A:
postgres=# begin;
BEGIN
postgres=# insert into t123 select 1 , 'test123' from generate_series(1,1000000);
INSERT 0 1000000
5、查询，有问题，扫描了17203个数据块。
B:
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t123_id on public.t123 (cost=0.43..2.45 rows=1 width=10) (actual time=127.646..127.647 rows=1 loops=1)
Output: id, info
Index Cond: (t123.id = 1)
Buffers: shared hit=17203
Planning time: 0.601 ms
Execution time: 127.723 ms
(6 rows)
6、可见，并删除
A:
postgres=# end;
COMMIT
postgres=# select ctid from t123 where id=1 limit 1;
ctid
-------------
(10810,150)
(1 row)
postgres=# delete from t123 where id=1 and ctid <>'(10810,150)';
DELETE 1000000
7、查询，有问题，扫描了20648个数据块。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t123_id on public.t123 (cost=0.43..2.45 rows=1 width=10) (actual time=0.032..146.112 rows=1 loops=1)
Output: id, info
Index Cond: (t123.id = 1)
Buffers: shared hit=20648
Planning time: 0.106 ms
Execution time: 146.142 ms
(6 rows)
8、垃圾回收，回收掉垃圾heap tuple, index item。
postgres=# vacuum verbose analyze t123;
INFO: vacuuming "public.t123"
INFO: scanned index "idx_t123_id" to remove 1000000 row versions
DETAIL: CPU: user: 0.20 s, system: 0.01 s, elapsed: 0.21 s
INFO: "t123": removed 1000000 row versions in 5407 pages
DETAIL: CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s
INFO: index "idx_t123_id" now contains 1000000 row versions in 6206 pages
DETAIL: 1000000 index row versions were removed.
3442 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "t123": found 149 removable, 1000000 nonremovable row versions in 10811 out of 10811 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 372671760
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.33 s, system: 0.02 s, elapsed: 0.36 s.
INFO: vacuuming "pg_toast.pg_toast_1596783"
INFO: index "pg_toast_1596783_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pg_toast_1596783": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 372671760
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.t123"
INFO: "t123": scanned 10811 of 10811 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
VACUUM
9、查询，有问题，扫描了6个数据块。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t123 where id=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t123_id on public.t123 (cost=0.42..2.44 rows=1 width=9) (actual time=0.014..0.019 rows=1 loops=1)
Output: id, info
Index Cond: (t123.id = 1)
Buffers: shared hit=6
Planning time: 0.150 ms
Execution time: 0.040 ms
(6 rows)
通过pageinspect可以观察到heap page, index page的结构。请参考
《Use pageinspect EXTENSION view PostgreSQL Page's raw infomation》
《PostgreSQL 黑科技 - 空间聚集存储, 内窥GIN, GiST, SP-GiST索引》
1、数据库优化，自动垃圾回收，避免长事务（事务开启后，该事务后产生的垃圾无法被回收，直到事务结束（可以通过内核优化解决））
《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》
《PostgreSQL垃圾回收代码分析 - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》
2、开启快照过旧。
#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
# (change requires restart)
3、内核优化1
仅保留其他会话需要读到的版本，例如同一条记录，如果被多次更新，那么这么多垃圾版本，在回收时，只保留当前其他活跃会话所需要的版本。
目前的做法，不管其他会话要不要读到，只判断TUPLE版本是不是当前最早打开事务之后产生的，如果是就不回收。
4、内核优化2
ZHEAP存储引擎，使用UNDO来保留旧版本，并且事务结束立即回收（只要其他会话不需要的话）。
《[未完待续] PostgreSQL 扩展存储引擎介绍 - zheap - 1》
目前PostgreSQL的存储引擎使用多版本是并发发事务处理的关键一环，多版本带来了一些问题，如上所述。普通用户依照上面的解决办法(配置自动垃圾回收，配置快照过旧，尽量避免长事务)，可以尽可能的避免问题。
而在内核层面，有两种解决办法啊，
1、一种方法是优化垃圾回收的逻辑，尽可能的回收不需要的版本，只保留当前其他活跃会话所需要的版本。
2、PostgreSQL 11或12会引入新的存储引擎zheap。从内核层面解决以上问题。
《PostgreSQL freeze 风暴导致的IOPS飙升 - 事后追溯》
《PostgreSQL的"天气预报" - 如何预测Freeze IO风暴》
《PostgreSQL merge join 评估成本时可能会查询索引 - 硬解析务必引起注意 - 批量删除数据后, 未释放empty索引页导致mergejoin执行计划变慢 case》
src/backend/access/heap/README.HOT
《Use pageinspect EXTENSION view PostgreSQL Page's raw infomation》
《PostgreSQL 黑科技 - 空间聚集存储, 内窥GIN, GiST, SP-GiST索引》
《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》
《PostgreSQL垃圾回收代码分析 - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》