timescaledb：时间序列数据

在使用 Postgres 17 的项目中，timescaledb 扩展已被弃用。在使用 Postgres 15 的项目中仍然支持，但在将这些项目升级到 Postgres 17 之前需要先移除它。更多信息请参见升级到 Postgres 17 说明。

🌐 The timescaledb extension is deprecated in projects using Postgres 17. It continues to be supported in projects using Postgres 15, but will need to dropped before those projects are upgraded to Postgres 17. See the Upgrading to Postgres 17 notes for more information.

如果你正在使用超表，请按照迁移指南将其转换为由 pg_partman 管理的原生分区。

🌐 If you are using hypertables, follow the migration guide to convert to native partitioning managed by pg_partman.

如需额外帮助，可以通过在 Supabase 控制面板中创建支持工单来联系成功团队。

🌐 For additional support, contact our Success team by creating a support ticket in the Supabase Dashboard.

timescaledb 是一个 Postgres 扩展，旨在更好地处理时间序列数据。它在标准 Postgres 数据库的基础上，提供了一个可扩展、高性能的时间序列数据存储和查询解决方案。

timescaledb 使用一个时间序列感知的存储模型和索引技术来提升 Postgres 在处理时间序列数据时的性能。这个扩展会根据时间间隔把数据分块，从而实现高效的扩展，尤其是对于大数据集。数据随后会被压缩，针对写入密集型工作负载进行优化，并通过分区进行并行处理。timescaledb 还包括一套用于时间序列数据的函数、操作符和索引，可以减少查询时间，并让数据操作更简单。

Supabase 项目附带 TimescaleDB Apache 2 版。社区版才有的功能在这里无法使用。

🌐 Supabase projects come with TimescaleDB Apache 2 Edition. Functionality only available under the Community Edition is not available.

启用扩展 #

🌐 Enable the extension

在仪表板中转到数据库页面。
点击侧边栏的扩展。
搜索 timescaledb 并启用这个扩展。

虽然 SQL 代码是 create extension，但这相当于“启用扩展”。要禁用扩展，你可以调用 drop extension。

🌐 Even though the SQL code is create extension, this is the equivalent of "enabling the extension". To disable an extension you can call drop extension.

在一个独立的 schema（比如 extensions）中创建扩展是个好习惯，这样可以保持你的 public schema 干净。

🌐 It's good practice to create the extension within a separate schema (like extensions) to keep your public schema clean.

用法 #

🌐 Usage

为了演示 timescaledb 的工作原理，来看看一个例子，我们有一个存储不同传感器温度数据的表。创建一个名为“temperatures”的表，并存储两个传感器的数据。

🌐 To demonstrate how timescaledb works, consider an example where we have a table that stores temperature data from different sensors. Create a table named "temperatures" and store data for two sensors.

首先我们创建一个超级表，它是一个根据时间间隔分块的虚拟表。超级表作为实际表的代理，让查询和管理时间序列数据变得更简单。

🌐 First we create a hypertable, which is a virtual table that is partitioned into chunks based on time intervals. The hypertable acts as a proxy for the actual table and makes it easy to query and manage time-series data.

1
create table temperatures (
2
  time timestamptz not null,
3
  sensor_id int not null,
4
  temperature double precision not null
5
);
6
7
select create_hypertable('temperatures', 'time');

接下来，我们可以填一些值

🌐 Next, we can populate some values

1
insert into temperatures (time, sensor_id, temperature)
2
values
3
    ('2023-02-14 09:00:00', 1, 23.5),
4
    ('2023-02-14 09:00:00', 2, 21.2),
5
    ('2023-02-14 09:05:00', 1, 24.5),
6
    ('2023-02-14 09:05:00', 2, 22.3),
7
    ('2023-02-14 09:10:00', 1, 25.1),
8
    ('2023-02-14 09:10:00', 2, 23.9),
9
    ('2023-02-14 09:15:00', 1, 24.9),
10
    ('2023-02-14 09:15:00', 2, 22.7),
11
    ('2023-02-14 09:20:00', 1, 24.7),
12
    ('2023-02-14 09:20:00', 2, 23.5);

最后，我们可以使用 timescaledb 的 time_bucket 函数来查询表，将时间序列分成指定大小的区间（在本例中为 1 小时），并在每个组内平均 temperature 的读数。

🌐 And finally we can query the table using timescaledb's time_bucket function to divide the time-series into intervals of the specified size (in this case, 1 hour) averaging the temperature reading within each group.

1
select
2
    time_bucket('1 hour', time) AS hour,
3
    avg(temperature) AS average_temperature
4
from
5
    temperatures
6
where
7
    sensor_id = 1
8
    and time > NOW() - interval '1 hour'
9
group by
10
    hour;

资源 #

🌐 Resources

官方 timescaledb 文档