阿里云RDS PostgreSQL时序数据的优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。 https://blog.csdn.net/chszs/article/details/80628826 阿里云RDS PostgreSQL时序数据的优化2008年6月8日版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。
版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。 https://blog.csdn.net/chszs/article/details/80628826

阿里云RDS PostgreSQL时序数据的优化

  • 2008年6月8日
  • 版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。

PostgreSQL是最流行的开源数据库之一。PostgreSQL的一个优势是它可以通过多种方式进行优化,例如数据合并和数据清理。而数据合并和数据清理在一些应用场景下是必需的。

例如:

  • 当数据表的具体内容被更新(插入、更新、删除)时,我们需要先合并,然后迅速得到每个主键的最新值。
  • 当我们有大量连续不断地报告数据的传感器时,我们需要及时收集每个传感器的最新读数。
  • 我们可以使用窗口查询进行这种操作,但我们需要快速检索批量数据。

通常有四种优化时序数据的方法:

  1. 当只有很少的唯一值和一个未知的范围时,我们可以使用递归(recursion)。
  2. 当只有很少的唯一值并且它们的范围已经确定,我们可以使用子查询(subquery)。
  3. 当有许多唯一值时,窗口查询(Window query)比上述方法更合适。
  4. 流计算(Stream computing)是所有场景中最好的。

本文只会比较前三种方法。流计算不需要进行比较,因为它是所有场景中最强大的方法。

递归 vs. 子查询 vs. 窗口查询

在比较中,将使用一个包含500万条唯一值的数据库作为数据源,并在以下情况下比较这些方法。

递归

情景1、有大量有效的唯一值(100万个唯一值)

第1步:创建一个表

\timing  
drop table test;  
create unlogged table test(id int , info text, crt_time timestamp);

第2步:构建数据

insert into test select ceil(random()*1000000), md5(random()::text), clock_timestamp() from generate_series(1,5000000);

第3步:创建一个索引

create index idx_test_1 on test (id, crt_time desc);

第4步:递归查询效率

explain (analyze,verbose,timing,costs,buffers) with recursive skip as (    
  (    
    select test as v from test where id in (select id from test where id is not null order by id,crt_time desc limit 1) limit 1  
  )    
  union all    
  (    
    select (  
      select t as v from test t where t.id>(s.v).id and t.id is not null order by id,crt_time desc limit 1  
    ) from skip s where (s.v).id is not null  
  )      -- The "where (s.v).id is not null" must be included. Else you will be stuck in an infinite loop.   
)     
select (t.v).id, (t.v).info, (t.v).crt_time from skip t where t.* is not null;   
                                                                                      QUERY PLAN                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 CTE Scan on skip t  (cost=54.35..56.37 rows=100 width=44) (actual time=0.042..6626.084 rows=993288 loops=1)  
   Output: (t.v).id, (t.v).info, (t.v).crt_time  
   Filter: (t.* IS NOT NULL)  
   Rows Removed by Filter: 1  
   Buffers: shared hit=3976934  
   CTE skip  
     ->  Recursive Union  (cost=0.91..54.35 rows=101 width=69) (actual time=0.034..6006.615 rows=993289 loops=1)  
           Buffers: shared hit=3976934  
           ->  Limit  (cost=0.91..0.93 rows=1 width=69) (actual time=0.033..0.033 rows=1 loops=1)  
                 Output: test.*  
                 Buffers: shared hit=8  
                 ->  Nested Loop  (cost=0.91..10.19 rows=500 width=69) (actual time=0.032..0.032 rows=1 loops=1)  
                       Output: test.*  
                       Buffers: shared hit=8  
                       ->  HashAggregate  (cost=0.48..0.49 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)  
                             Output: test_1.id  
                             Group Key: test_1.id  
                             Buffers: shared hit=4  
                             ->  Limit  (cost=0.43..0.47 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=1)  
                                   Output: test_1.id, test_1.crt_time  
                                   Buffers: shared hit=4  
                                   ->  Index Only Scan using idx_test_1 on public.test test_1  (cost=0.43..173279.36 rows=5000002 width=12) (actual time=0.015..0.015 rows=1 loops=1)  
                                         Output: test_1.id, test_1.crt_time  
                                         Index Cond: (test_1.id IS NOT NULL)  
                                         Heap Fetches: 1  
                                         Buffers: shared hit=4  
                       ->  Index Scan using idx_test_1 on public.test  (cost=0.43..9.64 rows=6 width=73) (actual time=0.009..0.009 rows=1 loops=1)  
                             Output: test.*, test.id  
                             Index Cond: (test.id = test_1.id)  
                             Buffers: shared hit=4  
           ->  WorkTable Scan on skip s  (cost=0.00..5.14 rows=10 width=32) (actual time=0.006..0.006 rows=1 loops=993289)  
                 Output: (SubPlan 1)  
                 Filter: ((s.v).id IS NOT NULL)  
                 Rows Removed by Filter: 0  
                 Buffers: shared hit=3976926  
                 SubPlan 1  
                   ->  Limit  (cost=0.43..0.49 rows=1 width=81) (actual time=0.005..0.005 rows=1 loops=993288)  
                         Output: t_1.*, t_1.id, t_1.crt_time  
                         Buffers: shared hit=3976926  
                         ->  Index Scan using idx_test_1 on public.test t_1  (cost=0.43..102425.17 rows=1666667 width=81) (actual time=0.005..0.005 rows=1 loops=993288)  
                               Output: t_1.*, t_1.id, t_1.crt_time  
                               Index Cond: ((t_1.id > (s.v).id) AND (t_1.id IS NOT NULL))  
                               Buffers: shared hit=3976926  
 Planning time: 0.354 ms  
 Execution time: 6706.105 ms  
(45 rows)

情景二、只有很少有效的唯一值(1,000个唯一值)

第1步:创建一个表

\timing  
drop table test;  
create unlogged table test(id int , info text, crt_time timestamp);

第2步:构建数据

insert into test select ceil(random()*1000), md5(random()::text), clock_timestamp() from generate_series(1,5000000);

第3步:创建一个索引

create index idx_test_1 on test (id, crt_time desc);  

第4步:递归查询效率

Query statement stays unchanged  
                                                                                      QUERY PLAN                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 CTE Scan on skip t  (cost=55.09..57.11 rows=100 width=44) (actual time=0.046..8.859 rows=1000 loops=1)  
   Output: (t.v).id, (t.v).info, (t.v).crt_time  
   Filter: (t.* IS NOT NULL)  
   Rows Removed by Filter: 1  
   Buffers: shared hit=4007  
   CTE skip  
     ->  Recursive Union  (cost=0.91..55.09 rows=101 width=69) (actual time=0.039..8.203 rows=1001 loops=1)  
           Buffers: shared hit=4007  
           ->  Limit  (cost=0.91..1.67 rows=1 width=69) (actual time=0.038..0.038 rows=1 loops=1)  
                 Output: test.*  
                 Buffers: shared hit=8  
                 ->  Nested Loop  (cost=0.91..6335.47 rows=8333 width=69) (actual time=0.038..0.038 rows=1 loops=1)  
                       Output: test.*  
                       Buffers: shared hit=8  
                       ->  HashAggregate  (cost=0.48..0.49 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)  
                             Output: test_1.id  
                             Group Key: test_1.id  
                             Buffers: shared hit=4  
                             ->  Limit  (cost=0.43..0.47 rows=1 width=12) (actual time=0.016..0.017 rows=1 loops=1)  
                                   Output: test_1.id, test_1.crt_time  
                                   Buffers: shared hit=4  
                                   ->  Index Only Scan using idx_test_1 on public.test test_1  (cost=0.43..173279.55 rows=5000002 width=12) (actual time=0.015..0.015 rows=1 loops=1)  
                                         Output: test_1.id, test_1.crt_time  
                                         Index Cond: (test_1.id IS NOT NULL)  
                                         Heap Fetches: 1  
                                         Buffers: shared hit=4  
                       ->  Index Scan using idx_test_1 on public.test  (cost=0.43..6284.98 rows=5000 width=73) (actual time=0.015..0.015 rows=1 loops=1)  
                             Output: test.*, test.id  
                             Index Cond: (test.id = test_1.id)  
                             Buffers: shared hit=4  
           ->  WorkTable Scan on skip s  (cost=0.00..5.14 rows=10 width=32) (actual time=0.008..0.008 rows=1 loops=1001)  
                 Output: (SubPlan 1)  
                 Filter: ((s.v).id IS NOT NULL)  
                 Rows Removed by Filter: 0  
                 Buffers: shared hit=3999  
                 SubPlan 1  
                   ->  Limit  (cost=0.43..0.49 rows=1 width=81) (actual time=0.007..0.007 rows=1 loops=1000)  
                         Output: t_1.*, t_1.id, t_1.crt_time  
                         Buffers: shared hit=3999  
                         ->  Index Scan using idx_test_1 on public.test t_1  (cost=0.43..102425.80 rows=1666667 width=81) (actual time=0.007..0.007 rows=1 loops=1000)  
                               Output: t_1.*, t_1.id, t_1.crt_time  
                               Index Cond: ((t_1.id > (s.v).id) AND (t_1.id IS NOT NULL))  
                               Buffers: shared hit=3999  
 Planning time: 0.353 ms  
 Execution time: 8.980 ms  
(45 rows)

子查询

情景1、有大量有效的唯一值(100万个唯一值)

第1步:子查询查询效率

如果ID的值范围过宽,子查询效率会很低下。

需要维护一个唯一的ID表。这里我们使用generate_series作为测试的替代。

explain (analyze,verbose,timing,costs,buffers) select (select test from test where id=t.id order by crt_time desc limit 1) from generate_series(1,1000000) t(id);  
                                                                 QUERY PLAN                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------  
 Function Scan on pg_catalog.generate_series t  (cost=0.00..1976.65 rows=1000 width=32) (actual time=70.682..2835.109 rows=1000000 loops=1)  
   Output: (SubPlan 1)  
   Function Call: generate_series(1, 1000000)  
   Buffers: shared hit=3997082  
   SubPlan 1  
     ->  Limit  (cost=0.43..1.97 rows=1 width=77) (actual time=0.002..0.002 rows=1 loops=1000000)  
           Output: test.*, test.crt_time  
           Buffers: shared hit=3997082  
           ->  Index Scan using idx_test_1 on public.test  (cost=0.43..9.64 rows=6 width=77) (actual time=0.002..0.002 rows=1 loops=1000000)  
                 Output: test.*, test.crt_time  
                 Index Cond: (test.id = t.id)  
                 Buffers: shared hit=3997082  
 Planning time: 0.119 ms  
 Execution time: 2892.712 ms  
(14 rows)

情景二、只有很少有效的唯一值(1,000个唯一值)

第1步:子查询查询效率

查询语句更改为

explain (analyze,verbose,timing,costs,buffers) select (select test from test where id=t.id order by crt_time desc limit 1) from generate_series(1,1000) t(id);  
                                                                   QUERY PLAN                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------  
 Function Scan on pg_catalog.generate_series t  (cost=0.00..1699.41 rows=1000 width=32) (actual time=0.107..7.041 rows=1000 loops=1)  
   Output: (SubPlan 1)  
   Function Call: generate_series(1, 1000)  
   Buffers: shared hit=4000  
   SubPlan 1  
     ->  Limit  (cost=0.43..1.69 rows=1 width=77) (actual time=0.006..0.007 rows=1 loops=1000)  
           Output: test.*, test.crt_time  
           Buffers: shared hit=4000  
           ->  Index Scan using idx_test_1 on public.test  (cost=0.43..6284.98 rows=5000 width=77) (actual time=0.006..0.006 rows=1 loops=1000)  
                 Output: test.*, test.crt_time  
                 Index Cond: (test.id = t.id)  
                 Buffers: shared hit=4000  
 Planning time: 0.131 ms  
 Execution time: 7.126 ms  
(14 rows)

窗口查询

情景1、有大量有效的唯一值(100万个唯一值)

第1步:窗口查询效率

explain (analyze,verbose,timing,costs,buffers) select id,info,crt_time from (select row_number() over (partition by id order by crt_time desc) as rn, * from test) t where rn=1;  
postgres=# explain (analyze,verbose,timing,costs,buffers) select id,info,crt_time from (select row_number() over (partition by id order by crt_time desc) as rn, * from test) t where rn=1;  
                                                                       QUERY PLAN                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on t  (cost=0.43..310779.41 rows=25000 width=45) (actual time=0.027..6398.308 rows=993288 loops=1)  
   Output: t.id, t.info, t.crt_time  
   Filter: (t.rn = 1)  
   Rows Removed by Filter: 4006712  
   Buffers: shared hit=5018864  
   ->  WindowAgg  (cost=0.43..248279.39 rows=5000002 width=53) (actual time=0.026..5973.497 rows=5000000 loops=1)  
         Output: row_number() OVER (?), test.id, test.info, test.crt_time  
         Buffers: shared hit=5018864  
         ->  Index Scan using idx_test_1 on public.test  (cost=0.43..160779.35 rows=5000002 width=45) (actual time=0.019..4058.476 rows=5000000 loops=1)  
               Output: test.id, test.info, test.crt_time  
               Buffers: shared hit=5018864  
 Planning time: 0.121 ms  
 Execution time: 6446.901 ms  
(13 rows)

情景二、只有很少有效的唯一值(1,000个唯一值)

第1步:窗口查询效率

查询语句保持不变

                                                                       QUERY PLAN                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on t  (cost=0.43..310779.61 rows=25000 width=45) (actual time=0.027..6176.801 rows=1000 loops=1)  
   Output: t.id, t.info, t.crt_time  
   Filter: (t.rn = 1)  
   Rows Removed by Filter: 4999000  
   Buffers: shared hit=4744850 read=18157  
   ->  WindowAgg  (cost=0.43..248279.58 rows=5000002 width=53) (actual time=0.026..5822.576 rows=5000000 loops=1)  
         Output: row_number() OVER (?), test.id, test.info, test.crt_time  
         Buffers: shared hit=4744850 read=18157  
         ->  Index Scan using idx_test_1 on public.test  (cost=0.43..160779.55 rows=5000002 width=45) (actual time=0.020..4175.082 rows=5000000 loops=1)  
               Output: test.id, test.info, test.crt_time  
               Buffers: shared hit=4744850 read=18157  
 Planning time: 0.108 ms  
 Execution time: 6176.924 ms  
(13    rows)

横向效率比较图

这里写图片描述

结论

随着物联网的兴起,越来越多的业务会使用时序数据,在必须根据这些数据提供服务的情况下,计算数据中的最新值和滑动窗口中的值是至关重要的。

PostgreSQL是开源数据库的最佳选择,因为它为相同的问题提供了几种解决方案。参考数据优化方法,我们可以得出结论:

  1. 递归适用于只有少量唯一值但范围未知的情况。
  2. 子查询适用于唯一值很少且范围已确定的情况。
  3. 当有大量唯一值时,窗口查询比子查询更合适。
  4. 流计算是所有场景的最佳选择。
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
17天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
17天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
22天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
22天前
|
存储 人工智能 关系型数据库
阿里云AnalyticDB for PostgreSQL 入选VLDB 2025:统一架构破局HTAP,Beam+Laser引擎赋能Data+AI融合新范式
在数据驱动与人工智能深度融合的时代,企业对数据仓库的需求早已超越“查得快”这一基础能力。面对传统数仓挑战,阿里云瑶池数据库AnalyticDB for PostgreSQL(简称ADB-PG)创新性地构建了统一架构下的Shared-Nothing与Shared-Storage双模融合体系,并自主研发Beam混合存储引擎与Laser向量化执行引擎,全面解决HTAP场景下性能、弹性、成本与实时性的矛盾。 近日,相关研究成果发表于在英国伦敦召开的数据库领域顶级会议 VLDB 2025,标志着中国自研云数仓技术再次登上国际舞台。
152 0
|
4月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
301 62
|
11月前
|
消息中间件 关系型数据库 Kafka
一种小资源情况下RDS数据实时同步StarRocks方案
使用一台4C8 G服务器轻松实现2个MySQL实例中通过负责分库分表规则之后的5000多张表的数据实时同步到StarRocks
452 67
|
7月前
|
SQL 关系型数据库 PostgreSQL
【YashanDB 知识库】从 PostgreSQL 迁移到 YashanDB 如何进行数据行数比对
【YashanDB 知识库】从 PostgreSQL 迁移到 YashanDB 如何进行数据行数比对
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】从PostgreSQL迁移到YashanDB如何进行数据行数比对
本文介绍了通过Oracle视图`v$sql`和`v$sql_plan`分析SQL性能的方法。首先,可通过`plan_hash_value`从`v$sql_plan`获取SQL执行计划,结合示例展示了具体查询方式。文章还创建了一个UDF函数`REPEAT`用于格式化输出,便于阅读复杂执行计划。最后,通过实例展示了如何根据`plan_hash_value`获取SQL文本及其内存中的执行计划,帮助优化性能问题。

推荐镜像

更多