¿Cómo funciona COPY y por qué es mucho más rápido que INSERT?

Hoy pasé el día mejorando el rendimiento de mi script de Python, que inserta datos en mi base de datos de Postgres. Anteriormente estaba insertando registros como tales:

query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)"; for d in data: cursor.execute(query, d) 

Luego reescribí mi script para que cree un archivo en memoria que se usa para el comando COPY de Postgres, que me permite copiar datos de un archivo a mi tabla:

 f = StringIO(my_tsv_string) cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'\t' ENCODING 'utf-8' QUOTE E'\b' NULL ''", f) 

El método de COPY fue asombrosamente más rápido .

 METHOD | TIME (secs) | # RECORDS ======================================= COPY_FROM | 92.998 | 48339 INSERT | 1011.931 | 48377 

Pero no puedo encontrar ninguna información de por qué? ¿Cómo funciona de manera diferente a un INSERT multilínea de tal manera que lo hace mucho más rápido?

Ver este punto de referencia también:

     # original 0.008857011795043945: query_builder_insert 0.0029380321502685547: copy_from_insert # 10 records 0.00867605209350586: query_builder_insert 0.003248929977416992: copy_from_insert # 10k records 0.041108131408691406: query_builder_insert 0.010066032409667969: copy_from_insert # 1M records 3.464181900024414: query_builder_insert 0.47070908546447754: copy_from_insert # 10M records 38.96936798095703: query_builder_insert 5.955034017562866: copy_from_insert 

    Hay una serie de factores en el trabajo aquí:

    • Latencia de red y retrasos de ida y vuelta
    • Gastos generales por statement en PostgreSQL
    • Conmutadores de contexto y retrasos del progtwigdor
    • Costes de COMMIT , si para las personas que realizan un compromiso por inserto (usted no lo es)
    • COPY optimizaciones específicas para carga masiva

    Latencia de conexion

    Si el servidor es remoto, es posible que esté “pagando” un “precio” de tiempo fijo por statement de, por ejemplo, 50 ms (1/20 de segundo). O mucho más para algunos DBs alojados en la nube. Como la siguiente inserción no puede comenzar hasta que la última se complete con éxito, esto significa que su tasa máxima de inserciones es de 1000 / round-latency-in-ms filas por segundo. A una latencia de 50 ms (“tiempo de ping”), eso es 20 filas / segundo. Incluso en un servidor local, este retraso es distinto de cero. Wheras COPY simplemente rellena las ventanas de envío y recepción de TCP, y transmite filas tan rápido como el DB puede escribirlas y la red puede transferirlas. La latencia no lo afecta mucho y puede estar insertando miles de filas por segundo en el mismo enlace de red.

    Costos por statement en PostgreSQL

    También hay costos para analizar, planificar y ejecutar una statement en PostgreSQL. Debe tomar lockings, abrir archivos de relaciones, buscar índices, etc. COPY intenta hacer todo esto una vez, al inicio, y luego concentrarse en cargar filas lo más rápido posible.

    Costos de cambio de contexto / tarea

    Hay más costos de tiempo pagados debido a que el sistema operativo tiene que cambiar entre postgres en espera de una fila mientras la aplicación lo prepara y lo envía, y luego su aplicación espera la respuesta de postgres mientras postgres procesa la fila. Cada vez que cambias de una a otra, pierdes un poco de tiempo. Es posible que se pierda más tiempo en suspender y reanudar varios estados de kernel de bajo nivel cuando los procesos entran y salen de los estados de espera.

    Perder las optimizaciones de COPY

    Además de todo eso, COPY tiene algunas optimizaciones que puede usar para algunos tipos de cargas. Si no hay una clave generada y los valores predeterminados son constantes, por ejemplo, puede calcularlos previamente y omitir el ejecutor por completo, cargando rápidamente los datos en la tabla en un nivel inferior que omite parte del trabajo normal de PostgreSQL por completo. Si CREATE TABLE o TRUNCATE en la misma transacción que COPY , puede hacer aún más trucos para hacer la carga más rápida al evitar la contabilidad normal de transacciones necesaria en una base de datos de varios clientes.

    A pesar de esto, la COPY de PostgreSQL aún podría hacer mucho más para acelerar las cosas, cosas que aún no sabe cómo hacer. Podría omitir automáticamente las actualizaciones de índices y luego reconstruir los índices si está cambiando más de una cierta proporción de la tabla. Podría hacer actualizaciones de índice en lotes. Mucho más.

    Comprometer costos

    Una última cosa a considerar es comprometer los costos. Probablemente no sea un problema para usted, ya que psycopg2 puede abrir una transacción y no se compromete hasta que se lo psycopg2 . A menos que le digas que use autocommit. Pero para muchos controladores de base de datos, la confirmación automática es la predeterminada. En tales casos, estarías haciendo un commit por cada INSERT . Eso significa un disco al ras, donde el servidor se asegura de que escribe todos los datos en la memoria en el disco y le dice a los discos que escriban sus propios cachés en el almacenamiento persistente. Esto puede llevar mucho tiempo y varía mucho según el hardware. Mi computadora portátil NVMe BTRFS basada en SSD puede hacer solo 200 fsyncs / second, frente a 300,000 escrituras no sincronizadas / segundo. ¡Así que solo cargará 200 filas / segundo! Algunos servidores solo pueden hacer 50 fsyncs / second. Algunos pueden hacer 20.000. Entonces, si tiene que confirmar regularmente, intente cargar y confirmar en lotes, hacer inserciones de varias filas, etc. Debido a que COPY solo realiza una confirmación al final, los costos de confirmación son insignificantes. Pero esto también significa que COPY no puede recuperarse de los errores en la mitad de los datos; Deshace toda la carga a granel.

    Copia utiliza carga masiva, lo que significa que inserta varias filas a la vez, mientras que la inserción simple hace una inserción a la vez, sin embargo, puede insertar varias líneas con la inserción siguiendo la syntax:

     insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn) 

    para obtener más información sobre el uso de carga masiva, consulte, por ejemplo, la forma más rápida de cargar filas de 1 m en postgresql por Daniel Westermann .

    La pregunta de cuántas líneas debe insertar a la vez, depende de la longitud de la línea, una buena regla general es insertar 100 líneas por instrucción de inserción.

    Hacer INSERTOS en una transacción para acelerar.

    Pruebas en bash sin transacción:

     > time ( for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done ) | psql root | uniq -c 100000 INSERT 0 1 real 0m15.257s user 0m2.344s sys 0m2.102s 

    Y con la transacción:

     > time ( echo 'BEGIN;' && for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done && echo 'COMMIT;' ) | psql root | uniq -c 1 BEGIN 100000 INSERT 0 1 1 COMMIT real 0m7.933s user 0m2.549s sys 0m2.118s