前言
Postgresql DB 目前預設在更新資料時會在 Heap Table 新增一條新版本資料,舊版本會先存在直到使用 VACUUM 回收
HOT update 作用在,當資料更新時通過 Heap Block 內部串聯所有 tuple 版本,則 Index 索引不變
HOT update 必須滿足以下兩個條件:
- 索引欄位值不變。(其中任意一個索引字段的值發生了變化,則所有索引都需要新增版本)
- 新版本資料和舊版本資料在同一個HEAP Block中。
在測試前我們要先開啟 pageinspect
了解查看 block 底層訊息
sample data
1 | create extension pageinspect -- 打開可以查看底層 Page 功能,需要有 admin 權限 |
1 | CREATE TABLE tt2(id int,a int,b int); |
接著我們透過 bt_page_items
查看 Index 儲存資料,heap_page_items
查看 heap table 儲存資料
1 | SELECT * FROM bt_page_items('ix_tt2_id',1); |
會發現因為 Index 資料 ctid
,data
存放資料順序不一樣外(因為 btree by 不一樣的 column),其餘都一樣
HOT Update
我們針對於沒有 Index column (b
) 更新資料
1 | UPDATE tt2 |
利用 bt_page_items
查詢 Index 發現看起來沒有變動
1 | postgres=# SELECT * FROM bt_page_items('ix_tt2_id',1); |
但我們利用 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 | postgres-# SELECT * FROM heap_page_items(get_raw_page('tt2', 0)); |
No HOT update
接著我們針對於 a column 進行 update 動作,來觸發 No HOT update
1 | UPDATE tt2 |
會發現 tt2
所有 Index 都會新增 5 筆 tuple,那是因為我們目前操作 HOT Update
1 | postgres=# SELECT * FROM bt_page_items('ix_tt2_a',1); |
而 Heap Table 也不負眾望的又多新增5筆資料 16 ~ 20 為最新資料
1 |
|
所以上面例子 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 | select relname,schemaname,n_dead_tup as "死元組數", |
這時我們會使用 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 | postgres=# vacuum verbose tt2; |
使用完 vacuum
後我們對於 Index 資料查詢發現之前存在的舊有資料(贓資料都不見了).
1 | postgres=# SELECT * FROM bt_page_items('ix_tt2_id',1); |
但我們查詢 Heap Table 時發現資料還是 20 筆,只是原本存在的髒資料都被清空了
那是因為我們只是做一般的 vacuum 如果要把 Heap Table 資料空間釋放給 OS
我們需要做 vacuum full
1 | postgres=# SELECT * FROM heap_page_items(get_raw_page('tt2', 0)); |
vacuum full
1 | postgres=# vacuum full verbose tt2; |
會發現不只 Heap Table 空間被釋放了,就連 Index t_ctid
位置也改變了
1 | postgres=# SELECT * FROM heap_page_items(get_raw_page('tt2', 0)); |
小結
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 許可協議。轉載請註明出處!