postgresql HOT (heap only tuple) update 深入淺出

前言

Postgresql DB 目前預設在更新資料時會在 Heap Table 新增一條新版本資料,舊版本會先存在直到使用 VACUUM 回收

HOT update 作用在,當資料更新時通過 Heap Block 內部串聯所有 tuple 版本,則 Index 索引不變

HOT update 必須滿足以下兩個條件:

  1. 索引欄位值不變。(其中任意一個索引字段的值發生了變化,則所有索引都需要新增版本)
  2. 新版本資料和舊版本資料在同一個HEAP Block中。

在測試前我們要先開啟 pageinspect 了解查看 block 底層訊息

sample data

1
create extension pageinspect -- 打開可以查看底層 Page 功能,需要有 admin 權限
1
2
3
4
5
6
7
CREATE TABLE tt2(id int,a int,b int);  

INSERT INTO tt2
SELECT generate_series(1,10), random()*100, random()*100;

CREATE INDEX ix_tt2_id ON tt2 (ID);
CREATE INDEX ix_tt2_a ON tt2 (a);

接著我們透過 bt_page_items 查看 Index 儲存資料,heap_page_items 查看 heap table 儲存資料

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
SELECT * FROM bt_page_items('ix_tt2_id',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
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00
6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00
7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00
8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00
9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00
10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00


SELECT * FROM bt_page_items('ix_tt2_a',1);

itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
1 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00
2 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00
3 | (0,8) | 16 | f | f | 11 00 00 00 00 00 00 00
4 | (0,3) | 16 | f | f | 1a 00 00 00 00 00 00 00
5 | (0,7) | 16 | f | f | 34 00 00 00 00 00 00 00
6 | (0,4) | 16 | f | f | 40 00 00 00 00 00 00 00
7 | (0,9) | 16 | f | f | 41 00 00 00 00 00 00 00
8 | (0,5) | 16 | f | f | 46 00 00 00 00 00 00 00
9 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00
10 | (0,10) | 16 | f | f | 5e 00 00 00 00 00 00 00

postgres=# SELECT * FROM heap_page_items(get_raw_page('tt2', 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 | 8152 | 1 | 36 | 539 | 0 | 0 | (0,1) | 3 | 2304 | 24 | | | \x010000001800000032000000
2 | 8112 | 1 | 36 | 539 | 0 | 0 | (0,2) | 3 | 2304 | 24 | | | \x020000003e00000041000000
3 | 8072 | 1 | 36 | 539 | 0 | 0 | (0,3) | 3 | 2304 | 24 | | | \x03000000320000004e000000
4 | 8032 | 1 | 36 | 539 | 0 | 0 | (0,4) | 3 | 2304 | 24 | | | \x040000000e00000033000000
5 | 7992 | 1 | 36 | 539 | 0 | 0 | (0,5) | 3 | 2304 | 24 | | | \x05000000160000002e000000
6 | 7952 | 1 | 36 | 539 | 0 | 0 | (0,6) | 3 | 2304 | 24 | | | \x06000000480000002d000000
7 | 7912 | 1 | 36 | 539 | 0 | 0 | (0,7) | 3 | 2304 | 24 | | | \x070000004000000035000000
8 | 7872 | 1 | 36 | 539 | 0 | 0 | (0,8) | 3 | 2304 | 24 | | | \x080000005a0000001d000000
9 | 7832 | 1 | 36 | 539 | 0 | 0 | (0,9) | 3 | 2304 | 24 | | | \x090000001900000047000000
10 | 7792 | 1 | 36 | 539 | 0 | 0 | (0,10) | 3 | 2304 | 24 | | | \x0a0000002600000048000000
(10 rows)

會發現因為 Index 資料 ctiddata 存放資料順序不一樣外(因為 btree by 不一樣的 column),其餘都一樣

HOT Update

我們針對於沒有 Index column (b) 更新資料

1
2
3
UPDATE tt2 
SET b=b+1
WHERE id > 5;

利用 bt_page_items 查詢 Index 發現看起來沒有變動

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
postgres=# SELECT * FROM bt_page_items('ix_tt2_id',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
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00
6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00
7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00
8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00
9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00
10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00
(10 rows)

postgres=# SELECT * FROM bt_page_items('ix_tt2_a',1);

itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
1 | (0,6) | 16 | f | f | 04 00 00 00 00 00 00 00
2 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00
3 | (0,10) | 16 | f | f | 12 00 00 00 00 00 00 00
4 | (0,2) | 16 | f | f | 1e 00 00 00 00 00 00 00
5 | (0,5) | 16 | f | f | 2c 00 00 00 00 00 00 00
6 | (0,7) | 16 | f | f | 36 00 00 00 00 00 00 00
7 | (0,4) | 16 | f | f | 3e 00 00 00 00 00 00 00
8 | (0,9) | 16 | f | f | 47 00 00 00 00 00 00 00
9 | (0,1) | 16 | f | f | 4e 00 00 00 00 00 00 00
10 | (0,3) | 16 | f | f | 56 00 00 00 00 00 00 00

但我們利用 heap_page_items 查詢原本 Heap Table 資料會發現 lp = 11 ~ 15 使用的 t_cid 剛好可以對應

  • 6 => 11
  • 7 => 12
  • 8 => 13
  • 9 => 14
  • 10 => 15

那是因為我們在使用 HOT Update 此次更新會在原本 Heap Table 新增修改的資料用原本的 Row 指向更新的 Row

這樣Index在 look up 時候就可以得到最新資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
postgres-# SELECT * FROM heap_page_items(get_raw_page('tt2', 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 | 8152 | 1 | 36 | 533 | 0 | 0 | (0,1) | 3 | 2304 | 24 | | | \x010000004e0000004e000000
2 | 8112 | 1 | 36 | 533 | 0 | 0 | (0,2) | 3 | 2304 | 24 | | | \x020000001e00000022000000
3 | 8072 | 1 | 36 | 533 | 0 | 0 | (0,3) | 3 | 2304 | 24 | | | \x03000000560000004b000000
4 | 8032 | 1 | 36 | 533 | 0 | 0 | (0,4) | 3 | 2304 | 24 | | | \x040000003e00000012000000
5 | 7992 | 1 | 36 | 533 | 0 | 0 | (0,5) | 3 | 2304 | 24 | | | \x050000002c0000004d000000
6 | 7952 | 1 | 36 | 533 | 536 | 0 | (0,11) | 16387 | 256 | 24 | | | \x060000000400000011000000
7 | 7912 | 1 | 36 | 533 | 536 | 0 | (0,12) | 16387 | 256 | 24 | | | \x070000003600000029000000
8 | 7872 | 1 | 36 | 533 | 536 | 0 | (0,13) | 16387 | 256 | 24 | | | \x080000000800000061000000
9 | 7832 | 1 | 36 | 533 | 536 | 0 | (0,14) | 16387 | 256 | 24 | | | \x09000000470000005b000000
10 | 7792 | 1 | 36 | 533 | 536 | 0 | (0,15) | 16387 | 256 | 24 | | | \x0a0000001200000049000000
11 | 7752 | 1 | 36 | 536 | 0 | 0 | (0,11) | 32771 | 10240 | 24 | | | \x060000000400000012000000
12 | 7712 | 1 | 36 | 536 | 0 | 0 | (0,12) | 32771 | 10240 | 24 | | | \x07000000360000002a000000
13 | 7672 | 1 | 36 | 536 | 0 | 0 | (0,13) | 32771 | 10240 | 24 | | | \x080000000800000062000000
14 | 7632 | 1 | 36 | 536 | 0 | 0 | (0,14) | 32771 | 10240 | 24 | | | \x09000000470000005c000000
15 | 7592 | 1 | 36 | 536 | 0 | 0 | (0,15) | 32771 | 10240 | 24 | | | \x0a000000120000004a000000

No HOT update

接著我們針對於 a column 進行 update 動作,來觸發 No HOT update

1
2
3
UPDATE tt2 
SET a=a+1
WHERE id > 5;

會發現 tt2 所有 Index 都會新增 5 筆 tuple,那是因為我們目前操作 HOT Update

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
postgres=# SELECT * FROM bt_page_items('ix_tt2_a',1);  

itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
1 | (0,4) | 16 | f | f | 0e 00 00 00 00 00 00 00
2 | (0,5) | 16 | f | f | 16 00 00 00 00 00 00 00
3 | (0,1) | 16 | f | f | 18 00 00 00 00 00 00 00
4 | (0,9) | 16 | f | f | 19 00 00 00 00 00 00 00
5 | (0,19) | 16 | f | f | 1a 00 00 00 00 00 00 00
6 | (0,10) | 16 | f | f | 26 00 00 00 00 00 00 00
7 | (0,20) | 16 | f | f | 27 00 00 00 00 00 00 00
8 | (0,3) | 16 | f | f | 32 00 00 00 00 00 00 00
9 | (0,2) | 16 | f | f | 3e 00 00 00 00 00 00 00
10 | (0,7) | 16 | f | f | 40 00 00 00 00 00 00 00
11 | (0,17) | 16 | f | f | 41 00 00 00 00 00 00 00
12 | (0,6) | 16 | f | f | 48 00 00 00 00 00 00 00
13 | (0,16) | 16 | f | f | 49 00 00 00 00 00 00 00
14 | (0,8) | 16 | f | f | 5a 00 00 00 00 00 00 00
15 | (0,18) | 16 | f | f | 5b 00 00 00 00 00 00 00
(15 rows)

postgres=# SELECT * FROM bt_page_items('ix_tt2_id',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
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00
6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00
7 | (0,16) | 16 | f | f | 06 00 00 00 00 00 00 00
8 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00
9 | (0,17) | 16 | f | f | 07 00 00 00 00 00 00 00
10 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00
11 | (0,18) | 16 | f | f | 08 00 00 00 00 00 00 00
12 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00
13 | (0,19) | 16 | f | f | 09 00 00 00 00 00 00 00
14 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00
15 | (0,20) | 16 | f | f | 0a 00 00 00 00 00 00 00
(15 rows)

而 Heap Table 也不負眾望的又多新增5筆資料 16 ~ 20 為最新資料

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

postgres=# SELECT * FROM heap_page_items(get_raw_page('tt2', 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 | 8152 | 1 | 36 | 539 | 0 | 0 | (0,1) | 3 | 2304 | 24 | | | \x010000001800000032000000
2 | 8112 | 1 | 36 | 539 | 0 | 0 | (0,2) | 3 | 2304 | 24 | | | \x020000003e00000041000000
3 | 8072 | 1 | 36 | 539 | 0 | 0 | (0,3) | 3 | 2304 | 24 | | | \x03000000320000004e000000
4 | 8032 | 1 | 36 | 539 | 0 | 0 | (0,4) | 3 | 2304 | 24 | | | \x040000000e00000033000000
5 | 7992 | 1 | 36 | 539 | 0 | 0 | (0,5) | 3 | 2304 | 24 | | | \x05000000160000002e000000
6 | 7952 | 1 | 36 | 539 | 542 | 0 | (0,11) | 16387 | 1280 | 24 | | | \x06000000480000002d000000
7 | 7912 | 1 | 36 | 539 | 542 | 0 | (0,12) | 16387 | 1280 | 24 | | | \x070000004000000035000000
8 | 7872 | 1 | 36 | 539 | 542 | 0 | (0,13) | 16387 | 1280 | 24 | | | \x080000005a0000001d000000
9 | 7832 | 1 | 36 | 539 | 542 | 0 | (0,14) | 16387 | 1280 | 24 | | | \x090000001900000047000000
10 | 7792 | 1 | 36 | 539 | 542 | 0 | (0,15) | 16387 | 1280 | 24 | | | \x0a0000002600000048000000
11 | 7752 | 1 | 36 | 542 | 543 | 0 | (0,16) | 32771 | 8448 | 24 | | | \x06000000480000002e000000
12 | 7712 | 1 | 36 | 542 | 543 | 0 | (0,17) | 32771 | 8448 | 24 | | | \x070000004000000036000000
13 | 7672 | 1 | 36 | 542 | 543 | 0 | (0,18) | 32771 | 8448 | 24 | | | \x080000005a0000001e000000
14 | 7632 | 1 | 36 | 542 | 543 | 0 | (0,19) | 32771 | 8448 | 24 | | | \x090000001900000048000000
15 | 7592 | 1 | 36 | 542 | 543 | 0 | (0,20) | 32771 | 8448 | 24 | | | \x0a0000002600000049000000
16 | 7552 | 1 | 36 | 543 | 0 | 0 | (0,16) | 3 | 10240 | 24 | | | \x06000000490000002e000000
17 | 7512 | 1 | 36 | 543 | 0 | 0 | (0,17) | 3 | 10240 | 24 | | | \x070000004100000036000000
18 | 7472 | 1 | 36 | 543 | 0 | 0 | (0,18) | 3 | 10240 | 24 | | | \x080000005b0000001e000000
19 | 7432 | 1 | 36 | 543 | 0 | 0 | (0,19) | 3 | 10240 | 24 | | | \x090000001a00000048000000
20 | 7392 | 1 | 36 | 543 | 0 | 0 | (0,20) | 3 | 10240 | 24 | | | \x0a0000002700000049000000
(20 rows)

所以上面例子 no HOT Update 會觸發至少 3 個 IO (3個block),而 HOT Update 只觸發至少 1 個

Vacuum

PostgreSQL 使用 MVCC 來管理 tuple row version,所以當你在Delete or Update 時並不會將資料真的刪除而是在tuple header標記已經刪除的標記(存在 Heap table data,index沒有相關資訊)

UPDATE 在PostgreSQL中被視爲 DELETE + INSERT

如果沒有清理這些dead tuple對於任何 transaction 是不可見的將永遠留在文件中

對於DELETE和UPDATE比較多的的表,dead tuple可能佔據很多磁盤空間.

透過下面語法可以查詢目前資料表的tuple狀態

1
2
3
4
5
6
7
select relname,schemaname,n_dead_tup as "死元組數",
(case when n_live_tup > 0 then
n_dead_tup::float8/n_live_tup::float8
else
0
end) as "死/活元組的比例"
from pg_stat_all_tables

這時我們會使用 Vacuum 來幫我們回收過時的 tuple 資料刪除dead tuple

每個 heap 關連都有一個可見性映射表(VM,Visibility Map),用來追蹤那些頁面對於Transaction可見的tuple.跟那些頁面凍結的tuple

這就會影響到Index 使用 Index scan 或 bitmap index scan,如果Heap上都是可見就會執行Index scan比較有效率

visibility map 要被更新我們可以跑 VACUUM 指令

use vacuum to clean up rows

我們可以利用 vacuum 來清理我們的髒資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
postgres=#  vacuum verbose tt2;  
INFO: vacuuming "public.tt2"
INFO: scanned index "ix_tt2_id" to remove 5 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: scanned index "ix_tt2_a" to remove 5 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: "tt2": removed 5 row versions in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "ix_tt2_id" now contains 10 row versions in 2 pages
DETAIL: 5 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: index "ix_tt2_a" now contains 10 row versions in 2 pages
DETAIL: 5 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: "tt2": found 10 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 544
There were 5 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

使用完 vacuum 後我們對於 Index 資料查詢發現之前存在的舊有資料(贓資料都不見了).

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
postgres=# SELECT * FROM bt_page_items('ix_tt2_id',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
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00
5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00
6 | (0,16) | 16 | f | f | 06 00 00 00 00 00 00 00
7 | (0,17) | 16 | f | f | 07 00 00 00 00 00 00 00
8 | (0,18) | 16 | f | f | 08 00 00 00 00 00 00 00
9 | (0,19) | 16 | f | f | 09 00 00 00 00 00 00 00
10 | (0,20) | 16 | f | f | 0a 00 00 00 00 00 00 00
(10 rows)

postgres=# SELECT * FROM bt_page_items('ix_tt2_a',1);

itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
1 | (0,4) | 16 | f | f | 0e 00 00 00 00 00 00 00
2 | (0,5) | 16 | f | f | 16 00 00 00 00 00 00 00
3 | (0,1) | 16 | f | f | 18 00 00 00 00 00 00 00
4 | (0,19) | 16 | f | f | 1a 00 00 00 00 00 00 00
5 | (0,20) | 16 | f | f | 27 00 00 00 00 00 00 00
6 | (0,3) | 16 | f | f | 32 00 00 00 00 00 00 00
7 | (0,2) | 16 | f | f | 3e 00 00 00 00 00 00 00
8 | (0,17) | 16 | f | f | 41 00 00 00 00 00 00 00
9 | (0,16) | 16 | f | f | 49 00 00 00 00 00 00 00
10 | (0,18) | 16 | f | f | 5b 00 00 00 00 00 00 00
(10 rows)

但我們查詢 Heap Table 時發現資料還是 20 筆,只是原本存在的髒資料都被清空了

那是因為我們只是做一般的 vacuum 如果要把 Heap Table 資料空間釋放給 OS

我們需要做 vacuum full

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
postgres=# SELECT * FROM heap_page_items(get_raw_page('tt2', 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 | 8152 | 1 | 36 | 539 | 0 | 0 | (0,1) | 3 | 2304 | 24 | | | \x010000001800000032000000
2 | 8112 | 1 | 36 | 539 | 0 | 0 | (0,2) | 3 | 2304 | 24 | | | \x020000003e00000041000000
3 | 8072 | 1 | 36 | 539 | 0 | 0 | (0,3) | 3 | 2304 | 24 | | | \x03000000320000004e000000
4 | 8032 | 1 | 36 | 539 | 0 | 0 | (0,4) | 3 | 2304 | 24 | | | \x040000000e00000033000000
5 | 7992 | 1 | 36 | 539 | 0 | 0 | (0,5) | 3 | 2304 | 24 | | | \x05000000160000002e000000
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 0 | 0 | 0 | | | | | | | | | |
8 | 0 | 0 | 0 | | | | | | | | | |
9 | 0 | 0 | 0 | | | | | | | | | |
10 | 0 | 0 | 0 | | | | | | | | | |
11 | 0 | 0 | 0 | | | | | | | | | |
12 | 0 | 0 | 0 | | | | | | | | | |
13 | 0 | 0 | 0 | | | | | | | | | |
14 | 0 | 0 | 0 | | | | | | | | | |
15 | 0 | 0 | 0 | | | | | | | | | |
16 | 7952 | 1 | 36 | 543 | 0 | 0 | (0,16) | 3 | 10496 | 24 | | | \x06000000490000002e000000
17 | 7912 | 1 | 36 | 543 | 0 | 0 | (0,17) | 3 | 10496 | 24 | | | \x070000004100000036000000
18 | 7872 | 1 | 36 | 543 | 0 | 0 | (0,18) | 3 | 10496 | 24 | | | \x080000005b0000001e000000
19 | 7832 | 1 | 36 | 543 | 0 | 0 | (0,19) | 3 | 10496 | 24 | | | \x090000001a00000048000000
20 | 7792 | 1 | 36 | 543 | 0 | 0 | (0,20) | 3 | 10496 | 24 | | | \x0a0000002700000049000000
(20 rows)

vacuum full

1
2
3
4
5
6
postgres=#  vacuum full verbose tt2;  
INFO: vacuuming "public.tt2"
INFO: "tt2": found 0 removable, 10 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

會發現不只 Heap Table 空間被釋放了,就連 Index t_ctid 位置也改變了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
postgres=# SELECT * FROM heap_page_items(get_raw_page('tt2', 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 | 8152 | 1 | 36 | 539 | 0 | 0 | (0,1) | 3 | 2816 | 24 | | | \x010000001800000032000000
2 | 8112 | 1 | 36 | 539 | 0 | 0 | (0,2) | 3 | 2816 | 24 | | | \x020000003e00000041000000
3 | 8072 | 1 | 36 | 539 | 0 | 0 | (0,3) | 3 | 2816 | 24 | | | \x03000000320000004e000000
4 | 8032 | 1 | 36 | 539 | 0 | 0 | (0,4) | 3 | 2816 | 24 | | | \x040000000e00000033000000
5 | 7992 | 1 | 36 | 539 | 0 | 0 | (0,5) | 3 | 2816 | 24 | | | \x05000000160000002e000000
6 | 7952 | 1 | 36 | 543 | 0 | 0 | (0,6) | 3 | 11008 | 24 | | | \x06000000490000002e000000
7 | 7912 | 1 | 36 | 543 | 0 | 0 | (0,7) | 3 | 11008 | 24 | | | \x070000004100000036000000
8 | 7872 | 1 | 36 | 543 | 0 | 0 | (0,8) | 3 | 11008 | 24 | | | \x080000005b0000001e000000
9 | 7832 | 1 | 36 | 543 | 0 | 0 | (0,9) | 3 | 11008 | 24 | | | \x090000001a00000048000000
10 | 7792 | 1 | 36 | 543 | 0 | 0 | (0,10) | 3 | 11008 | 24 | | | \x0a0000002700000049000000
(10 rows)

小結

HOT Update 機制對於我們在 PostgreSQL DB 更新寫入資料上有效能提升(減少IO操作),但就變成我們在設計 Index 需要慎思考慮不要把資料建立在太常更新的欄位.

如果 Index Key 太常異動會造成 Index 膨脹

這個就跟在 Sql-Server 我們查看 Update 執行計畫,可以發現 update 會吃 Index 但也會對於所有更新相關的 Index column 異動.

另外我們可以理解用下面介紹理解vacuumvacuum full動作

  • vacuum:像把東西暫時整理但垃圾還是放在家裡
  • vacuum full:把家裡整理重新放置位置還把垃圾清除

但在操作 vacuum full 時會耗費更多資源而且會把資料表 lock 起來,所以千萬不要在高峰時期做不然會爆炸…

此文作者:Daniel Shih(石頭)
此文地址https://isdaniel.github.io/postgresql-hotupdate-vacuum/
版權聲明:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!


如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^