将 Supabase 与 Timescale Cloud 集成

优化超表中的时序数据

时序数据表示系统、过程或行为随时间的变化。超表是 PostgreSQL 表，通过按时间自动分区数据来帮助您提高插入和查询性能。

1. 连接到您的 Timescale Cloud 服务并创建一个指向 Supabase 数据库的表

   CREATE TABLE signs (
       time timestamptz NOT NULL DEFAULT now(), 
       origin_time timestamptz NOT NULL, 
       name TEXT
   ) WITH (
     tsdb.hypertable,
     tsdb.partition_column='time'
   );

   复制

   如果您自托管 TimescaleDB v2.19.3 及更早版本，请创建一个 PostgreSQL 关系表" width="16" height="16" class="inline m-0 ml-1">，然后使用 create_hypertable 进行转换。然后通过调用 ALTER TABLE 启用 hypercore。

优化冷数据以进行分析

Hypercore 是 TimescaleDB 的混合行-列存储引擎，专门为实时分析而设计，并由时序数据驱动。Hypercore 的优势在于其能够无缝地在面向行和面向列的存储之间切换。这种灵活性使 Timescale Cloud 能够同时发挥两者的优势，解决实时分析中的关键挑战。

ALTER TABLE signs SET (
  timescaledb.enable_columnstore = true, 
  timescaledb.segmentby = 'name');

复制

创建优化的分析查询

连续聚合旨在加快大型数据集上的查询速度。Timescale Cloud 中的连续聚合使用 PostgreSQL 物化视图 在后台持续、增量地刷新查询，因此当您运行查询时，只需要计算已更改的数据，而不是整个数据集。

1. 创建指向 Supabase 数据库的连续聚合。

   CREATE MATERIALIZED VIEW IF NOT EXISTS signs_per_minute
   WITH (timescaledb.continuous)
   AS
   SELECT time_bucket('1 minute', time) as ts,
    name, 
    count(*) as total 
   FROM signs 
   GROUP BY 1, 2 
   WITH NO DATA;

   复制

2. 设置延迟统计数据，比较 origin_time 和 time。

   CREATE MATERIALIZED VIEW IF NOT EXISTS _signs_per_minute_delay
   WITH (timescaledb.continuous)
   AS
   SELECT time_bucket('1 minute', time) as ts, 
     stats_agg(extract(epoch from origin_time - time)::float8) as delay_agg, 
     candlestick_agg(time, extract(epoch from origin_time - time)::float8, 1) as delay_candlestick 
   FROM signs GROUP BY 1 
   WITH NO DATA;

   复制

3. 设置一个视图以接收来自 Supabase 的数据。

   CREATE VIEW signs_per_minute_delay
   AS
     SELECT ts, 
     average(delay_agg) as avg_delay, 
     stddev(delay_agg) as stddev_delay, 
     open(delay_candlestick) as open, 
     high(delay_candlestick) as high, 
     low(delay_candlestick) as low, 
     close(delay_candlestick) as close 
   FROM _signs_per_minute_delay

   复制

为您的分析查询添加刷新策略

您可以使用 start_offset 和 end_offset 来定义连续聚合将覆盖的时间范围。假设数据是即时插入的，将 start_offset 设置为 5 minutes，将 end_offset 设置为 1 minute。这意味着连续聚合每分钟刷新一次，并且刷新覆盖最近 5 分钟的数据。您将 schedule_interval 设置为 INTERVAL '1 minute'，这样连续聚合就会每分钟在您的 Timescale Cloud 服务上刷新。数据从 Supabase 访问，连续聚合则在另一侧每分钟刷新一次。

SELECT add_continuous_aggregate_policy('signs_per_minute',
 start_offset => INTERVAL '5 minutes',
 end_offset => INTERVAL '1 minute', 
 schedule_interval => INTERVAL '1 minute');

复制

对延迟插入的数据执行相同操作

SELECT add_continuous_aggregate_policy('_signs_per_minute_delay',
 start_offset => INTERVAL '5 minutes', 
 end_offset => INTERVAL '1 minute', 
 schedule_interval => INTERVAL '1 minute');

复制

在 Supabase 中连接外部服务器到您的 Timescale Cloud 服务

1. 使用 Supabase 控制面板或 psql 连接到您的 Supabase 项目。

2. 启用 postgres_fdw 扩展。

   CREATE EXTENSION postgres_fdw;

   复制

3. 创建一个指向您的 Timescale Cloud 服务的外部服务器。

   用您的连接详情更新以下命令，然后在 Supabase 数据库中运行它

   CREATE SERVER timescale
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (
       host '<value of host>',
       port '<value of port>',
       dbname '<value of dbname>',
       sslmode 'require',
       extensions 'timescaledb'
   );

   复制

为外部服务器创建用户映射

用您的连接详情更新以下命令，然后在 Supabase 数据库中运行它

CREATE USER MAPPING FOR CURRENT_USER 
SERVER timescale 
OPTIONS (
   user '<value of user>', 
   password '<value of password>'
);

复制

创建指向 Timescale Cloud 服务中某个表的外部表。

此查询引入了以下列

• time：默认值为 now()。这是因为 Timescale Cloud 使用 time 列来优化列式存储中的数据。
• origin_time：存储数据的原始时间戳。

通过使用这两列，您可以了解 Supabase (origin_time) 和数据插入到 Timescale Cloud 服务 (time) 之间的时间延迟。

CREATE FOREIGN TABLE signs (
  TIME timestamptz NOT NULL DEFAULT now(),
  origin_time timestamptz NOT NULL,
  NAME TEXT) 
SERVER timescale OPTIONS (
  schema_name 'public',
  table_name 'signs'
);

复制

在 Supabase 中创建外部表

1. 创建与 Timescale Cloud 服务中的 signs_per_minute 视图匹配的外部表。它代表了数据的顶层视图。

   CREATE FOREIGN TABLE signs_per_minute (
    ts timestamptz, 
    name text, 
    total int
   ) 
   SERVER timescale OPTIONS (schema_name 'public', table_name 'signs_per_minute');

   复制

2. 创建与 Timescale Cloud 服务中的 signs_per_minute_delay 视图匹配的外部表。

   CREATE FOREIGN TABLE signs_per_minute_delay (
      ts timestamptz, 
      avg_delay float8, 
      stddev_delay float8, 
      open float8, 
      high float8, 
      low float8, 
      close float8
   ) SERVER timescale OPTIONS (schema_name 'public', table_name 'signs_per_minute_delay');

   复制

将数据插入到您的 Supabase 数据库

连接到 Supabase 并运行以下查询

INSERT INTO signs (origin_time, name) VALUES (now(), 'test')

复制

检查您的 Timescale Cloud 服务中的数据

连接到您的 Timescale Cloud 服务并运行以下查询

SELECT * from signs;

复制

您会看到类似