Page 1 sur 1

intégration d'un très grand fichier dans Postgres

Posté : dim. 25 févr. 2018 18:58
par kermarrecSeb
Bonjour.

A la suite de la sortie de la deuxième version du fichier Pwned Passwords
https://www.troyhunt.com/ive-just-launc ... version-2/
Je me suis dis que j'allais me faire le service de vérification de mot de passe moi même sans passer le service https://haveibeenpwned.com
Ceci me permettrait donc faire des requête offline.
J'ai donc récupéré le fichier contenant le hash(sha1) d'une liste de mot de passe avec le nombre d'occurrence d'utilisation : https://haveibeenpwned.com/Passwords

Ce fichier fait 31,6Go décompressé.
Je l'ai intégré dans une base Postgres à l'aide de la commande copy (https://www.postgresql.org/docs/9.2/sta ... -copy.html)
Le caractère séparateur étant ':'
L'insertion semble avoir bien fonctionné, mais je n'en suis même pas sur vue la volumétrie.
J'en arrive à un blocage. les requêtes sont interminables.

Je suis débutant en optimisation de base de données et j'aurais besoin d'un coup de main sur le type de donnée à choisir pour la table, et surtout sur l'index à appliquer ou sur la façon de faire la requête.

la table à la structure suivante :
CREATE TABLE public.password
(
id text,
"number" bigint
)
WITH (
OIDS=FALSE
);

Je vous remercie par avance, en espérant que cette question vous intéressera.

Seb

Re: intégration d'un très grand fichier dans Postgres

Posté : mar. 27 févr. 2018 12:38
par Phil
Bonjour Seb,
Merci pour cette question et désolé pour l'attente, il y a une approbation à effectuer par l'admin pour les sujets postés lorsqu'on vient de s'inscrire et je n'avais pas vu le message hier. Sur du clé-valeur il y a d'autres solutions qu'une base PostgreSQL mais c'est tout à fait possible malgré tout.

Réponse 1 sur les types de données :
Pour le champ texte, text est OK. Pour le nombre d'occurrences du mot de passe, bigint est OK même si int suffirait probablement (2 milliards quand même)

Code : Tout sélectionner

CREATE TABLE password
(
id text,
number bigint
)
;
Pas besoin de guillemets, password, id et number ne sont pas des mots réservés par la norme SQL ou PostgreSQL : https://www.postgresql.org/docs/10/stat ... endix.html

Réponse 2 sur COPY :
La commande COPY est une bonne solution pour charger un fichier plat dans une table. Cette commande renvoie une confirmation immédiate du nombre de lignes copiées sous la forme "COPY compte" donc il est possible de savoir si c'est effectivement OK.
Après le chargement il est possible de voir un échantillon de la table avec :

Code : Tout sélectionner

select * from nom_table limit 10; 
ou encore compter le nombre de lignes avec

Code : Tout sélectionner

select count(*) from nom_table
Réponse 3 sur les SELECT :
Quelle est la syntaxe de la requête interminable ? Sur une table avec 2 champs, les requêtes ne pourront pas être très complexes. La clause where va intégrer un test sur id et/ou number. A priori plutôt sur ID qui est le mot de passe à tester ?
Si le test est une égalité simple (select * from password where id = ...) alors un index B-Tree ou un index Hash seront OK.

Code : Tout sélectionner

create index on password(id)
Le temps de réponse doit ensuite être de quelques ms au maximum pour tester sur un id particulier.

Re: intégration d'un très grand fichier dans Postgres

Posté : mar. 27 févr. 2018 22:24
par Phil
Démo sur un simple pc en utilisant le fichier v2 disponible au lien fourni https://haveibeenpwned.com/Passwords

Code : Tout sélectionner

select version();
                                                                                     version                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel (Ubuntu 11~~devel~20180226.2331-1~418.git364de25.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)


CREATE TABLE password
(
id text,
number bigint
)
;
CREATE TABLE

copy password from '/path/to/pwned-passwords-2.0.txt' with delimiter ':';
COPY 501636842
Durée : 975988,430 ms (16:15,988)

\d+
                       Liste des relations
 Schéma |   Nom    | Type  | Propriétaire | Taille | Description 
--------+----------+-------+--------------+--------+-------------
 public | password | table | postgres     | 39 GB  | 
(1 ligne)

create index password_h1 on password using hash(id);
CREATE INDEX
Durée : 2171269,934 ms (36:11,270)

 \di+
                              Liste des relations
 Schéma |     Nom     | Type  | Propriétaire |  Table   | Taille | Description 
--------+-------------+-------+--------------+----------+--------+-------------
 public | password_h1 | index | postgres     | password | 16 GB  | 
(1 ligne)


create extension pgcrypto;
CREATE EXTENSION

explain select number from password where id = upper(encode(digest('toto', 'sha1'), 'hex'));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Scan using password_h1 on password  (cost=0.00..8.02 rows=1 width=8)
   Index Cond: (id = '0B9C2625DC21EF05F6AD4DDF47C5F203837AA32C'::text)

select number from password where id = upper(encode(digest('toto', 'sha1'), 'hex'));
 number 
--------
  15320
(1 ligne)

Temps : 0,402 ms
Le chargement du fichier et la création de l'index (ici avec la méthode hash) prennent moins d'une heure.
Ensuite un petit essai avec le mot de passe toto donne 15320...comme prévu il est à présent possible d'affirmer en moins de 1ms que toto n'est pas un très bon mot de passe.

Re: intégration d'un très grand fichier dans Postgres

Posté : ven. 23 mars 2018 16:05
par Phil
Merci encore une fois pour cette question qui a indirectement permis d'améliorer la version 11 actuellement en développement : https://git.postgresql.org/gitweb/?p=po ... 877dc8ecb5

Re: intégration d'un très grand fichier dans Postgres

Posté : mar. 12 nov. 2019 13:28
par kermarrecSeb
Bonjour.
Veuillez m'excuser pour la lecture tardive.

Merci pour votre réponse, je vais lire tout ça attentivement.