postgresql数据库 timescaledb 时序库把大数据量表转换为超表的问题_PostgreSQL

postgresql数据库 timescaledb 时序库把大数据量表转换为超表的问题

2021-04-16 22:35yang_z_1 PostgreSQL

这篇文章主要介绍了postgresql数据库 timescaledb 时序库把大数据量表转换为超表,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下

前言

这几天工作的时候发现在 timescaledb 时序库中有部分大数据量的表不是超表，估计是当时建库的时候没有改影响插入，查询效率，因此需要改成超表
因工作原因部分内容做保密处理了

一创建新表

首先因为在 timescaledb 时序库中创建超表必须是要没有数据的表
因此第一步是创建一张跟原表一模一样的表（表名后面加个_cs）你可以直接copy建表语句，也可以直接用工具复制表结构

二把新表改为超表

把新建的表改为超表，7天一分区

									--我是7天一分区

									SELECT create_hypertable('表名_cs', 'alarm_time', chunk_time_interval => INTERVAL '7 day');

									-- 加个索引

									CREATE INDEX "表名_cs_create_time_idx" ON "hrmw"."表名_cs" USING btree (

									 "create_time" "code"."列名" DESC NULLS FIRST

									);

三插入数据

1.数据量不大的情况可以直接插入

1	`INSERT` `into` `表名_cs` `SELECT` `*` `from` `表名;`

2.如果数据量比较大可以采取一天一天插入或者一个月或者几个月一起插入

1 2	`INSERT` `into` `表名_cs` `SELECT` `` `from` `表名` `where` `alarm_time >=` `'2020-9-1';` `INSERT` `into` `表名_cs` `SELECT` `` `from` `表名` `where` `alarm_time >=` `'2020-10-1'` `and` `alarm_time <` `'2020-11-1'` `;`

3.运用函数（储存过程）

对于表数据量太大，一天一天的插入的话可以运用储存过程（postgresql 数据库叫函数）

下面是我写的函数。大家可以借鉴

									CREATE

									    OR REPLACE PROCEDURE "hrmw"."sp_into_表名_pt" ( ) AS $BODY$ BEGIN

									    -- 一般按照表名来建函数

									    DECLARE --我声明的变量有点多 按实际的来

									        target_text TEXT;

									    sqltext TEXT;

									    sqltext1 TEXT;

									    sqltext2 TEXT;

									    rd record;

									    nloop INT;

									    isexist TEXT;

									    datestr TEXT;

									    begindate TEXT;

									    n INT;

									    BEGIN

									            --查询最早一天的时间

									            sqltext := 'select to_char(min(alarm_time),''yyyy-mm-dd'') datestr from tb_hrmw_moni_target';    

									        EXECUTE sqltext INTO begindate;

									        n := date_part( 'day', now( ) - begindate :: DATE );

									--enddate:=(to_char(now(),'yyyy-mm-dd'))::text;

									        FOR nloop IN 0..n

									        LOOP

									        datestr := ( begindate :: DATE + nloop ) :: TEXT;

									        sqltext2 := '

									        insert into 表名_cs 

									        select

									        列名1,

									        code,

									        列名2,

									        alarm_time,

									        列名3,

									        列名4

									        from 表名

									        where alarm_time >= ''' || datestr || ' 00:00:00''

									        and alarm_time <= ''' || datestr || ' 23:59:59''

									        '; 

									        EXECUTE sqltext2;

									        COMMIT;

									    END loop;

									RETURN;

									END;

									END $BODY$ LANGUAGE plpgsql