Discussion :

[Ikit]

Bonjour,

J'aimerais votre avis sur la meilleure façon de faire pour insérer massivement des données dans plusieurs tables.
Quelques mots sur le context : il s'agit d'un logiciel d'analyse de données issues du séquençage génétique.
Voici le schéma des tables qui nous intéresse.

La table variant contient les mutations génétiques (ref & alt) localisées (chr & pos) dans le génome
La table sample contient les "patients"
La table sample_variant fait le liens entre les patients et les mutations analysées.

La taille de la table variant, et donc de la table sample_variant aussi, vont grossir très vite. Pour chaque patient analysé, on ajoutera environ 50 000 enregistrements. Mais plus on ajoutera des variants, plus on en rencontrera qui existe déjà, et donc plusieurs sample référenceront le même variant. Un calcul "grosse-maille" serait de dire qu'il y a environs 3 milliards de position possible dans le génom, et 4 mutation possibles (si on simplifie), ça ferait une table qui pourrait atteindre les 12 milliards d'enregistrements... ça commence à faire.


Bref ! Les présentations sont faites, maintenant les questions
J'ai un nouveau patient a ajouter en base. Je vais créer une nouvelle entrée dans la table sample => nouveau sample_id
Je vais ensuite vouloir intégrer dans la base les 50 000 variants du patient. Mais pour chacun d'eux, je dois vérifier si il n'existe pas déjà en base. et pour ça je suis obligé de faire par exemple une requête du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id FROM variant WHERE chr='chr1' AND pos=781334 AND ref='A' AND alt='C'

Pour pouvoir ensuite faire un create variant si il n'existe pas, et créer l'association dans la table sample_variant

- Y a-t-il une façon élégante (mais surtout efficace) de faire un "get_or_create" en postgresql ?
- J'ai lu sur la doc postgres qu'en utilisant les transactions, postgres gérait ensuite tout seul la paralélisation. Combien d'enregistrements par transaction me conseillez vous de mettre ? 500, 1000, 10 000, tout d'un coup ?
- Côté optimisation de la table, à part placer un index sur les 4 champs (chr, pos, ref, alt), je ne vois pas quoi faire d'autre. Vous avez d'autres idées ?

Merci pour votre aide.

[SQLpro]

Bonjour,

J'aimerais votre avis sur la meilleure façon de faire pour insérer massivement des données dans plusieurs tables.
Quelques mots sur le context : il s'agit d'un logiciel d'analyse de données issues du séquençage génétique.
Voici le schéma des tables qui nous intéresse.

La table variant contient les mutations génétiques (ref & alt) localisées (chr & pos) dans le génome
La table sample contient les "patients"
La table sample_variant fait le liens entre les patients et les mutations analysées.

La taille de la table variant, et donc de la table sample_variant aussi, vont grossir très vite. Pour chaque patient analysé, on ajoutera environ 50 000 enregistrements. Mais plus on ajoutera des variants, plus on en rencontrera qui existe déjà, et donc plusieurs sample référenceront le même variant. Un calcul "grosse-maille" serait de dire qu'il y a environs 3 milliards de position possible dans le génom, et 4 mutation possibles (si on simplifie), ça ferait une table qui pourrait atteindre les 12 milliards d'enregistrements... ça commence à faire.

PostGreSQL n'est pas très à l'aise pour gérer ce genre de volumétrie...

Dans des systèmes comme SQL Server le moteur est capable de paralléliser les traitements à tous les niveaux : lecture et mise à jour des informations en parallèle, écriture des disque en parallèle....
PG commence à faire du parallélisme de lecture, mais c'est au stade embryonnaire et le système de stockage de PG comme la journalisation est très lente... en comparaison.

Bref ! Les présentations sont faites, maintenant les questions
J'ai un nouveau patient a ajouter en base. Je vais créer une nouvelle entrée dans la table sample => nouveau sample_id
Je vais ensuite vouloir intégrer dans la base les 50 000 variants du patient. Mais pour chacun d'eux, je dois vérifier si il n'existe pas déjà en base. et pour ça je suis obligé de faire par exemple une requête du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id FROM variant WHERE chr='chr1' AND pos=781334 AND ref='A' AND alt='C'

Ce n'est pas ainsi qu'il faut procéder. Il suffit de créer une contrainte d'unicité et d'insérer massivement toutes les lignes d'un coup par une sous requête d'existence du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO MaTable
SELECT ...
WHERE NOT EXISTS(SELECT * FROM MaTable AS T WHERE ...)

Pour pouvoir ensuite faire un create variant si il n'existe pas, et créer l'association dans la table sample_variant

- Y a-t-il une façon élégante (mais surtout efficace) de faire un "get_or_create" en postgresql ?
- J'ai lu sur la doc postgres qu'en utilisant les transactions, postgres gérait ensuite tout seul la paralélisation. Combien d'enregistrements par transaction me conseillez vous de mettre ? 500, 1000, 10 000, tout d'un coup ?

Compte tenu de ce que je viens de vous dire... TOUS !

- Côté optimisation de la table, à part placer un index sur les 4 champs (chr, pos, ref, alt), je ne vois pas quoi faire d'autre. Vous avez d'autres idées ?

On ne place pas des index au petit bonheur la chance ! Cela s'étudie. De plus en matière d'indexation PG est loin de proposer des index aussi pointus que ceux de SQL Server avec notamment sa clause INCLUDE ou encore des index verticaux comme le ColumnStore de SQL Server.

Bref je vous conseille de faire des tests en parallèle entre une base PG et une base SQL Server noter les temps de réponse et décider !

A +

[Ikit]

Hello SQLpro.
Merci pour ta réponse

Ce n'est pas ainsi qu'il faut procéder. Il suffit de créer une contrainte d'unicité et d'insérer massivement toutes les lignes d'un coup par une sous requête d'existence du genre

D'accord, et donc si je comprend bien, une fois que j'ai massivement inséré mes variants, je crée les relations sample_variant avec une requête du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO sample_variant (sample_id, variant_id) VALUES 
(<sample_id>, (SELECT id FROM variant WHERE chr='chr1' AND pos=781334 AND ref='A' AND alt='C') ),
(<sample_id>, (SELECT id FROM variant WHERE chr='chr1' AND pos=781335 AND ref='G' AND alt='T') ),
(<sample_id>, (SELECT id FROM variant WHERE chr='chr1' AND pos=781336 AND ref='-' AND alt='CTA') ),
...

ou alors tu sous-entendais aussi que je change le schéma de la table sample_variant afin de ne pas référencer les variant via un id, mais directement via leur quadruple clés primaire (chr, pos, ref, alt) ?
ça me parait méga "bourrin" de faire des clés primaire comme ça sur au final presque tout les champs d'une table, non ?

On ne place pas des index au petit bonheur la chance ! Cela s'étudie

bah en fait je n'ai pas dis ça complétement au hasard non plus
ma condition de recherche tenant compte des 4 valeurs, l'index doit les prendre tout les 4 en compte, et je les ais ordonnés du plus au moins "stringeant". Je ne suis pas expert db, donc c'est ptet naif, mais c'est toujours comme ça que j'ai raisoné pour placer mes index jusqu'à présent.


Pour SQL server, je peux toujours essayer, mais l'appli étant une appli de recherche, à la base n'utilisant que des logiciels gratuit, je ne pense pas si je réussirais à obtenir une license.

[SQLpro]

Hello SQLpro.
Merci pour ta réponse



D'accord, et donc si je comprend bien, une fois que j'ai massivement inséré mes variants, je crée les relations sample_variant avec une requête du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO sample_variant (sample_id, variant_id) VALUES 
(<sample_id>, (SELECT id FROM variant WHERE chr='chr1' AND pos=781334 AND ref='A' AND alt='C') ),
(<sample_id>, (SELECT id FROM variant WHERE chr='chr1' AND pos=781335 AND ref='G' AND alt='T') ),
(<sample_id>, (SELECT id FROM variant WHERE chr='chr1' AND pos=781336 AND ref='-' AND alt='CTA') ),
...

OUI....

ou alors tu sous-entendais aussi que je change le schéma de la table sample_variant afin de ne pas référencer les variant via un id, mais directement via leur quadruple clés primaire (chr, pos, ref, alt) ?
ça me parait méga "bourrin" de faire des clés primaire comme ça sur au final presque tout les champs d'une table, non ?

Non, j'ai pas dit cela. Votre clef est bonne, votre index peut être pas....

bah en fait je n'ai pas dis ça complétement au hasard non plus
ma condition de recherche tenant compte des 4 valeurs, l'index doit les prendre tout les 4 en compte, et je les ais ordonnés du plus au moins "stringeant". Je ne suis pas expert db, donc c'est ptet naif, mais c'est toujours comme ça que j'ai raisoné pour placer mes index jusqu'à présent.


Pour SQL server, je peux toujours essayer, mais l'appli étant une appli de recherche, à la base n'utilisant que des logiciels gratuit, je ne pense pas si je réussirais à obtenir une license.

Il y a toujours un cout. Entre le matériel, l'humain et le soft, le gratuit n'est pas toujours la solution la plus économique. L'emmerdant c'est que c'est souvent une fois que le projet est terminé qu'on s'en aperçoit !

A +

PS : et MS fait des tarifs spéciaux dans ce cas de figure... Il faut prendre contact directement avec eux ! (si vous êtes un pole de recherche reconnu d'utilité publique notamment comme c'est le cas de mon client ITER...)

[alassanediakite]

PostGreSQL n'est pas très à l'aise pour gérer ce genre de volumétrie...

Dans des systèmes comme SQL Server le moteur est capable de paralléliser les traitements à tous les niveaux : lecture et mise à jour des informations en parallèle, écriture des disque en parallèle....
PG commence à faire du parallélisme de lecture, mais c'est au stade embryonnaire et le système de stockage de PG comme la journalisation est très lente... en comparaison.Ce n'est pas ainsi qu'il faut procéder. Il suffit de créer une contrainte d'unicité et d'insérer massivement toutes les lignes d'un coup par une sous requête d'existence du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO MaTable
SELECT ...
WHERE NOT EXISTS(SELECT * FROM MaTable AS T WHERE ...)

Compte tenu de ce que je viens de vous dire... TOUS !

On ne place pas des index au petit bonheur la chance ! Cela s'étudie. De plus en matière d'indexation PG est loin de proposer des index aussi pointus que ceux de SQL Server avec notamment sa clause INCLUDE ou encore des index verticaux comme le ColumnStore de SQL Server.

Bref je vous conseille de faire des tests en parallèle entre une base PG et une base SQL Server noter les temps de réponse et décider !

A +

Tu es vraiment incorrigible.
Toujours le même refrain: X n'est pas bon, choisissez SQL Server qui est PARFAIT et SQLpro est le seul CONNAISSEUR.
@ vos INSULTES

[SQLpro]

Tu es vraiment incorrigible.
Toujours le même refrain: X n'est pas bon, choisissez SQL Server qui est PARFAIT et SQLpro est le seul CONNAISSEUR.
@ vos INSULTES

Si PostGreSQL était le meilleur, alors Oracle, IBM DB2, SQL Server, Sybase ... auraient tous disparu ce qui n'est pas le cas....

Pose toi des questions, fait des comparatifs et des benchmarks avant de faire l'intégriste de PG !

A +