postgresql的upsert語法

INSERT INTO the_table (id, column_1, column_2)VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2;

實驗過程

兩個庫表，每個庫大概2326萬數據

每個庫數據量如下

操作文件每500000對應時間

從上面可以看出，庫表存量數據為2326萬左右的數據，利用pg的upsert功能進行數據的存在則update，不存在則insert的實現

其中第一個50萬花從13點39分開始到14點02分完成，總共有23分鐘

計算一下，500000除以23分鐘，也就是一分鐘只處理21739條，也就是一秒鐘才362條記錄，由於第一個時間可能包含了打開文件讀取的時間，有所增加

第二個50萬，以及後面的每個50萬記錄，大概時間在12分鐘到15分鐘直接，就算平均的14分鐘，這樣每分鐘35714條，每秒595條

這樣的效率對於大數據量，比如2千萬+數據量使用upinser並不一定可選

這是92文件的全部日誌，由於有兩條錯誤記錄，然後是10000條一提交，所以有20000條記錄進入了err日誌文件需要重跑，但可以大概的看一下總體的5814516條記錄往23264636條數據的PG資料庫進行upsert操作，總共花掉了2小時44分鐘

從上圖可以看出來，最終數據由23264636變為了29059152，新增了5794516條記錄，164分鐘處理時間

下面使用原文件再執行一次所花費時間如下

可以看出，這一次沒50萬條的記錄所花費時間在11到14分鐘，折中，每50萬條記錄12分鐘，總時長2小時28分鐘，比第一次少了16分鐘之多，也就是這一次每分鐘41666條記錄，比第一次多了5952，每秒多了99條，這可是第一次的16個點啊，效率明顯加強

下面總結一下兩次的效率和差異：

第一次打底數據為23267155，第二次打底數據為第一次結束后的最終數據29059152

第一次全部數據都執行了insert，第二次全部數據都執行了update

第一次總時長164分鐘，第二次總時長148分鐘

第一次每秒595條insert，第二次每秒694條update

所以postgresql資料庫的upsert功能原理是先去查找，當匹配到關鍵值時立馬進行update操作，若發現沒有則再做insert處理

而且upsert最終執行update的效率會高於最終執行insert的效率

當文件大部分為insert時，是否需要考慮直接使用insert功能不使用upsert的insert呢？

首先我們說說upsert的好處，那就是避免了去區分數據文件的數據是應該insert還是update，這其實也是省去了一定的處理時間，當然在很多事物不同步時也是可以選擇這樣的分佈處理，只要前期工作做得夠好就行，不然就向上面的報錯一樣，1條出現錯誤那那一批次全部失敗，當然也可以優化提交的記錄數，也可以一條一提交，這個需要自己根據自己的情況而定，目前美麗的程序員是不適合一條一提交，還好錯誤文件有提前報錯，稍作修改就可以再次執行插入那剩下的19998條記錄