Cómo acelerar el performance de inserción en PostgreSQL

Estoy probando el performance de inserción de Postgres. Tengo una tabla con una columna con número como su tipo de datos. Hay un índice también. Llené la database usando esta consulta:

insert into aNumber (id) values (564),(43536),(34560) ... 

Inserté 4 millones de filas muy rápidamente, 10 000 a la vez con la consulta anterior. Después de que la database alcanzó 6 millones de filas, el performance disminuyó drásticamente a 1 millón de filas cada 15 minutos. ¿Hay algún truco para boost el performance de inserción? Necesito un performance de inserción óptimo en este proyecto.

Usando Windows 7 Pro en una máquina con 5 GB de RAM.

Vea llenar una database en el manual de PostgreSQL, el excelente artículo habitual de depesz sobre el tema, y esta pregunta SO .

(Tenga en count que esta respuesta es sobre la carga masiva de datos en un DB existente o para crear uno nuevo. Si le interesa el performance de restauración de DB con la ejecución pg_restre o psql de la salida pg_dump , gran parte de esto no se aplica desde pg_dump y pg_restre ya hace cosas como crear desencadenantes e índices una vez que finaliza un esquema + restauración de datos) .

Hay mucho por hacer. La solución ideal sería importar a una tabla UNLOGGED sin índices, luego cambiarla a registrar y agregar los índices. Desafortunadamente en PostgreSQL 9.4 no hay soporte para cambiar las tablas de UNLOGGED a registradas. 9.5 agrega ALTER TABLE ... SET LOGGED para permitirte hacer esto.

Si puede desconectar su database para la import masiva, use pg_bulkload .

De otra manera:

  • Deshabilita cualquier activador en la table

  • Coloque los índices antes de comenzar la import, vuelva a crearlos después. (Se necesita mucho less time para generar un índice en una pasada que para agregarle los mismos datos progresivamente, y el índice resultante es mucho más compacto).

  • Si realiza la import en una sola transacción, es seguro eliminar las restricciones de key externa, hacer la import y volver a crear las restricciones antes de confirmar. No haga esto si la import se divide en varias transactions ya que podría introducir datos no válidos.

  • Si es posible, use COPY lugar de INSERT s

  • Si no puede usar COPY considere usar INSERT s de múltiples valores si es práctico. Parece que ya estás haciendo esto. No intente enumerar demasiados valores en un solo VALUES ; esos valores tienen que caber en la memory un par de veces, así que mantenlo en unos pocos cientos por statement.

  • Inserte sus insertos en transactions explícitas, haciendo cientos de miles o millones de insertos por transacción. No existe un límite práctico AFAIK, pero el procesamiento por lotes le permitirá recuperarse de un error al marcar el inicio de cada lote en sus datos de input. De nuevo, pareces estar haciendo esto ya.

  • Use synchronous_commit=off y un commit_delay enorme para networkingucir los costos de fsync (). Sin embargo, esto no ayudará mucho si ha agrupado su trabajo en grandes transactions.

  • INSERT o COPY en paralelo desde varias conexiones. Cuántos dependen del subsistema de disco de su hardware; como regla general, desea una connection por disco duro físico si usa almacenamiento conectado directamente.

  • Establezca un alto valor de checkpoint_segments y habilite log_checkpoints . Mire los loggings de PostgreSQL y asegúrese de que no se queje de que los puntos de control ocurran con demasiada frecuencia.

  • Si, y solo si no le importa perder todo su clúster PostgreSQL (su database y cualquier otro en el mismo clúster) a corrupción catastrófica si el sistema falla durante la import, puede detener Pg, establecer fsync=off , iniciar Pg, hacer su import, entonces (vitalmente) pare Pg y configure fsync=on nuevamente. Vea la configuration de WAL . No haga esto si ya hay datos que le interesan en cualquier database en su installation de PostgreSQL. Si configura fsync=off , también puede configurar full_page_writes=off ; nuevamente, solo recuerde volver a activarlo después de la import para evitar daños en la database y pérdida de datos. Consulte las configuraciones no duraderas en el manual de Pg.

También deberías ver cómo ajustar tu sistema:

  • Utilice SSD de buena calidad para el almacenamiento tanto como sea posible. Las buenas unidades de estado sólido (SSD) con cachings de escritura contra escritura fiables y protegidos de energía hacen que las tasas de compromiso sean increíblemente más rápidas. Son less beneficiosas si sigues los consejos anteriores, que networkingucen las descargas de discos / número de fsync() s, pero aún pueden ser una gran ayuda. No utilice SSD baratos sin la protección adecuada de falla de energía a less que no se preocupe por conservar sus datos.

  • Si está utilizando RAID 5 o RAID 6 para el almacenamiento directo, deténgase ahora. Retroceda sus datos, reestructure su matriz RAID a RAID 10 y vuelva a intentarlo. RAID 5/6 es inútil para el performance de escritura masiva, aunque un buen controller RAID con un gran caching puede ayudar.

  • Si tiene la opción de utilizar una controllera RAID de hardware con una gran caching de recuperación de respaldo respaldada por batería, esto realmente puede mejorar el performance de escritura para cargas de trabajo con muchas asignaciones. No ayuda tanto si está utilizando la confirmación asincrónica con commit_delay o si realiza less transactions grandes durante la carga masiva.

  • Si es posible, almacene WAL ( pg_xlog ) en una matriz de disco / disco separada. No tiene sentido usar un sistema de files separado en el mismo disco. Las personas a menudo eligen usar un par RAID1 para WAL. Nuevamente, esto tiene más efecto en los sistemas con altas tasas de compromiso, y tiene poco efecto si está usando una tabla no registrada como objective de carga de datos.

También puede estar interesado en Optimizar PostgreSQL para testings rápidas .

Use la COPY table TO ... WITH BINARY que según la documentation es " algo más rápida que los formattings de text y CSV ". Solo haga esto si tiene millones de filas para insert y si se siente cómodo con los datos binarys.

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

Además de la excelente publicación de Craig Ringer y la publicación de blog de depesz, si desea acelerar sus inserciones a través de la interfaz ODBC ( psqlodbc ) mediante el uso de inserciones de declaraciones preparadas dentro de una transacción, hay algunas cosas adicionales que debe hacer para hacerlo trabaja rapido:

  1. Establezca el nivel de rollback-on-errors en "Transaction" especificando Protocol=-1 en la cadena de connection. De forma pnetworkingeterminada, psqlodbc usa el nivel de "Declaración", que crea un SAVEPOINT para cada statement en lugar de una transacción completa, lo que hace que las inserciones sean más lentas.
  2. Utilice declaraciones preparadas del lado del server especificando UseServerSidePrepare=1 en la cadena de connection. Sin esta opción, el cliente envía toda la instrucción de inserción junto con cada fila que se inserta.
  3. Deshabilitar la confirmación automática en cada instrucción mediante SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
  4. Una vez que se hayan insertado todas las filas, SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); la transacción usando SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); . No es necesario abrir explícitamente una transacción.

Desafortunadamente, psqlodbc "implementa" SQLBulkOperations al emitir una serie de instrucciones de inserción no preparadas, de modo que para lograr la inserción más rápida es necesario codificar manualmente los pasos anteriores.

Para un performance de inserción óptimo, deshabilite el índice si esa es una opción para usted. Aparte de eso, un mejor hardware (disco, memory) también es útil