PostgreSQL、Greenplum DML合并操作 最佳實踐

postgresql , greenplum , 合并删除 , 合并更新 , 合并dml

在很多場景中會涉及到資料的合并，比如

1. 某業務系統的總使用者有1億，每天的活躍使用者有100萬，新增使用者10萬，每天需要将新增、活躍使用者的資料（比如他們的餘額變化、等等）合并到資料倉庫的使用者資訊表。

2. 物化視圖，某個表被使用者不斷的增、删、改。需要将這個表（基表）的某些字段或者某部分資料提取到一個物化視圖中。這個物化視圖不需要對每一筆基表的dml都實施操作，比如對單條記錄的操作，合并成一次操作。

3. 資料同步，将oltp的資料，同步到olap系統，由于olap系統的事務處理能力沒有tp系統強，是以也必須采用合并的方法，同一條記錄被多次更新時，需要将多次更新合并成一次更新。

4. 基于redo日志的邏輯資料複制，優化手段除了并行複制，還有一種就是合并複制。

不管是哪種資料合并，被合并的表最好是有主鍵的，本文也假設有主鍵來處理。否則會增加複雜度（需要使用整行記錄來區分），而且整行記錄有一個缺陷，例如根據行号定位重複記錄中的一條，這樣變更後，合并時可能會出錯。

對于以上幾種情況，比較複雜的是邏輯資料複制，它可能涉及到任意操作，單個key可能被删除，後續這個key又被插入、多次更新的情況。

同時還需要考慮事務一緻性的問題，每一次合并操作都需要保證一緻性。例如基于redo的邏輯複制，對于未結束的事務産生的redo，不能參與合并。

保證單個key，在合并時隻操作一次，同時確定未結束的事務不參與合并。

table : 庫\schema\表名

old : 主鍵值

new : 新插入的值 、 被變更的字段變更後的值

tag : insert 、 update 、 delete 、 truncate

以邏輯複制為例，分解一下資料合并的過程。

建立測試表

産生一些dml

将以上dml轉換為redo要素如下（通常以下資訊可以在資料庫的redo日志得到）

合并過程，對已送出的記錄，按pk進行分組，按執行先後順序排序

如果涉及到pk的變更，需要将其分解為delete和insert兩條

邏輯複制的合并相對來說比較複雜，但是postgresql是一個功能強大的資料庫，它支援視窗查詢，程式設計能力強大的plpgsql函數語言（還有python, java, perl等資料庫函數語言），使用sql還是比較友善的可以完成以上合并的。

除了邏輯複制，在olap中也經常要用到合并更新，主要的目的是減少olap系統sql的執行次數（因為olap系統并不是為tp業務設計，而是為批處理或大量運算設計的，多次sql如果能合并成一次的話，可以大幅提升效率）

下面就以更新的合并為例，簡單的講解一下資料合并的例子。

比如一張表有1億記錄，每天要更新其中的10萬條記錄。我們要做的是将10萬條update語句，合并成一條update語句。

合并方法

1. 首先将更新語句轉換為資料，插入一張臨時表

2. 然後使用join update來更新目标表

過程如下

建立一個生産表（目标表，必須有pk），假設它有1億（為了示範，僅使用100萬記錄）使用者資料。

建立一張臨時表，用來存儲合并前的dml，表結構如下，需要包含一個新增的序列pk，以及目标表的所有字段，以及每個字段對應的set位（表示該字段是否被更新）

我們這裡假設一條記錄，可能被多次更新。

插入100萬資料到prod表

tp系統中的update語句，我們将它轉換為目标值，插入臨時表

true大于false，将用于多條記錄的合并

如果一條記錄被多次update，需要将多個update合并為一個update

用到了視窗查詢，以目标表的pk為分組，按不同字段的set位優先取true的最後一條值，以及它的set狀态。

以上就是合并後的資料

更新時，使用case，将字段set位為true的值更新為新的值，false的不變。

删除更加簡單，隻需要将id記錄下來，delete from tbl where id in (...)即可，不再列舉。

使用兩張目标表，一張為合并更新（合并更新的資料來源于實時更新的觸發器日志），一張為實時更新。

使用pgbench，不斷更新prod_ck

将tmp1的更新合并到prod

驗證合并更新後prod和prod_ck是否一緻

資料合并的目标是将多條dml語句合并成一條，

包括将單條記錄的多次更新、插入、删除合并為一次更新、插入或删除操作，

也包括将多條記錄的多次dml合并成一條dml語句。

在資料邏輯複制、tp到ap業務系統的同步、物化視圖 等場景有着廣泛的應用。

特别是olap系統，由于并不是針對tp場景涉及，使用合并操作，可以大幅提升ap系統的操作效率。(greenplum更新和删除都是表級鎖, 效率也一般)

在greenplum單條記錄，基于pk的更新速度測試