Escolhendo a Melhor Configuração de Tabela para TimescaleDB

Descrição da Tarefa

É comum em marketplaces a necessidade de calcular o volume de alguns produtos em um período de 1 dia/7 dias/30 dias em quase tempo real. TimescaleDB ou qualquer outro banco de dados de séries temporais é perfeitamente adequado para esse tipo de tarefa. Vamos testar o TimescaleDB, pois em meu projeto atual já está sendo utilizado PostgreSQL. Vou usar um modelo muito simplificado do meu projeto atual (marketplace de NFTs). Temos milhares de coleções para vários blockchains e coletamos cada lista/lance ou transação de venda na tabela. O número total de transações é enorme: bilhões de linhas. A ideia de como podemos tornar esse cálculo rápido é dividir nossa tabela em buckets por tempo e calcular os números usando-os.

Preparação

Aqui está o esquema da tabela e alguns índices:

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));

Criamos alguns índices adicionais:

• CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time); -- isso é necessário para a consistência dos dados
• CREATE INDEX ON sale_changes(collection_id, time); -- isso é para uma agregação mais rápida por coleção

Além disso, adicionamos uma dimensão adicional para melhorar o desempenho das consultas específicas de blockchain. 16 -- é o número de buckets, atualmente é suficiente para os testes.

Eu fiz um backup com dados do ano atual e carreguei no meu TimescaleDB local:

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

Então, temos 8102147 (~8m) localmente.

Vamos usar duas consultas para testes de desempenho:

-- consulta geral
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 ?

-- Consulta de chain específica:
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 ?

Testes

Vamos testar o desempenho executando cada consulta 10 vezes e coletando métricas sobre o tempo de execução. Meu ambiente local:

• macbook m1 com 32 GB RAM
• timescaleDB 2.17.2-pg17 em docker
• configurações de memória aumentadas para pg: shared_buffers=8GB, work_mem=8GB

Primeira Tentativa

Este é o resultado usando a primeira consulta:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	71	105	77.3	74.5	9.7
1 dia	100	73	81	76.0	75.0	2.4
7 dias	100	161	192	168.1	166.0	8.5
30 dias	100	515	630	536.3	526.5	32.2

Este é o resultado usando a segunda consulta com o blockchain pequeno CELO:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	13	101	23.7	15.0	25.8
1 dia	100	13	99	23.4	14.5	25.2
7 dias	100	14	99	24.5	15.5	24.9
30 dias	100	16	104	26.1	17.5	26.0

Este é o resultado usando a segunda consulta com o grande blockchain ETHEREUM:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	27	114	37.8	29.5	25.5
1 dia	100	27	112	37.0	28.0	25.0
7 dias	100	66	172	79.6	68.5	30.9
30 dias	100	206	382	251.6	220.0	61.9

Podemos ver que as consultas para intervalos de 30 segundos não são tão rápidas quanto em outros, exceto no blockchain CELO. Isso é absolutamente esperado porque o banco de dados precisa processar mais linhas. Vamos tentar uma configuração diferente para ver se podemos melhorar o desempenho.

Segunda Tentativa

Vamos ativar a compressão. Usaremos collection_id como um segmento de compressão porque faz sentido manter esses números juntos.

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

Este é o resultado usando a primeira consulta:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	249	1091	355.9	261.5	247.0
1 dia	100	237	286	255.4	251.0	15.4
7 dias	100	247	278	259.4	260.5	8.9
30 dias	100	349	412	370.2	358.5	21.0

Este é o resultado usando a segunda consulta com o blockchain pequeno CELO:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	19	131	33.1	21.0	32.8
1 dia	100	18	128	32.9	21.5	32.1
7 dias	100	21	129	33.3	22.0	31.9
30 dias	100	25	151	42.1	25.0	36.9

Este é o resultado usando a segunda consulta com o grande blockchain ETHEREUM:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	90	203	115.7	99.0	34.6
1 dia	100	94	208	110.5	99.5	32.8
7 dias	100	95	219	111.1	98.5	36.3
30 dias	100	149	272	170.7	159.5	34.2

A tabela comprimida nos deu resultados piores... Mesmo para consultas de pequeno intervalo, no entanto, para 30 dias, no caso de todos os blockchains, melhorou o resultado. Acho que manter os números juntos por coleção ajudou aqui.

Terceira Tentativa

Vamos tentar aumentar o chunk para nossa hypertable, já que temos bastante RAM. Seguindo a recomendação oficial, é bom se o chunk puder representar 25% da memória. Atualmente, tenho 24GB de RAM alocados para o docker.

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));

Este é o resultado usando a primeira consulta:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	10	61	17.1	11.0	14.8
1 dia	100	10	18	11.6	11.0	2.2
7 dias	100	87	119	91.5	88.0	9.3
30 dias	100	367	514	390.4	373.5	42.9

Este é o resultado usando a segunda consulta com o blockchain pequeno CELO:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	2	14	5.0	4.5	3.2
1 dia	100	2	12	5.0	4.0	2.7
7 dias	100	3	15	6.2	5.0	3.4
30 dias	100	6	15	7.6	7.0	2.6

Este é o resultado usando a segunda consulta com o grande blockchain ETHEREUM:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	3	14	5.9	5.0	2.8
1 dia	100	5	13	6.0	5.0	2.4
7 dias	100	32	47	37.6	34.5	5.4
30 dias	100	129	140	133.3	132.0	3.2

Agora temos resultados muito melhores! Não gosto apenas da consulta para todos os chains para o período de 30 dias. As consultas CELO/ETHEREUM são realmente rápidas.

Quarta Tentativa

Vamos tentar usar compressão para bucket de 7 dias.

Este é o resultado usando a primeira consulta:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	47	153	60.3	49.5	31.0
1 dia	100	50	62	54.8	54.0	4.4
7 dias	100	62	73	67.1	67.0	3.6
30 dias	100	139	182	150.4	145.0	12.5

Este é o resultado usando a segunda consulta com o blockchain pequeno CELO:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	18	39	22.6	20.0	6.1
1 dia	100	20	36	25.1	23.0	5.7
7 dias	100	6	21	8.0	7.0	4.4
30 dias	100	7	21	9.4	8.0	4.0

Este é o resultado usando a segunda consulta com o grande blockchain ETHEREUM:

Período	Limite	Tempo Mín (ms)	Tempo Máx (ms)	Tempo Méd (ms)	Mediana (ms)	Desvio Padrão (ms)
1 hora	100	21	34	23.8	23.0	3.5
1 dia	100	23	59	29.0	24.5	10.5
7 dias	100	31	58	38.2	36.5	7.4
30 dias	100	59	74	64.5	63.5	4.2

Finalmente, nossas consultas estão extremamente rápidas. A mais lenta ainda é a de 30 dias para todos os chains, porém o tempo mediano é <0,2s, então eu diria que é rápida para agregação entre milhões de linhas.

Gráficos

Conclusão

Vamos analisar o tamanho das tabelas usando a consulta:

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

• tabela de chunk de 1 dia: 2.4 Gb
• tabela de chunk de 1 dia com compressão: 2.6 Gb
• tabela de chunk de 7 dias: 2.2 Gb
• tabela de chunk de 7 dias com compressão: 0.3 Gb

Há uma diferença de quase 10 vezes entre a primeira tabela e a última, o que explica por que a tabela de bucket de 7 dias com compressão é tão rápida. A configuração com chunk de 7 dias e compressão é a vencedora clara.