如何实现数据实时统计

PostgreSQL数据库工作学习随笔 2022-06-22

1689

PipelineDB是一个用于在时序数据上持续执行SQL查询的高性能PostgreSQL插件。SQL查询的输出被持久化到普通的表中，可以像其它的表或视图一样进行查询。可以认为持续查询的结果是一个高吞吐量并且快速更新的物化视图。经过测试确实不错，但是奈何2019年5月1日PipelineDB团队加入Confluent不再提供开源免费版，免费版只支持到PG11。

最近在研究timesacledb时序数据库，发现里面的连续聚合很有意思，它开源、支持历史数据按规则进行保留、统计数据实时更新。

话不多说，直接看测试情况。

创建超表
postgres=# create table agg_tab(id int,name varchar,dtime timestamp default clock_timestamp(),primary key (id,dtime));
CREATE TABLE
postgres=# 


按小时进行分区，每小时一个分区
postgres=# SELECT create_hypertable('agg_tab','dtime',chunk_time_interval => INTERVAL '1 hours');
NOTICE:  adding not-null constraint to column "dtime"
DETAIL:  Time dimensions cannot have NULL values.
   create_hypertable
-----------------------
 (3,public,test_avg,t)
(1 row)


postgres=#


创建连续聚合聚合，查询每小时人数统计
postgres=# CREATE MATERIALIZED VIEW view_agg_tab WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', dtime) as bucket,count(*) FROM agg_tab GROUP BY bucket;
NOTICE:  refreshing continuous aggregate "view_agg_tab"
HINT:  Use WITH NO DATA if you do not want to refresh the continuous aggregate on creation.
CREATE MATERIALIZED VIEW
postgres=# 


写入测试数据库
postgres=# insert into agg_tab select id,md5(random()::text),dtime from generate_series(1,3000) id,generate_series(to_date('20220318','yyyymmdd'),to_date('20220319','yyyymmdd'),'5m') dtime;
INSERT 0 867000
postgres=#


查询视图
postgres=# select * from view_agg_tab ;
       bucket        | count
---------------------+-------
 2022-03-19 00:00:00 |  3000
 2022-03-18 08:00:00 | 36000
 2022-03-18 15:00:00 | 36000
 2022-03-18 03:00:00 | 36000
 2022-03-18 22:00:00 | 36000
 2022-03-18 13:00:00 | 36000
 2022-03-18 11:00:00 | 36000
 2022-03-18 19:00:00 | 36000
 2022-03-18 17:00:00 | 36000
 2022-03-18 04:00:00 | 36000
 2022-03-18 01:00:00 | 36000
 2022-03-18 00:00:00 | 36000
 2022-03-18 02:00:00 | 36000
 2022-03-18 09:00:00 | 36000
 2022-03-18 07:00:00 | 36000
 2022-03-18 06:00:00 | 36000
 2022-03-18 05:00:00 | 36000
 2022-03-18 20:00:00 | 36000
 2022-03-18 16:00:00 | 36000
 2022-03-18 21:00:00 | 36000
 2022-03-18 12:00:00 | 36000
 2022-03-18 18:00:00 | 36000
 2022-03-18 14:00:00 | 36000
 2022-03-18 10:00:00 | 36000
 2022-03-18 23:00:00 | 36000
(25 rows)


postgres=# 


再次写入数据
postgres=# insert into agg_tab select id,md5(random()::text),dtime from generate_series(3001,5000) id,generate_series(to_date('20220318','yyyymmdd'),to_date('20220319','yyyymmdd'),'5m') dtime;
INSERT 0 578000
postgres=#


再次查询视图，可以看到数据已经实时统计
postgres=# select * from view_agg_tab ;
       bucket        | count
---------------------+-------
 2022-03-19 00:00:00 |  5000
 2022-03-18 08:00:00 | 60000
 2022-03-18 15:00:00 | 60000
 2022-03-18 03:00:00 | 60000
 2022-03-18 22:00:00 | 60000
 2022-03-18 13:00:00 | 60000
 2022-03-18 11:00:00 | 60000
 2022-03-18 19:00:00 | 60000
 2022-03-18 17:00:00 | 60000
 2022-03-18 04:00:00 | 60000
 2022-03-18 01:00:00 | 60000
 2022-03-18 00:00:00 | 60000
 2022-03-18 02:00:00 | 60000
 2022-03-18 09:00:00 | 60000
 2022-03-18 07:00:00 | 60000
 2022-03-18 06:00:00 | 60000
 2022-03-18 05:00:00 | 60000
 2022-03-18 20:00:00 | 60000
 2022-03-18 16:00:00 | 60000
 2022-03-18 21:00:00 | 60000
 2022-03-18 12:00:00 | 60000
 2022-03-18 18:00:00 | 60000
 2022-03-18 14:00:00 | 60000
 2022-03-18 10:00:00 | 60000
 2022-03-18 23:00:00 | 60000
(25 rows)


postgres=#


复制

连续聚合有点类似于 PostgreSQL 的物化视图，但与物化视图不同的是，连续聚合可以连续和增量刷新。刷新可以手动完成，也可以通过在后台运行的策略完成，并且可以覆盖整个连续聚合或仅覆盖特定时间范围。在任何一种情况下，刷新只会重新计算自上次刷新以来已更改的聚合存储桶。

默认情况下，对连续聚合的查询使用实时聚合（在 TimescaleDB 1.7 中首次引入）将物化聚合与源超表中的最新数据相结合。通过以这种方式结合原始数据和物化数据，实时聚合可产生准确和最新的结果，同时仍受益于大部分结果的预先计算的聚合。

实时聚合是任何新的连续聚合的默认行为。要禁用实时聚合并仅显示物化数据，请 timescaledb.materialized_only=true在创建连续聚合视图时添加该参数，或使用将其设置在现有连续聚合上 ALTER MATERIALIZED VIEW。

大数据

文章转载自PostgreSQL数据库工作学习随笔，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

如何实现数据实时统计

评论

相关阅读