postgreSql Timescale (创建超表和物化视图)

目次
1、创建数据库test
2、把数据库转为时序库 
3、创建超表
4、超表数据保存策略 6个月前数据主动删除
5、 验证查询超表空间
6、创建物化视图步骤

---

参考：https://docs.timescale.com/getting-started/latest/aggregation/
Timescale Cloud 是一个基于云的 PostgreSQL 平台，适用于资源麋集型工作负载。我们帮助您更快地构建、进一步扩展并控制预算。Timescale Cloud 服务是一个经过优化的 100% PostgreSQL 数据库实例，您可以按原样利用，也可以利用特定于业务需求的功能进行扩展。可用的功能包括：

• 时间序列和分析：带有 TimescaleDB 的 PostgreSQL。您熟悉和喜爱的 PostgreSQL，具有用于大规模存储和查询时间序列数据的功能，可用于分析和其他用例。利用超表、连续聚合和列式存储获得更快的基于时间的查询。利用本机压缩、数据保存策略和无底数据分层到 Amazon S3 节流存储空间。
  
• AI 和向量：带有向量扩展的 PostgreSQL。利用 PostgreSQL 作为向量数据库，并利用专门构建的扩展来重新到尾构建 AI 应用程序。利用 pgvector 和 pgvectorscale 扩展进行快速准确的相似性搜刮。利用 pgai 扩展创建向量嵌入并对数据执行 LLM 推理。
  
• PostgreSQL：值得信任的行业标准 RDBMS。非常适合需要强数据一致性、复杂关系和高级查询功能的应用程序。通过自定义函数、数据类型和扩展获得 ACID 合规性、广泛的 SQL 支持、JSON 处置惩罚和可扩展性。Timescale Cloud 持续监控您的服务并防止常见的 PostgreSQL 内存不敷瓦解。
  

全部 Timescale Cloud 服务都包罗您盼望用于生产和开辟环境的工具：实时迁移、 主动备份和 PITR、高可用性、读取副本、数据分叉、连接池、分层存储、 基于利用环境的存储、安全的控制台内SQL 编辑、服务指标 和见解、简化的维护等等。  

1、创建数据库test

1. # create database "test";

复制代码

2、把数据库转为时序库 

1. # 在数据库会话中执行
2. CREATE EXTENSION timescaledb;

复制代码

3、创建超表

利用的财务数据集，创建一个名为的超表 stocks_real_time，此中包罗生意业务量排名前 100 位的代码的逐秒股票生意业务数据。

创建一个常规 PostgreSQL 表来存储实时股票生意业务数据CREATE TABLE：

  

3. CREATE TABLE stocks_real_time (
5.     time TIMESTAMPTZ NOT NULL,
7.     symbol TEXT NOT NULL,
9.     price DOUBLE PRECISION NULL,
11.     day_volume INT NULL
13. );

复制代码

4、超表数据保存策略 6个月前数据主动删除

1. SELECT add_retention_policy('stocks_real_time ', INTERVAL '6 months');

复制代码

  5、 验证查询超表空间

1. SELECT *
2. FROM timescaledb_information.dimensions
3. WHERE hypertable_name = 'stocks_real_time';

复制代码

1. [/code]  [size=5]6、创建物化视图步骤[/size]
3.      [code]-- 聚合查询语句
4. select
5.     day_volume,
6.     max(time) last_time,
7.     time_bucket(interval '1 day',data_time) as bucket
8. from stocks_real_time td
9. group by time ,bucket
10. order by bucket desc ;
13. -- 创建物化视图
14. CREATE MATERIALIZED view stocks_real_day_calculation
15.             WITH (timescaledb.continuous) AS
16. select
17.     day_volume,
18.     max(time) last_time,
19.     time_bucket(interval '1 day',data_time) as bucket
20. from stocks_real_time td
21. group by time ,bucket
22. order by bucket desc ;
26. -- 创建刷新策略
27. SELECT add_continuous_aggregate_policy(
28.                'stocks_real_day_calculation',          -- 物化视图的名称
29.                start_offset =>  INTERVAL '3 day',  -- 开始偏移量，设置为3天
30.                end_offset => INTERVAL '1 day',  -- 结束偏移量，设置为1天
31.                schedule_interval => INTERVAL '1 hour'  -- 刷新间隔，每1小时刷新一次
32.        );
34. -- 聚合数据保留策略 3年前聚合数据删除
35. SELECT add_retention_policy('stocks_real_day_calculation', INTERVAL '3 years');
37. -- 查询视图数据
38. select * from stocks_real_day_calculation;
40. --删除物化视图
41. drop  MATERIALIZED VIEW stocks_real_day_calculation

复制代码

  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。