作者：罗伯特·伯尼尔 发表于2024 年 3 月 12 日
原文链接：
https://www.percona.com/blog/managing-time-series-data-using-timescaledb-powered-postgresql/
PostgreSQL 扩展很棒！只需添加一个扩展，即可将原本普通的通用数据库管理系统转变为能够以高度优化的方式处理数据需求的系统。一些扩展（例如pg_repack ）已经简化和增强了现有功能，而其他扩展（例如PostGIS和pgvector）则添加了全新的功能。
我想向您介绍 PostgreSQL 扩展TimescaleDB。顾名思义，TimescaleDB 是针对时间记录的数据。在数据库系统中，时间序列数据通常是一维或多维的相对较小的数据点。一个很好的例子是天气数据的收集，即时间与气压、紫外线指数、风速、温度、湿度等。
时间序列数据处理可能会给任何数据库系统带来负担：
l 数据插入正在以惊人的速度涌入。
l 数据保留要求可能达到数百TB，甚至PB。
l 通常需要存档数据和实时数据的数据分析和报告生成功能。
虽然 PostgreSQL 非常有能力摄取、管理和分析时间序列数据，但还有其他解决方案（其中许多是专有的）可以更快地执行数据摄取并生成可操作的见解。
这让我们回到了 TimescaleDB。
创建 TimescaleDB 的目的是通过简化和自动化经验丰富的 DBA 用来优化和维护数据库生命周期健康状况的许多步骤，来提高将时间序列数据摄取到 PostgreSQL 的速率。
安装并启用 TimescaleDB 扩展
当然，欣赏这些可能性的最佳方法是安装扩展并尝试一下！
第一步：创建 PostgreSQL 文件存储库配置
因为我的Linux机器是Ubuntu，所以使用以下命令来安装扩展：
echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
第二步：获取扩展名
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c) -s) main" | sudo tee etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey
| sudo apt-key add -
apt update && apt upgrade -y
由于我使用的是 Ubuntu“Jammy”，因此我有以下可用软件包：
# this command shows returns the package list
apt search postgresql | grep -A 3 -E '^timescaledb-2-postgresql'
# A nice collection of packages covering all currently supported versions of postgres
timescaledb-2-postgresql-12/jammy 2.11.2~ubuntu22.04 amd64
An open-source time-series database based on PostgreSQL, as an extension.
timescaledb-2-postgresql-13/jammy 2.13.1~ubuntu22.04 amd64
An open-source time-series database based on PostgreSQL, as an extension.
timescaledb-2-postgresql-14/jammy 2.13.1~ubuntu22.04 amd64
An open-source time-series database based on PostgreSQL, as an extension.
timescaledb-2-postgresql-15/jammy 2.13.1~ubuntu22.04 amd64
An open-source time-series database based on PostgreSQL, as an extension.
timescaledb-2-postgresql-16/jammy 2.13.1~ubuntu22.04 amd64
An open-source time-series database based on PostgreSQL, as an extension.
第三步：安装 TimescaleDB 软件包
apt install timescaledb-2-postgresql-16可用的软件包括：
timescaledb-2-loader-postgresql-16 # The loader for TimescaleDB
timescaledb-2-postgresql-16 # The timescale extension for postgres 16
timescaledb-toolkit-postgresql-16 # A library of analytical hyperfunctions,
# time-series pipelining, and other SQL utilities
timescaledb-tools # A suite of tools that can be used with
# TimescaleDB
# PGBIN/timescaledb-parallel-copy
# PGBIN/timescaledb-tune
第四步：调整数据集群
CLI 实用程序timescaledb-tune非常棒，因为它可用于生成一组优化的 Postgres 运行时参数。然而，它假设 Postgres 是主机上运行的唯一主要进程，并且不竞争 RAM 或 CPU。
建议的测试调用：
/usr/bin/timescaledb-tune --help 2>& 1>/dev/stdout | less
这是将结果保存在 postgresql.auto.conf 中的调用：
/usr/bin/timescaledb-tune
--conf-path var/lib/postgresql/16/main/postgresql.auto.conf
-pg-version 16
-quiet
-yes
shared_preload_libraries = 'timescaledb'
shared_buffers = 5967MB
effective_cache_size = 17901MB
maintenance_work_mem = 2047MB
work_mem = 5091kB
timescaledb.max_background_workers = 16
max_worker_processes = 31
max_parallel_workers_per_gather = 6
max_parallel_workers = 12
wal_buffers = 16MB
min_wal_size = 512MB
max_wal_size = 1GB
default_statistics_target = 100
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 100
max_locks_per_transaction = 256
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 256
timescaledb.last_tuned = '2024-03-01T20:49:53Z'
timescaledb.last_tuned_version = '0.15.0'
现在可以重新启动该服务：
systemctl restart postgresql@16-main
$ netstat -tlnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN 6786/systemd-resolv
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 6579/sshd: usr/sbi
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 11447/postgres
tcp6 0 0 :::22 :::* LISTEN 6579/sshd: usr/sbi
第五步：创建数据库和扩展
非常标准的东西，创建您的数据库和扩展：
createdb db01
psql db01<<_eof_
create extension timescaledb;
-- optional; turn off telemetry
alter system set timescaledb.telemetry_level=off;
_eof_
创建后，TimescaleDB 扩展会在数据库中安装许多函数、视图和表：
dn
List of schemas
Name | Owner
--------------------------+-------------------
_timescaledb_cache | postgres
_timescaledb_catalog | postgres
_timescaledb_config | postgres
_timescaledb_functions | postgres
_timescaledb_internal | postgres
public | pg_database_owner
timescaledb_experimental | postgres
timescaledb_information | postgres
使用时间刻度
现在一切都已准备就绪，我们准备好探索了。
场景一
创建表
让我们创建两组表：一组是 PostgreSQL 中的标准堆表，而第二组当然是 TimescaleDB 的hypertable。
创建 TimescaleDB 超表会使用表中找到的任何日期/时间类型列自动定义分区规则。在本例中，它的列t_stamp的数据类型为timestamptz（带时区的时间戳）作为键。
-- create a standard postgres table
create table t_timescale(
id uuid
,c2 int default random()*1E6
,c3 int default random()*1E6
,c4 int default random()*1E6
,c5 int default random()*1E6
,c6 int default random()*1E6
,c7 int default random()*1E6
,t_stamp timestamptz not null default clock_timestamp()
);
这是创建超级表所需的命令：
select create_hypertable('t_timescale', 't_stamp');
普通堆表是这样创建的：
create table t_standard (like t_timescale);
增加/减少块大小
TimescaleDB 将其表分区为块。尽管默认大小限制为 7 天，但它可以更改为所需的任何时间范围。
出于本博客的目的，并且由于我使用的是小型数据集，因此表t_timescale的块间隔设置为 10 分钟：
select set_chunk_time_interval('t_timescale', interval '10 minutes');
块大小最佳实践：
· 切勿使块大于 PostgreSQL 共享缓冲区和可用 RAM。
· 如有疑问，请始终从已知小于共享缓冲区的块开始。原因是，为了适应可用 RAM，将较小的块变大比将较大的块变小更容易管理。
填充表
在填充表之前，会调用psql 元命令计时：
timing
标准表在创建表时没有任何索引，从而加速了数据填充，而超级表在创建时添加了索引。请注意：这些数字会根据所使用的硬件而有很大差异，即我的硬件相对较低。
-- HYPER --
-- 44:14 minutes: 54GB
insert into t_timescale(id) select gen_random_uuid() from generate_series (1, 5E8);
-- determining table size
select * from pg_size_pretty(hypertable_size('t_timescale'));
-- HEAP --
-- 08:58 minutes : 36 GB
insert into t_standard(id) select * from t_timescale
-- 04:18 minutes: 10 GB
create index on t_standard(t_stamp);
仔细查看 t_timescale 架构定义，我们会发现已创建了五个分区子表。回想一下数据填充之前 10 分钟的块间隔设置，随着时间间隔增加 10 分钟，将创建新分区：
Table Structure: t_timescale
Column | Type |
---------+--------------------------+
id | uuid |
c2 | integer |
c3 | integer |
c4 | integer |
c5 | integer |
c6 | integer |
c7 | integer |
t_stamp | timestamp with time zone |
Indexes:
"t_timescale_t_stamp_idx" btree (t_stamp DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON t_timescale FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
Child tables: _timescaledb_internal._hyper_2_2_chunk,
_timescaledb_internal._hyper_2_2_chunk,
_timescaledb_internal._hyper_2_3_chunk,
_timescaledb_internal._hyper_2_5_chunk,
_timescaledb_internal._hyper_2_6_chunk
Access method: heap
检查其中一个块确认分区设置为 10 分钟：
db01=# d _hyper_2_6_chunk
Table "_timescaledb_internal._hyper_2_6_chunk"
Column | Type | Collation | Nullable | Default
---------+--------------------------+-----------+----------+---------------------------------------------------
id | uuid | | |
c2 | integer | | | (random() * '1000000'::numeric::double precision)
c3 | integer | | | (random() * '1000000'::numeric::double precision)
c4 | integer | | | (random() * '1000000'::numeric::double precision)
c5 | integer | | | (random() * '1000000'::numeric::double precision)
c6 | integer | | | (random() * '1000000'::numeric::double precision)
c7 | integer | | | (random() * '1000000'::numeric::double precision)
t_stamp | timestamp with time zone | | not null | clock_timestamp()
Indexes:
"_hyper_2_6_chunk_t_timescale_t_stamp_idx" btree (t_stamp DESC)
Check constraints:
"constraint_6" CHECK (t_stamp >= '2024-01-30 19:20:00+00'::timestamp with time zone
AND t_stamp < '2024-01-30 19:30:00+00'::timestamp with time zone)
Inherits: public.t_timescale
如下结果所示，大小不一致是因为记录数在指定的 10 分钟间隔内变化：
List of Tables
Schema | Name | Size
-----------------------+------------------------+--------------
public | t_standard | 36000 MB | TOTAL 36 GB
-----------------------+------------------------+--------------
_timescaledb_internal | _hyper_2_2_chunk | 4292 MB |
_timescaledb_internal | _hyper_2_3_chunk | 8540 MB |
_timescaledb_internal | _hyper_2_4_chunk | 8712 MB |
_timescaledb_internal | _hyper_2_5_chunk | 8204 MB |
_timescaledb_internal | _hyper_2_6_chunk | 6769 MB | TOTAL 36.5 GB
您会注意到 timescaleDB 索引明显大于为表 t_standard 创建的 Btree 索引。显然，它们包含更多信息/数据：
List of Indexes
Schema | Name | Type | Size
-----------------------+--------------------------------------------+-------+------------------------
public | _t_standard_t_stamp_idx | index | 10 GB TOTAL 10 GB
-----------------------------------------------------------------------------------------------------
_timescaledb_internal | _hyper_2_2_chunk_t_timescale_t_stamp_idx | _hyper_2_2_chunk | 2262 MB
_timescaledb_internal | _hyper_2_3_chunk_t_timescale_t_stamp_idx | _hyper_2_3_chunk | 4500 MB
_timescaledb_internal | _hyper_2_4_chunk_t_timescale_t_stamp_idx | _hyper_2_4_chunk | 4591 MB
_timescaledb_internal | _hyper_2_5_chunk_t_timescale_t_stamp_idx | _hyper_2_5_chunk | 4323 MB
_timescaledb_internal | _hyper_2_6_chunk_t_timescale_t_stamp_idx | _hyper_2_6_chunk | 3567 MB TOTAL 19.2 GB
管理超表块
下面仅关注简单的块管理。
块通用函数调用
这些 TimescaleDB 函数用于管理块：
Schema | Name
--------+-------------------------
public | chunks_detailed_size
public | drop_chunks
public | move_chunk
public | reorder_chunk
public | set_adaptive_chunking
public | set_chunk_time_interval
public | show_chunks
块压缩函数调用
这些 TimescaleDB 函数用于管理表的压缩：
Schema | Name
--------+------------------------------
public | add_compression_policy
public | chunk_compression_stats
public | compress_chunk
public | decompress_chunk
public | hypertable_compression_stats
public | recompress_chunk
public | remove_compression_policy
时间刻度块运行时参数
与 Postgres 运行时参数的一般集合类似，其中一些时间尺度参数跨数据集群运行，而其他参数可以针对特定关系甚至会话进行变更微调。
select name, setting
from pg_settings
where name ~ '^timescale'
and name ~ 'compress'
union
select name, setting
from pg_settings
where name ~ '^timescale'
and name ~ 'chunk';
name | setting
-------------------------------------------------+---------
timescaledb.enable_bulk_decompression | on
timescaledb.enable_chunk_append | on
timescaledb.enable_chunkwise_aggregation | on
timescaledb.enable_compression_indexscan | on
timescaledb.enable_decompression_logrep_markers | off
timescaledb.enable_decompression_sorted_merge | on
timescaledb.enable_dml_decompression | on
timescaledb.enable_hypertable_compression | on
timescaledb.enable_parallel_chunk_append | on
timescaledb.enable_transparent_decompression | on
timescaledb.max_cached_chunks_per_hypertable | 1024
timescaledb.max_open_chunks_per_insert | 1024
场景二
以上述函数和运行时参数的一小部分为例，下一个场景演示如何压缩从单个块到基于在正常生产条件下创建的块年龄为表设置综合策略的所有内容。
压缩一个块
表压缩是最适合归类为杀手级特性的特性之一。特别酷的是，不仅可以减少空间消耗，还可以在超级表上查询和执行DML操作。
-- Get Current State of CHUNKS
db01=# dt+ _timescaledb_internal._hyper*chunk
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size
-----------------------+------------------+-------+----------+-------------+---------------+---------+
_timescaledb_internal | _hyper_2_2_chunk | table | postgres | permanent | heap | 4292 MB |
_timescaledb_internal | _hyper_2_3_chunk | table | postgres | permanent | heap | 8540 MB |
_timescaledb_internal | _hyper_2_4_chunk | table | postgres | permanent | heap | 8712 MB |
_timescaledb_internal | _hyper_2_5_chunk | table | postgres | permanent | heap | 8204 MB |
_timescaledb_internal | _hyper_2_6_chunk | table | postgres | permanent | heap | 6769 MB |
-- ENABLE COMPRESSION
alter table t_timescale
set (
timescaledb.compress,
timescaledb.compress_orderby = 't_stamp asc'
);
-- Time: 163081.593 ms (02:43.082)
select * from compress_chunk('_timescaledb_internal._hyper_2_4_chunk',true);
请注意块的大小是如何归零的。为了获得现在压缩的表的真实大小，您需要使用函数调用，请参阅下面的示例调用。
db01=# dt+ _timescaledb_internal._hyper*chunk
Schema | Name | Type | Owner | Persistence | Access method | Size
-----------------------+------------------+-------+----------+-------------+---------------+----------
_timescaledb_internal | _hyper_2_2_chunk | table | postgres | permanent | heap | 4292 MB |
_timescaledb_internal | _hyper_2_3_chunk | table | postgres | permanent | heap | 8540 MB |
_timescaledb_internal | _hyper_2_4_chunk | table | postgres | permanent | heap | 0 bytes |
_timescaledb_internal | _hyper_2_5_chunk | table | postgres | permanent | heap | 8204 MB |
_timescaledb_internal | _hyper_2_6_chunk | table | postgres | permanent | heap | 6769 MB |
该函数提供了描述现在压缩块的完整指标集：
-- Get Current State of Compression
select * from hypertable_compression_stats('t_timescale');
db01=# select * from hypertable_compression_stats('t_timescale');
-[ RECORD 1 ]------------------+------------
total_chunks | 5
number_compressed_chunks | 1
before_compression_table_bytes | 9135104000
before_compression_index_bytes | 4813725696
before_compression_toast_bytes | 0
before_compression_total_bytes | 13948829696
after_compression_table_bytes | 24150016
after_compression_index_bytes | 0
after_compression_toast_bytes | 4946141184
after_compression_total_bytes | 4970291200
node_name
解压一个块
解压缩一个块就像压缩一个块一样容易。典型的原因是在必须执行的各种 DML 操作时最大限度地提高性能。
-- Decompressing Chunk Size
-- Time: 267639.222 ms (04:27.639)
select * from decompress_chunk('_timescaledb_internal._hyper_2_4_chunk');
设置块压缩策略
如前所述，设置 CHUNK 间隔（时间）是一个相当简单的过程。设置压缩策略也是如此。人们只需选择超表以及它创建后多长时间才最终压缩。具有基于时间的压缩参数认识到需要在最短的时间内处理最新的数据：
-- RECALL: ENABLE COMPRESSION
alter table t_timescale
set (
timescaledb.compress,
timescaledb.compress_orderby = 't_stamp asc'
);
此压缩策略会压缩超过 30 分钟的块：
select add_compression_policy('t_stamp', compress_after => INTERVAL '30m');
add_compression_policy
------------------------
1000
测试新设置的压缩策略是通过插入新记录来完成的：
-- Populate new records
--为 t_timescale 表中的 id 字段生成 2 亿条记录，并且每条记录的 id 值都是通过 gen_random_uuid() 函数生成的一个随机 UUID。
insert into t_timescale(id) select gen_random_uuid() from generate_series (1, 2E8);
INSERT 0 200000000
Time: 1100742.301 ms (18:20.742)
select * from pg_size_pretty(hypertable_size('t_timescale'));
pg_size_pretty
----------------
41 GB
注意新旧分区大小的差异：
dt+ _timescaledb_internal._hyper*chunk
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size
----------------------+-------------------+-------+----------+-------------+---------------+--------
_timescaledb_internal | _hyper_2_12_chunk | table | postgres | permanent | heap | 3231 MB
_timescaledb_internal | _hyper_2_14_chunk | table | postgres | permanent | heap | 8265 MB
_timescaledb_internal | _hyper_2_15_chunk | table | postgres | permanent | heap | 3111 MB
_timescaledb_internal | _hyper_2_2_chunk | table | postgres | permanent | heap | 0 bytes
_timescaledb_internal | _hyper_2_3_chunk | table | postgres | permanent | heap | 0 bytes
_timescaledb_internal | _hyper_2_4_chunk | table | postgres | permanent | heap | 0 bytes
_timescaledb_internal | _hyper_2_5_chunk | table | postgres | permanent | heap | 0 bytes
_timescaledb_internal | _hyper_2_6_chunk | table | postgres | permanent | heap | 0 bytes
db01=# select * from hypertable_compression_stats('t_timescale');
-[ RECORD 1 ]------------------+------------
total_chunks | 8
number_compressed_chunks | 5
before_compression_table_bytes | 38291226624
before_compression_index_bytes | 20177428480
before_compression_toast_bytes | 0
before_compression_total_bytes | 58468655104
after_compression_table_bytes | 101351424
after_compression_index_bytes | 0
after_compression_toast_bytes | 20732502016
after_compression_total_bytes | 20833853440
node_name
禁用压缩策略是这样完成的：
SELECT remove_compression_policy('t_timescale');
警告
与所有技术一样，尤其是新技术，总是存在某种形式的限制。TimescaleDB 也不例外：
· 不支持引用超表的外键约束。
· 用于分区的时间维度（列）不能有 NULL 值。
· UNIQUE 索引必须包含作为分区维度的所有列。
· 不支持在分区（块）之间移动值的 UPDATE 语句。
· 不再支持水平扩展和多节点支持。相反，建议使用分布式网络文件系统（例如 Ceph）来进行扩展。
参考
https://docs.timescale.com/
https://docs.timescale.com/api/latest/
https://docs.timescale.com/api/latest/compression/alter_table_compression/
https://docs.timescale.com/use-timescale/latest/compression/about-compression/
文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
