一个简单算法可以帮助物联网,金融 用户 节约98%的数据存储成本 (PostgreSQL,Greenplum帮你做到)

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: PostgreSQL简直是工业界和学术界的完美结合,怎么说呢?下面娓娓道来。前段时间为大家分享了物联网行业分析以及PostgreSQL 为物联网场景量身定制的特性介绍。https://yq.aliyun.com/articles/18034https://yq.aliyun.com/edu/les.

PostgreSQL简直是工业界和学术界的完美结合,怎么说呢?
下面娓娓道来。
前段时间为大家分享了物联网行业分析以及PostgreSQL 为物联网场景量身定制的特性介绍。
https://yq.aliyun.com/articles/18034
https://yq.aliyun.com/edu/lesson/67
今天再给大家分享一枚重磅炸弹,PostgreSQL 帮助物联网用户 降低98%的存储成本。这是个什么概念呢?
举个例子,你原来要花100万买存储才能存下的数据,现在只需要花2万就能搞定。
下面我会给大家介绍一下是如何做到的。
在物联网和金融行业中,存在庞大的数据体量,以一个城市的电子眼为例,一个月存储的车牌信息就可能几百亿,一年可能达到几千亿甚至上万亿。这仅仅是物联网中某一项单一应用的数据量。
这么大的数据量,存入数据库会有多大呢?
我来模拟一些数据:
5个字段,随机生成一些数据,其中一个字符串,3个INT,1个时间类型。

digoal=# create table heap_test(c1 int, c2 text, c3 int, c4 int, c5 timestamp);
digoal=# 
insert into heap_test 
  select 
  random()*5000000, 
  repeat(md5(c::text),32), 
  c+random()*50000000, 
  random()*1000000,  
  current_date+(150000*random())::int
  from
  (select trunc(random()*100000) c from generate_series(1,50000000) t(id)) t;

并行执行10次,生成5亿数据。
传统的堆数据存储,5亿数据就达到500GB。
怎样能把500GB的数据压缩到5GB呢?
原理如下:
堆表行压缩
1
堆表块压缩
2
列存储原理
3
列存储块压缩
4

拿Greenplum来做一下验证,PostgreSQL的列存储可以外挂cstore, monetdb引擎。或者等阿里云AliCloudDB for PostgreSQL后期的版本开放这个功能。
使用列存储,开启压缩,可以将数据量压缩到 21GB。

digoal=# create table ao1_test(c1 int, c2 text, c3 int, c4 int, c5 timestamp) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=column,COMPRESSTYPE=zlib,CHECKSUM=false);
digoal=# insert into ao1_test select * from heap_test ;
digoal=# analyze ao1_test;
ANALYZE
digoal=# select pg_size_pretty(pg_total_relation_size('ao1_test'));
 pg_size_pretty 
----------------
21GB
(1 row)

压缩比达到了 这就完了吗?
显然还没,压缩比和数据存储关系是非常大的,为了进一步提升列存储的压缩比,我们必须找到一个非常合理的数据排序才能实现这一的目的。
为了达到这个目的,我们需要关注几个要素:
.1. 字段值的冗余度,冗余度越高的值,排序后的压缩比越高。
.2. 字段值的平均宽度,平均宽度大的值,压缩取得的绝对效果比宽度小的值好。
.3. 字段与字段间的线性相关性,线性关系好的字段,按其中一个A字段排序后线性关系也好,从而相关字段B的压缩效果也会比较好,从而达到双赢的目的。
以上三个要素都提到了统计学的知识, PostgreSQL数据库在这方面是非常强大的。
接下来会展示PostgreSQL利用窗口计算和统计分析,推算出最佳压缩比的字段排序组合。
第一个要素,字段值冗余度的计算方法:

case 
when n_distinct < 0 then 1 + n_distinct 
when n_distinct = 1 then 1 
when n_distinct > 1 then 1 - n_distinct/reltuples 
end

第二个要素,字段值平均宽度的算法:

avg(pg_column_size(column_name)

第三个要素,字段值之间线性相关性的计算方法:
同时需要计算 N*(N-1)/2 种相邻字段的相关性,相关性决定了压缩率的好坏,任意数据类型的相关性计算可以参考我前面写的文章:
https://yq.aliyun.com/articles/18038
原理如下:
1
2

最后是计算综合压缩率,需要计算每种组合的综合压缩率:
对于有N列的表来说,会产生N阶乘种排序组合,也就是需要计算 N! 组数据的相关性。
如何得到N种组合的顺序,可以参考我前面写的一篇文章:
https://yq.aliyun.com/articles/17228

代码如下:
依赖函数,过滤重复字段,用于生成N阶排列组合:

CREATE or replace FUNCTION has_dupli_val(VARIADIC arr int[]) RETURNS boolean AS 
$$

  select count(distinct val) <> count(*) dist_val from unnest($1) t(val) where val is not null;

$$
 language sql strict;

终极目的 :
计算每种排列组合的压缩率:
这里需要用到矩阵,窗口查询,相关性计算等统计学的知识。
用PostgreSQL可以把书本上的知识运用起来,还不错的,工业界和学术界的完美结合。
下面还是拿Greenplum来做一下验证,PostgreSQL的列存储可以外挂cstore, monetdb引擎。或者等阿里云AliCloudDB for PostgreSQL后期的版本开放这个功能。

create or replace function best_compress_cols(samp int8) returns void as 
$$

declare
  cols text[] := array['c1', 'c2', 'c3', 'c4', 'c5'];  -- 参与计算的字段(最多5列, 至少有一列的冗余度>10%)
  sub int[];  -- 计算过程中用到的数组下标
  res_sub int[];  -- 计算结果用到的数组下标
  comp_rat numeric;  -- 计算过程中存储的压缩率
  res_comp_rat numeric := 0;  -- 计算结果的压缩率
  cols_w_avg int[] := array[4, 1028, 4, 4, 8];  --  参与计算的字段, 对应的平均宽度
  dup_rat numeric[] := array[0.941288981586695, 0.999979166666666666666667, 0.996877203229815, 0.999979166814228, 0.999697591731092];   -- 参与计算的字段, 对应的冗余度
  corr numeric[][];
begin

--  计算字段之间的相关性, 存入矩阵corr[][]
execute
'
select
  array[
    array[1,c12,c13,c14,c15] ,
    array[c12,1,c23,c24,c25] ,
    array[c13,c23,1,c34,c35] ,
    array[c14,c24,c34,1,c45] ,
    array[c15,c25,c35,c45,1] 
  ] 
from
(
  select 
    corr(c1,c2) c12, 
    corr(c1,c3) c13, 
    corr(c1,c4) c14, 
    corr(c1,c5) c15, 
    corr(c2,c3) c23, 
    corr(c2,c4) c24, 
    corr(c2,c5) c25, 
    corr(c3,c4) c34, 
    corr(c3,c5) c35, 
    corr(c4,c5) c45
  from 
  (
    select 
      rank() over(order by c1) c1,
      rank() over(order by c2) c2,
      rank() over(order by c3) c3,
      rank() over(order by c4) c4,
      rank() over(order by c5) c5
    from
    (
      select '
||
cols[1]||' c1, '||cols[2]||' c2, '||cols[3]||' c3, '||cols[4]||' c4, '||cols[5]||' c5 '
||
'     from 
      heap_test 
      order by random() limit '
||
samp
||
'
    ) t
  ) t
) t '
into corr;

-- 生成字段序号排列组合
for sub in 
  with t(id) as (select generate_series(1,5)) 
  select array[t1.id,t2.id,t3.id,t4.id,t5.id] from t t1,t t2,t t3,t t4,t t5 where not has_dupli_val(t1.id,t2.id,t3.id,t4.id,t5.id) 
LOOP

comp_rat := abs( cols_w_avg[sub[1]] * dup_rat[sub[1]] * corr[sub[1]][sub[1]] )+ 
            abs( cols_w_avg[sub[2]] * dup_rat[sub[2]] * corr[sub[1]][sub[2]] )+
            abs( cols_w_avg[sub[3]] * dup_rat[sub[3]] * corr[sub[2]][sub[3]] )+
            abs( cols_w_avg[sub[4]] * dup_rat[sub[4]] * corr[sub[3]][sub[4]] )+
            abs( cols_w_avg[sub[5]] * dup_rat[sub[5]] * corr[sub[4]][sub[5]] );

raise notice '%, %, %, %, %, compress ratio: % ', cols[sub[1]], cols[sub[2]], cols[sub[3]], cols[sub[4]], cols[sub[5]], comp_rat;

if res_comp_rat = 0 then
  res_comp_rat := comp_rat;
  res_sub := sub;
elseif  comp_rat > res_comp_rat then
  res_comp_rat := comp_rat;
  res_sub := sub;
end if;

END LOOP;

-- 输出压缩比最大的组合
raise notice 'resutl: %, %, %, %, %. compress ratio: % ', cols[res_sub[1]], cols[res_sub[2]], cols[res_sub[3]], cols[res_sub[4]], cols[res_sub[5]], res_comp_rat;
end;

$$
 language plpgsql strict;

采样5000万数据,计算每种组合的压缩率,并输出最佳组合。

select best_compress_cols(50000000);  --  采样5000万记录,计算最佳压缩排序.

结果如下:

NOTICE:  resutl: c2, c4, c3, c5, c1. compress ratio: 1031.11112165362824768278103927944140

按照这个顺序重组数据,重组后的数据空间占用:

digoal=# create table ao2_test(c1 int, c2 text, c3 int, c4 int, c5 timestamp) with (APPENDONLY=true,BLOCKSIZE=2097152,ORIENTATION=column,COMPRESSTYPE=zlib,CHECKSUM=false);
digoal=# insert into ao2_test select * from heap_test order by c2, c4, c3, c5, c1 ;
digoal=# analyze ao2_test;
ANALYZE
digoal=# select pg_size_pretty(pg_total_relation_size('ao2_test'));
 pg_size_pretty 
----------------
4603MB
(1 row)

以此方法,数据空间占用从500GB到4.5GB,压缩比达到了111 : 1。
物联网,选择PostgreSQL,还不错哦。
后续继续会放出PostgreSQL数据库在更多物联网细分行业的解决方案,敬请期待。
https://yq.aliyun.com/articles/18034
https://yq.aliyun.com/edu/lesson/67

相关实践学习
钉钉群中如何接收IoT温控器数据告警通知
本实验主要介绍如何将温控器设备以MQTT协议接入IoT物联网平台,通过云产品流转到函数计算FC,调用钉钉群机器人API,实时推送温湿度消息到钉钉群。
阿里云AIoT物联网开发实战
本课程将由物联网专家带你熟悉阿里云AIoT物联网领域全套云产品,7天轻松搭建基于Arduino的端到端物联网场景应用。 开始学习前,请先开通下方两个云产品,让学习更流畅: IoT物联网平台:https://iot.console.aliyun.com/ LinkWAN物联网络管理平台:https://linkwan.console.aliyun.com/service-open
目录
相关文章
|
5月前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
关系型数据库 物联网 PostgreSQL
沉浸式学习PostgreSQL|PolarDB 11: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析
物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库. 监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库. 应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库. 以上数据具有时序特征, 对数据库的关键能力要求如下: 数据高速写入 高速按时间区间读取和分析, 目的是发现异常, 分析规律. 尽量节省存储空间
737 1
|
2月前
|
监控 物联网 关系型数据库
使用PostgreSQL触发器解决物联网设备状态同步问题
在物联网监控系统中,确保设备状态(如在线与离线)的实时性和准确性至关重要。当设备状态因外部因素改变时,需迅速反映到系统内部。因设备状态数据分布在不同表中,直接通过应用同步可能引入复杂性和错误。采用PostgreSQL触发器自动同步状态变化是一种高效方法。首先定义触发函数,在设备状态改变时更新管理模块表;然后创建触发器,在状态字段更新后执行此函数。此外,还需进行充分测试、监控性能并实施优化,以及在触发函数中加入错误处理和日志记录功能。这种方法不仅提高自动化程度,增强数据一致性与实时性,还需注意其对性能的影响并采取优化措施。
|
4月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL和greenplum的copy命令可以添加字段吗?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令可以添加字段吗?
70 3
|
4月前
|
监控 关系型数据库 数据库
PostgreSQL和greenplum的copy命令如何使用?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令如何使用?
67 2
|
12月前
|
存储 NoSQL 关系型数据库
深入探索地理空间查询:如何优雅地在MySQL、PostgreSQL及Redis中实现精准的地理数据存储与检索技巧
深入探索地理空间查询:如何优雅地在MySQL、PostgreSQL及Redis中实现精准的地理数据存储与检索技巧
1793 0
|
存储 SQL 算法
PostgreSQL 14中TOAST的新压缩算法LZ4,它有多快?
PostgreSQL 14中TOAST的新压缩算法LZ4,它有多快?
305 0
|
5月前
|
人工智能 监控 物联网
基于物联网、大数据、云计算、人工智能等技术的智慧工地源码(Java+Spring Cloud +UniApp +MySql)
基于物联网、大数据、云计算、人工智能等技术的智慧工地源码(Java+Spring Cloud +UniApp +MySql)
800 1
|
5月前
|
监控 关系型数据库 Java
SpringBoot【集成 01】Druid+Dynamic+Greenplum(实际上用的是PostgreSQL的驱动)及 dbType not support 问题处理(附hikari相关配置)
SpringBoot【集成 01】Druid+Dynamic+Greenplum(实际上用的是PostgreSQL的驱动)及 dbType not support 问题处理(附hikari相关配置)
254 0
|
搜索推荐 关系型数据库 PostgreSQL
PostgreSQL的排序算法
PostgreSQL的排序算法
77 1

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 下一篇
    无影云桌面