PostgreSQL 流式数据处理(聚合、过滤、转换…)系列 - 7
背景
2013年帮朋友做的方案。写了一些列文档来解决当时某个大数据BI平台的异步流式数据处理的功能。
逐步优化，化繁为简。
在业务层面，统计，数据的过滤，数据的清洗，数据的事件触发等。是比较常见的需求。
比如以COUNT就是一个很典型的例子。
在9.2以前全表的count只能通过扫描全表来得到, 即使有pk也必须扫描全表.
9.2版本增加了index only scan的功能, count(*)可以通过仅仅扫描pk就可以得到.
但是如果是一个比较大的表, pk也是很大的, 扫描pk也是个不小的开销.
到了9.6，开始支持并行查询，通过并行，一张1亿的表，COUNT可能只需要几百毫秒。这是一个质的飞跃。（但是还有很多时候用并行并不是最好的）
另外社区也除了一个流式处理的数据库，pipelineDB，但是它的社区版本限制了一个DATABASE只能使用1024个流视图，在编码的地方使用了1BYTE存储CV。
那么回到postgresql数据库本身，有没有办法来优化count全表的操作呢, 如果你的场景真的有必要频繁的count全表, 那么可以尝试一下使用以下方法来优化你的场景.
正文
这一篇主要针对的是数组的瘦身优化, 因为前面6篇blog中在统计时抽取的是所有字段组成的数组, 这样内存空间消耗会比较大.
实际上我们只需要被用于统计的字段.
例如log_c1_cnt_day这个统计表, 只需要用到xid, c1, crt_time这三个字段.
因此需要新建统计维度相关的类型. 用法如下.
digoal=# create type log_c1_stat AS(xid int8, c1 int, crt_time timestamp);
CREATE TYPE
postgres=# do language plpgsql $$
declare
v_log_c1_stat log_c1_stat[];
begin
select array_agg(i_stat) into v_log_c1_stat from (select (xid,c1,crt_time)::log_c1_stat as i_stat from log limit 10) t;
raise notice '%', v_log_c1_stat;
raise notice '%', (v_log_c1_stat[1]).xid;
end;
$$;
NOTICE: {"(505354161,0,\"2013-04-27 11:04:09.815764\")","(505354161,9,\"2013-04-27 11:04:09.815764\")","(505354161,8,\"2013-04-27 11:04:09.815764\")","(505354161,1,\"2013-04-27 11:04:09.815764\")","(505354161,0,\"2013-04-27 11:04:09.815764\")","(505354161,4,\"2013-04-27 11:04:09.815764\")","(505354161,1,\"2013-04-27 11:04:09.815764\")","(505354161,9,\"2013-04-27 11:04:09.815764\")","(505354161,1,\"2013-04-27 11:04:09.815764\")","(505354161,1,\"2013-04-27 11:04:09.815764\")"}
NOTICE: 505354161
DO
具体的函数我就不重写了.
大家可以参考前面的6篇BLOG进行相应修改.
另一个可以优化的点是多维度处理时的优化, 例如首先计算天的count, 那么在计算按周的count时, 可以使用天的count结果, 而不需要从原始数据进行统计. 按月, 年的统计依次类推.
为方便大家查询, 汇总PostgreSQL实时和非实时数据统计的案例分析文章系列 - 如下 :
1. http://blog.163.com/digoal@126/blog/static/163877040201331252945440/
2. http://blog.163.com/digoal@126/blog/static/16387704020133151402415/
3. http://blog.163.com/digoal@126/blog/static/16387704020133155179877/
4. http://blog.163.com/digoal@126/blog/static/16387704020133156636579/
5. http://blog.163.com/digoal@126/blog/static/16387704020133218305242/
6. http://blog.163.com/digoal@126/blog/static/16387704020133224161563/
7. http://blog.163.com/digoal@126/blog/static/16387704020133271134563/
8. http://blog.163.com/digoal@126/blog/static/16387704020134311144755/