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

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 statement de inserción masiva que no sepa?

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).

Existe una alternativa al uso de COPY, que es la syntax de valores multirrutas que admite Postgres. De la documentación :

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 statement 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.

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 statement, 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 boost el tamaño de wal_buffers a 16 MB también puede ayudar.

UNNEST función UNNEST con matrices se puede usar junto con la syntax 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 syntax 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; 

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 objective (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 objective, etc.

Luego solo hago un “insertar en el objective select * from tmp” o similar.

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

Puede usar la COPY table TO ... WITH BINARY que es ” algo más rápido que el texto y los formatos CSV “. 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 .

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 

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