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

Ver llenar una base de datosen el manual de PostgreSQL, el excelente artículo de depeszsobre el tema, y esta pregunta.

(Tenga en cuenta que esta respuesta es sobre la carga masiva de datos en una base de datos existente o para crear una nueva. Si está interesado en restaurar el rendimiento de la base de datos con pg_restore o psql ejecución de la salida pg_dump, gran parte de esto no se aplica ya que pg_dump y pg_restore ya hacen cosas como crear disparadores e índices después de esquema+restauración de datos) .

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

Si puede desconectar su base de datos para la importación masiva, use pg_bulkload.

De lo contrario:

• Desactivar cualquier disparador en el cuadro

• Soltar índices antes de iniciar la importación, volver a crearlos después. (Toma mucho menos tiempo construir un índice en una pasada que agregar los mismos datos progresivamente, y el índice resultante es mucho más compacto).

• Si realiza la importación dentro de una sola transacción, es seguro eliminar las restricciones de clave externa, realizar la importación y volver a crear las restricciones antes de confirmar. No haga esto si la importación está dividida múltiples transacciones, ya que puede introducir datos no válidos.

• Si es posible, utilice COPY en lugar de INSERT s

• Si no puede usar COPY considere usar INSERTs de múltiples valores si es práctico. Parece que ya estás haciendo esto. Sin embargo, no intente enumerar también muchos valores en un solo VALUES; esos valores tienen que caber en la memoria un par de veces, así que manténgalo en unos pocos cientos por declaración.

• Agrupe sus inserciones en explicit transacciones, haciendo cientos de miles o millones de insertos por transacción. No hay límite práctico AFAIK, pero el procesamiento por lotes le permitirá recuperarse de un error marcando el inicio de cada lote en sus datos de entrada. De nuevo, parece que ya estás haciendo esto.

• Use synchronous_commit=off y un enorme commit_delay para reducir los costos de fsync (). Sin embargo, esto no ayudará mucho si has agrupado tu trabajo en grandes transacciones.

• INSERT o COPY en paralelo desde varias conexiones. La cantidad depende del subsistema de disco de su hardware; como regla general, desea una conexión por disco duro físico si utiliza almacenamiento conectado directamente.

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

• Si y solo si no le importa perder todo su clúster de PostgreSQL (su base de datos y cualquier otra en el mismo clúster) a una corrupción catastrófica si el sistema se bloquea durante la importación, puede detener Pg, establecer fsync=off, iniciar Pg, hacer su importación, luego (vitalmente) detener Pg y establecer fsync=on de nuevo. Ver Configuración WAL. No haga esto si ya hay algún dato que le importe en cualquier base de datos en su instalación de PostgreSQL. Si configura fsync=off también puede configurar full_page_writes=off; nuevamente, solo recuerde volver a activarlo después de la importación para evitar la corrupción de la base de datos y la pérdida de datos. Ver configuración no duradera en el Pg manual.

Usted también debe mirar el ajuste de su sistema:

• Utilice SSD de buena calidad para el almacenamiento tanto como sea posible. Los buenos SSD con cachés de escritura confiables y protegidos con energía hacen que las tasas de confirmación sean increíblemente más rápidas. Son menos beneficiosos cuando sigue los consejos anteriores, lo que reduce las descargas de disco / el número de fsync()s, pero aún puede ser una gran ayuda. No use SSD baratos sin una protección adecuada contra fallas de energía a menos que no le importe mantener sus datos.

• Si está utilizando RAID 5 o RAID 6 para el almacenamiento con conexión directa, deténgase ahora. Haga una copia de seguridad de sus datos, reestructure su matriz RAID a RAID 10 e inténtelo de nuevo. RAID 5/6 no tiene esperanza para el rendimiento de escritura masiva, aunque un buen controlador RAID con una gran caché puede ayudar.

• Si tiene la opción de usar un controlador RAID de hardware con una gran caché de escritura respaldada por batería, esto realmente puede mejorar el rendimiento de escritura para cargas de trabajo con cometer. No ayuda tanto si estás usando commit async con un commit_delay o si estás haciendo menos transacciones grandes durante la carga masiva.

• Si es posible, almacene WAL (pg_xlog) en una matriz disk / disk separada. No tiene mucho sentido usar un sistema de archivos separado en el mismo disco. La gente a menudo elige usar un par RAID1 para WAL. Una vez más, esto tiene más efecto en los sistemas con altas tasas de confirmación, y tiene poco efecto si está utilizando una tabla no registrada como la carga de datos objetivo.

También puede interesarle Optimizar PostgreSQL para pruebas rápidas.