Postgres的寫入放大問題

PostgreSQL中，index會以{block, item_offset}的方式儲存每個index key所對應到的資料行，所以一般狀況下做索引查詢會從index對應到block中的第N個line pointer，再由line pointer去取得所需的資料行。由於Postgres對於交易分離的實作方法，資料在被更新的時候就會有一筆新的紀錄被插入到儲存空間裡，和舊的紀錄並存，所以index也會需要被更新以有辦法指向新的紀錄。
如果該table上面有建立不少的index，那麼這些index就要一一去做更新的動作，而且PostgreSQL的b-tree index從葉子節點做插入，節點變大到一個程度會需要進行分裂，並且將新的兄弟節點包含的index key下限寫進父節點裡面，而父節點在經過多次的資料插入之後，也有可能會面臨需要分裂與更新其父節點的情形，這樣子會製造不少的I/O寫入。

Heap-Only-Tuple update

PostgreSQL為了避免上面的問題發生，設計了一個叫做Heap-Only-Tuple的機制，這種更新方式在符合下列條件的時候可以做:
1.需要更新的欄位沒有被任何的index使用到
2.新的版本跟舊的版本可以塞在同一個Postgres block裡面的時候
如果做了Heap-only tuple update，就不會在資料所在分頁以外的地方做寫入，也就是說index定位到line pointer之間的關係維持不變，而原先tuple(Postgres資料結構中用來記錄一筆資料的單位)會被標註為Heap Hot Updated，可以透過其標頭的t_ctid來找到下一筆更新的紀錄，形成HOT chain。
當舊的版本已經不再被需要而可以刪除了，由於index還指著對應他的line pointer，所以在不修改index的情況下，會被設成LP_REDIRECT來轉接到新紀錄的line pointer上面。等到作VACUUM的時候，該line pointer會被設為LP_DEAD，讓index查詢的時候可以知道，index清除完畢之後就可以將line pointer設定為LP_UNUSED讓之後新增的資料行可以重複使用。

圖示

(資料來源:https://www.youtube.com/watch?v=B6IiuJRXw_E)

一般的UPDATE

HOT update

//在Row v1過期之後

學到的功課

1.適度降低Postgres的fill factor，可以在每個page中預留一些空間，增加Heap-only-tuple update的發生機率
2.對於Index的使用是一個要考慮的重點，過度使用index可能會在某些情況下讓資料庫的效能降低