Compartir a través de

Procedimientos recomendados para cargar datos de forma masiva en Azure Database for PostgreSQL: servidor flexible

  • Artículo

SE APLICA A: Azure Database for PostgreSQL con servidor flexible

En este artículo se describen varios métodos para cargar datos de forma masiva en Azure Database for PostgreSQL: servidor flexible, junto con procedimientos recomendados para cargas de datos iniciales en bases de datos vacías y cargas de datos incrementales.

Métodos de carga

Los siguientes métodos de carga de datos están ordenados de mayor a menor tiempo:

  • Ejecute un comando INSERT de un solo registro.
  • Realice el procesamiento por lotes de 100 a 1000 filas por confirmación. Puede usar un bloque de transacciones para encapsular varios registros por confirmación.
  • Ejecute INSERT con varios valores de fila.
  • Ejecute el comando COPY.

El método preferido para cargar datos en una base de datos es usar el comando COPY. Si el comando COPY no es posible, el uso de INSERT por lotes es el siguiente mejor método. El subproceso múltiple con un comando COPY es el método óptimo para cargas masivas de datos.

Procedimientos recomendados para cargas de datos iniciales

Quitar índices

Antes de realizar una carga de datos inicial, se recomienda quitar todos los índices de las tablas. Siempre es más eficaz crear los índices después de cargar los datos.

Quitar restricciones

A continuación, se describen las principales restricciones de eliminación:

  • Restricciones de clave únicas

    Para lograr un sólido rendimiento, se recomienda quitar las restricciones de clave única antes de una carga de datos inicial y volver a crearlas una vez completada esta operación. Sin embargo, al quitar restricciones de clave únicas, se cancelan las medidas de seguridad de los datos duplicados.

  • Restricciones de clave externa

    Se recomienda quitar restricciones de clave externa antes de cargar los datos iniciales y volver a crearlas una vez completada la carga de datos.

    Al cambiar el parámetro session_replication_role por replica también se deshabilitan todas las comprobaciones de clave externa. Sin embargo, tenga en cuenta que hacer el cambio puede dejar los datos en un estado incoherente si este procedimiento no se usa correctamente.

Uso de tablas sin registrar

Tenga en cuenta las ventajas y desventajas de usar tablas sin registrar antes de usarlas en cargas de datos iniciales.

El uso de tablas sin registrar hace que la carga de datos sea más rápida. Los datos escritos en tablas sin registrar no se escriben en el registro de escritura previa.

Las desventajas de usar tablas sin registrar son las siguientes:

  • No son seguras para bloqueos. Una tabla sin registrar se trunca automáticamente después de un bloqueo o apagado no limpio.
  • Los datos de tablas sin etiqueta no se pueden replicar en servidores en espera.

Para crear una tabla sin registrar o cambiar una tabla existente por una tabla sin registrar, use las siguientes opciones:

  • Cree una nueva tabla no registrada mediante la siguiente sintaxis:

    CREATE UNLOGGED TABLE <tablename>;

  • Convierta una tabla registrada existente en una tabla no registrada mediante la siguiente sintaxis:

    ALTER TABLE <tablename> SET UNLOGGED;

Ajuste de parámetros de servidor

  • autovacuum: durante la carga inicial de datos, es mejor desactivar autovacuum. Una vez completada la carga inicial, se aconseja ejecutar una operación VACUUM ANALYZE manual en todas las tablas de la base de datos, y luego activar autovacuum.

Nota

Siga las recomendaciones siguientes solo si hay suficiente memoria y espacio en disco.

  • maintenance_work_mem: se puede establecer en un máximo de 2 gigabytes (GB) en una instancia de servidor flexible de Azure Database for PostgreSQL. maintenance_work_mem ayuda a acelerar la creación de autovaciado, índice y clave externa.

  • checkpoint_timeout: en una instancia de servidor flexible de Azure Database for PostgreSQL, el valor checkpoint_timeout se puede aumentar hasta un máximo de 24 horas a partir de la configuración predeterminada de 5 minutos. Se recomienda aumentar el valor a 1 hora antes de cargar los datos inicialmente en la instancia de servidor flexible de Azure Database for PostgreSQL.

  • checkpoint_completion_target: se recomienda un valor de 0,9.

  • max_wal_size: se puede establecer en el valor máximo permitido en una instancia de servidor flexible de Azure Database for PostgreSQL, que es de 64 GB mientras realiza la carga de datos inicial.

  • wal_compression: se puede activar. La habilitación de este parámetro puede tener un costo adicional de CPU destinado a la compresión durante el registro de escritura previa (WAL) y en la descompresión durante la reproducción de WAL.

Recomendaciones del servidor flexible de Azure Database for PostgreSQL

Antes de comenzar una carga de datos inicial en la instancia de servidor flexible de Azure Database for PostgreSQL, se recomienda lo siguiente:

  • Deshabilite la alta disponibilidad en el servidor. Puede habilitarla una vez completada la carga inicial en el servidor principal.
  • Cree réplicas de lectura una vez completada la carga de datos inicial.
  • Reduzca al mínimo el registro o deshabilítelo por completo durante las cargas de datos iniciales (por ejemplo: deshabilite pgaudit, pg_stat_statements, almacén de consultas).

Volver a crear los índices y agregar restricciones

Suponiendo que haya quitado los índices y restricciones antes de la carga inicial, se recomienda usar valores altos en maintenance_work_mem (como se mencionó anteriormente) para crear índices y agregar restricciones. Además, a partir de la versión 11 de PostgresSQL, se pueden modificar los parámetros siguientes para acelerar la creación de índices paralelos después de la carga de datos inicial:

  • max_parallel_workers: establece el número máximo de trabajos que el sistema puede admitir para consultas paralelas.

  • max_parallel_maintenance_workers: controla el número máximo de procesos de trabajo que se pueden usar en CREATE INDEX.

También puede crear los índices realizando la configuración recomendada en el nivel de sesión. Aquí se muestra un ejemplo de cómo se hace:

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

Procedimientos recomendados para cargas de datos incrementales

Tablas de particiones

Siempre se recomienda crear particiones de tablas grandes. Algunas ventajas de la creación de particiones, especialmente durante las cargas incrementales, son las siguientes:

  • La creación de nuevas particiones basadas en las nuevas diferencias hace que sea eficaz agregar nuevos datos a la tabla.
  • Se facilita el mantenimiento de las tablas. Puede quitar una partición durante una carga de datos incremental para evitar eliminaciones que consumen tiempo en tablas grandes.
  • El vaciado automático solo se desencadenaría en particiones que se cambiaron o agregaron durante las cargas incrementales, lo que facilita el mantenimiento de estadísticas en la tabla.

Mantener estadísticas de tabla actualizadas

La supervisión y el mantenimiento de las estadísticas de tabla son importantes para el rendimiento de las consultas en la base de datos. Esto también incluye escenarios en los que tiene cargas incrementales. PostgreSQL usa el proceso de demonio de autovaciado para limpiar las tuplas inactivas y analizar las tablas para mantener actualizadas las estadísticas. Para más información, consulte Supervisión y optimización automáticas.

Creación de índices en restricciones de clave externa

Crear índices en claves externas en las tablas secundarias sería beneficioso en los escenarios siguientes:

  • Actualizaciones o eliminaciones de datos en la tabla primaria. Cuando los datos se actualizan o eliminan en la tabla principal, se realizan búsquedas en la tabla secundaria. Para que las búsquedas sean más rápidas, puede indexar claves externas en la tabla secundaria.
  • Consultas, donde puede ver la combinación de tablas principales y secundarias en columnas de clave.

Identificación de índices sin usar

Identifique los índices sin usar en la base de datos y colóquelos. Los índices son una sobrecarga en las cargas de datos. Cuantos menos índices haya en una tabla, mejor será el rendimiento durante la ingesta de datos.

Puede identificar los índices sin usar de dos maneras: mediante el Almacén de consultas y con una consulta de uso de índices.

Almacén de consultas

El Almacén de consultas ayuda a identificar los índices que se pueden quitar en función de los patrones de uso de consultas en la base de datos. Para obtener instrucciones paso a paso, consulte el Almacén de consultas.

Una vez que el Almacén de consultas está habilitado en el servidor, se puede usar la siguiente consulta para identificar los índices que se pueden quitar mediante la conexión a la base de datos azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Uso de índices

También puede usar la consulta siguiente para identificar los índices sin usar:

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

Las columnas number_of_scans, tuples_read y tuples_fetched indicarían el valor de la columna usage.number_of_scans del índice de cero puntos como un índice que no se usa.

Ajuste de parámetros de servidor

Nota

Siga las recomendaciones de los parámetros siguientes solo si hay suficiente memoria y espacio en disco.

  • maintenance_work_mem: este parámetro se puede establecer en un máximo de 2 GB en la instancia de servidor flexible de Azure Database for PostgreSQL. maintenance_work_mem ayuda a acelerar la creación de índices y adiciones de claves externas.

  • checkpoint_timeout: en la instancia de servidor flexible de Azure Database for PostgreSQL, el valor checkpoint_timeout se puede aumentar a 10 o 15 minutos desde la configuración predeterminada de 5 minutos. Aumentar checkpoint_timeout a un valor mayor, como 15 minutos, puede reducir la carga de E/S, pero la desventaja es que se tarda más tiempo en recuperarse si hay un bloqueo. Se recomienda tenerlo en cuenta detenidamente antes de realizar el cambio.

  • checkpoint_completion_target: se recomienda un valor de 0,9.

  • max_wal_size: este valor depende de la SKU, el almacenamiento y la carga de trabajo. A continuación, se muestra una manera de llegar al valor correcto de max_wal_size.

    Durante las horas punta del horario comercial, llegue a un valor haciendo lo siguiente:

    a. Ejecute la consulta siguiente para tomar el número de secuencia de registro de WAL (LSN) actual:

    SELECT pg_current_wal_lsn ();

    b. Espere el número de segundos que indica checkpoint_timeout. Para tomar el LSN de WAL actual, ejecute la consulta siguiente:

    SELECT pg_current_wal_lsn ();

    c. Use los dos resultados para comprobar la diferencia en GB:

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: se puede activar. La habilitación de este parámetro puede conllevar un costo adicional de CPU dedicada a la compresión durante el registro WAL y a la descompresión durante la reproducción de WAL.

Pasos siguientes