为 TimescaleDB 选择最佳表配置

任务描述

对于市场平台来说，一个常见的任务是在近乎实时的情况下，计算某些产品在 1 天/7 天/30 天内的交易量。TimescaleDB 或其他时间序列数据库非常适合这种任务。由于我当前的项目已经在使用 PostgreSQL，所以我们来测试一下 TimescaleDB。我将使用我当前项目（NFT 市场平台）中的一个非常简化的模型。我们针对多个区块链拥有数千个集合，并在表中记录每一次列表/出价或销售交易。交易的总数非常庞大：数十亿行。我们可以通过将表按时间划分成桶并使用这些桶进行计算来加快计算速度。

准备工作

这是表的模式和一些索引：

create table public.sale_changes
(
    time          timestampz not null,
    id            varchar(1024),
    collection_id varchar(512),
    blockchain    varchar(512),
    floor         numeric(78, 10),
    sales         bigint,
    volume        numeric(78, 10)
);
select create_hypertable('sale_changes', 'time', chunk_time_interval = > interval '1 day');
create unique index ON sale_changes(blockchain, id, time);
create index ON sale_changes(collection_id, time);
create * from add_dimension('sale_changes', by_hash('blockchain', 16));

我们创建了一些附加索引：

• CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time); -- 这是为了数据的一致性
• CREATE INDEX ON sale_changes(collection_id, time); -- 这是为了加快集合的聚合速度

此外，我们还为提升区块链特定查询的性能添加了额外的维度。“16”是桶的数量，目前对于测试来说已经足够。

我备份了当前年的数据并在本地 TimescaleDB 中加载：

• POLYGON: 4917155 行
• ETHEREUM: 3032708 行
• APTOS: 129760 行
• CELO: 13692 行
• ZKSYNC: 8832 行

所以我们本地有 8102147 (~800万) 行。

我们将使用以下两个查询进行性能测试：

-- all query
select csc.*,
       cs.floor,
       cs.owners from (select collection_id,
       SUM(volume),
       SUM(sales)  AS total_sales,
       CASE
           WHEN first(floor, time) = 0 THEN 0
           ELSE ROUND(((last(floor, time) - first(floor, time)) / first(floor, time)) * 100, 2)
           END         AS floor_change
from sale_changes
where time >= NOW() - INTERVAL '$period'
group by collection_id
order by floor_change DESC
limit ?

-- Specific chain query:
select csc.*,
       cs.floor,
       cs.owners
from (select collection_id,
             SUM(volume),
             SUM(sales) AS total_sales,
             CASE
                 WHEN first(floor, time) = 0 THEN 0
          ELSE ROUND(((last (floor, time) - first (floor, time)) / first (floor, time)) * 100, 2)
          END AS floor_change
                           from $table
WHERE blockchain = ? and time >= NOW() - INTERVAL '$period'
group by collection_id
order by floor_change DESC
limit ?

测试

我们将通过每个查询执行 10 次，并收集有关执行时间的指标来测试性能。 我的本地环境：

• MacBook M1，具有32 GB RAM
• TimescaleDB 2.17.2-pg17，在 Docker 中运行
• 为 PostgreSQL 增加了内存设置：shared_buffers=8GB, work_mem=8GB

第一尝试

以下是使用第一个查询的结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	71	105	77.3	74.5	9.7
1 天	100	73	81	76.0	75.0	2.4
7 天	100	161	192	168.1	166.0	8.5
30 天	100	515	630	536.3	526.5	32.2

以下是使用第二个查询的小区块链 CELO 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	13	101	23.7	15.0	25.8
1 天	100	13	99	23.4	14.5	25.2
7 天	100	14	99	24.5	15.5	24.9
30 天	100	16	104	26.1	17.5	26.0

以下是使用第二个查询的大区块链 ETHEREUM 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	27	114	37.8	29.5	25.5
1 天	100	27	112	37.0	28.0	25.0
7 天	100	66	172	79.6	68.5	30.9
30 天	100	206	382	251.6	220.0	61.9

可以看到 30 秒间隔的查询没有其他查询快，除了 CELO 区块链外。这是意料之中的，因为数据库需要处理更多行。我们尝试不同的配置，看看能否改善性能。

第二尝试

让我们启用压缩。我们将使用 collection_id 作为压缩段，因为将这些数字保存在一起是有意义的。

ALTER TABLE sale_changes SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'collection_id'
    );
SELECT compress_chunk(c) from show_chunks('sale_changes') c;

这是使用第一个查询的结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	249	1091	355.9	261.5	247.0
1 天	100	237	286	255.4	251.0	15.4
7 天	100	247	278	259.4	260.5	8.9
30 天	100	349	412	370.2	358.5	21.0

这是使用第二个查询的小区块链 CELO 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	19	131	33.1	21.0	32.8
1 天	100	18	128	32.9	21.5	32.1
7 天	100	21	129	33.3	22.0	31.9
30 天	100	25	151	42.1	25.0	36.9

这是使用第二个查询的大区块链 ETHEREUM 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	90	203	115.7	99.0	34.6
1 天	100	94	208	110.5	99.5	32.8
7 天	100	95	219	111.1	98.5	36.3
30 天	100	149	272	170.7	159.5	34.2

压缩表给我们带来了更糟糕的结果... 即使对于时间较短的查询也是如此，不过对于所有区块链的30天查询结果有所改善。我猜保持数字按 collection_id 整合在一起发挥了作用。

第三尝试

让我们尝试增大我们的超表的块大小，因为有充足的 RAM。按照官方推荐，块占内存的 25% 是个好选择。我目前为 Docker 分配了 24GB RAM。

select create_hypertable('sale_changes', 'time', chunk_time_interval => interval '7 day');
CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time);
CREATE INDEX ON sale_changes(collection_id, time);
SELECT * FROM add_dimension('sale_changes', by_hash('blockchain', 16));

这是使用第一个查询的结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	10	61	17.1	11.0	14.8
1 天	100	10	18	11.6	11.0	2.2
7 天	100	87	119	91.5	88.0	9.3
30 天	100	367	514	390.4	373.5	42.9

这是使用第二个查询的小区块链 CELO 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	2	14	5.0	4.5	3.2
1 天	100	2	12	5.0	4.0	2.7
7 天	100	3	15	6.2	5.0	3.4
30 天	100	6	15	7.6	7.0	2.6

这是使用第二个查询的大区块链 ETHEREUM 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	3	14	5.9	5.0	2.8
1 天	100	5	13	6.0	5.0	2.4
7 天	100	32	47	37.6	34.5	5.4
30 天	100	129	140	133.3	132.0	3.2

现在我们有了更好的结果！我不太喜欢对于30天期间的所有链查询。不过 CELO/ETHEREUM 查询非常快。

第四尝试

让我们尝试为7天块启用压缩。

这是使用第一个查询的结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	47	153	60.3	49.5	31.0
1 天	100	50	62	54.8	54.0	4.4
7 天	100	62	73	67.1	67.0	3.6
30 天	100	139	182	150.4	145.0	12.5

这是使用第二个查询的小区块链 CELO 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	18	39	22.6	20.0	6.1
1 天	100	20	36	25.1	23.0	5.7
7 天	100	6	21	8.0	7.0	4.4
30 天	100	7	21	9.4	8.0	4.0

这是使用第二个查询的大区块链 ETHEREUM 结果：

时间段	限制	最短时间 (ms)	最长时间 (ms)	平均时间 (ms)	中位数时间 (ms)	标准差 (ms)
1 小时	100	21	34	23.8	23.0	3.5
1 天	100	23	59	29.0	24.5	10.5
7 天	100	31	58	38.2	36.5	7.4
30 天	100	59	74	64.5	63.5	4.2

终于，我们的查询速度非常快。最慢的是 30 天全链查询，不过中位时间 <0.2s，所以我认为在数百万行的聚合查询中已经很快了。

图表

结论

让我们使用以下查询查看表大小：

select 
    hypertable_name, 
    pg_size_pretty(hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass))
from timescaledb_information.hypertables;

• 1 天块表：2.4 Gb
• 1 天块表（压缩）：2.6 Gb
• 7 天块表：2.2 Gb
• 7 天块表（压缩）：0.3 Gb

第一张表和最后一张表之间几乎有10倍的差异，这解释了为什么采用压缩的7天块表如此快速。 7 天块加压缩的配置明显是胜利者。