Al utilizar múltiples sentencias INSERT, desactiva el autocommit y realiza un solo commit al final. (En SQL simple, esto significa emitir BEGIN al principio y COMMIT al final. Algunas bibliotecas de clientes podrían hacer esto por ti, en cuyo caso debes asegurarse de que la biblioteca lo haga cuando tú quieras). Si permites que cada inserción se comprometa por separado, PostgreSQL realiza mucho trabajo por cada fila añadida. Un beneficio adicional de realizar todas las inserciones en una sola transacción es que si la inserción de una fila fallara, la inserción de todas las filas insertadas hasta ese punto se revertiría, por lo que no te quedarás con datos parcialmente cargados.

Utiliza COPY para cargar todas las filas en un solo comando, en lugar de utilizar una serie de comandos INSERT. El comando COPY está optimizado para cargar grandes cantidades de filas; es menos flexible que INSERT, pero incurre en una sobrecarga significativamente menor para grandes cargas de datos. Dado que COPY es un solo comando, no es necesario desactivar el autocommit si utilizas este método para poblar una tabla.

Si no puedes utilizar COPY, podría ayudar utilizar PREPARE para crear una sentencia INSERT preparada y luego utilizar EXECUTE tantas veces como sea necesario. Esto evita parte de la sobrecarga de analizar y planificar repetidamente el INSERT. Diferentes interfaces proporcionan esta funcionalidad de diversas formas; busca “sentencias preparadas” (prepared statements) en la documentación de la interfaz.

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

El comando COPY es más rápido cuando se utiliza dentro de la misma transacción que un comando CREATE TABLE or TRUNCATE anterior. En tales casos, no es necesario escribir en el 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 está en minimal, ya que todos los comandos deben escribir en el WAL de lo contrario.

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

Si estás añadiendo grandes cantidades de datos a una tabla existente, podría ser ventajoso eliminar los índices, cargar la tabla y luego recrear los índices. Por supuesto, el rendimiento de la base de datos para otros usuarios podría verse afectado durante el tiempo que falten los índices. También se debe pensar dos veces antes de eliminar un índice único, ya que la comprobación de errores que proporciona la restricción de unicidad se perderá mientras falte el índice.

Al igual que con los índices, una restricción de clave foránea se puede comprobar “en lote” de manera más eficiente que fila por fila. Por lo tanto, podría ser útil eliminar las restricciones de clave foránea, cargar los datos y volver a crear las restricciones. Nuevamente, existe un equilibrio entre la velocidad de carga de datos y la pérdida de comprobación de errores mientras falta la restricción.

Además, cuando cargas datos en una tabla con restricciones de clave foránea existentes, cada nueva fila requiere una entrada en la lista del servidor de eventos de disparadores pendientes (ya que es la ejecución de un disparador lo que comprueba la restricción de clave foránea de la fila). Cargar muchos millones de filas puede hacer que la cola de eventos de disparadores desborde la memoria disponible, provocando un intercambio en disco (swapping) intolerable o incluso el fallo absoluto del comando. Por lo tanto, puede ser necesario, y no solo deseable, eliminar y volver a aplicar las claves foráneas al cargar grandes cantidades de datos. Si no es aceptable eliminar temporalmente la restricción, el único recurso restante puede ser dividir la operación de carga en transacciones más pequeñas.

Aumentar temporalmente la variable de configuración maintenance_work_mem al cargar grandes cantidades de datos puede mejorar el rendimiento. Esto ayudará a acelerar los comandos CREATE INDEX y ALTER TABLE ADD FOREIGN KEY. No influirá mucho en el propio COPY, por lo que este consejo solo es útil si utilizas una o ambas de las técnicas anteriores.

Aumentar temporalmente la variable de configuración max_wal_size también puede hacer que las cargas de datos grandes sean más rápidas. Esto se debe a que la carga de una gran cantidad de datos en PostgreSQL provocará que los puntos de control (checkpoints) ocurran con más frecuencia que la habitual (especificada por la variable de configuración checkpoint_timeout). Cada vez que ocurre un punto de control, todas las páginas modificadas (dirty pages) deben volcarse al disco. Al aumentar temporalmente max_wal_size durante las cargas masivas de datos, se puede reducir el número de puntos de control requeridos.

Al cargar grandes cantidades de datos en una instalación que utiliza el archivado del WAL o la replicación por transmisión, podría ser más rápido realizar una nueva copia de seguridad base (base backup) una vez completada la carga que procesar una gran cantidad de datos del WAL incrementales. Para evitar el registro incremental del WAL durante la carga, desactiva el archivado y la replicación por transmisión configurando wal_level en minimal, archive_mode en off y max_wal_senders en cero. Pero ten en cuenta que cambiar estos ajustes requiere reiniciar el servidor y hace que las copias de seguridad base anteriores no estén disponibles para la recuperación de archivos y el servidor en espera, lo que podría provocar la pérdida de datos.

Además de evitar el tiempo que tardan el archivador o el emisor del WAL en procesar los datos del WAL, hacer esto acelerará ciertos comandos, ya que no escriben en el WAL en absoluto si wal_level es minimal y la subtransacción actual (o la transacción de nivel superior) creó o vació la tabla o el índice que modifican. (Pueden garantizar la seguridad contra fallos de forma más barata realizando un fsync al final que escribiendo en el WAL).

Siempre que hayas 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. Ejecutar ANALYZE (o VACUUM ANALYZE) garantiza que el planificador disponga de estadísticas actualizadas sobre la tabla. Sin estadísticas o con estadísticas obsoletas, el planificador podría tomar decisiones deficientes durante la planificación de consultas, lo que llevaría a un rendimiento deficiente en cualquier tabla con estadísticas inexactas o inexistentes. Ten en cuenta que si el demonio de autovacuum está habilitado, podría ejecutar ANALYZE automáticamente; consulta la Section 24.1.3 y la Section 24.1.6 para más información.

Los scripts de volcado generados por pg_dump aplican automáticamente varias de las pautas anteriores, pero no todas. Para restaurar un volcado de pg_dump lo más rápido posible, debes realizar algunas acciones adicionales manualmente. (Ten en cuenta que estos puntos se aplican al restaurar un volcado, no al crearlo. Los mismos puntos se aplican tanto si cargas un volcado de texto con psql como si utilizas pg_restore para cargarlo desde un archivo de archivo de pg_dump).

Por defecto, pg_dump utiliza COPY, y cuando genera un volcado completo de esquema y datos, tiene cuidado de cargar los datos antes de crear los índices y las claves foráneas. Así que en este caso varias pautas se gestionan automáticamente. Lo que te queda por hacer es:

Un volcado de solo datos seguirá utilizando COPY, pero no elimina ni recrea índices, y no suele tocar las claves foráneas. ^[14] Por lo tanto, al cargar un volcado de solo datos, depende de ti eliminar y recrear los índices y las claves foráneas si deseas utilizar esas técnicas. Sigue siendo útil aumentar max_wal_size al cargar los datos, pero no te molestes en aumentar maintenance_work_mem; más bien, lo harías al recrear manualmente los índices y las claves foráneas después. Y no olvides ejecutar ANALYZE cuando hayas terminado; consulta la Section 24.1.3 y la Section 24.1.6 para obtener más información.