Quel est le moyen le plus rapide d'effectuer une insertion en masse dans Postgres ?

Question

Je dois insérer par programme des dizaines de millions d'enregistrements dans une base de données Postgres. Actuellement, j'exécute des milliers d'instructions d'insertion dans une seule requête.

Existe-t-il une meilleure façon de procéder, une déclaration d'insertion en masse que je ne connais pas ?

Answer 1

      ((ceci est un WIKI que vous pouvez modifier et améliorer la réponse !))

Le fichier externe est le meilleur et le plus typique des données en vrac.

Le terme " données en vrac " est lié à " beaucoup de données ", il est donc naturel d'utiliser données brutes originales sans qu'il soit nécessaire de le transformer en SQL. Les fichiers de données brutes typiques pour l'"insertion en masse" sont les suivants CSV y JSON formats.

Insertion en vrac avec une certaine transformation

En ETL et les processus d'ingestion, nous devons modifier les données avant de les insérer. Les tables temporaires consomment (beaucoup) d'espace disque, et ce n'est pas le moyen le plus rapide de le faire. Le site Enveloppeur de données étrangères PostgreSQL (FDW) est le meilleur choix.

Exemple CSV . Supposons que le tablename (x, y, z) sur SQL et un fichier CSV comme

fieldname1,fieldname2,fieldname3
etc,etc,etc
... million lines ...

Vous pouvez utiliser la méthode SQL classique COPY à charger ( en l'état données originales) en tmp_tablename ils insèrent les données filtrées dans tablename ... Mais, pour éviter la consommation de disque, le mieux est d'ingérer directement par

INSERT INTO tablename (x, y, z)
  SELECT f1(fieldname1), f2(fieldname2), f3(fieldname3) -- the transforms 
  FROM tmp_tablename_fdw
  -- WHERE condictions
;

Vous devez préparer la base de données pour FDW, et au lieu de statiques tmp_tablename_fdw vous pouvez utiliser une fonction qui le génère :

CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE tmp_tablename_fdw(
  ...
) SERVER import OPTIONS ( filename '/tmp/pg_io/file.csv', format 'csv');

Exemple JSON . Un ensemble de deux fichiers, myRawData1.json y Ranger_Policies2.json peut être ingéré par :

INSERT INTO tablename (fname, metadata, content)
 SELECT fname, meta, j  -- do any data transformation here
 FROM jsonb_read_files('myRawData%.json')
 -- WHERE any_condiction_here
;

où la fonction jsonb_read_files() lit tous les fichiers d'un dossier, défini par un masque :

CREATE or replace FUNCTION jsonb_read_files(
  p_flike text, p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int, fname text, fmeta jsonb, j jsonb) AS $f$
  WITH t AS (
     SELECT (row_number() OVER ())::int id, 
           f AS fname,
           p_fpath ||'/'|| f AS f
     FROM pg_ls_dir(p_fpath) t(f)
     WHERE f LIKE p_flike
  ) SELECT id, fname,
         to_jsonb( pg_stat_file(f) ) || jsonb_build_object('fpath', p_fpath),
         pg_read_file(f)::jsonb
    FROM t
$f$  LANGUAGE SQL IMMUTABLE;

Absence de streaming gzip

La méthode la plus fréquente pour "l'ingestion de fichiers" (principalement dans le domaine du Big Data) consiste à conserver le fichier original sur le disque dur. format gzip et le transférer avec algorithme de streaming tout ce qui peut fonctionner rapidement et sans consommation de disque dans des pipes unix :

 gunzip remote_or_local_file.csv.gz | convert_to_sql | psql 

Ainsi, l'idéal (le futur) est un option de serveur pour le format .csv.gz .

Note après le commentaire de @CharlieClark : actuellement (2022) rien à faire, la meilleure alternative semble pgloader STDIN :

  gunzip -c file.csv.gz | pgloader --type csv ... - pgsql:///target?foo

Answer 2

Cela dépend surtout de l'activité (autre) dans la base de données. Les opérations de ce type ont pour effet de geler l'ensemble de la base de données pour d'autres sessions. Il faut également tenir compte du modèle de données et de la présence de contraintes, de déclencheurs, etc.

Ma première approche est toujours la suivante : créer une table (temporaire) dont la structure est similaire à celle de la table cible ( create table tmp AS select * from target where 1=0 ), et commence par lire le fichier dans la table temporaire. Ensuite, je vérifie ce qui peut être vérifié : les doublons, les clés qui existent déjà dans la cible, etc.

Ensuite, je fais juste un do insert into target select * from tmp ou similaire.

Si cela échoue ou prend trop de temps, je l'abandonne et j'envisage d'autres méthodes (abandon temporaire des index/contraintes, etc.).

Answer 3

J'ai implémenté un chargeur de données Postgresq très rapide avec les méthodes natives de libpq. Essayez mon paquet https://www.nuget.org/packages/NpgsqlBulkCopy/

Answer 4

Je viens de rencontrer ce problème et je recommande csvsql ( libère ) pour les importations en masse vers Postgres. Pour effectuer une insertion en masse, il suffit de createdb et ensuite utiliser csvsql qui se connecte à votre base de données et crée des tables individuelles pour un dossier entier de CSV.

$ createdb test 
$ csvsql --db postgresql:///test --insert examples/*.csv

Answer 5

Peut-être que je suis déjà en retard. Mais, il y a une bibliothèque Java appelée pgbulkinsert par Bytefish. Moi et mon équipe avons pu insérer en masse 1 million d'enregistrements en 15 secondes. Bien sûr, nous avons effectué d'autres opérations comme la lecture de plus d'un million d'enregistrements à partir d'un fichier situé sur Minio, le traitement de plus d'un million d'enregistrements, le filtrage des enregistrements en cas de doublons, et enfin l'insertion d'un million d'enregistrements dans la base de données Postgres. Et tous ces processus ont été achevés en 15 secondes. Je ne me rappelle pas exactement combien de temps il a fallu pour effectuer l'opération DB, mais je pense que c'était environ moins de 5 secondes. Pour plus de détails, voir https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html