PostgreSQL 流式統計 - insert on conflict 實作 流式 UV(distinct), min, max, avg, sum, count ...

PostgreSQL , 流式統計 , insert on conflict , count , avg , min , max , sum

流式統計count, avg, min, max, sum等是一個比較有意思的場景，可用于實時大屏，實時繪制統計圖表。

比如菜鳥、淘寶、阿裡遊戲、以及其他業務系統的FEED日志，按各個次元實時統計輸出結果。(實時FEED統計，實時各次元線上人數等)

PostgreSQL insert on conflict文法以及rule, trigger的功能，可以實作對資料的實時統計，ECS 56核的性能名額：

單執行個體單表單行流式處理性能可以達到39萬行/s，批量寫入流式處理達到336萬行/s的性能。

實時統計每個SID的value的min, max, sum，以及記錄數。

1、建立測試表，包含統計字段，統計字段的内容由PostgreSQL自動生成。

2、建立明細表，用于檢查PostgreSQL流計算的結果是否正确

3、流計算算法由insert on conflict SQL完成，如下

4、大批量寫入測試

5、驗證算法正确性

實時統計後，我們的查詢響應時間從1817毫秒降到了0.5毫秒，将近4萬倍的性能提升。

我們這個測試CASE，單個次元，多表，根據CPU剩餘，估算得到的性能應該是:

38.5萬行/s

117.65萬行/s。

我們這個測試CASE，單個次元，單表，批量寫入，根據CPU剩餘（單ECS使用多執行個體或者使用UNLOGGED TABLE），估算得到的性能應該是:

336萬行/s

因為我們統計的次元可能不止一個，例如明細表中的多個字段，可能都會被統計到。

例如

這種情況下，我們需要如何實作流式統計呢？

除了使用pipelinedb，實際上PostgreSQL使用insert on conflict和trigger或rule，也能實作一樣的功能。

1、定義明細表

2、定義每個次元的目标統計表

3、定義次元表的insert on conflict SQL

4、定義明細表trigger或rule，順序調用insert on conflict 寫入多個次元表

5、測試

6、測試結果

7、驗證結果

殼子表沒有寫入，把rule改成do also的話，就會寫入本地表。是不是很爽呢？

通過定義明細分區表

1、定義明細分區表

4、定義明細分區表trigger或rule，順序調用insert on conflict 寫入多個次元表

定義上層哈希分布寫

上層應用，或者中間件，實作多個PostgreSQL執行個體，分布寫入。

結合hll資料類型，可以實作實時的UV統計。

壓測，18萬TPS。

通過hyperloglog類型查詢UV估值如下，靠譜：

HLL插件的知識參考如下:

<a href="https://github.com/aggregateknowledge/postgresql-hll">https://github.com/aggregateknowledge/postgresql-hll</a>

<a href="https://github.com/digoal/blog/blob/master/201608/20160825_02.md">《Greenplum 最佳實踐 - 估值插件hll的使用(以及hll分式聚合函數優化)》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130228_01.md">《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 3》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130227_01.md">《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 2》</a>

<a href="https://github.com/digoal/blog/blob/master/201302/20130226_01.md">《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 1》</a>

結合PostgreSQL的UDF功能，可以大大的簡化SQL的複雜度

例子

使用函數接口寫入，不用拼寫長長的SQL。

如果要記錄明細，同時要實時統計。可以使用rule進行設計。

如果是增量寫，可以通過where來過濾不需要的寫入（位點）。

這種方法，資料既寫log_table，同時又會執行流式統計XXXXX COMMAND。

<a href="https://github.com/digoal/blog/blob/master/201711/20171111_01.md">《PostgreSQL 異步消息實踐 - Feed系統實時監測與響應(如 電商主動服務) - 分鐘級到毫秒級的實作》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_28.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 27 - (OLTP) 物聯網 - FEED日志, 流式處理 與 閱後即焚 (CTE)》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_23.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 22 - (OLTP) merge insert|upsert|insert on conflict|合并寫入》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_33.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 32 - (OLTP) 高吞吐資料進出(堆存、行掃、無需索引) - 閱後即焚(JSON + 函數流式計算)》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_32.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 31 - (OLTP) 高吞吐資料進出(堆存、行掃、無需索引) - 閱後即焚(讀寫大吞吐并測)》</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170728_01.md">《打造雲端流計算、線上業務、資料分析的業務資料閉環 - 阿裡雲RDS、HybridDB for PostgreSQL最佳實踐》</a>