PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介: PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.

本文将介绍PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等

测试环境为macOS+docker, PolarDB部署请参考下文:

Timescale DB 部署

目前PolarDB 开源版本兼容PG 11, 所以只能使用TimescaleDB 1.7.x的版本, 未来PolarDB升级到14后, 可以使用TimescaleDB 2.x的版本.

cd ~  
  
git clone -b 1.7.x --depth 1 https://github.com/timescale/timescaledb  
cd timescaledb  
  
./bootstrap  -DREGRESS_CHECKS=OFF  
  
cd build && make  
  
sudo make install  
AI 代码解读

修改polardb配置

vi ~/tmp_master_dir_polardb_pg_1100_bld/postgresql.conf  
  
vi ~/tmp_replica_dir_polardb_pg_1100_bld1/postgresql.conf  
vi ~/tmp_replica_dir_polardb_pg_1100_bld2/postgresql.conf  
  
shared_preload_libraries = 'timescaledb,......'  
AI 代码解读

更多的参数配置和优化建议参考:

使用TimescaleDB

postgres=# create extension timescaledb ;  
WARNING:   
WELCOME TO  
 _____ _                               _     ____________    
|_   _(_)                             | |    |  _  \ ___ \   
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /   
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \   
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /  
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/  
               Running version 1.7.4  
For more information on TimescaleDB, please visit the following links:  
  
 1. Getting started: https://docs.timescale.com/getting-started  
 2. API reference documentation: https://docs.timescale.com/api  
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture  
  
Note: TimescaleDB collects anonymous reports to better understand and assist our users.  
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.  
  
CREATE EXTENSION  
AI 代码解读
postgres=# \dx  
                                      List of installed extensions  
    Name     | Version |   Schema   |                            Description                              
-------------+---------+------------+-------------------------------------------------------------------  
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language  
 postgis     | 3.3.2   | public     | PostGIS geometry and geography spatial types and functions  
 timescaledb | 1.7.5   | public     | Enables scalable inserts and complex queries for time-series data  
(3 rows)  
AI 代码解读

创建普通时序表

-- We start by creating a regular SQL table  
  
CREATE TABLE conditions (  
  time        TIMESTAMPTZ       NOT NULL,  
  location    TEXT              NOT NULL,  
  temperature DOUBLE PRECISION  NULL,  
  humidity    DOUBLE PRECISION  NULL  
);  
AI 代码解读

将普通表转化为timescale时序表

-- This creates a hypertable that is partitioned by time  
--   using the values in the `time` column.  
  
SELECT create_hypertable('conditions', 'time');  
AI 代码解读

写入测试数据

INSERT INTO conditions(time, location, temperature, humidity)  
  VALUES (NOW(), 'office', 70.0, 50.0);  
AI 代码解读

查询时序表基表内容

SELECT * FROM conditions ORDER BY time DESC LIMIT 100;  
AI 代码解读

基表自动分片存储

postgres=# \d+ conditions  
                                            Table "public.conditions"  
   Column    |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description   
-------------+--------------------------+-----------+----------+---------+----------+--------------+-------------  
 time        | timestamp with time zone |           | not null |         | plain    |              |   
 location    | text                     |           | not null |         | extended |              |   
 temperature | double precision         |           |          |         | plain    |              |   
 humidity    | double precision         |           |          |         | plain    |              |   
Indexes:  
    "conditions_time_idx" btree ("time" DESC)  
Triggers:  
    ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()  
Child tables: _timescaledb_internal._hyper_1_1_chunk  
AI 代码解读
INSERT INTO conditions(time, location, temperature, humidity)   
select now()+(id||' second')::interval,   
  md5((random()*1000)::int::text),   
  random()*100, random()*100   
  from generate_series(1,1000000) id;  
  
  
postgres=# \d+ conditions  
                                            Table "public.conditions"  
   Column    |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description   
-------------+--------------------------+-----------+----------+---------+----------+--------------+-------------  
 time        | timestamp with time zone |           | not null |         | plain    |              |   
 location    | text                     |           | not null |         | extended |              |   
 temperature | double precision         |           |          |         | plain    |              |   
 humidity    | double precision         |           |          |         | plain    |              |   
Indexes:  
    "conditions_time_idx" btree ("time" DESC)  
Triggers:  
    ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()  
Child tables: _timescaledb_internal._hyper_1_1_chunk,  
              _timescaledb_internal._hyper_1_2_chunk  
  
  
postgres=# SELECT * FROM conditions ORDER BY time DESC LIMIT 100;  
             time              |             location             |    temperature    |     humidity        
-------------------------------+----------------------------------+-------------------+-------------------  
 2023-01-16 16:27:12.442233+00 | 70efdf2ec9b086079795c442636b55fb | 0.917297508567572 |  45.0286225881428  
 2023-01-16 16:27:11.442233+00 | b056eb1587586b71e2da9acfe4fbd19e |  59.0947337448597 |  49.3321735877544  
 2023-01-16 16:27:10.442233+00 | 28dd2c7955ce926456240b2ff0100bde |  26.5667649917305 |  88.5223139543086  
 2023-01-16 16:27:09.442233+00 | 1ecfb463472ec9115b10c292ef8bc986 |  12.9402264486998 |   23.304360313341  
 2023-01-16 16:27:08.442233+00 | 82161242827b703e6acf9c726942a1e4 |  48.1451884843409 |  97.9283190798014  
 2023-01-16 16:27:07.442233+00 | 812b4ba287f5ee0bc9d43bbf5bbe87fb |  76.0097410064191 |  20.2729247976094  
 2023-01-16 16:27:06.442233+00 | d645920e395fedad7bbbed0eca3fe2e0 |  97.6623016409576 |  22.9934238363057  
 2023-01-16 16:27:05.442233+00 | 0d0fd7c6e093f7b804fa0150b875b868 |  7.43439155630767 |  96.3830435648561  
 2023-01-16 16:27:04.442233+00 | 6e2713a6efee97bacb63e52c54f0ada0 |  30.4179009050131 |  36.7151976097375  
 2023-01-16 16:27:03.442233+00 | fb7b9ffa5462084c5f4e7e85a093e6d7 |  22.1182454843074 |  23.0733227450401  
 2023-01-16 16:27:02.442233+00 | d1f255a373a3cef72e03aa9d980c7eca |  95.6964490003884 |  43.6015542596579  
 2023-01-16 16:27:01.442233+00 | 89f0fd5c927d466d6ec9a21b9ac34ffa |  60.8098595868796 |  26.7892859410495  
 ...  
AI 代码解读

分片字段自动创建索引

postgres=# explain SELECT * FROM conditions ORDER BY time DESC LIMIT 100;  
                                                            QUERY PLAN                                                               
-----------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.42..7.77 rows=100 width=56)  
   ->  Custom Scan (ChunkAppend) on conditions  (cost=0.42..32778.88 rows=446297 width=56)  
         Order: conditions."time" DESC  
         ->  Index Scan using _hyper_1_2_chunk_conditions_time_idx on _hyper_1_2_chunk  (cost=0.42..32778.88 rows=446297 width=56)  
         ->  Index Scan using _hyper_1_1_chunk_conditions_time_idx on _hyper_1_1_chunk  (cost=0.42..48162.05 rows=656108 width=56)  
(5 rows)  
AI 代码解读

实时聚合基表数据例子

https://legacy-docs.timescale.com/v1.7/using-timescaledb/continuous-aggregates

创建基表

CREATE TABLE conditions (  
      time TIMESTAMPTZ NOT NULL,  
      device INTEGER NOT NULL,  
      temperature FLOAT NOT NULL,  
      PRIMARY KEY(time, device)  
);  
SELECT create_hypertable('conditions', 'time');  
AI 代码解读

创建自动聚合视图

CREATE VIEW conditions_summary_hourly  
WITH (timescaledb.continuous) AS  
SELECT device,  
       time_bucket(INTERVAL '1 hour', time) AS bucket,  
       AVG(temperature),  
       MAX(temperature),  
       MIN(temperature)  
FROM conditions  
GROUP BY device, bucket;  
  
  
CREATE VIEW conditions_summary_daily  
WITH (timescaledb.continuous) AS  
SELECT device,  
       time_bucket(INTERVAL '1 day', time) AS bucket,  
       AVG(temperature),  
       MAX(temperature),  
       MIN(temperature)  
FROM conditions  
GROUP BY device, bucket;  
AI 代码解读

写入测试数据

INSERT INTO conditions(time, device, temperature)   
select now()+(id||' second')::interval,   
  (random()*100)::int,   
  random()*100    
  from generate_series(1,1000000) id;  
AI 代码解读

查询聚合视图

SELECT * FROM conditions_summary_daily  
WHERE device = 5  
  AND bucket >= '2023-01-01' AND bucket < '2023-01-10';  
  
  
 device |         bucket         |       avg        |       max        |        min           
--------+------------------------+------------------+------------------+--------------------  
      5 | 2023-01-05 00:00:00+00 | 52.8728757359047 | 99.9651623424143 |  0.113607617095113  
      5 | 2023-01-06 00:00:00+00 | 50.9738177677259 | 99.9353400431573 | 0.0549898017197847  
      5 | 2023-01-07 00:00:00+00 | 49.2079831483183 | 99.9868880026042 | 0.0576195307075977  
      5 | 2023-01-08 00:00:00+00 | 48.3715454505876 | 99.9165495857596 |  0.242615444585681  
      5 | 2023-01-09 00:00:00+00 | 49.0718302013499 | 99.7824223246425 | 0.0885920133441687  
(5 rows)  
AI 代码解读
SELECT * FROM conditions_summary_daily  
WHERE max - min > 1800  
  AND bucket >= '2023-01-01' AND bucket < '2023-04-01'  
ORDER BY bucket DESC, device DESC LIMIT 20;  
AI 代码解读

修改聚合视图的自动刷新延迟、保留时间窗口、手工基于时间窗口维护保留数据

ALTER VIEW conditions_summary_hourly SET (  
  timescaledb.refresh_lag = '1 hour'  
);  
  
  
ALTER VIEW conditions_summary_daily SET (  
  timescaledb.ignore_invalidation_older_than = '30 days'  
);  
  
  
SELECT drop_chunks(INTERVAL '30 days', 'conditions_summary_daily');  
AI 代码解读

修改自动聚合视图风格, 是否只查询已聚合内容、或包含未聚合内容(需实时查询基表进行计算):

ALTER VIEW conditions_summary_hourly SET (  
    timescaledb.materialized_only = false  
);  
  
ALTER VIEW conditions_summary_daily SET (  
    timescaledb.materialized_only = false  
);  
AI 代码解读

参考

https://legacy-docs.timescale.com/v1.7/main

https://legacy-docs.timescale.com/v1.7/using-timescaledb/continuous-aggregates

https://github.com/timescale/timescaledb-tune

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
打赏
0
2
2
3
20684
分享
相关文章
PolarDB 开源基础教程系列 7.5 应用实践之 TPCH性能优化
PolarDB在复杂查询、大数据量计算与分析场景的测试和优化实践.
39 7
PolarDB 开源基础教程系列 7.3 应用实践之 精准营销场景
本文介绍了基于用户画像的精准营销技术,重点探讨了如何通过标签组合快速圈选目标人群。实验分为三部分: 1. **传统方法**:使用字符串存储标签并进行模糊查询,但性能较差,每次请求都需要扫描全表。 2. **实验1**:引入`pg_trgm`插件和GIN索引,显著提升了单个模糊查询条件的性能。 3. **实验2**:改用数组类型存储标签,并结合GIN索引加速包含查询,性能进一步提升。 4. **实验3**:利用`smlar`插件实现近似度过滤,支持按标签重合数量或比例筛选。
28 3
PolarDB 开源基础教程系列 9 开源社区合作和共建
本文介绍了玩转 PolarDB 开源社区指南:如何搭建 PolarDB 开发环境及参与开源社区。 主要内容: 1. **搭建开发环境**:提供多种 Docker 镜像供开发者选择,支持 x86_64 和 ARM64 架构,适配 CentOS、Debian、Ubuntu 等多个 Linux 发行版。 2. **编译与部署**:通过 Docker 容器克隆 PolarDB 源码并编译安装,支持构建一写多读集群测试 ePQ MPP 优化器功能。 3. **参与开源社区**:介绍个人、生态伙伴和用户如何从社区中获取技能、建立连接、积累战绩并提升影响力。社区活动涵盖公开课、训练营、编程大赛、企业行等。
60 5
PolarDB 开源基础教程系列 8 数据库生态
PolarDB是一款开源的云原生分布式数据库,源自阿里云商业产品。为降低使用门槛,PolarDB携手伙伴打造了完整的开源生态,涵盖操作系统、芯片、存储、集成管控、监控、审计、开发者工具、数据同步、超融合计算、ISV软件、开源插件、人才培养、社区合作及大型用户合作等领域。通过这些合作伙伴,PolarDB提供了丰富的功能和服务,支持多种硬件和软件环境,满足不同用户的需求。更多信息请访问[PolarDB开源官方网站](https://openpolardb.com/home)。
45 4
PolarDB 开源基础教程系列 7.4 应用实践之 AI大模型外脑
PolarDB向量数据库插件通过实现通义大模型AI的外脑,解决了通用大模型无法触达私有知识库和产生幻觉的问题。该插件允许用户将新发现的知识和未训练的私有知识分段并转换为向量,存储在向量数据库中,并创建索引以加速相似搜索。当用户提问时,系统将问题向量化并与数据库中的向量进行匹配,找到最相似的内容发送给大模型,从而提高回答的准确性和相关性。此外,PolarDB支持多种编程语言接口,如Python,使数据库具备内置AI能力,极大提升了数据处理和分析的效率。
41 4
让PolarDB更了解您--PolarDB云原生数据库核心功能体验馆
让PolarDB更了解您——PolarDB云原生数据库核心功能体验馆,由阿里云数据库产品事业部负责人宋震分享。内容涵盖PolarDB技术布局、开源进展及体验馆三大部分。技术布局包括云计算加速数据库演进、数据处理需求带来的变革、软硬协同优化等;开源部分介绍了兼容MySQL和PostgreSQL的两款产品;体验馆则通过实际操作让用户直观感受Serverless、无感切换、SQL2Map等功能。
114 7
零基础教你用云数据库PolarDB搭建企业网站,完成就送桌面收纳桶!
零基础教你用云数据库PolarDB搭建企业网站,完成就送桌面收纳桶,邀请好友完成更有机会获得​小米Watch S3、小米体重称​等诸多好礼!
零基础教你用云数据库PolarDB搭建企业网站,完成就送桌面收纳桶!
PolarDB PostgreSQL版:商业数据库替换与企业上云首选
PolarDB PostgreSQL版是商业数据库替换与企业上云的首选。其技术架构实现存储计算分离,具备极致弹性和扩展性,支持Serverless、HTAP等特性。产品在弹性、性能、成本优化和多模处理方面有显著提升,如冷热数据自动分层、Ganos多模引擎等。已在汽车、交通、零售等行业成功应用,典型案例包括小鹏汽车、中远海科等,帮助企业大幅降低运维成本并提高业务效率。
49 13
PolarDB分布式版:与云融合的分布式数据库发展新阶段
PolarDB分布式版标志着分布式数据库与云融合的新阶段。它经历了三个发展阶段:从简单的分布式中间件,到一体化分布式架构,再到云原生分布式数据库。PolarDB充分利用云资源的弹性、高性价比、高可用性和隔离能力,解决了大规模数据扩展性问题,并支持多租户场景和复杂事务处理。零售中台的建设背景包括国家数字化转型战略及解决信息孤岛问题,采用分布式数据库提升高可用性和性能,满足海量订单处理需求。展望未来,零售中台将重点提升容灾能力、优化资源利用并引入AI技术,以实现更智能的服务和更高的业务连续性。
瑶池数据库大讲堂|PolarDB HTAP:为在线业务插上实时分析的翅膀
瑶池数据库大讲堂介绍PolarDB HTAP,为在线业务提供实时分析能力。内容涵盖MySQL在线业务的分析需求与现有解决方案、PolarDB HTAP架构优化、针对分析型负载的优化(如向量化执行、多核并行处理)及近期性能改进和用户体验提升。通过这些优化,PolarDB HTAP实现了高效的数据处理和查询加速,帮助用户更好地应对复杂业务场景。

热门文章

最新文章

相关产品

  • 云原生数据库 PolarDB