postgresql - funciones - ¿Cuál es la forma más rápida de hacer una inserción masiva en Postgres?

postgresql pdf

Question

Necesito insertar programáticamente 10 de millones de registros en una base de datos postgres. Actualmente estoy ejecutando miles de instrucciones de inserción en una sola "consulta".

¿Hay una mejor manera de hacer esto, alguna declaración de inserción masiva que no sepa?

Answer 1

Acabo de encontrarme con este problema y recomendaría csvsql para las importaciones masivas a Postgres. Para realizar una inserción masiva, simplemente createdb y luego usa csvsql , que se conecta a su base de datos y crea tablas individuales para una carpeta completa de CSV.

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

Answer 2

En su mayoría depende de la (otra) actividad en la base de datos. Operaciones como esta efectivamente congelan toda la base de datos para otras sesiones. Otra consideración es el modelo de datos y la presencia de restricciones, factores desencadenantes, etc.

Mi primer enfoque es siempre: crear una tabla (temp) con una estructura similar a la tabla objetivo (crear la tabla tmp AS select * from target donde 1 = 0), y comenzar leyendo el archivo en la tabla temporal. Luego verifico qué se puede verificar: duplicados, claves que ya existen en el objetivo, etc.

Luego solo hago un "insertar en el objetivo select * from tmp" o similar.

Si esto falla, o toma demasiado tiempo, lo aborto y considero otros métodos (cayendo temporalmente índices / restricciones, etc.)

Answer 3

Existe una alternativa al uso de COPY, que es la sintaxis de valores multirrutas que admite Postgres. De la 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'');

El código anterior inserta dos filas, pero puede extenderlo arbitrariamente, hasta que llegue al número máximo de tokens de declaración preparados (puede ser $ 999, pero no estoy 100% seguro de eso). Algunas veces uno no puede usar COPIA, y este es un reemplazo digno para esas situaciones.

Answer 4

Implementé el cargador de datos Postgresq muy rápido con métodos libpq nativos. Prueba mi paquete https://www.nuget.org/packages/NpgsqlBulkCopy/

Answer 5

PostgreSQL tiene una guía sobre cómo llenar mejor una base de datos inicialmente, y sugieren usar el comando COPY para las filas de carga masiva. La guía contiene algunos otros buenos consejos sobre cómo acelerar el proceso, como eliminar índices y claves externas antes de cargar los datos (y volver a agregarlos después).

Answer 6

Puede usar la COPY table TO ... WITH BINARY que es " COPY ". Solo haga esto si tiene millones de filas para insertar y si se siente cómodo con los datos binarios.

Aquí hay una receta de ejemplo en Python, usando psycopg2 con entrada binaria .

Answer 7

Una forma de acelerar las cosas es realizar explícitamente múltiples insertos o copias dentro de una transacción (digamos 1000). El comportamiento predeterminado de Postgres es comprometerse después de cada declaración, de modo que al realizar el procesamiento por lotes de las confirmaciones, puede evitar algunos gastos generales. Como dice la guía en la respuesta de Daniel, es posible que deba desactivar la confirmación automática para que esto funcione. También tenga en cuenta el comentario al pie que sugiere que aumentar el tamaño de wal_buffers a 16 MB también puede ayudar.

Answer 8

UNNEST función UNNEST con matrices se puede usar junto con la sintaxis VALORES multirruta. Creo que este método es más lento que el uso de COPY pero me resulta útil en el trabajo con psycopg y python (la list python pasada a cursor.execute pasa a ser pg ARRAY ):

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

sin VALUES con subselección con comprobación de presencia adicional:

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 misma sintaxis para las actualizaciones masivas:

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;