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

Posted on | Edited on | In , | Comments: | Views:
Symbols count in article: 14k | Reading time ≈ 26 mins.

前言

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 資料 ctid，data 存放資料順序不一樣外(因為 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 異動.

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

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

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

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

Related Posts

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