🚫 Ad Blocker Detected

Please disable your AD blocker to continue using this site. Ads help us keep the content free! please press keyboard F5 to refresh page after disabled AD blocker

請關閉廣告攔截器以繼續使用本網站。廣告有助於我們保證內容免費。謝謝! 關閉後請按 F5 刷新頁面

Hello World

0%

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

Posted on Edited on In , Disqus:
Symbols count in article: 15k Reading time ≈ 13 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
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,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
20
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
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 |      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
如果本文對您幫助很大，可街口支付斗內鼓勵石頭^^