PostgreSQL 重複 資料清洗 優化教程

postgresql , 重複資料清洗 , with recursive , 遞歸 , 流式計算 , pipelinedb , 視窗查詢 , file_fdw , insert on conflict , llvm , 并行建立索引

重複資料清洗是一個比較常見的業務需求，比如有些資料庫不支援唯一限制，或者程式設計之初可能沒有考慮到需要在某些列上面加唯一限制，導緻應用在上線一段時間後，産生了一些重複的資料。

那麼重複資料的清洗需求就來了。

有哪些清洗手段，如何做到高效的清洗呢？

一個小小的應用場景，帶出了10項資料庫技術點，聽我道來。

比如一個表，有幾個字段本來應該是唯一的，産生了重複值，現在給你一個規則，保留重複值中的一條，其他删掉。

删除重複的 (sid + crt_time) 組合，并保留重複值中，mdf_time最大的一條。

生成測試資料100萬條，1/10 的重複機率，同時為了避免重複資料在一個資料塊中，每跳躍500條生成一條重複值。

就生成測試資料 ，是不是覺得已經很炫酷了呢？一條sql就造了一批這樣的資料。

驗證, 重複記錄的ctid不在同一個資料塊中。

驗證方法是不是很酷呢？用了視窗查詢。

包含重複的值大概這麼多

你如果覺得這個還挺快的，偷偷告訴你測試環境cpu型号。

intel(r) xeon(r) cpu e5-2630 0 @ 2.30ghz

接下來開始去重了

将去重後的結果插入一張新的表中，耗時5.8秒

分析優化空間，顯示排序可以優化

索引，消除排序，優化後隻需要3.9秒

對于線上業務，postgresql可以使用并行concurrently建立索引，不會堵塞dml。

遞歸查詢、遞歸收斂

有幾個case用這種方法提升了幾百倍性能

<a href="https://github.com/digoal/blog/blob/master/201611/20161128_01.md">《時序資料合并場景加速分析和實作 - 複合索引，視窗分組查詢加速，變态遞歸加速》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161128_02.md">《distinct xx和count(distinct xx)的變态遞歸優化方法 - 索引收斂(skip scan)掃描》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161201_01.md">《用postgresql找回618秒逝去的青春 - 遞歸收斂優化》</a>

當重複值很多時，可以使用此法，效果非常好

有uk時這樣用

導入需要處理的時，新增一個row_number字段，并建立where row_number&lt;&gt;1的partial index.

删除時删除此部分記錄即可，2秒搞定需求。

驗證

假如重複資料來自文本，從文本去重後，導入資料庫，再導出文本。

怎麼聽起來像把資料庫當成了文本處理工具在用呢？

沒關系，反正目的就是要快速。

怎麼一氣呵成呢？

首先是檔案外部表，其次是copy管道，一氣呵成。

<a href="https://www.postgresql.org/docs/9.6/static/file-fdw.html">https://www.postgresql.org/docs/9.6/static/file-fdw.html</a>

拆分成多個檔案，并行處理，耗時降低到800毫秒左右。注意這沒有結束，最後還需要merge sort對全局去重。

速度提升到了1秒以内完成，還可以繼續提高并行度，總耗時降低到200毫秒左右。

注意這沒有結束，最後還需要merge sort對全局去重。 略

并行導入單表處理後倒出，中間結果不需要儲存，是以使用unlogged table

5.28秒。

前面用到了很多種方法來進行優化，下面總結一下

主要用于篩選出重複值，并加上标記。

需要去重的字段作為視窗，規則字段作為排序字段，建立好複合索引，即可開始了。

如果你的資料來自文本，那麼可以采用一氣呵成的方法來完成去重，即把資料庫當成文本處理平台，通過postgresql的file_fdw外部表直接通路檔案，在sql中進行去重。

如果你的資料來自文本，可以将文本切割成多個小檔案，使用外部表，并行的去重，但是注意，去完重後，需要用merge sort再次去重。

另一方面，postgresql 9.6已經支援單個query使用多個cpu核來處理，可以線性的提升性能。（去重需要考慮合并的問題）。

使用遞歸查詢，可以對重複度很高的場景進行優化，曾經在幾個case中使用，優化效果非常明顯，從幾十倍到幾百倍不等。

postgresql 9.5新增的特性，可以在資料導入時完成去重的操作。 直接導出結果。

并行裝載(目前不能在同一條query中多次update一條記錄)

處理多行時，減少上下文切換。

性能可以提升一倍左右。

<a href="https://github.com/digoal/blog/blob/master/201612/20161216_01.md">《分析加速引擎黑科技 - llvm、列存、多核并行、算子複用 大聯姻 - 一起來開啟postgresql的百寶箱》</a>

在資料導入過程中，流式去重，是不是很炫酷呢。

<a href="https://github.com/digoal/blog/blob/master/201612/20161220_01.md">《流計算風雲再起 - postgresql攜pipelinedb力挺iot》</a>

在建立索引時，為了防止堵塞dml操作，可以使用concurrently的方式建立，不會影響dml操作。

建立索引時，加大maintenance_work_mem可以提高建立索引的速度。

為了加快導入速度，可以切片，并行導入。

将來可以在file_fdw這種外部通路接口中做到分片并行導入。

如果資料庫隻做計算，也就是說在資料庫中處理的中間結果無需保留時，可以适應bulk的方式導入，或者使用unlogged table。

可以提高導入的速度，同時導入時也可以關閉autovacuum.

1. 如果資料已經在資料庫中，在原表基礎上，删除重複資料，耗時約2秒。

2. 如果資料要從文本導入，并将去重後的資料導出，整個流程約耗時5.28秒。