数据并发访问在PostgreSQL管理多版本并发控制(MVCC)模型。数据快照为每个SQL语句,这样他们总是保持一致的数据,即使其他事务并发修改它。这将导致管理多个版本的同一行当行修改了一个或多个事务。从用户的角度来看,有可能只有一行的数据,但在内部PostgreSQL可能会维护一个或多个版本的这一行。
行版本是否可见事务保持堆的行数据。优化能见度信息的抓取PostgreSQL叉还维护了“_vm”关系,跟踪哪个页面只包含已知的元组可见所有活动事务。
死版本不再可见的任何事务由真空清理过程。在这之前指数和堆页面可能包含大量的死元组(这取决于你的工作负载)的性质。很update-intensive工作量,这可能是一个巨大的数量!
乍一看,似乎无害,但是这种死亡索引创建一个元组级联效应的积累导致显著的性能退化。PostgreSQL 13中的重复数据删除技术工作之后,下一个逻辑步骤是为了防止btree指数扩张减少页面分裂。
物理数据存储
PostgreSQL保持固定大小的数据存储单元称为页面。定义一个页面的大小在PostgreSQL服务器编译过程。默认的页面大小是8 k,但是这可以改变到一个更高的价值。虽然改变页面大小复杂事物的其他工具可能需要重新编译或重新配置。
每个表和索引存储在一个数组的页面。表中插入数据时,数据写入页面有足够的自由空间。否则,创建一个新页面。
不过有点不同的索引。第一页的索引是一个元包含控制信息索引页面。也可以有特殊的页面维护与索引相关的信息。对于btree指数,数据必须按索引列排序和堆元组ID(表)内的元组的物理位置。因此必须发生在正确的插入和更新页面保持排序顺序。如果页面没有足够空间的元组必须创建一个新页面,和一些物品的页面都搬到新页面。父页面这些叶子页面分为递归。
避免页面分裂
b -树索引页分裂时出现新的或新的non-HOT元版本添加到索引。热是一个缩写“堆只有元组”。基本而言,它是一种去除死行一个给定页面上(重组),从而使新行空间。通过避免或延迟页面分裂,我们可以避免或减缓指数扩张,因此减少膨胀。这是令人兴奋的!
虽然没有太多可以做新元组更新可以这样过时版本的管理逻辑不变索引元组(即不变索引列)可以保持逐步删除空闲空间的新版本。这个过程是在计划提供一个提示的帮助下,“指数不变”指数的方法。这是真的如果没有索引列是改变了这个更新的结果。
自底向上删除索引操作期间完成预期”版本生产页面分裂”时(“指数不变”提示是正确的)。过时版本的逻辑不变索引元组被新版本的页面空间。这种方法可能可以避免页面分裂。
自底向上的删除在行动
看到这种方法的实际好处,让我们更深入地了解b -树索引。我们要比较btree PostgreSQL版本之间的指数大小13和14。更详细检查索引数据,我将使用“pageinspect”扩展可用的contrib模块。“pageinspect”扩展允许我们看到底层页面内容索引或表。
让我们开始创建pageinspect扩展。你可能需要安装contrib模块,或者如果你正在建立从源安装它,然后继续。
|
1
|
创建
扩展
如果
不
存在
pageinspect;
|
现在让我们创建一个表“foo”两列,创建两个索引覆盖指数,并分析表。
|
1
2
3
4
|
下降
表
如果
存在
foo;
创建
表
喷火
与
(autovacuum_enabled=
假)
作为
(选择
GENERATE_SERIES(1,1000)
作为
col1,
字符串的子串(MD5(随机()::文本),0,25)
作为
价值);
创建
指数
在
foo (col1);
创建
指数
在
foo (col1)包括(价值);
|
是时候检查的页面数量,元组,“foo”表的大小关系。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
选择
relname
,relkind
,relpages
,reltuples
,PG_SIZE_PRETTY (PG_RELATION_SIZE (oid))
从
pg_class
在哪里
relname
就像
“% foo %”
订单
通过
relkind
DESC;
relname|relkind|relpages|reltuples|pg_size_pretty
- - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
喷火|r|8|1000年|64年kB
foo_col1_idx|我|5|1000年|40kB
foo_col1_value_idx|我|9|1000年|72年kB
(3
行)
|
14.1和13.5给相同的输出上面的查询。
禁用顺序和位图扫描索引扫描。这将迫使查询在本例中使用索引扫描。
|
1
2
|
集
enable_seqscan=
假;
集
enable_bitmapscan=
假;
|
创建四个新版本的元组。
|
1
2
3
4
|
更新
喷火
集
价值
=
价值
| |
“x”;
更新
喷火
集
价值
=
价值
| |
“x”;
更新
喷火
集
价值
=
价值
| |
“x”;
更新
喷火
集
价值
=
价值
| |
“x”;
|
以上查询结果更新1000行。“分析”表,以确保我们的数据是准确的。也让我们评论的页面数量,元组,“foo”表的大小关系。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
24
25
26
27
|
分析
foo;
选择
relname
,relkind
,relpages
,reltuples
,PG_SIZE_PRETTY (PG_RELATION_SIZE (oid))
从
pg_class
在哪里
relname
就像
“% foo %”
订单
通过
relkind
DESC;
——PostgreSQL 14.1
relname|relkind|relpages|reltuples|pg_size_pretty
- - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
喷火|r|8|1000年|288年kB
foo_col1_idx|我|5|1000年|88年kB
foo_col1_value_idx|我|9|1000年|216年kB
(3
行)
——PostgreSQL 13.5
- - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
喷火|r|8|1000年|288年kB
foo_col1_idx|我|5|1000年|104年kB
foo_col1_value_idx|我|9|1000年|360年kB
(3
行)
|
表的大小增加了两个版本的相同但是,指数在14.1 13.5相比明显较小的尺寸。太好了,不过可以肯定的是我们检查页面内容理解幕后发生了什么。
回顾第一索引页的内容(而不是元页面)清楚地展示了自底向上的删除是如何保持索引尺寸小。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
24
25
26
27
28
29日
30.
31日
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
选择
itemoffset
,ctid
,itemlen
,
零位
,var
,死
,htid
从
bt_page_items (“foo_col1_value_idx”,1)
限制
15;
PostgreSQL14.1
itemoffset|ctid|itemlen|
零位
|var|死|htid
- - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - -
1|(7,- 1)|16|f|f||
2|(7181)|40|f|t|f|(7181)
3|(7225)|48|f|t|f|(7225)
4|(7182)|40|f|t|f|(7182)
5|(7226)|48|f|t|f|(7226)
6|(7183)|40|f|t|f|(7183)
7|(7227)|48|f|t|f|(7227)
8|(7184)|40|f|t|f|(7184)
9|(7228)|48|f|t|f|(7228)
10|(7185)|40|f|t|f|(7185)
11|(7229)|48|f|t|f|(7229)
12|(7186)|40|f|t|f|(7186)
13|(7230)|48|f|t|f|(7230)
14|(7187)|40|f|t|f|(7187)
15|(7231)|48|f|t|f|(7231)
(15
行)
PostgreSQL13.5
itemoffset|ctid|itemlen|
零位
|var|死|htid
- - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - -
1|(0,1)|16|f|f||
2|(0,1)|40|f|t|f|(0,1)
3|49 (7)|40|f|t|f|49 (7)
4|(7137)|40|f|t|f|(7137)
5|(7181)|40|f|t|f|(7181)
6|(7225)|48|f|t|f|(7225)
7|(0,2)|40|f|t|f|(0,2)
8|50 (7)|40|f|t|f|50 (7)
9|(7138)|40|f|t|f|(7138)
10|(7182)|40|f|t|f|(7182)
11|(7226)|48|f|t|f|(7226)
12|(0,3)|40|f|t|f|(0,3)
13|(7,51)|40|f|t|f|(7,51)
14|(7139)|40|f|t|f|(7139)
15|(7183)|40|f|t|f|(7183)
(15
行)
|
看“itemoffset”2 - 3为13.5 14.1和2到6告诉我们整个的故事。13.5是携带的整个元组集版本14.1而洁净的死者元组以腾出空间。用更少的版本,有较少的页面导致腹胀,给我们一个较小的索引大小。
结论
减少由于底删除索引大小是一个巨大的加在PostgreSQL版本14。Btree索引机制,普通索引扫描设置LP_DEAD国旗。不过这不是设置位图索引扫描。一旦设置,空间可以回收不需要真空。然而,这是一个完全不同的类的元组。从长远来看,这种自下而上的删除策略有助于大大减少重复的特定类。它不仅减少了负载真空,还有助于保持索引健康导致更快的访问速度。如果你有一个高更新的工作负载,肯定会有储蓄在资源利用和成本提供更好的性能。
