Elegir la mejor configuración de tabla para timescaleDB

Descripción de la tarea

Existe una tarea bastante común para los mercados de calcular el volumen de algunos productos en un período de 1 día/7 días/30 días en tiempo casi real. TimescaleDB o cualquier otra base de datos de series temporales es perfectamente adecuada para este tipo de tarea. Vamos a probar TimescaleDB, ya que en mi proyecto actual ya se está usando PostgreSQL. Utilizaré un modelo muy simplificado de mi proyecto actual (mercado NFT). Tenemos miles de colecciones para un grupo de blockchains y recopilamos cada lista/oferta o transacción de venta en la tabla. El número total de transacciones es enorme: miles de millones de filas. La idea de cómo podemos hacer que el cálculo sea rápido es dividir nuestra tabla en bloques por tiempo y calcular el número utilizando esos bloques.

Preparación

Esta es un esquema de la tabla y algunos í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));

Creamos algunos índices adicionales:

• CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time); -- esto es necesario para la consistencia de los datos
• CREATE INDEX ON sale_changes(collection_id, time); -- esto es para una agregación más rápida por colección

Además, añadimos una dimensión adicional para un mejor rendimiento en consultas específicas de blockchain. 16 -- es el número de bloques, actualmente es suficiente para las pruebas.

Hice un respaldo con los datos del año actual y lo cargué en mi timescaleDB local:

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

Así que tenemos 8102147 (~8m) localmente.

Utilizaremos estas 2 consultas para probar el rendimiento:

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

Pruebas

Vamos a probar el rendimiento ejecutando cada consulta 10 veces y recopilando métricas sobre el tiempo de ejecución. Mi ambiente local:

• macbook m1 con 32 GB de RAM
• timescaleDB 2.17.2-pg17 en docker
• configuraciones de memoria aumentadas para pg: shared_buffers=8GB, work_mem=8GB

Primer intento

Este es el resultado de usar la primera consulta:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	71	105	77.3	74.5	9.7
1 día	100	73	81	76.0	75.0	2.4
7 días	100	161	192	168.1	166.0	8.5
30 días	100	515	630	536.3	526.5	32.2

Este es el resultado de usar la segunda consulta con la blockchain pequeña CELO:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	13	101	23.7	15.0	25.8
1 día	100	13	99	23.4	14.5	25.2
7 días	100	14	99	24.5	15.5	24.9
30 días	100	16	104	26.1	17.5	26.0

Este es el resultado de usar la segunda consulta con la blockchain grande ETHEREUM:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	27	114	37.8	29.5	25.5
1 día	100	27	112	37.0	28.0	25.0
7 días	100	66	172	79.6	68.5	30.9
30 días	100	206	382	251.6	220.0	61.9

Podemos ver que las consultas para intervalos de 30 segundos no son tan rápidas como las demás, excepto para la blockchain CELO. Es absolutamente esperado, ya que la base de datos tiene que procesar más filas. Vamos a probar una configuración diferente y ver si podemos mejorar el rendimiento.

Segundo intento

Vamos a activar la compresión. Utilizaremos collection_id como un segmento de compresión porque tiene sentido mantener estos 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 es el resultado de usar la primera consulta:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	249	1091	355.9	261.5	247.0
1 día	100	237	286	255.4	251.0	15.4
7 días	100	247	278	259.4	260.5	8.9
30 días	100	349	412	370.2	358.5	21.0

Este es el resultado de usar la segunda consulta con la blockchain pequeña CELO:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	19	131	33.1	21.0	32.8
1 día	100	18	128	32.9	21.5	32.1
7 días	100	21	129	33.3	22.0	31.9
30 días	100	25	151	42.1	25.0	36.9

Este es el resultado de usar la segunda consulta con la blockchain grande ETHEREUM:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	90	203	115.7	99.0	34.6
1 día	100	94	208	110.5	99.5	32.8
7 días	100	95	219	111.1	98.5	36.3
30 días	100	149	272	170.7	159.5	34.2

La tabla comprimida nos dio peores resultados... Incluso para consultas para intervalos cortos, sin embargo, para los 30 días en el caso de todas las blockchains mejoró el resultado. Supongo que mantener el número juntos por collection_id ayudó aquí.

Tercer intento

Vamos a intentar aumentar el chunk para nuestra hypertable, dado que tenemos mucha RAM. Siguiendo la recomendación oficial es bueno si el chunk puede ser del 25% de la memoria. Actualmente tengo 24GB de RAM asignados a 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 es el resultado de usar la primera consulta:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	10	61	17.1	11.0	14.8
1 día	100	10	18	11.6	11.0	2.2
7 días	100	87	119	91.5	88.0	9.3
30 días	100	367	514	390.4	373.5	42.9

Este es el resultado de usar la segunda consulta con la blockchain pequeña CELO:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	2	14	5.0	4.5	3.2
1 día	100	2	12	5.0	4.0	2.7
7 días	100	3	15	6.2	5.0	3.4
30 días	100	6	15	7.6	7.0	2.6

Este es el resultado de usar la segunda consulta con la blockchain grande ETHEREUM:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	3	14	5.9	5.0	2.8
1 día	100	5	13	6.0	5.0	2.4
7 días	100	32	47	37.6	34.5	5.4
30 días	100	129	140	133.3	132.0	3.2

¡Ahora tenemos resultados mucho mejores! Solo me preocupa la consulta de todas las cadenas para el período de 30 días. Las consultas de CELO/ETHEREUM son realmente rápidas.

Cuarto intento

Probemos usar la compresión para el bloque de 7 días.

Este es el resultado de usar la primera consulta:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	47	153	60.3	49.5	31.0
1 día	100	50	62	54.8	54.0	4.4
7 días	100	62	73	67.1	67.0	3.6
30 días	100	139	182	150.4	145.0	12.5

Este es el resultado de usar la segunda consulta con la blockchain pequeña CELO:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	18	39	22.6	20.0	6.1
1 día	100	20	36	25.1	23.0	5.7
7 días	100	6	21	8.0	7.0	4.4
30 días	100	7	21	9.4	8.0	4.0

Este es el resultado de usar la segunda consulta con la blockchain grande ETHEREUM:

Periodo	Límite	Tiempo Mínimo (ms)	Tiempo Máximo (ms)	Tiempo Promedio (ms)	Tiempo Mediano (ms)	Desv. Est. (ms)
1 hora	100	21	34	23.8	23.0	3.5
1 día	100	23	59	29.0	24.5	10.5
7 días	100	31	58	38.2	36.5	7.4
30 días	100	59	74	64.5	63.5	4.2

Finalmente, nuestras consultas son increíblemente rápidas. La más lenta sigue siendo la de 30 días para todas las cadenas, sin embargo, el tiempo mediano <0.2s, así que diría que es rápida para una agregación entre millones de filas.

Gráficos

Conclusión

Echemos un vistazo a los tamaños de tabla usando la consulta:

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

• Tabla de chunks de 1 día: 2.4 Gb
• Tabla de chunks de 1 día con compresión: 2.6 Gb
• Tabla de chunks de 7 días: 2.2 Gb
• Tabla de chunks de 7 días con compresión: 0.3 Gb

Es casi una diferencia de 10x entre la primera tabla y la última, lo que explica por qué la tabla de buckets de 7 días con compresión es tan rápida. La configuración con chunks de 7 días y compresión es claramente la ganadora.