Choisir la meilleure configuration de table pour TimescaleDB

Description de la tâche

Il est assez courant pour les marketplaces de calculer le volume de certains produits sur une période de 1 jour/7 jours/30 jours en quasi temps réel. TimescaleDB ou toute autre base de données de séries temporelles est parfaitement adaptée à ce type de tâche. Testons TimescaleDB car mon projet actuel utilise déjà PostgreSQL. Je vais utiliser un modèle très simplifié de mon projet actuel (marketplace NFT). Nous avons des milliers de collections pour un ensemble de blockchains et nous collectons chaque liste/enchère ou transaction de vente dans la table. Le nombre total de transactions est énorme : des milliards de lignes. L'idée de la façon dont nous pouvons rendre le calcul rapide est de diviser notre table en segments temporels et de calculer le nombre en les utilisant.

Préparation

Voici un schéma de table et quelques index :

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

Nous avons créé quelques index supplémentaires :

• CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time); -- cela est nécessaire pour la cohérence des données
• CREATE INDEX ON sale_changes(collection_id, time); -- cela permet une agrégation plus rapide par collection

De plus, nous avons ajouté une dimension supplémentaire pour améliorer les performances des requêtes spécifiques à chaque blockchain. 16 -- est le nombre de segments, actuellement suffisant pour les tests.

J'ai effectué une sauvegarde des données pour l'année en cours et je l'ai chargée dans mon TimescaleDB local :

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

Nous avons donc environ 8102147 (~8m) en local.

Utilisons deux requêtes pour le test de performance :

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

Tests

Nous testerons la performance en exécutant chaque requête 10 fois et en collectant des métriques sur le temps d'exécution. Mon environnement local :

• macbook m1 avec 32 Go de RAM
• timescaleDB 2.17.2-pg17 dans docker
• paramètres mémoire augmentés pour pg: shared_buffers=8GB, work_mem=8GB

Première tentative

Voici le résultat de l'utilisation de la première requête :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	71	105	77.3	74.5	9.7
1 jour	100	73	81	76.0	75.0	2.4
7 jours	100	161	192	168.1	166.0	8.5
30 jours	100	515	630	536.3	526.5	32.2

Voici le résultat de l'utilisation de la deuxième requête avec la petite blockchain CELO :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	13	101	23.7	15.0	25.8
1 jour	100	13	99	23.4	14.5	25.2
7 jours	100	14	99	24.5	15.5	24.9
30 jours	100	16	104	26.1	17.5	26.0

Voici le résultat de l'utilisation de la deuxième requête avec la grande blockchain ETHEREUM :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	27	114	37.8	29.5	25.5
1 jour	100	27	112	37.0	28.0	25.0
7 jours	100	66	172	79.6	68.5	30.9
30 jours	100	206	382	251.6	220.0	61.9

Nous pouvons voir que les requêtes pour l'intervalle de 30 secondes ne sont pas aussi rapides que les autres, sauf pour la blockchain CELO. Cela est tout à fait attendu car la base de données doit traiter plus de lignes. Essayons une configuration différente et voyons si nous pouvons améliorer les performances.

Deuxième tentative

Activons la compression. Nous utiliserons collection_id comme segment de compression car il est logique de garder ces nombres ensemble.

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

Voici le résultat de l'utilisation de la première requête :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	249	1091	355.9	261.5	247.0
1 jour	100	237	286	255.4	251.0	15.4
7 jours	100	247	278	259.4	260.5	8.9
30 jours	100	349	412	370.2	358.5	21.0

Voici le résultat de l'utilisation de la deuxième requête avec la petite blockchain CELO :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	19	131	33.1	21.0	32.8
1 jour	100	18	128	32.9	21.5	32.1
7 jours	100	21	129	33.3	22.0	31.9
30 jours	100	25	151	42.1	25.0	36.9

Voici le résultat de l'utilisation de la deuxième requête avec la grande blockchain ETHEREUM :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	90	203	115.7	99.0	34.6
1 jour	100	94	208	110.5	99.5	32.8
7 jours	100	95	219	111.1	98.5	36.3
30 jours	100	149	272	170.7	159.5	34.2

Les tables compressées nous ont donné des résultats moins bons... Même pour les requêtes avec de petits intervalles, cependant pour 30 jours dans le cas de toutes les blockchains, le résultat s'est amélioré. Je suppose que garder les nombres ensemble par collection_id a aidé ici.

Troisième tentative

Essayons d'augmenter le segment pour notre hypertable, car nous avons beaucoup de RAM. Suivant la recommandation officielle, il est bon si le segment peut être de 25% de la mémoire. J'ai actuellement 24 Go de RAM alloués à 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));

Voici le résultat de l'utilisation de la première requête :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	10	61	17.1	11.0	14.8
1 jour	100	10	18	11.6	11.0	2.2
7 jours	100	87	119	91.5	88.0	9.3
30 jours	100	367	514	390.4	373.5	42.9

Voici le résultat de l'utilisation de la deuxième requête avec la petite blockchain CELO :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	2	14	5.0	4.5	3.2
1 jour	100	2	12	5.0	4.0	2.7
7 jours	100	3	15	6.2	5.0	3.4
30 jours	100	6	15	7.6	7.0	2.6

Voici le résultat de l'utilisation de la deuxième requête avec la grande blockchain ETHEREUM :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	3	14	5.9	5.0	2.8
1 jour	100	5	13	6.0	5.0	2.4
7 jours	100	32	47	37.6	34.5	5.4
30 jours	100	129	140	133.3	132.0	3.2

Nous avons maintenant de bien meilleurs résultats ! Seule la requête pour toutes les chaînes pendant une période de 30 jours ne me plaît pas. Les requêtes CELO/ETHEREUM sont vraiment rapides.

Quatrième tentative

Essayons d'utiliser la compression pour le segment de 7 jours.

Voici le résultat de l'utilisation de la première requête :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	47	153	60.3	49.5	31.0
1 jour	100	50	62	54.8	54.0	4.4
7 jours	100	62	73	67.1	67.0	3.6
30 jours	100	139	182	150.4	145.0	12.5

Voici le résultat de l'utilisation de la deuxième requête avec la petite blockchain CELO :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	18	39	22.6	20.0	6.1
1 jour	100	20	36	25.1	23.0	5.7
7 jours	100	6	21	8.0	7.0	4.4
30 jours	100	7	21	9.4	8.0	4.0

Voici le résultat de l'utilisation de la deuxième requête avec la grande blockchain ETHEREUM :

Période	Limite	Temps Min (ms)	Temps Max (ms)	Temps Moy. (ms)	Temps Médian (ms)	Écart-type (ms)
1 heure	100	21	34	23.8	23.0	3.5
1 jour	100	23	59	29.0	24.5	10.5
7 jours	100	31	58	38.2	36.5	7.4
30 jours	100	59	74	64.5	63.5	4.2

Enfin, nos requêtes sont extrêmement rapides. La plus lente est toujours celle de 30 jours pour toutes les chaînes, cependant le temps médian est <0.2s donc je dirais que c'est rapide pour l'agrégation parmi des millions de lignes.

Graphiques

Conclusion

Jetons un coup d'œil aux tailles des tables à l'aide de la requête :

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

• Table chunk 1 jour : 2.4 Gb
• Table chunk 1 jour avec compression : 2.6 Gb
• Table chunk 7 jours : 2.2 Gb
• Table chunk 7 jours avec compression : 0.3 Gb

Il y a presque 10x de différence entre la première table et la dernière, cela explique pourquoi la table avec segment de 7 jours et compression est si rapide. La configuration avec un segment de 7 jours et compression est le grand gagnant.