Consejos de rendimiento de Postgresql: rellenar una base de datos

14.4.1. Desactivar Autocommit

14.4.2. Usar COPY

14.4.3. Eliminar índices

14.4.4. Eliminar restricciones de clave externa

14.4.5. Incrementar maintenance_work_mem

14.4.6. Incrementar max_wal_size

14.4.7. Deshabilitar la replicación de transmisión y archivado de WAL

14.4.8. Correr ANALYZE Después

14.4.9. Algunas notas sobre pg_dump

Es posible que deba insertar una gran cantidad de datos al completar una base de datos por primera vez. Esta sección contiene algunas sugerencias sobre cómo hacer que este proceso sea lo más eficiente posible.

14.4.1. Desactivar Autocommit

Cuando se utilizan varios INSERTs, desactive la confirmación automática y realice una confirmación al final. (En SQL simple, esto significa emitir BEGIN al principio y COMMIT al final. Algunas bibliotecas cliente pueden hacer esto a sus espaldas, en cuyo caso debe asegurarse de que la biblioteca lo haga cuando lo desee). Si permite que cada inserción se confirme por separado, PostgreSQL está haciendo mucho trabajo para cada fila que está agregado. Un beneficio adicional de hacer todas las inserciones en una transacción es que si fallara la inserción de una fila, la inserción de todas las filas insertadas hasta ese punto se revertiría, por lo que no se quedará atascado con datos parcialmente cargados.

14.4.2. Usar COPY

Use COPY para cargar todas las filas en un comando, en lugar de usar una serie de INSERT comandos. los COPY el comando está optimizado para cargar un gran número de filas; es menos flexible que INSERT, pero incurre en una sobrecarga significativamente menor para grandes cargas de datos. Ya que COPY es un solo comando, no es necesario deshabilitar el compromiso automático si usa este método para completar una tabla.

Si no puede usar COPY, puede ser útil utilizar PREPARE para crear un INSERT declaración, y luego use EXECUTE tantas veces como sea necesario. Esto evita parte de la sobrecarga de analizar y planificar repetidamente INSERT. Las diferentes interfaces proporcionan esta función de diferentes maneras; buscar “declaraciones preparadas“ en la documentación de la interfaz.

Tenga en cuenta que cargar una gran cantidad de filas usando COPY es casi siempre más rápido que usar INSERT, incluso si PREPARE se utiliza y las inserciones múltiples se agrupan en una sola transacción.

COPY es más rápido cuando se utiliza dentro de la misma transacción que un anterior CREATE TABLE o TRUNCATE mando. En tales casos, no es necesario escribir WAL, porque en caso de error, los archivos que contienen los datos recién cargados se eliminarán de todos modos. Sin embargo, esta consideración solo se aplica cuando wal_level es minimal ya que todos los comandos deben escribir WAL en caso contrario.

14.4.3. Eliminar índices

Si está cargando una tabla recién creada, el método más rápido es crear la tabla, cargar de forma masiva los datos de la tabla usando COPY, luego cree los índices necesarios para la tabla. Crear un índice sobre datos preexistentes es más rápido que actualizarlo de forma incremental a medida que se carga cada fila.

Si está agregando grandes cantidades de datos a una tabla existente, podría ser beneficioso eliminar los índices, cargar la tabla y luego volver a crear los índices. Por supuesto, el rendimiento de la base de datos para otros usuarios puede verse afectado durante el tiempo que faltan los índices. También se debe pensar dos veces antes de eliminar un índice único, ya que la comprobación de errores proporcionada por la restricción única se perderá mientras falta el índice.

14.4.4. Eliminar restricciones de clave externa

Al igual que con los índices, un extranjero key la restricción se puede comprobar “al por mayor“ de manera más eficiente que fila por fila. Por lo tanto, podría ser útil dejar de lado key restricciones, cargue datos y vuelva a crear las restricciones. Una vez más, existe una compensación entre la velocidad de carga de datos y la pérdida de la comprobación de errores mientras falta la restricción.

Es más, cuando carga datos en una tabla con archivos externos existentes key restricciones, cada nueva fila requiere una entrada en la lista del servidor de eventos desencadenantes pendientes (ya que es el disparo de un desencadenador que verifica el exterior de la fila key restricción). La carga de muchos millones de filas puede hacer que la cola de eventos de activación se desborde de la memoria disponible, lo que lleva a un intercambio intolerable o incluso a una falla absoluta del comando. Por lo tanto, puede ser necesario, no solo deseable, eliminar y volver a aplicar extranjeros keys al cargar grandes cantidades de datos. Si eliminar temporalmente la restricción no es aceptable, el único otro recurso puede ser dividir la operación de carga en transacciones más pequeñas.

14.4.5. Incrementar maintenance_work_mem

El aumento temporal de la variable de configuración maintenance_work_mem cuando se cargan grandes cantidades de datos puede mejorar el rendimiento. Esto ayudará a acelerar CREATE INDEX comandos y ALTER TABLE ADD FOREIGN KEY comandos. No servirá de mucho COPY en sí mismo, por lo que este consejo solo es útil cuando está utilizando una o ambas de las técnicas anteriores.

14.4.6. Incrementar max_wal_size

El aumento temporal de la variable de configuración max_wal_size también puede acelerar las cargas de datos grandes. Esto se debe a que cargar una gran cantidad de datos en PostgreSQL hará que los puntos de control ocurran con más frecuencia que la frecuencia normal de puntos de control (especificada por el checkpoint_timeout variable de configuración). Siempre que se produzca un punto de control, todas las páginas sucias se deben descargar en el disco. Aumentando max_wal_size temporalmente durante las cargas masivas de datos, se puede reducir la cantidad de puntos de control que se requieren.

14.4.7. Deshabilitar la replicación de transmisión y archivado de WAL

Cuando se cargan grandes cantidades de datos en una instalación que utiliza el archivado WAL o la replicación de transmisión, puede ser más rápido realizar una nueva copia de seguridad base después de que se haya completado la carga que procesar una gran cantidad de datos WAL incrementales. Para evitar el registro de WAL incremental durante la carga, desactive el archivado y la replicación de transmisión estableciendo wal_level en minimal, archive_mode a offy max_wal_senders a cero. Pero tenga en cuenta que cambiar esta configuración requiere reiniciar el servidor.

Además de evitar que el archivador o el remitente de WAL tengan que procesar los datos de WAL, hacer esto hará que ciertos comandos sean más rápidos, porque no escriben WAL en absoluto si wal_level es minimal y la subtransacción actual (o transacción de nivel superior) creó o truncó la tabla o el índice que cambiaron. (Pueden garantizar la seguridad en caso de colisión de forma más económica si realizan una fsync al final que escribiendo WAL.)

14.4.8. Correr ANALYZE Después

Siempre que haya alterado significativamente la distribución de datos dentro de una tabla, se recomienda encarecidamente ejecutar ANALYZE. Esto incluye la carga masiva de grandes cantidades de datos en la tabla. Corriendo ANALYZE (o VACUUM ANALYZE) asegura que el planificador tenga estadísticas actualizadas sobre la tabla. Sin estadísticas o estadísticas obsoletas, el planificador puede tomar malas decisiones durante la planificación de consultas, lo que da lugar a un rendimiento deficiente en las tablas con estadísticas inexactas o inexistentes. Tenga en cuenta que si el demonio de autovacío está habilitado, podría ejecutarse ANALYZE automáticamente; consulte la Sección 24.1.3 y la Sección 24.1.6 para obtener más información.

14.4.9. Algunas notas sobre pg_dump

Los scripts de volcado generados por pg_dump aplican automáticamente varias, pero no todas, las pautas anteriores. Para volver a cargar un volcado de pg_dump lo más rápido posible, debe hacer algunas cosas adicionales manualmente. (Tenga en cuenta que estos puntos se aplican mientras restaurando un basurero, no mientras creando eso. Los mismos puntos se aplican si se carga un volcado de texto con psql o se usa pg_restore para cargar desde un archivo de almacenamiento pg_dump).

Por defecto, pg_dump usa COPY, y cuando está generando un volcado completo de esquemas y datos, se debe tener cuidado de cargar los datos antes de crear índices y keys. Entonces, en este caso, varias pautas se manejan automáticamente. Lo que le queda por hacer es:

• Establezca valores apropiados (es decir, mayores de lo normal) para maintenance_work_mem y max_wal_size.

• Si usa el archivado WAL o la replicación de transmisión, considere deshabilitarlos durante la restauración. Para hacer eso, configure archive_mode para off, wal_level para minimal, y max_wal_senders a cero antes de cargar el vertedero. Luego, vuelva a configurarlos en los valores correctos y realice una copia de seguridad base nueva.

• Experimente con los modos de volcado y restauración en paralelo de pg_dump y pg_restore y encuentre la cantidad óptima de trabajos simultáneos para usar. Vaciado y restauración en paralelo mediante el -j La opción debería brindarle un rendimiento significativamente mayor que el modo en serie.

• Considere si todo el volcado debe restaurarse como una sola transacción. Para hacer eso, pase el -1 o --single-transaction opción de línea de comandos para psql o pg_restore. Al utilizar este modo, incluso el error más pequeño revertirá la restauración completa, descartando posiblemente muchas horas de procesamiento. Dependiendo de cuán interrelacionados estén los datos, eso podría parecer preferible a la limpieza manual, o no. COPY Los comandos se ejecutarán más rápido si usa una sola transacción y tiene desactivado el archivo WAL.

• Si hay varias CPU disponibles en el servidor de la base de datos, considere usar pg_restore’s --jobs opción. Esto permite la carga de datos simultánea y la creación de índices.

• Correr ANALYZE después.

Se seguirá utilizando un volcado de solo datos COPY, pero no suelta ni vuelve a crear índices, y normalmente no toca extraños keys. ^[13] Por lo tanto, al cargar un volcado de solo datos, depende de usted eliminar y volver a crear índices y archivos externos. keys si desea utilizar esas técnicas. Sigue siendo útil aumentar max_wal_size mientras carga los datos, pero no se moleste en aumentar maintenance_work_mem; más bien, lo haría mientras recrea manualmente índices y archivos externos keys después. Y no te olvides de ANALYZE cuando termines; consulte la Sección 24.1.3 y la Sección 24.1.6 para obtener más información.

^[13] Puede obtener el efecto de deshabilitar keys usando el --disable-triggers opción, pero tenga en cuenta que eso elimina, en lugar de solo posponer, las key validación, por lo que es posible insertar datos incorrectos si lo usa.