postgresql , es , 搜尋引擎 , 全文檢索 , 日志分析 , 反向索引 , 優化 , 分區 , 分片 , 審計日志 , 行為日志 , schemaless
在很多系統中會記錄使用者的行為日志，行為日志包括浏覽行為、社交行為、操作行為等。
典型的應用例如：資料庫的sql審計、企業内部的堡壘機（行為審計）等。
前面寫了一篇最佳實踐，通過postgresql來存儲審計日志，同時對審計日志需要檢索的字段建立全文索引。
ssd機器可以達到7萬/s的寫入（換算成全文索引條目，約280萬/s的條目建立速度）。達到這個性能名額時，cpu，磁盤io全部吃滿。
全文如下：
<a href="https://github.com/digoal/blog/blob/master/201705/20170516_01.md">《行為、審計日志 (實時索引/實時搜尋)模組化 - 最佳實踐》</a>
除了使用全文索引，還有其他方法呢？
本文将采用另一個角度來解決審計日志的檢索和高速寫入問題。
審計日志帶三個次元的查詢條件，一個是uid，一個是時間範圍，最後是詞條比對。
1. uid表示客戶id，用來區分不同使用者産生的行為資料。
2. ts字段，表示日志是什麼時間點産生的。
3. 行為資料字段，表示使用者的行為。
優化思路：
1. 将uid作為表名的一部分，每個uid一張表。
（好處：省一個字段，節約空間。同時在資料組織時不會混淆不同使用者的資料，查詢時消除了io放大的問題，提升了查詢效率。）
（缺點：每個uid一張表，表可能很多，中繼資料會變大。變更結構時，可能需要涉及較多表。）
2. ts字段，采用brin塊級索引，因為每個使用者産生的行為資料，都是時間順序的，是以堆存儲與值順序有非常強的線性相關性。
3. 将資料打散存放，使用中繼資料記錄uid對應的db list，随機寫入對應的dbs，查詢時按ts範圍查詢，查詢所有的dbs彙聚（應用層負責merge sort）後傳回（行為字段不使用索引）。
postgresql 10内置了merge sort的功能，是以你如果需要一個中間層來實作merge sort的話，pg也是個不錯的選擇。
隻需要将所有的資料源配置為fdw子表即可。
例如
方案1：
gin索引 build全文索引的方式，6萬tps時，基本榨幹了cpu和io資源。bcache gc或輕微的io抖動，會導緻比較嚴重的性能變化。
方案2：
通過uid+ts_prefix分區，確定一個使用者的資料在一份堆存儲中，減少檢索時的io開銷。
ts字段具備時序屬性，通過brin塊級索引降低索引大小。
當資料量達到一定程度時，自動觸發pg10并行查詢特性，提升查詢性能。
由于uid資料已經分片，查詢時會輸入ts和文本比對兩個變量，資料配置設定到每個節點已經不多，使用模糊查詢代替全文檢索，加上pg10的多核并行，完全可以滿足查詢響應時延需求。
create table db_meta
(
dbid int primary key, -- 每個資料庫節點一條記錄，表示一個資料庫分片
groupid int, -- 每個分片屬于一個分組
conn_info text -- 連接配接資訊(url)
);
create table uid_mapping
uid int primary key, -- 客戶唯一标示
dbgroupid int -- 資料庫分組，表示這個使用者的資料随機寫入這個分組的所有分片中。
行為資料保留一段時間後清除。
如果使用者覺得這樣設計比較麻煩，可以将所有的資料庫作為一個大池，所有使用者都随機寫入這個大池。
這種設計就好像greenplum和hawq的設計理念。greenplum是大池思想，hawq是分而治之思想。
主表結構：
每個使用者的表名為<code>bptest_$uid_$yyyymmdd</code>。
結構和索引與主表保持一緻。
ts字段的存儲順序與值的順序有非常強的線性相關性，采用塊級索引。
brin索引相比btree索引節省幾百倍空間，同時提升寫入性能。
每個分片屬于一個組，每個uid的資料随機的寫入一個指定組的所有分片。
就好像greenplum和hawq的設計理念。greenplum是大池思想，hawq是分而治之思想。
當需要查詢某個uid的行為資料時，并行查詢所有分片的資料，按ts字段merge sort并傳回。
merge sort可以放在資料庫中實作，也可以在應用層實作。
如果merge sort放在資料庫層實作，可以使用postgresql 10的postgres_fdw，每個uid的每個分片對應一張fdw table，挂在uid對應的父表中。
當查詢父表時，按ts排序，會使用merge sort。
merge sort功能詳見：
<a href="https://github.com/digoal/blog/blob/master/201703/20170313_09.md">《postgresql 10.0 preview 性能增強 - mergesort(gather merge)》</a>
排序下推功能詳見：
<a href="https://github.com/digoal/blog/blob/master/201703/20170312_20.md">《postgresql 10.0 preview sharding增強 - pushdown 增強》</a>
如果在應用層實作，方法與之類似，并行的查詢uid對應的所有分片，每個分片都是有order by傳回，在應用層使用merge sort的方法傳回給用戶端。
由于每個uid對應若幹張表<code>bptest_$uid_$yyyymmdd</code>，我們可以在資料庫端設計類似mongo的schemaless寫入風格：
有表時則插入，沒有表時則建立後再插入。
實作方法詳見
<a href="https://github.com/digoal/blog/blob/master/201705/20170511_01.md">《postgresql schemaless 的實作(類mongodb collection)》</a>
建立一個自動建表的函數，用于自動建立目标表。
建立一個插入資料的函數，使用動态sql，如果遇到表不存在的錯誤，則調用建表函數進行建表。
資料庫端的schemaless會犧牲一部分性能，因為無法使用綁定變量。
建議業務層實作schemaless(自動拼接表名，自動建表)，以提高性能。
曆史資料，可以清除，直接drop分表即可(bptest_$uid_$yyyymmdd)。
如果有保留資料的需求，可以通過阿裡雲rds postgresql的oss_fdw接口将資料寫入oss對象存儲永久儲存，要讀取時，通過fdw讀取。
雲端存儲與計算分離用法：
<a href="https://help.aliyun.com/document_detail/44461.html">《rds postgresql : 使用 oss_fdw 讀寫oss對象存儲》</a>
<a href="https://help.aliyun.com/document_detail/35457.html">《hybriddb postgresql : 使用 oss_fdw 讀寫oss對象存儲》</a>
如果有審計日志的分析需求，可以将rds postgresql資料寫入oss，通過hybriddb for postgresql進行分析。
1. 環境變量配置
2. 初始化sql
初始化每個資料庫執行個體
12個庫，100個uid。
每個uid每個庫寫入1000萬記錄，每個uid總共寫入1.2億，所有uid總共寫入120億記錄。
使用gen_rand_str生成指定長度的随機字元串。
測試腳本
寫入性能:
1. 使用brin索引時 9.47萬/s
2. 使用btree索引時 7.9萬/s
3. 伺服器資源開銷：
1. 大部分cpu開銷在産生随機串的函數中，是以實際場景，cpu的消耗會小很多。
如下
2. bcache問題
bcache垃圾回收時，對io的影響非常嚴重。
await已經到秒級
3. 配置了smooth checkpoint後，checkpoint已經沒有問題, sync時間非常短暫。
單節點2100萬記錄。
查詢需求：
1. 範圍查詢，排序輸出
傳回462萬記錄，2.5秒。
2. 範圍+全文檢索查詢，排序輸出
傳回2941196萬記錄，8.5秒。
3. 分頁數評估
如果業務允許，建議使用評估值，評估值的準确性取決于統計資訊的準确性，使用<code>alter table 表名 alter column 列名 set statistics 1000</code>可以調整列的統計精準度，預設為100。
<a href="https://github.com/digoal/blog/blob/master/201605/20160506_01.md">《論count與offset使用不當的罪名 和 分頁的優化》</a>
評估記錄數與實際記錄數對比如下，足夠精确：
4. 分頁查詢傳回
流式傳回，傳回10行僅需0.562毫秒。
如果要回翻，使用scroll遊标
1. 資料量：
單個uid，單節點，一天2100萬記錄(12gb, 索引600mb)。（100個節點/分片，單個使用者一天約21億資料量）
2. 寫入性能
2.1. 使用brin索引時 9.47萬/s
2.2. 使用btree索引時 7.9萬/s
3. 範圍查詢，排序輸出
4. 範圍+全文檢索查詢，排序輸出
傳回294萬記錄，8.5秒。
5. 分頁數評估
精确度：+- 5% 左右
響應速度：1毫秒左右。
6. 精确分頁數
與實際資料量、條件有關。1秒以上
7. 分頁查詢
範圍+全文檢索查詢，排序輸出： 每擷取1000條記錄約11毫秒。
（與命中率有關），極端情況為處理所有記錄，隻有最後一條記錄滿足條件。
使用jdbc或libpq時，一個連接配接可以設定多個執行個體，将從先到後，自動選擇一個可讀寫的執行個體。（相當于用戶端自動failover）。
配置示例，假設有4個資料庫執行個體，可以配置4個資料源如下：
當任意一個執行個體出現問題時，每個資料源還是能擷取到下一個可用的連接配接，不會堵塞寫入。
當執行個體修複後，依舊使用首選執行個體。
使用這種方法，可以最大化的提高可用性，無需備庫。
另外異常的執行個體活了之後，就會繼續被首選，無需擔心傾斜問題，因為不保留曆史。時間會抹平傾斜問題。
<a href="https://github.com/digoal/blog/blob/master/201608/20160824_02.md">《postgresql 最佳實踐 - 水準分庫(基于plproxy)》</a>
<a href="https://github.com/digoal/blog/blob/master/201512/20151220_02.md">《阿裡雲apsaradb rds for postgresql 最佳實踐 - 2 教你rds pg的水準分庫》</a>
<a href="https://github.com/digoal/blog/blob/master/201703/20170312_11.md">《postgresql 10.0 preview sharding增強 - 支援append節點并行》</a>
<a href="https://github.com/digoal/blog/blob/master/201703/20170312_07.md">《postgresql 10.0 preview sharding增強 - postgres_fdw 多節點異步并行執行》</a>
<a href="https://github.com/digoal/blog/blob/master/201610/20161027_01.md">《postgresql 9.6 sharding based on fdw & pg_pathman》</a>
<a href="https://github.com/digoal/blog/blob/master/201610/20161004_01.md">《postgresql 9.6 單元化,sharding (based on postgres_fdw) - 核心層支援前傳》</a>
<a href="https://github.com/digoal/blog/blob/master/201610/20161005_01.md">《postgresql 9.6 sharding + 單元化 (based on postgres_fdw) 最佳實踐 - 通用水準分庫場景設計與實踐》</a>
<a href="https://github.com/digoal/blog/blob/master/201604/20160414_01.md">《postgresql 物聯網黑科技 - 瘦身幾百倍的索引(brin index)》</a>
<a href="https://github.com/digoal/blog/blob/master/201704/20170420_01.md">《postgresql 10.0 preview 功能增強 - libpq支援多主機連接配接(failover,lb)讓資料庫ha和應用配合更緊密》</a>