生产案例 | 费解的索引失效
前言
今天同事又找到我，现象是上周有个库建了索引之后用不上，但过了一阵又好了。最开始怀疑是否和pg_pathman有关？因为对pg_pathman着实好感不够啊，我记录在案的pathman bug大约7个，不过后面发现不是pathman的问题，因为这个索引过了一阵又可以使用了。那么是否和indcheckxmin有关？因为之前实际环境中遇到过indcheckxmin导致的类似现象，不过是在一个事务里面创建的索引无法使用的case，和这个场景不太类似。另外，之前在群里，也有个哥们问到
然后过了一会，索引就可以用了，
他们环境也配置了old_snapshot_threshold，看样子有必要了解清楚为什么配置了old_snapshot_threshold参数会有这个问题了。
old_snapshot_threshold
看下官网介绍
Sets the minimum amount of time that a query snapshot can be used without risk of a “snapshot too old” error occurring when using the snapshot. Data that has been dead for longer than this threshold is allowed to be vacuumed away. This can help prevent bloat in the face of snapshots which remain in use for a long time. To prevent incorrect results due to cleanup of data which would otherwise be visible to the snapshot, an error is generated when the snapshot is older than this threshold and the snapshot is used to read a page which has been modified since the snapshot was built.
If this value is specified without units, it is taken as minutes. A value of
-1(the default) disables this feature, effectively setting the snapshot age limit to infinity. This parameter can only be set at server start.Useful values for production work probably range from a small number of hours to a few days. Small values (such as
0or1min) are only allowed because they may sometimes be useful for testing. While a setting as high as60dis allowed, please note that in many workloads extreme bloat or transaction ID wraparound may occur in much shorter time frames.When this feature is enabled, freed space at the end of a relation cannot be released to the operating system, since that could remove information needed to detect the “snapshot too old” condition. All space allocated to a relation remains associated with that relation for reuse only within that relation unless explicitly freed (for example, with
VACUUM FULL).This setting does not attempt to guarantee that an error will be generated under any particular circumstances. In fact, if the correct results can be generated from (for example) a cursor which has materialized a result set, no error will be generated even if the underlying rows in the referenced table have been vacuumed away. Some tables cannot safely be vacuumed early, and so will not be affected by this setting, such as system catalogs. For such tables this setting will neither reduce bloat nor create a possibility of a “snapshot too old” error on scanning.
设置可以使用查询快照的最小时间量，以规避使用快照时出现"snapshot too old" 错误的风险。超过此阈值时间的数据将可以被清除。这可以有助于阻止长时间使用的快照造成的快照膨胀。为了阻止由于本来对该快照可见的数据被清理导致的不正确结果，当快照比这个阈值更旧并且该快照被用来读取一个该快照建立以来被修改过的页面时，将会产生一个错误。
如果指定值时没有单位，则以分钟为单位。值 -1 (默认值) 禁用此功能，实际上将快照的时限设置为无穷大。这个参数只能在服务器启动时设置。
对于生产工作有用的值的范围可能从几个小时到几天不等。只允许小的值（例如0或者1min）是因为它们有时可能对测试有用。虽然允许高达60d的设置，但是请注意很多负载情况下，很短的时间帧里就可能发生极大的膨胀或者事务 ID 回卷。
当这个特性被启用时，关系末尾的被清出的空间不能被释放给操作系统，因为那可能会移除用于检测"snapshot too old"情况所需的信息。所有分配给关系的空间还将与该关系关联在一起便于重用，除非它们被显式地释放（例如，用VACUUM FULL）。
这个设置不会尝试保证在任何特殊情况下都会生成错误。事实上，如果（例如）可以从一个已经物化了一个结果集的游标中生成正确的结果，即便被引用表中的底层行已经被清理掉也不会生成错误。某些表不能被过早地安全清除，并且因此将不受这个设置的影响，例如系统目录。对于这些表，这个设置将不能降低膨胀，也不能降低在扫描时产生"snapshot too old"错误的可能性。
大意就是控制快照的最大生存期。这段时间过后，PostgreSQL会清理这些死元组，并且如果某些长事务仍然需要它们，则会提示"snapshot too old"的错误。
而且通过官网的介绍，我们可以得出一些结论：
1.用于限制死元组无限保留，防止表膨胀2.设置了该参数之后，普通vacuum无法截断末尾的页
关于第二点，假如各位读者有留意的话，也就是最近两篇文章里面的vacuum phase的"truncating heap"。
堆表截断
vacuum我们知道是用来回收死元组、统计信息收集和年龄冻结等操作，普通的vacuum基本不会回收空间，清理死元组用于新的元组插入，也就是说表的大小并不会变小。但是有一种情况例外，在默认情况下，表末端的垃圾页可以被truncate，从磁盘中回收空间。例如表的末尾的100个数据块里面全是垃圾，那么这100个数据块可以truncate，文件也会变小。而位于其他位置的垃圾页，不能回收，因为会影响寻址 (ctid) 的变化。
所以，就衍生出了一种技巧，SQL如下
WITH a AS (
DELETE FROM t
WHERE ctid = ANY (
SELECT
ctid
FROM
t
ORDER BY
ctid::text DESC
LIMIT 10000)
RETURNING
*)
INSERT INTO t
SELECT
*
FROM
a;
VACUUM t;核心思想就是将文件末尾的行重新插入，这样就可能会插入到前面的数据块中，然后执行普通的vacuum，便也可能减少表的大小，这个方法特别适用于磁盘空间快要满了但又想立即释放的情况。
但是有利有弊，比如读写分离的场景，vacuum截断末尾的页是排它锁，会同步到备库，备库假如有大查询的话会导致不必要的锁冲突，所以在12的版本中，这个截断动作可以被控制，如果表的参数vacuum_truncate设置为false，那么末尾的垃圾页不会被回收。alter table test set (vacuum_truncate = off)；另外收缩文件需要对表加AccessExclusiveLock，所以如果你不期望有这个锁冲突，同时这个表又还会有新的数据写入（这些空间可以被新写入的数据填满）时，可以将vacuum_truncate设置为false，特别是那种频繁写入的场景，回收了又要extend block（还是排它锁），多此一举。
回收的动作是should_attempt_truncation来做的。
/*
* should_attempt_truncation - should we attempt to truncate the heap?
*
* Don't even think about it unless we have a shot at releasing a goodly
* number of pages. Otherwise, the time taken isn't worth it.
*
* Also don't attempt it if wraparound failsafe is in effect. It's hard to
* predict how long lazy_truncate_heap will take. Don't take any chances.
* There is very little chance of truncation working out when the failsafe is
* in effect in any case. lazy_scan_prune makes the optimistic assumption
* that any LP_DEAD items it encounters will always be LP_UNUSED by the time
* we're called.
*
* Also don't attempt it if we are doing early pruning/vacuuming, because a
* scan which cannot find a truncated heap page cannot determine that the
* snapshot is too old to read that page.
*
* This is split out so that we can test whether truncation is going to be
* called for before we actually do it. If you change the logic here, be
* careful to depend only on fields that lazy_scan_heap updates on-the-fly.
*/
static bool
should_attempt_truncation(LVRelState *vacrel)
{
BlockNumber possibly_freeable;
if (!vacrel->do_rel_truncate || vacrel->failsafe_active)
return false;
possibly_freeable = vacrel->rel_pages - vacrel->nonempty_pages;
if (possibly_freeable > 0 &&
(possibly_freeable >= REL_TRUNCATE_MINIMUM ||
possibly_freeable >= vacrel->rel_pages / REL_TRUNCATE_FRACTION) &&
old_snapshot_threshold < 0)
return true;
else
return false;
}可以看到，注释里写的很清楚，无法知悉被回收的页是否能够判断snapshot is too old
* Also don't attempt it if we are doing early pruning/vacuuming, because a
* scan which cannot find a truncated heap page cannot determine that the
* snapshot is too old to read that page.让我们实际看个例子
postgres=# create table test_trunc(id int,info text);
CREATE TABLE
postgres=# insert into test_trunc select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000
postgres=# \dt+ test_trunc
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+--------+-------------
public | test_trunc | table | postgres | permanent | heap | 712 kB |
(1 row)
postgres=# delete from test_trunc ;
DELETE 10000
postgres=# vacuum verbose test_trunc ;
INFO: vacuuming "public.test_trunc"
INFO: table "test_trunc": removed 10000 dead item identifiers in 84 pages
INFO: table "test_trunc": found 10000 removable, 0 nonremovable row versions in 84 out of 84 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1657209
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table "test_trunc": truncated 84 to 0 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pg_toast.pg_toast_68475"
INFO: table "pg_toast_68475": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1657210
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# \dt+ test_trunc
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+-------+-------------
public | test_trunc | table | postgres | permanent | heap | 24 kB |
(1 row)可以看到，表的大小变成了24KB，那么再打开old_snapshot_threshold看看
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
1min
(1 row)
postgres=# \dt+ test_trunc
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+-------+-------------
public | test_trunc | table | postgres | permanent | heap | 24 kB |
(1 row)
postgres=# insert into test_trunc select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000
postgres=# \dt+ test_trunc
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+--------+-------------
public | test_trunc | table | postgres | permanent | heap | 704 kB |
(1 row)
postgres=# delete from test_trunc ;
DELETE 10000
postgres=# vacuum verbose test_trunc ;
INFO: vacuuming "public.test_trunc"
INFO: table "test_trunc": removed 10000 dead item identifiers in 84 pages
INFO: table "test_trunc": found 10000 removable, 0 nonremovable row versions in 84 out of 84 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1657212
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: vacuuming "pg_toast.pg_toast_68475"
INFO: table "pg_toast_68475": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1657212
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# \dt+ test_trunc
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+--------+-------------
public | test_trunc | table | postgres | permanent | heap | 712 kB |
(1 row)可以看到，表的大小并未被截断。
表膨胀
另外一个作用也是我们最为熟知的，就是预防死元组的无限膨胀，导致膨胀。核心函数如下：
/*
* HeapTupleSatisfiesVacuum
*
* Determine the status of tuples for VACUUM purposes. Here, what
* we mainly want to know is if a tuple is potentially visible to *any*
* running transaction. If so, it can't be removed yet by VACUUM.
*
* OldestXmin is a cutoff XID (obtained from GetOldestXmin()). Tuples
* deleted by XIDs >= OldestXmin are deemed "recently dead"; they might
* still be visible to some open transaction, so we can't remove them,
* even if we see that the deleting transaction has committed.
*/
HTSV_Result
HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,
Buffer buffer)
...
if (!TransactionIdPrecedes(xmax, OldestXmin)) ---判断是否xmax大于oldestxmin，如果是，则返回
return HEAPTUPLE_RECENTLY_DEAD;
.../*
* lazy_scan_heap() -- scan an open heap relation
*
* This routine prunes each page in the heap, which will among other
* things truncate dead tuples to dead line pointers, defragment the
* page, and set commit status bits (see heap_page_prune). It also builds
* lists of dead tuples and pages with free space, calculates statistics
* on the number of live tuples in the heap, and marks pages as
* all-visible if appropriate. When done, or when we run low on space for
* dead-tuple TIDs, invoke vacuuming of indexes and call lazy_vacuum_heap
* to reclaim dead line pointers.
*/
static void
lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
Relation *Irel, int nindexes, bool aggressive)
case HEAPTUPLE_RECENTLY_DEAD:
/*
* If tuple is recently deleted then we must not remove it
* from relation.
*/
nkeep += 1;
all_visible = false;
break;如果返回了HEAPTUPLE_RECENTLY_DEAD，vacuum就会跳过对这一行的清理，这也是导致表膨胀最核心的原因。此处不再深入，细节可以翻看之前的文章《揭开表膨胀的神秘面纱》。
注意，在14里面有了一些变化，由于对GetSnapshotData做了优化，移走了xmin之后，新增了GlobalVis*相关函数来判断元组是否可清理 ，同时在PROC_HDR结构体中对PGPROC的xid做镜像。
* 1) GlobalVisSharedRels, which only considers an XID's
* effects visible-to-everyone if neither snapshots in any database, nor a
* replication slot's xmin, nor a replication slot's catalog_xmin might
* still consider XID as running.
*
* 2) GlobalVisCatalogRels, which only considers an XID's
* effects visible-to-everyone if neither snapshots in the current
* database, nor a replication slot's xmin, nor a replication slot's
* catalog_xmin might still consider XID as running.
*
* I.e. the difference to GlobalVisSharedRels is that
* snapshot in other databases are ignored.
*
* 3) GlobalVisDataRels, which only considers an XID's
* effects visible-to-everyone if neither snapshots in the current
* database, nor a replication slot's xmin consider XID as running.
*
* I.e. the difference to GlobalVisCatalogRels is that
* replication slot's catalog_xmin is not taken into account.
*
* 4) GlobalVisTempRels, which only considers the current session, as temp
* tables are not visible to other sessions.
typedef struct PROC_HDR
{
/* Array of PGPROC structures (not including dummies for prepared txns) */
PGPROC *allProcs;
/* Array mirroring PGPROC.xid for each PGPROC currently in the procarray */
TransactionId *xids;看下具体效果，开启一个事务，让他获得一个永久事务ID
postgres=# create table test_bloat(id int,info text);
CREATE TABLE
postgres=# insert into test_bloat values(1,'test');
INSERT 0 1
postgres=# insert into test_bloat values(2,'test');
INSERT 0 1
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid();
pg_backend_pid
----------------
13183
(1 row)
postgres=*# select txid_current();
txid_current
--------------
7505695
(1 row)同时另外一个会话不断进行更新
postgres=# update test_bloat set info = 'hello';\watch 0.01不一会儿，就可以看到表膨胀了
postgres=# \dt+ test_bloat
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------------+-------+----------+-------------+--------+-------------
public | test_bloat | table | postgres | permanent | 536 kB |
(1 row)手动vacuum一下，可以看到，11352条死元组无法被vacuum，DETAIL: 11352 dead row versions cannot be removed yet, oldest xmin: 7505695
postgres=# vacuum VERBOSE test_bloat ;
INFO: vacuuming "public.test_bloat"
INFO: "test_bloat": found 0 removable, 11354 nonremovable row versions in 62 out of 62 pages
DETAIL: 11352 dead row versions cannot be removed yet, oldest xmin: 7505695
There were 0 unused item identifiers.
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: vacuuming "pg_toast.pg_toast_171002"
INFO: index "pg_toast_171002_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_171002": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7505695
There were 0 unused item identifiers.
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.
VACUUM这个是怎么算出来的呢
•总计 62 个block•填充满的block里面含有185个元组•61 * 185 + 69总计11354个元组，减去最开始的2条活元组，总计11352条死元组
postgres=# select count(*) from heap_page_items(get_raw_page('test_bloat', 0));
count
-------
185
(1 row)
postgres=# select relpages from pg_class where relname = 'test_bloat';
relpages
----------
62
(1 row)
postgres=# select count(*) from heap_page_items(get_raw_page('test_bloat', 61));
count
-------
69
(1 row)
postgres=# select 61 * 185 + 69;
?column?
----------
11354
(1 row)可以看到，最老的事务之后的元组都无法清理，长事务提交之后，即可正常清理，DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7511374
postgres=# vacuum VERBOSE test_bloat ;
INFO: vacuuming "public.test_bloat"
INFO: "test_bloat": removed 11352 row versions in 61 pages
INFO: "test_bloat": found 11352 removable, 2 nonremovable row versions in 62 out of 62 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7511374
There were 11228 unused item identifiers.
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: vacuuming "pg_toast.pg_toast_171002"
INFO: index "pg_toast_171002_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_171002": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7511374
There were 0 unused item identifiers.
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.
VACUUM那么再看一下，打开了old_snapshot_threshold参数是怎样的
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
1min
(1 row)
postgres=# truncate table test_trunc ;
TRUNCATE TABLE
postgres=# insert into test_trunc select n,md5(random()::text) from generate_series(1,1000000) as n;
INSERT 0 1000000
postgres=# \dt+ test_trunc
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+-------+-------------
public | test_trunc | table | postgres | permanent | heap | 65 MB |
(1 row)
postgres=# update test_trunc set info = 'hello';
UPDATE 1000000然后另外一个会话进行查询，同时确保执行时间超过了old_snapshot_threshold
postgres=# select *, pg_sleep(75) from test_trunc;
---查询中在原会话中执行vacuum，时间到了之后就会提示snapshot too old（这里直接借鉴了一下网上的图，因为等待过于久了）
索引失效
除了这个场景，再看一个不怎么常见的场景，但一旦碰到，会让人十分费解 — 没错，索引失效！
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
1min
(1 row)
postgres=# drop table test_trunc ;
DROP TABLE
postgres=# create table test_trunc(id int,info text);
CREATE TABLE
postgres=# insert into test_trunc select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000另开一个会话，开启一个事务，但不提交
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid(),txid_current_if_assigned();
pg_backend_pid | txid_current_if_assigned
----------------+--------------------------
28058 |
(1 row)
postgres=*# insert into test values(1);
INSERT 0 1
postgres=*# select pg_backend_pid(),txid_current_if_assigned();
pg_backend_pid | txid_current_if_assigned
----------------+--------------------------
28058 | 1657257
(1 row)新建一个索引
postgres=# create index t_idx on test_trunc(id);
CREATE INDEX
postgres=# \d test_trunc
Table "public.test_trunc"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
Indexes:
"t_idx" btree (id)
postgres=# explain select id from test_trunc where id = 99;
QUERY PLAN
------------------------------------------------------------
Seq Scan on test_trunc (cost=0.00..209.00 rows=1 width=4)
Filter: (id = 99)
(2 rows)
postgres=# set enable_seqscan to off;
SET
postgres=# explain select id from test_trunc where id = 99;
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on test_trunc (cost=10000000000.00..10000000209.00 rows=1 width=4)
Filter: (id = 99)
(2 rows)可以看到和之前的"事务内索引失效"的场景十分相似，索引死活无法使用？即使设置了"禁用成本"依旧无法使用。让我们把之前的事务提交一下再看看。果然，这次又可以正常使用索引了！
postgres=*# commit ;
COMMITpostgres=# explain select id from test_trunc where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------
Index Only Scan using t_idx on test_trunc (cost=0.29..4.30 rows=1 width=4)
Index Cond: (id = 99)
(2 rows)那么看一下系统表里的定义
postgres=# select indexrelid::regclass as relname,indisvalid,indcheckxmin,indisready,indislive from pg_index where indexrelid = 't_idx'::regclass;
-[ RECORD 1 ]+------
relname | t_idx
indisvalid | t
indcheckxmin | t
indisready | t
indislive | t
postgres=# select xmin,relname from pg_class where relname = 't_idx';
xmin | relname
---------+---------
1657258 | t_idx
(1 row)可以看到，这里的indcheckxmin是true，参照官网解释
If true, queries must not use the index until the
xminof thispg_indexrow is below theirTransactionXminevent horizon, because the table may contain broken HOT chains with incompatible rows that they can see如果为真，直到此pg_index行的xmin低于查询的TransactionXmin视界之前，查询都不能使用此索引，因为表可能包含具有它们可见的不相容行的损坏HOT链。
什么是broken hot chain？什么是hot safe？这里涉及到几个名词，可能参考src/backend/access/heap/README.HOT
•Broken HOT Chain：A HOT chain in which the key value for an index has changed.This is not allowed to occur normally but if a new index is created it can happen. In that case various strategies are used to ensure that no transaction for which the older tuples are visible can use the index.•Cold update：A normal, non-HOT update, in which index entries are made for the new version of the tuple.•Dead line pointer：A stub line pointer, that does not point to anything, but cannot be removed or reused yet because there are index pointers to it. Semantically same as a dead tuple. It has state LP_DEAD.•Heap-only tuple：A proposed tuple update is said to be HOT-safe if it changes none of the tuple's indexed columns. It will only become an actual HOT update if we can find room on the same page for the new tuple version.•HOT-safe：A proposed tuple update is said to be HOT-safe if it changes none of the tuple's indexed columns. It will only become an actual HOT update if we can find room on the same page for the new tuple version.•HOT update：An UPDATE where the new tuple becomes a heap-only tuple, and no new index entries are made.•HOT-updated tuple：An updated tuple, for which the next tuple in the chain is a heap-only tuple. Marked with HEAP_HOT_UPDATED flag.•Indexed column：A column used in an index definition. The column might not actually be stored in the index --- it could be used in a functional index's expression, or used in a partial index predicate. HOT treats all these cases alike.•Redirecting line pointer：A line pointer that points to another line pointer and has no associated tuple. It has the special lp_flags state LP_REDIRECT,and lp_off is the OffsetNumber of the line pointer it links to. This is used when a root tuple becomes dead but we cannot prune the line pointer because there are non-dead heap-only tuples further down the chain.•Root tuple：The first tuple in a HOT update chain; the one that indexes point to.•Update chain：A chain of updated tuples, in which each tuple's ctid points to the next tuple in the chain. A HOT update chain is an update chain(or portion of an update chain) that consists of a root tuple and one or more heap-only tuples. A complete update chain can contain both HOT and non-HOT (cold) updated tuples.
术语有点多，在这里我们只关心这么几个术语（Glossary）：
•HOT 断链（Broken hot chain）一个HOT链，其中索引的键值已更改。这通常不允许发生，但是如果创建了新索引，则可能发生。在那种情况下，可以使用各种策略来确保没有任何可见较早的元组的事务可以使用索引。这一点可以类比CIC，CIC要求创建过程中尽可能避免长事务，因为长事务的存在，可能会看到HOT 断链之前的数据•冷更新（Cold update） 正常的非HOT更新，为元组的新版本创建索引条目。•HOT 安全（hot safe ） 如果目的元组更新不更改元组的索引列，则被称为HOT安全的。（如果一个提议的元组更新没有改变元组的索引列，那么它就被认为是 HOT 安全的。如果我们可以在同一页面上为新的元组版本找到空间，它只会成为真正的 HOT 更新）•HOT更新（hot update） 一个UPDATE，其中新的元组成为heap-only tuple，并且没有创建新的索引条目。
也就是说，非hot-safe的会导致indcheckxmin为true
postgres=# create table test_hot(id int) with ( fillfactor = 80);
CREATE TABLE
postgres=# insert into test_hot values(1);
INSERT 0 1
postgres=# begin;
BEGIN
postgres=*# update test_hot set id = 99 where id = 1;
UPDATE 1
postgres=*# create index hot_idx on test_hot(id);
CREATE INDEX
postgres=*# select indexrelid::regclass as relname,indisvalid,indcheckxmin,indisready,indislive from pg_index where indexrelid = 'hot_idx'::regclass;
relname | indisvalid | indcheckxmin | indisready | indislive
---------+------------+--------------+------------+-----------
hot_idx | t | t | t | t
(1 row)
postgres=*# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
-1
(1 row)这种可以看到indcheckxmin是true，因为我更新了index column，就无法使用HOT，自然是非hot-safe的。
回到刚刚的例子，让我们对比一下关了old_snapshot_threshold参数的样子
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
-1
(1 row)
postgres=# drop table test_trunc ;
DROP TABLE
postgres=# create table test_trunc(id int,info text);
CREATE TABLE
postgres=# insert into test_trunc select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000另开一个会话，开启一个事务，但不提交
postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid(),txid_current_if_assigned();
pg_backend_pid | txid_current_if_assigned
----------------+--------------------------
30014 |
(1 row)
postgres=*# insert into test values(1);
INSERT 0 1
postgres=*# select pg_backend_pid(),txid_current_if_assigned();
pg_backend_pid | txid_current_if_assigned
----------------+--------------------------
30014 | 1657266
(1 row)新建一个索引
postgres=# create index t_idx on test_trunc(id);
CREATE INDEX
postgres=# \d test_trunc
Table "public.test_trunc"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
Indexes:
"t_idx" btree (id)
postgres=# explain select id from test_trunc where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------
Index Only Scan using t_idx on test_trunc (cost=0.29..4.30 rows=1 width=4)
Index Cond: (id = 99)
(2 rows)这次可以看到，索引可以正常走了。
那么关键就是old_snapshot_threshold参数了。设置了这个参数会如何？
影响何如
先看一下源码中对于Snapshot的简单定义
/*
* Struct representing all kind of possible snapshots.
*
* There are several different kinds of snapshots:
* * Normal MVCC snapshots
* * MVCC snapshots taken during recovery (in Hot-Standby mode)
* * Historic MVCC snapshots used during logical decoding
* * snapshots passed to HeapTupleSatisfiesDirty()
* * snapshots passed to HeapTupleSatisfiesNonVacuumable()
* * snapshots used for SatisfiesAny, Toast, Self where no members are
* accessed.
*
* TODO: It's probably a good idea to split this struct using a NodeTag
* similar to how parser and executor nodes are handled, with one type for
* each different kind of snapshot to avoid overloading the meaning of
* individual fields.
*/
typedef struct SnapshotData
{
--快照分为多种类型
SnapshotType snapshot_type; /* type of snapshot */
/*
* The remaining fields are used only for MVCC snapshots, and are normally
* just zeroes in special snapshots. (But xmin and xmax are used
* specially by HeapTupleSatisfiesDirty, and xmin is used specially by
* HeapTupleSatisfiesNonVacuumable.)
*
* An MVCC snapshot can never see the effects of XIDs >= xmax. It can see
* the effects of all older XIDs except those listed in the snapshot. xmin
* is stored as an optimization to avoid needing to search the XID arrays
* for most tuples.
*/
TransactionId xmin; /* all XID < xmin are visible to me */
TransactionId xmax; /* all XID >= xmax are invisible to me */
/*
* For normal MVCC snapshot this contains the all xact IDs that are in
* progress, unless the snapshot was taken during recovery in which case
* it's empty. For historic MVCC snapshots, the meaning is inverted, i.e.
* it contains *committed* transactions between xmin and xmax.
*
* note: all ids in xip[] satisfy xmin <= xip[i] < xmax
*/
TransactionId *xip;
uint32 xcnt; /* # of xact ids in xip[] */
/*
* For non-historic MVCC snapshots, this contains subxact IDs that are in
* progress (and other transactions that are in progress if taken during
* recovery). For historic snapshot it contains *all* xids assigned to the
* replayed transaction, including the toplevel xid.
*
* note: all ids in subxip[] are >= xmin, but we don't bother filtering
* out any that are >= xmax
*/
--对于非历史MVCC快照，包含正在进行中的子事务IDs。
--对于历史MVCC快照，包含所有分配给已回放事务的所有xids（包括顶层xid）
TransactionId *subxip;
int32 subxcnt; /* # of xact ids in subxip[] */
--这个就是subtransactions overflow
bool suboverflowed; /* has the subxip array overflowed? */
bool takenDuringRecovery; /* recovery-shaped snapshot? */
bool copied; /* false if it's a static snapshot */
--Cmin、cmax，还有combcid
CommandId curcid; /* in my xact, CID < curcid are visible */
/*
* An extra return value for HeapTupleSatisfiesDirty, not used in MVCC
* snapshots.
*/
uint32 speculativeToken;
/*
* For SNAPSHOT_NON_VACUUMABLE (and hopefully more in the future) this is
* used to determine whether row could be vacuumed.
*/
struct GlobalVisState *vistest;
/*
* Book-keeping information, used by the snapshot manager
*/
uint32 active_count; /* refcount on ActiveSnapshot stack */
uint32 regd_count; /* refcount on RegisteredSnapshots */
pairingheap_node ph_node; /* link in the RegisteredSnapshots heap */
TimestampTz whenTaken; /* timestamp when snapshot was taken */
XLogRecPtr lsn; /* position in the WAL stream when taken */
/*
* The transaction completion count at the time GetSnapshotData() built
* this snapshot. Allows to avoid re-computing static snapshots when no
* transactions completed since the last GetSnapshotData().
*/
uint64 snapXactCompletionCount;
} SnapshotData;首先是没有打开的例子
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
-1
(1 row)
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
7524
(1 row)
postgres=# begin;
BEGIN
postgres=*# select 1;用GDB追一下，获取的快照如下
(gdb) b GetSnapshotData
Breakpoint 1 at 0x8cd50e: file procarray.c, line 2207.
(gdb) p *snapshot
$1 = {snapshot_type = SNAPSHOT_MVCC, xmin = 734, xmax = 734, xip = 0x1f3f1a0, xcnt = 0, subxip = 0x1f93740, subxcnt = 0, suboverflowed = false, takenDuringRecovery = false,
copied = false, curcid = 0, speculativeToken = 0, vistest = 0x0, active_count = 0, regd_count = 0, ph_node = {first_child = 0x0, next_sibling = 0x0, prev_or_parent = 0x0},
whenTaken = 0, lsn = 0, snapXactCompletionCount = 2}
(gdb) set print pretty on
(gdb) p *snapshot
$2 = {
snapshot_type = SNAPSHOT_MVCC,
xmin = 734,
xmax = 734,
xip = 0x1f3f1a0,
xcnt = 0,
subxip = 0x1f93740,
subxcnt = 0,
suboverflowed = false,
takenDuringRecovery = false,
copied = false,
curcid = 0,
speculativeToken = 0,
vistest = 0x0,
active_count = 0,
regd_count = 0,
ph_node = {
first_child = 0x0,
next_sibling = 0x0,
prev_or_parent = 0x0
},
whenTaken = 0,
lsn = 0,
snapXactCompletionCount = 2
}再打开这个参数对比一下
(gdb) set print pretty on
(gdb) p *snapshot
$1 = {
snapshot_type = SNAPSHOT_MVCC,
xmin = 734,
xmax = 734,
xip = 0x2e6d1d0,
xcnt = 0,
subxip = 0x2ec1770,
subxcnt = 0,
suboverflowed = false,
takenDuringRecovery = false,
copied = false,
curcid = 0,
speculativeToken = 0,
vistest = 0x0,
active_count = 0,
regd_count = 0,
ph_node = {
first_child = 0x0,
next_sibling = 0x0,
prev_or_parent = 0x0
},whenTaken = 700020671757626,
lsn = 24197376,
snapXactCompletionCount = 1
}
可以看到，打开了这个参数后，多了个whenTaken和lsn
•lsn 表示获取快照时WAL的写入位置•whenTaken 表示获取快照时的时间
获取Snapshot之后，接着在TestForOldSnapshot函数里面进行判断，是否snapshot is too old
/*
* Check whether the given snapshot is too old to have safely read the given
* page from the given table. If so, throw a "snapshot too old" error.
*
* This test generally needs to be performed after every BufferGetPage() call
* that is executed as part of a scan. It is not needed for calls made for
* modifying the page (for example, to position to the right place to insert a
* new index tuple or for vacuuming). It may also be omitted where calls to
* lower-level functions will have already performed the test.
*
* Note that a NULL snapshot argument is allowed and causes a fast return
* without error; this is to support call sites which can be called from
* either scans or index modification areas.
*
* For best performance, keep the tests that are fastest and/or most likely to
* exclude a page from old snapshot testing near the front.
*/
static inline void
TestForOldSnapshot(Snapshot snapshot, Relation relation, Page page)
{
Assert(relation != NULL);
if (old_snapshot_threshold >= 0
&& (snapshot) != NULL
&& ((snapshot)->snapshot_type == SNAPSHOT_MVCC
|| (snapshot)->snapshot_type == SNAPSHOT_TOAST)
&& !XLogRecPtrIsInvalid((snapshot)->lsn)
&& PageGetLSN(page) > (snapshot)->lsn)
TestForOldSnapshot_impl(snapshot, relation);
}
#define PageGetLSN(page) \
PageXLogRecPtrGet(((PageHeader) (page))->pd_lsn)这里逻辑比较清晰，如果PAGE的LSN大于快照的LSN，则调用TestForOldSnapshot_impl，而这个函数里面的逻辑也很简单，就是判断whenTaken时间戳是否小于快照的时间戳
/*
* Implement slower/larger portions of TestForOldSnapshot
*
* Smaller/faster portions are put inline, but the entire set of logic is too
* big for that.
*/
void
TestForOldSnapshot_impl(Snapshot snapshot, Relation relation)
{
if (RelationAllowsEarlyPruning(relation)
&& (snapshot)->whenTaken < GetOldSnapshotThresholdTimestamp())
ereport(ERROR,
(errcode(ERRCODE_SNAPSHOT_TOO_OLD),
errmsg("snapshot too old")));
}GetOldSnapshotThresholdTimestamp用于获取快照的时间戳
/*
* Get timestamp through which vacuum may have processed based on last stored
* value for threshold_timestamp.
*
* XXX: So far, we never trust that a 64-bit value can be read atomically; if
* that ever changes, we could get rid of the spinlock here.
*/
TimestampTz
GetOldSnapshotThresholdTimestamp(void)
{
TimestampTz threshold_timestamp;
SpinLockAcquire(&oldSnapshotControl->mutex_threshold);
threshold_timestamp = oldSnapshotControl->threshold_timestamp;
SpinLockRelease(&oldSnapshotControl->mutex_threshold);
return threshold_timestamp;
}由GUC old_snapshot_threshold控制，每一分钟记录一个事务ID，用于判断快照过旧
/*
* GUC parameters
*/
int old_snapshot_threshold; /* number of minutes, -1 disables */
volatile OldSnapshotControlData *oldSnapshotControl;
/*
* Structure for dealing with old_snapshot_threshold implementation.
*/
typedef struct OldSnapshotControlData
{
/*
* Variables for old snapshot handling are shared among processes and are
* only allowed to move forward.
*/
slock_t mutex_current; /* protect current_timestamp */
TimestampTz current_timestamp; /* latest snapshot timestamp */
slock_t mutex_latest_xmin; /* protect latest_xmin and next_map_update */
TransactionId latest_xmin; /* latest snapshot xmin */
TimestampTz next_map_update; /* latest snapshot valid up to */
slock_t mutex_threshold; /* protect threshold fields */
TimestampTz threshold_timestamp; /* earlier snapshot is old */
TransactionId threshold_xid; /* earlier xid may be gone */
/*
* Keep one xid per minute for old snapshot error handling.
*
* Use a circular buffer with a head offset, a count of entries currently
* used, and a timestamp corresponding to the xid at the head offset. A
* count_used value of zero means that there are no times stored; a
* count_used value of OLD_SNAPSHOT_TIME_MAP_ENTRIES means that the buffer
* is full and the head must be advanced to add new entries. Use
* timestamps aligned to minute boundaries, since that seems less
* surprising than aligning based on the first usage timestamp. The
* latest bucket is effectively stored within latest_xmin. The circular
* buffer is updated when we get a new xmin value that doesn't fall into
* the same interval.
*
* It is OK if the xid for a given time slot is from earlier than
* calculated by adding the number of minutes corresponding to the
* (possibly wrapped) distance from the head offset to the time of the
* head entry, since that just results in the vacuuming of old tuples
* being slightly less aggressive. It would not be OK for it to be off in
* the other direction, since it might result in vacuuming tuples that are
* still expected to be there.
*
* Use of an SLRU was considered but not chosen because it is more
* heavyweight than is needed for this, and would probably not be any less
* code to implement.
*
* Persistence is not needed.
*/
int head_offset; /* subscript of oldest tracked time */
TimestampTz head_timestamp; /* time corresponding to head xid */
int count_used; /* how many slots are in use */
TransactionId xid_by_minute[FLEXIBLE_ARRAY_MEMBER];
} OldSnapshotControlData;这里简单知晓了逻辑之后，我们回过头来再看一下设置indcheckxmin的逻辑
/*
* If we found any potentially broken HOT chains, mark the index as not
* being usable until the current transaction is below the event horizon.
* See src/backend/access/heap/README.HOT for discussion. Also set this
* if early pruning/vacuuming is enabled for the heap relation. While it
* might become safe to use the index earlier based on actual cleanup
* activity and other active transactions, the test for that would be much
* more complex and would require some form of blocking, so keep it simple
* and fast by just using the current transaction.
*
如果我们发现任何可能损坏的HOT链，就将索引标记为不可用，直到当前事务低于视界范围。
如果为堆表启用了早期修剪/清理，也会设置此项。
* However, when reindexing an existing index, we should do nothing here.
* Any HOT chains that are broken with respect to the index must predate
* the index's original creation, so there is no need to change the
* index's usability horizon. Moreover, we *must not* try to change the
* index's pg_index entry while reindexing pg_index itself, and this
* optimization nicely prevents that. The more complex rules needed for a
* reindex are handled separately after this function returns.
*
在concurrent create index的时候也不需要设置indcheckxmin，因为有indisvalid
* We also need not set indcheckxmin during a concurrent index build,
* because we won't set indisvalid true until all transactions that care
* about the broken HOT chains or early pruning/vacuuming are gone.
*
* Therefore, this code path can only be taken during non-concurrent
* CREATE INDEX. Thus the fact that heap_update will set the pg_index
* tuple's xmin doesn't matter, because that tuple was created in the
* current transaction anyway. That also means we don't need to worry
* about any concurrent readers of the tuple; no other transaction can see
* it yet.
*/
if ((indexInfo->ii_BrokenHotChain || EarlyPruningEnabled(heapRelation)) &&
!isreindex &&
!indexInfo->ii_Concurrent)
{
Oid indexId = RelationGetRelid(indexRelation);
Relation pg_index;
HeapTuple indexTuple;
Form_pg_index indexForm;
pg_index = table_open(IndexRelationId, RowExclusiveLock);
indexTuple = SearchSysCacheCopy1(INDEXRELID,
ObjectIdGetDatum(indexId));
if (!HeapTupleIsValid(indexTuple))
elog(ERROR, "cache lookup failed for index %u", indexId);
indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
/* If it's a new index, indcheckxmin shouldn't be set ... */
Assert(!indexForm->indcheckxmin);
indexForm->indcheckxmin = true;
CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
heap_freetuple(indexTuple);
table_close(pg_index, RowExclusiveLock);
}可以看到，源码注释里，我们可以得出如下一些结论
1.如果我们发现任何可能损坏的HOT链，就将索引标记为不可用，直到当前事务低于视界范围。2.如果为堆表启用了早期修剪/清理，也会设置此项。3.在concurrent create index的时候也不需要设置indcheckxmin，因为有indisvalid控制
而EarlyPruningEnabled，则很清晰了，判断old_snapshot_threshold >= 0，同时判断是否是普通表（即unlogged、临时表）、是否是系统表等。
/*
* Common definition of relation properties that allow early pruning/vacuuming
* when old_snapshot_threshold >= 0.
*/
#define RelationAllowsEarlyPruning(rel) \
( \
RelationIsPermanent(rel) && !IsCatalogRelation(rel) \
&& !RelationIsAccessibleInLogicalDecoding(rel) \
)
#define EarlyPruningEnabled(rel) (old_snapshot_threshold >= 0 && RelationAllowsEarlyPruning(rel))那么到这，我们可以有一些系统性的结论了：
•开启了old_snapshot_threshold之后，会设置indcheckxmin为true•在CIC的时候，不需要设置indcheckxmin，但是也要控制长事务，即使不是同一个对象，也会阻塞索引的创建，可以参考之前的文章，需要预防HOT unsafe的问题•存在HOT Broken chain的时候，会设置indcheckxmin为true•reindex的时候，不需要设置indcheckxmin
那么再深入一下，old_snapshot_threshold的行为，刚刚提到了，old_snapshot_threshold设置之后，Keep one xid per minute for old snapshot error handling.，每一分钟会添加一个xid到数组里面
TransactionId xid_by_minute[FLEXIBLE_ARRAY_MEMBER];引用一下德哥的原话
如果配置了old_snapshot_threshold.，则需要记录每分钟最大的backend_xid(没有则取最小未分配事务号) list（而非backend_xmin），vacuum时用于判断。vacuum时，在old_snapshot_threshold最老的记录与当前系统最小的backend_xmin, backend_xid中取最大值，超过这个值的垃圾不回收。
使用这种方法可以避免在此之前的，长时间持有xmin的事务或SQL，导致对其以后产生的垃圾无法回收的问题
在PostgreSQL 14的版本里，提供了 old_snapshot 插件用于观察，看下例子
postgres=# create table test(id int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# insert into test values(1);
INSERT 0 1
postgres=*# select txid_current();
txid_current
--------------
736
(1 row)
postgres=*# select clock_timestamp();
clock_timestamp
-------------------------------
2022-03-08 12:09:53.538058+08
(1 row)另开一个会话，不断查看
postgres=# select * from pg_old_snapshot_time_mapping();
array_offset | end_timestamp | newest_xmin
--------------+------------------------+-------------
0 | 2022-03-08 12:09:00+08 | 735
1 | 2022-03-08 12:10:00+08 | 735
2 | 2022-03-08 12:11:00+08 | 736
3 | 2022-03-08 12:12:00+08 | 736
4 | 2022-03-08 12:13:00+08 | 736
5 | 2022-03-08 12:14:00+08 | 736
6 | 2022-03-08 12:15:00+08 | 736
7 | 2022-03-08 12:16:00+08 | 736
8 | 2022-03-08 12:17:00+08 | 736
(9 rows)可以看到，快照里面记录了每一分钟存在的最老未提交的事务ID。大致回收原理是
•垃圾回收 遇上某 dead tuple xmax id > 某个未结束事务，原则上无法vacuum回收这条垃圾tuple•如果设置了old_snapshot_threshold，如果这个未结束事务 的xid < OldSnapshotTimeMapping结构中最早的一条newest xmin并且OldSnapshotTimeMapping结构中最早的这条时间戳, 距离当前时间已经大于 old_snapshot_threshold参数，则触发oldest snapshot conflict，这个事务被回滚，垃圾tuple也可以被回收.
回顾
知道了原理之后，再来思考一下索引失效的问题，先通过一个普通例子来发散我们的思考，进行类比，步骤如下
1.drop table test1;2.create table test1(id int);3.insert into test1 values(generate_series(1,10));4.delete from test1 where id > 7;5.create index myidx on test1(id);6.select * from heap_page_items(get_raw_page('test1', 0));7.select * from bt_page_items('myidx', 1);
postgres=# SELECT * FROM heap_page_items(get_raw_page('test1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 955 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 955 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 955 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 955 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 955 | 0 | 0 | (0,5) | 1 | 2304 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 955 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 955 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7936 | 1 | 28 | 955 | 956 | 0 | (0,8) | 8193 | 1280 | 24 | | | \x08000000
9 | 7904 | 1 | 28 | 955 | 956 | 0 | (0,9) | 8193 | 1280 | 24 | | | \x09000000
10 | 7872 | 1 | 28 | 955 | 956 | 0 | (0,10) | 8193 | 1280 | 24 | | | \x0a000000
(10 rows)
postgres=# select * from bt_page_items('myidx', 1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,1) |
2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (0,2) |
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,3) |
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (0,4) |
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 | f | (0,5) |
6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 | f | (0,6) |
7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 | f | (0,7) |
(7 rows)第二次，
1.drop table test1;2.create table test1(id int);3.新开一个事务，并让他获取事务ID4.insert into test1 values(generate_series(1,10));5.delete from test1 where id > 7;6.create index myidx on test1(id);7.select * from heap_page_items(get_raw_page('test1', 0));8.select * from bt_page_items('myidx', 1);
postgres=# SELECT * FROM heap_page_items(get_raw_page('test1', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 962 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 962 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 962 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 962 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 962 | 0 | 0 | (0,5) | 1 | 2304 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 962 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 962 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7936 | 1 | 28 | 962 | 963 | 0 | (0,8) | 8193 | 1280 | 24 | | | \x08000000
9 | 7904 | 1 | 28 | 962 | 963 | 0 | (0,9) | 8193 | 1280 | 24 | | | \x09000000
10 | 7872 | 1 | 28 | 962 | 963 | 0 | (0,10) | 8193 | 1280 | 24 | | | \x0a000000
(10 rows)
postgres=# select * from bt_page_items('myidx', 1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+--------+---------+-------+------+-------------------------+------+--------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,1) |
2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (0,2) |
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 | f | (0,3) |
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 | f | (0,4) |
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 | f | (0,5) |
6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 | f | (0,6) |
7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 | f | (0,7) |
8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 | f | (0,8) |
9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 | f | (0,9) |
10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 | f | (0,10) |
(10 rows)这一步因为开了一个事务，delete的操作是在事务开启之后执行的，可以看到索引项里面有10条了，因为刚刚那个事务可能还需要查看刚刚删掉的三条元组，那么假如使用了这个索引，索引里面也得有这三条数据，不然通过索引查询和顺序查找数据就不一致了。而第一步因为没有开启事务，确认那三条元组已经没有事务需要了，索引里面直接就可以不需要那三条索引项，这样我vacuum的时候也可以节省时间。
那么回到索引失效，当我开启了old_snapshot_threshold，表里面的数据会过期，被强制删除，这样索引就无法保证一致性了。
不过话又说回来了，我强制清理过期元组的的时候不是顺带将索引清理了吗？什么情况下还会出现unsafe的状态呢？
下面是强制使用顺序扫描的源码片段
/*
* It's possible that get_relation_info did not generate an IndexOptInfo
* for the desired index; this could happen if it's not yet reached its
* indcheckxmin usability horizon, or if it's a system index and we're
* ignoring system indexes. In such cases we should tell CLUSTER to not
* trust the index contents but use seqscan-and-sort.
*/
if (lc == NULL) /* not in the list? */
return true; /* use sort */不过源码里面也有一些注释，可能是为了嫌麻烦？美其名曰 复杂工程简单化吧。
While it might become safe to use the index earlier based on actual cleanup activity and other active transactions, the test for that would be much more complex and would require some form of blocking, so keep it simple and fast by just using the current transaction.
虽然根据实际的清理活动和其他活动的事务，更早使用索引可能会变得安全，但这方面的测试要复杂得多，并且需要某种形式的阻塞，所以只要使用当前事务，就可以保持简单和快速。
哪些情况可能导致snapshot too old错误
直接白嫖一下德哥的，hiahia
包含了backend_xmin的事务，只要SQL的执行时间超过old_snapshot_threshold阈值，并且该SQL读取到了LSN超过快照存储的LSN的数据块时。
•snapshot too old报错通常出现在非常大的SQL，同时读取的数据块在不断的变化。•snapshot too old也可能出现在pg_dump备份数据库时，因为pg_dump使用的是repeatable read隔离级别，快照是在事务启动后的第一条SQL创建的，备份时间长的话，极有可能在备份过程中读取到LSN大于快照LSN的数据块，导致snapshot too old报错。
小结
1.当开启了old_snapshot_threshold的时候，假如在创建索引之前存在长事务，该索引需要等待长事务结束才可使用，所以创建索引的时候务必先看看库里是否有长事务，不管是普通的create index还是create index concurrently，否则现象会让你多走很多弯路2.开启了old_snapshot_threshold之后，会设置indcheckxmin为true，设置indcheckxmin的目的是告诉其他事务，本索引可能是unsafe的。对应的事务在生成执行计划的时候，如果发现索引的indcheckxmin标记为true，则需要比较创建索引的事务和当前事务的先后顺序，决定是否能使用索引。3.在CIC的时候，不需要设置indcheckxmin，但是也要控制长事务，即使不是同一个对象，也会阻塞索引的创建，主要是需要预防HOT unsafe的问题4.存在HOT Broken chain的时候，会设置indcheckxmin为true5.reindex的时候，不需要设置indcheckxmin
长事务是DBkiller，在PostgreSQL中，危害更加显眼，因为MVCC的独特实现机制。在此列举一下暂时能想到的长事务罪证
1.会阻止vacuum的清理，导致表膨胀2.阻塞索引的使用，会让问题现象变得十分费解3.假如还有子事务，搭配长事务，很容易使性能急剧下降，参考之前的文章4.年龄只能降到系统存在的最早的长事务即 min (pg_stat_activity.(backend_xid, backend_xmin))，因此也需要密切关注长事务。5.logical decoding下，长事务会阻塞复制槽的创建，其实是为了推到一个一致性的位点开始解析，所以会阻塞逻辑复制、CDC等6.logical decoding下，大事务会导致WAL日志堆积7.长事务会导致流复制的备库WAL日志部分堆积
所以我们可以设置idle_in_transaction_session_timeout来控制空闲长事务，以及old_snapshot_threshold，不过通过这篇文章，能让各位知道old_snapshot_threshold的潜在问题。除此之外，还有类似长事务的效果：
1.LONG SQL(包括查，增，删，改，DDL所有的SQL)2.打开游标后不关闭3.在不必要的场景使用repeatable read或serializable事务隔离级别4.对大的数据库执行pg_dump进行逻辑备份(隐式repeatable read隔离级别的全库备份)5.长时间不关闭申请了事务号的事务(增，删，改，DDL的SQL)，还包括2PC
2PC格外注意！尤其是在logical decoding下，各位读者可以自行测试，现象十分有趣。
参考
https://postgreshelp.com/postgresql-14-snapshot-too-old/
https://developer.aliyun.com/article/61292
http://blog.itpub.net/6906/viewspace-2746704/
https://www.modb.pro/db/91486
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
