在PostgreSQL中,当更新一行数据时,实际上旧行并没有删除,只是插入了一行新数据。如果这个表其他列上有索引,而更新的列上没有索引,因为新行的物理位置发生变化,因此需要更新索引,这将导致性能下降。为了解决这个问题,PostgreSQL引入了Heap Only Tuple(HOT)技术,如果更新后的新行和旧行位于同一个数据块内,则旧行会有一个指针指向新行,这样就不用更新索引了,通过索引访问到旧行数据,进而访问到新行数据。
一、数据块结构
要了解HOT技术,先来看一下PostgreSQL的数据块结构。如下图:
-
页头,存储LSN号、校验位等元数据信息。
-
行数据指针数组,存储指向实际数据的指针,共32位,前15位为行数据Page内偏移量,中间2位为标志位,后面15位为行数据长度。
-
实际行数据
-
特殊数据
查看实际数据情况和索引项情况。两条索引id=1
和id=2
项分别指向两条数据行指针,两条数据的ctid分别为(0,1)
,(0,2)
。lp(line pointer)
行数据指针,分别指向2条数据。每个页面8192字节,两行数据存储的开始地址分别为8160
,8128
。
postgres=# create table a(id int primary key, v text); CREATE TABLE postgres=# insert into a values (1, 'a'),(2, 'b'); INSERT 0 2 postgres=# SELECT lp,lp_off, lp_flags, lp_len,t_ctid,t_data FROM heap_page_items(get_raw_page('a', 0)); lp | lp_off | lp_flags | lp_len | t_ctid | t_data ----+--------+----------+--------+--------+---------------- 1 | 8160 | 1 | 30 | (0,1) | \x010000000561 2 | 8128 | 1 | 30 | (0,2) | \x020000000562 (2 rows) postgres=# SELECT * FROM bt_page_items('a_pkey', 1); itemoffset | ctid | itemlen | nulls | vars | data ------------+-------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 (2 rows)
二、更新后查看数据
更新第一条数据后,可以发现索引项并没有变化,索引仍然指向(0,1)
第一行数据行指针,而第一行数据内部ctid数据指向了第三条数据。这样通过索引访问时,仍然可以访问到正常的数据。
postgres=# update a set v = 'aa' where id = 1; UPDATE 1 postgres=# select * from a; id | v ----+---- 2 | b 1 | aa (2 rows) postgres=# SELECT lp,lp_off, lp_flags, lp_len,t_ctid,t_xmin,t_xmax,t_data FROM heap_page_items(get_raw_page('a', 0)); lp | lp_off | lp_flags | lp_len | t_ctid | t_xmin | t_xmax | t_data ----+--------+----------+--------+--------+--------+--------+------------------ 1 | 8160 | 1 | 30 | (0,3) | 713 | 714 | \x010000000561 2 | 8128 | 1 | 30 | (0,2) | 713 | 0 | \x020000000562 3 | 8096 | 1 | 31 | (0,3) | 714 | 0 | \x01000000076161 (3 rows) postgres=# SELECT * FROM bt_page_items('a_pkey', 1); itemoffset | ctid | itemlen | nulls | vars | data ------------+-------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 (2 rows)
三、vacuum后查看数据
来考虑另一个问题,第一行数据是一条死数据dead tuple
,经过vacuum之后,其占用的存储空间会被回收,回收后又是如何访问到正常的数据呢?
vacuum后,原第一行数据存储空间进行了回收。可以发现索引项并没有变化,索引仍然指向(0,1)
第一行数据行指针,只是第一行数据的行指针指向了第三行数据的行指针。
postgres=# vacuum a; VACUUM postgres=# SELECT lp,lp_off, lp_flags, lp_len,t_ctid,t_xmin,t_xmax,t_data FROM heap_page_items(get_raw_page('a', 0)); lp | lp_off | lp_flags | lp_len | t_ctid | t_xmin | t_xmax | t_data ----+--------+----------+--------+--------+--------+--------+------------------ 1 | 3 | 2 | 0 | | | | 2 | 8160 | 1 | 30 | (0,2) | 713 | 0 | \x020000000562 3 | 8128 | 1 | 31 | (0,3) | 714 | 0 | \x01000000076161 (3 rows) postgres=# SELECT * FROM bt_page_items('a_pkey', 1); itemoffset | ctid | itemlen | nulls | vars | data ------------+-------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 (2 rows)
四、HOT使用的条件
1、新老数据行必须位于同一个数据块内。如原来的数据块中无法放下新行,则无法使用HOT。
2、更新的列上如果有索引,此列上的索引不能使用HOT技术。
针对条件一,如果一张表经常做update操作,我们可以设置数据块的填充因子,使更新操作的新旧行都位于同一个数据块内。
--数据块填充到达50%后,就不再写入数据,开辟下一个数据块写入。 postgres=# alter table a set (fillfactor = 50); ALTER TABLE postgres=#