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

PostgreSQL a un guide sur la meilleure façon d'alimenter une base de données au départ, et ils suggèrent d'utiliser l'option COPY pour le chargement en masse des rangées. Le guide contient d'autres bons conseils pour accélérer le processus, comme la suppression des index et des clés étrangères avant le chargement des données (et leur réinsertion après).

Answer 2

Il existe une alternative à l'utilisation de COPY, à savoir la syntaxe des valeurs multi-lignes que Postgres prend en charge. À partir de la page documentation :

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

Le code ci-dessus insère deux lignes, mais vous pouvez l'étendre arbitrairement, jusqu'à ce que vous atteigniez le nombre maximum de tokens d'instructions préparées (cela pourrait être 999 $, mais je n'en suis pas sûr à 100%). Parfois, on ne peut pas utiliser COPY, et ceci est un remplacement valable pour ces situations.

Answer 3

Une façon d'accélérer les choses est d'effectuer explicitement plusieurs insertions ou copies dans une transaction (disons 1000). Le comportement par défaut de Postgres est de commiter après chaque déclaration, donc en regroupant les commits, vous pouvez éviter une certaine surcharge. Comme le dit le guide dans la réponse de Daniel, vous devrez peut-être désactiver le commit automatique pour que cela fonctionne. Notez également le commentaire en bas de page qui suggère d'augmenter la taille des tampons wal_buffers à 16 Mo, ce qui peut également aider.

Answer 4

UNNEST avec des tableaux peut être utilisée avec la syntaxe VALUES multi-lignes. Je pense que cette méthode est plus lente que l'utilisation de la fonction COPY mais il m'est utile dans mon travail avec psycopg et python (python list transmis à cursor.execute devient pg ARRAY ) :

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
VALUES (
    UNNEST(ARRAY[1, 2, 3]), 
    UNNEST(ARRAY[100, 200, 300]), 
    UNNEST(ARRAY['a', 'b', 'c'])
);

sans VALUES en utilisant une sous-sélection avec un contrôle d'existence supplémentaire :

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
SELECT * FROM (
    SELECT UNNEST(ARRAY[1, 2, 3]), 
           UNNEST(ARRAY[100, 200, 300]), 
           UNNEST(ARRAY['a', 'b', 'c'])
) AS temptable
WHERE NOT EXISTS (
    SELECT 1 FROM tablename tt
    WHERE tt.fieldname1=temptable.fieldname1
);

la même syntaxe pour les mises à jour en masse :

UPDATE tablename
SET fieldname1=temptable.data
FROM (
    SELECT UNNEST(ARRAY[1,2]) AS id,
           UNNEST(ARRAY['a', 'b']) AS data
) AS temptable
WHERE tablename.id=temptable.id;

Answer 5

Vous pouvez utiliser COPY table TO ... WITH BINARY qui est " un peu plus rapide que les formats texte et CSV ." Ne faites cela que si vous avez des millions de lignes à insérer et si vous êtes à l'aise avec les données binaires.

Voici un exemple de recette en Python, utilisant psycopg2 avec une entrée binaire .