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>